The Problem
One of the initiatives I’ve been working toward with my team this year is automating as much of our SQL Server patching as possible. Over time our environment has become increasingly complex, and while Microsoft provides some rich GUI utilities for managing Always On Availability Groups, it becomes difficult to use them efficiently at scale under tight downtime windows. When you have lots of AGs across lots of servers that adds up to lots of clicking “Next” over the course of several hours.
The Solution
In order to speed up the patching process, reduce human error, and create an easily repeatable process for more junior members to follow, I created a script utilizing the dbatools module. (Side note, I’m absolutely in love with dbatools.) The script utilizes a couple of different parameters to:
- Fail all AGs over to the primary nodes (designated as A).
- Fail all AGs over to the secondary nodes (designated as B).
- After all maintenance is complete, fail all AGs over to their preferred “home” nodes.
- Carry these operations out on either Production or Pre-Production environments.
Examples
The actual script we used is heavily tailored to our environment and naming scheme, but I’ve posted a modified version of it here in hopes that the code and principles can be of help to others as well. Here are a couple of examples of it in action.
- Execute against the pre-production (PPRD) environment and fail all AGs that are on the secondary (B) nodes over to the primary ones (A).
.\SQLAGFailovers.ps1 -Environment PPRD -Destination A- Execute against the production environment and fail all AGs over to their preferred nodes.
.\SQLAGFailovers.ps1 -Environment PROD -Destination HomeCaveats
The example script below is meant to be just that, an example. So all standard disclaimers apply. Here are some notable parts you would have to tailor to your own environment.
- The list of AGs ($aglist) for both PPRD and PROD are environment-specific. You would have to replace them with your own in the format of “AGNAME,PORT”. (I experimented with having it pull this dynamically using Failover Cluster resources, but so far haven’t found a good way to do this and match them to our custom ports.)
- There are two different naming schemes assumed in this example. The first, in PPRD, is matching exact node names. This is probably the most universally applicable method across environments.
- The second, in PROD, is looking for node names that end in ‘A’ or ‘B’. This is much more specific and might not apply broadly, but it’s a good example of how you can utilize more specific pattern matching.
- The “home” section of both PPRD and PROD would need to be tailored to your setup. The goal here is to put them back in their day-to-day homes, so you would first check to see if they’re already on the desired node and, if not, initiate a failover.
The Code
Below is the example code in full. If you have questions about any part of it, suggestions for how to improve it, or anything else please feel free to leave a comment below! I had a lot of fun putting this together, and I hope it’s a help to others out there.
(You can also find the full script at my GitHub page.)
###########################################################################################################
# Script Purpose: Automates failover of PPRD and PROD AGs during maintenance
# Author: Landon Fowler
###########################################################################################################
###############
# Paramaeters #
###############
Param(
[Parameter(Mandatory=$True)]
[string]$Environment,
[Parameter(Mandatory=$True)]
[string]$Destination
)
############
# Modules #
############
Import-Module dbatools
#############
# Funcitons #
#############
################################################################################################################################
function PPRDMove
{
# Build list of AGs
$aglist = @(
"AG1PPRD,50000"
"AG2PPRD,51000"
"AG3PPRD,52000"
)
# Iterate through the list of AGs
foreach ($ag in $agList)
{
# Split out AG name
$agName = ($ag.Split(","))[0]
# Determine the replica information
$replicas = Get-DbaAgReplica -SqlInstance $ag
# From the replica info, get just the server name of the primary.
$primary = ($replicas | where {$_.Role -eq "Primary"} | select -ExpandProperty name).Split("\")[0]
# From the replica info, get the instance name (server\instance).
$secondary = $replicas | where {$_.Role -eq "Secondary"} | select -ExpandProperty name
# Actions to take if AGs are being failed over to the A nodes.
if ($Destination.ToUpper() -eq "A")
{
# If the primary replica is one of the B nodes, fail over to A. Otherwise do nothing.
if (($primary -eq "SERVER2PPRD") -or ($primary -eq "SERVER4PPRD"))
{
$message = "Moving " + $agName + " to " + $secondary
Write-Host $message -ForegroundColor Green
Invoke-DbaAgFailover -SqlInstance $secondary -AvailabilityGroup $agName -EnableException -Confirm:$false
}
else
{
$message = $agName + " is already on the A node."
Write-Host $message -ForegroundColor Green
}
}
# Actions to take if AGs are being failed over to the B nodes.
elseif ($Destination.ToUpper() -eq "B")
{
# If the primary replica is one of the A nodes, fail over to B. Otherwise do nothing.
if (($primary -eq "SERVER1PPRD") -or ($primary -eq "SERVER3PPRD"))
{
$message = "Moving " + $agName + " to " + $secondary
Write-Host $message -ForegroundColor Green
Invoke-DbaAgFailover -SqlInstance $secondary -AvailabilityGroup $agName -EnableException -Confirm:$false
}
else
{
$message = $agName + " is already on the B node."
Write-Host $message -ForegroundColor Green
}
}
# Actions to take if returning to Home nodes.
elseif ($Destination.ToUpper() -eq "HOME")
{
# AG1PPRD
if ($agName -eq "AG1PPRD")
{
if ($primary -eq "SERVER2PPRD")
{
$message = "Moving " + $agName + " to " + $secondary
Write-Host $message -ForegroundColor Green
Invoke-DbaAgFailover -SqlInstance $secondary -AvailabilityGroup $agName -EnableException -Confirm:$false
}
}
# AG2PPRD
elseif ($agName -eq "AG2PPRD")
{
if ($primary -eq "SERVER3PPRD")
{
$message = "Moving " + $agName + " to " + $secondary
Write-Host $message -ForegroundColor Green
Invoke-DbaAgFailover -SqlInstance $secondary -AvailabilityGroup $agName -EnableException -Confirm:$false
}
}
# AG3PPRD
elseif ($agName -eq "AG3PPRD")
{
if ($primary -eq "SERVER1PPRD")
{
$message = "Moving " + $agName + " to " + $secondary
Write-Host $message -ForegroundColor Green
Invoke-DbaAgFailover -SqlInstance $secondary -AvailabilityGroup $agName -EnableException -Confirm:$false
}
}
# Give a message if none of the AG names in the list match.
else
{
Write-Host "AG name not found." -ForegroundColor Yellow
}
}
# Give a message if something other than A or B was specified for the destination.
else
{
Write-Host 'Invalid destination specified. Please use "A" or "B".' -ForegroundColor Yellow
}
}
}
################################################################################################################################
function PRODMove
{
# Build list of AGs
$aglist = @(
"AG1PROD,50000"
"AG2PROD,51000"
"AG3PROD,52000"
)
# Iterate through the list of AGs
foreach ($ag in $agList)
{
# Split out AG name
$agName = ($ag.Split(","))[0]
# Determine the replica information
$replicas = Get-DbaAgReplica -SqlInstance $ag
# From the replica info, get just the server name of the primary.
$primary = ($replicas | where {$_.Role -eq "Primary"} | select -ExpandProperty name).Split("\")[0]
# From the replica info, get the instance name (server\instance).
$secondary = $replicas | where {$_.Role -eq "Secondary"} | select -ExpandProperty name
# Actions to take if AGs are being failed over to the A nodes.
if ($Destination.ToUpper() -eq "A")
{
# If the primary replica is one of the B nodes, fail over to A. Otherwise do nothing.
if ($primary.substring($primary.length -1) -eq "B")
{
$message = "Moving " + $agName + " to " + $secondary
Write-Host $message -ForegroundColor Green
Invoke-DbaAgFailover -SqlInstance $secondary -AvailabilityGroup $agName -EnableException -Confirm:$false
}
else
{
$message = $agName + " is already on the A node."
Write-Host $message -ForegroundColor Green
}
}
# Actions to take if AGs are being failed over to the B nodes.
elseif ($Destination.ToUpper() -eq "B")
{
# If the primary replica is one of the A nodes, fail over to B. Otherwise do nothing.
if ($primary.substring($primary.length -1) -eq "A")
{
$message = "Moving " + $agName + " to " + $secondary
Write-Host $message -ForegroundColor Green
Invoke-DbaAgFailover -SqlInstance $secondary -AvailabilityGroup $agName -EnableException -Confirm:$false
}
else
{
$message = $agName + " is already on the B node."
Write-Host $message -ForegroundColor Green
}
}
# Actions to take if returning to Home nodes.
elseif ($Destination.ToUpper() -eq "HOME")
{
# AG1PROD
if ($agName -eq "AG1PROD")
{
if ($primary -eq "AG2PROD")
{
$message = "Moving " + $agName + " to " + $secondary
Write-Host $message -ForegroundColor Green
Invoke-DbaAgFailover -SqlInstance $secondary -AvailabilityGroup $agName -EnableException -Confirm:$false
}
}
# AG2PROD
elseif ($agName -eq "AG2PROD")
{
if ($primary -eq "AG1PROD")
{
$message = "Moving " + $agName + " to " + $secondary
Write-Host $message -ForegroundColor Green
Invoke-DbaAgFailover -SqlInstance $secondary -AvailabilityGroup $agName -EnableException -Confirm:$false
}
}
# AG3PROD
elseif ($agName -eq "AG3PROD")
{
if ($primary -eq "AG2PROD")
{
$message = "Moving " + $agName + " to " + $secondary
Write-Host $message -ForegroundColor Green
Invoke-DbaAgFailover -SqlInstance $secondary -AvailabilityGroup $agName -EnableException -Confirm:$false
}
}
# Give a message if none of the AG names in the list match.
else
{
Write-Host "AG name not found." -ForegroundColor Yellow
}
}
# Give a message if something other than A or B was specified for the destination.
else
{
Write-Host 'Invalid destination specified. Please use "A" or "B".' -ForegroundColor Yellow
}
}
}
################################################################################################################################
########
# Body #
########
# If working with PPRD, call its function.
if ($Environment -eq "PPRD")
{
PPRDMove
}
# If working with PROD, call its function.
elseif ($Environment -eq "PROD")
{
# Give an extra warning when working in PROD and prompt for whether to continue.
Write-Host "WARNING: You are about to take action on PRODUCTION systems. Are you sure you want to continue? (Y or N)" -ForegroundColor DarkYellow
$prompt = Read-Host
if ($prompt -eq "Y")
{
PRODMove
}
else
{
Write-Host "Carefully edging back to safety..." -ForegroundColor Green
}
}
# Exit if no environment is specified
else
{
Write-Host 'Invalid environment specified. Please use "PPRD" or "PROD".' -ForegroundColor Yellow
}
Leave a Reply