Features of the script<\/h3>\n\n- Fails over a single Availability Group (AG) with zero data loss.<\/li>\n<\/ul>\n
\n- Does not require the current or target configurations to use synchronous commit in order to achieve zero data loss.<\/li>\n<\/ul>\n
\n- Accepts JSON files or strings as input for the target AG state. This is useful for saving configurations for fail-back and for performing scripted tests regularly.<\/li>\n<\/ul>\n
\n- It is aware of the AG state, enabling it to move from configuration A to B without stumbling on limitations such as maximum number of synchronous commit replicas.<\/li>\n<\/ul>\n
\n- Before and after print outs of the AG state for logging and troubleshooting purposes.<\/li>\n<\/ul>\n
\n- Available on GitHub here<\/a>.<\/li>\n<\/ul>\n
Prerequisites<\/h3>\n\n- SQLPS module\u00a0\u2013 this is installed with Microsoft SQL Server 2012 and above, by default. If you don\u2019t have SQL Server installed you can also install the, smaller footprint,\u00a0feature pack here<\/a>.<\/li>\n<\/ul>\n
\n- PowerShell v3.0 \u2013 to be honest, this might work in PowerShell v2.0 but I have only tested it in v3.0. v3.0 is packaged with\u00a0Windows Server 2012 and Windows 8 or above but, if you aren\u2019t using one of those operating systems, you can install it yourself by following these instructions<\/a>.<\/li>\n<\/ul>\n
The script<\/h3>\n[CmdletBinding(SupportsShouldProcess=$true)]\nParam\n(\n [Parameter(Mandatory=$false,\n\t\t\t ValueFromPipeline=$true,\n\t\t\t ValueFromPipelineByPropertyName=$true,\n\t\t\t HelpMessage=\"Path to a file with a json string representing the desired fail-over configuration.\")]\n\t[string]$jsonFilePath,\n [Parameter(Mandatory=$false,\n\t\t\t ValueFromPipeline=$true,\n\t\t\t ValueFromPipelineByPropertyName=$true,\n\t\t\t HelpMessage=\"A json string representing the desired fail-over configuration.\")]\n\t[string]$jsonString\n)\n\nfunction Get-AGState ()\n{\n return Invoke-Sqlcmd -Query \"SELECT replica_server_name\n , AG.name AS [AG_Name], HAGS.primary_replica\n , availability_mode_desc, failover_mode_desc\n FROM sys.availability_replicas AR\n INNER JOIN sys.dm_hadr_availability_group_states HAGS\n INNER JOIN sys.availability_groups AG ON AG.group_id = HAGS.group_id\n ON HAGS.group_id = AR.group_id;\" -ServerInstance \"$selectedNode\";\n}<\/p>\n\n
function Get-DatabaseSyncStates([string]$AG, [string]$Server)\n{\n return Invoke-Sqlcmd -Query \"select DB_NAME(database_id) [Database]\n , ST.synchronization_state_desc [SyncState]\n FROM sys.dm_hadr_database_replica_states ST\n INNER JOIN sys.availability_groups AG ON AG.group_id = ST.group_id\n INNER JOIN sys.availability_replicas AR ON AR.replica_id = ST.replica_id\n WHERE AG.name = '$AG<\/code>'\n\t AND AR.replica_server_name = '$Server<\/code>';\" -ServerInstance \"$Server\";\n}<\/p>\n\nfunction ValidateTargetReplicaConfiguration()\n{\n [string[]]$primaries = $targetConfig.Replicas | Where-Object {$_.isPrimary -like \"true\"}\n if($primaries.Count -ne 1)\n {\n throw \"There must be one, and only one, primary in the configuration.\";\n }<\/p>\n\n
[string[]]$syncReplicas = $targetConfig.Replicas | Where-Object {$_.AvailabilityMode -like \"SynchronousCommit\"}\n if($syncReplicas.Count -gt 3)\n {\n throw \"The requested configuration exceeds the maximum of three synchronous replicas which can be in an Availability Group configuration.\";\n }<\/p>\n\n
if(($targetConfig.Replicas.Count -gt 5) -and ($majorSQLVersion -lt 12))\n {\n throw \"The requested configuration exceeds the maximum of five replicas which can be in an Availability Group configuration for a SQL Server version below 12.\";\n }\n \n if(($targetConfig.Replicas.Count -gt 9) -and ($majorSQLVersion -ge 12))\n {\n throw \"The requested configuration exceeds the maximum of nine replicas which can be in an Availability Group configuration for a SQL Server version of 12 or greater.\";\n }\n}<\/p>\n\n
#Error handling\n$ErrorActionPreference = \"Stop\";\nTrap \n{\n $err = $_.Exception\n while ( $err.InnerException )\n {\n $err = $err.InnerException\n write-output $err.Message\n };\n}<\/p>\n\n
$totalScriptSteps = 13;\n$currentStep = 0;<\/p>\n\n
#Load dependencies\n$currentStep++;\nWrite-Progress -Activity \"Loading dependencies.\" -Status \"Importing SQLPS module.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\nImport-Module SQLPS -DisableNameChecking;<\/p>\n\n#Validate input \/ load jsonString\n$currentStep++;\nWrite-Progress -Activity \"Validating input.\" -Status \"Checking parameter requirements.\" -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);<\/p>\n\n
if ((!$jsonString) -and ($jsonFilePath))\n{\n if(!(Test-Path $jsonFilePath))\n {\n throw \"Invalid file path. ($jsonFilePath)\";\n }\n $jsonString = Get-Content $jsonFilePath\n}\nelseif (!$jsonString)\n{\n throw \"You must input a JSON file path or a JSON string.\";\n}<\/p>\n\n
#Load JSON\n$currentStep++;\nWrite-Progress -Activity \"Validating input.\" -Status \"Loading JSON.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\n$targetConfig = $jsonString | ConvertFrom-Json;\n$currentStep++;\nWrite-Progress -Activity \"Validating input.\" -Status \"Validating target replica configuration.\" -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);\nValidateTargetReplicaConfiguration;<\/p>\n\n#Build server object\n$currentStep++;\nWrite-Progress -Activity \"Validating input.\" -Status \"Testing server connection.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\n$selectedNode = ($targetConfig.Replicas | Where-Object {$_.isPrimary -like \"true\"})[0].Name;\n$newPrimaryReplicaName = $selectedNode;\nif($newPrimaryReplicaName -notcontains \"\\\")\n{\n $newPrimaryReplicaName += \"\\DEFAULT\";\n}<\/p>\n\nTest connection<\/h1>\nWe check the version number as a light weight method of checking the connection.<\/h1>\nError handling will abort script if this command fails.<\/h1>\nInvoke-Sqlcmd -ServerInstance $selectedNode -Query \"SELECT @@Version;\" | Out-Null;\n\n
$currentStep++;\nWrite-Progress -Activity \"Validating input.\" -Status \"Retrieving current Availability Group state.\" -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);\n$AGState = Get-AGState;\nWrite-Host -Object \"Current Availability Group state:\" -ForegroundColor Green -BackgroundColor Black;\n$AGState | Format-Table;\nWrite-Host -Object \"---------------------------------\" -ForegroundColor Green -BackgroundColor Black;<\/p>\n\n
if($AGState -eq $null)\n{\n throw \"Failed to retrieve Availability Group state from $newPrimaryReplicaName\";\n}<\/p>\n\n
[string]$currentPrimaryReplica = $AGState[0].primary_replica;\nif($currentPrimaryReplica -notcontains \"\\\")\n{\n $currentPrimaryReplica += \"\\DEFAULT\";\n}<\/p>\n\n
if($currentPrimaryReplica -eq \"$newPrimaryReplicaName\")\n{\n throw \"$newPrimaryReplicaName is already the primary replica.\";\n}<\/p>\n\n
Failover prep<\/h1>\nYou cannot have more than 3 synchronous commit replicas at one time.<\/h1>\nBut we want to maintain synchronous commit replicas during the fail-over, if possible.<\/h1>\nSo, we set just enough to async so that we can set our fail-over replica to synchronous.<\/h1>\n$currentStep++;\nWrite-Progress -Activity \"Failover preparation.\" -Status \"Pre-configuring async replicas to avoid hitting synchronous commit limit during fail-over.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\n[string]$AGname = $AGState[0].AG_Name;\n[string[]]$syncReplicas = $AGState | Where-Object {$_.availability_mode_desc -like \"SYNCHRONOUS_COMMIT\"};\n$count = 0;\nwhile($syncReplicas.Count -ge 3)\n{\n [string]$asyncReplicaName = ($targetConfig.Replicas | Where-Object {$_.Name -notlike ($currentPrimaryReplica.Replace('\\DEFAULT',''))} \n | Sort-Object AvailabilityMode, Name)[$count].Name;\n Set-SqlAvailabilityReplica -AvailabilityMode \"AsynchronousCommit\" -FailoverMode \"Manual\" <\/code>\n -Path \"SQLSERVER:\\Sql\\$currentPrimaryReplica\\AvailabilityGroups\\$AGname\\AvailabilityReplicas\\$asyncReplicaName\";\n\n$count++;\n $AGState = Get-AGState;\n [string[]]$syncReplicas = $AGState | Where-Object {$_.availability_mode_desc -like \"SYNCHRONOUS_COMMIT\"};\n}<\/p>\n\n
$currentStep++;\nWrite-Progress -Activity \"Failover preparation.\" -Status \"Setting new primary target replica to synchronous commit to achieve zero data loss during fail-over.\" -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);\n$tempName = $newPrimaryReplicaName.Replace('\\DEFAULT','');\nSet-SqlAvailabilityReplica -AvailabilityMode \"SynchronousCommit\" -FailoverMode \"Manual\" \n -Path \"SQLSERVER:\\Sql\\$currentPrimaryReplica\\AvailabilityGroups\\$AGname\\AvailabilityReplicas\\$tempName\";<\/p>\n\n#wait for new primary to sync up\n$currentStep++;\nWrite-Progress -Activity \"Failover preparation.\" -Status \"Verifying that data synchronization is complete.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\n[System.Data.DataRow[]]$synchronizingDatabases = (Get-DatabaseSyncStates -AG $AGname -Server $tempName) | Where-Object {$_.SyncState -like \"SYNCHRONIZING\"};\n$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()\n$currentStep++;\nwhile($synchronizingDatabases.Count -gt 0)\n{\n $currentTime = $stopWatch.Elapsed;\n $elapsedTime = [string]::Format(\"Elapsed time: {0:d2}:{1:d2}:{2:d2}\", $CurrentTime.hours, $CurrentTime.minutes, $CurrentTime.seconds)\n Write-Progress -Activity \"Waiting for databases to synchronize.\" -Status $elapsedTime -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);\n Start-Sleep -s 2;\n $synchronizingDatabases = (Get-DatabaseSyncStates -AG $AGname -Server $tempName) | Where-Object {$_.SyncState -like \"SYNCHRONIZING\"};\n} \n$stopWatch.Stop();<\/p>\n\nFailover<\/h1>\n$to = $newPrimaryReplicaName.Replace('\\DEFAULT','');\n$from = $currentPrimaryReplica.Replace('\\DEFAULT','');\n$currentStep++;\nWrite-Progress -Activity \"Failover.\" -Status \"Failing over, without data loss, from $from to $to.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\nSwitch-SqlAvailabilityGroup -Path \"SQLSERVER:\\Sql\\$newPrimaryReplicaName\\AvailabilityGroups\\$AGname\";\n\n#Final Configuration<\/p>\n\n
Set availability and failover modes<\/h1>\nSort ASC by AvailabilityMode so that Async is before Sync.<\/h1>\nThis prevents us from adding a 4th sync replica before we establish the asyncs<\/h1>\n$currentStep++;\nWrite-Progress -Activity \"Final configuration.\" -Status \"Set all replicas availability mode and failover mode to match target configuration.\" -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);\nforeach($replica in ($targetConfig.Replicas | Sort-Object AvailabilityMode))\n{\n [string]$newAvailabilityMode = $replica.AvailabilityMode;\n [string]$newFailoverMode = $replica.FailoverMode;\n [string]$replicaName = $replica.Name;\n Set-SqlAvailabilityReplica -AvailabilityMode $newAvailabilityMode -FailoverMode $newFailoverMode <\/code>\n -Path \"SQLSERVER:\\Sql\\$newPrimaryReplicaName\\AvailabilityGroups\\$AGname\\AvailabilityReplicas\\$replicaName\";\n}\n\n
$currentStep++;\nWrite-Progress -Activity \"State review.\" -Status \"Getting Availability Group state for review.\" -PercentComplete ($currentStep\/$totalScriptSteps*100);\nWrite-Host -Object \"Final Availability Group state:\" -ForegroundColor Green -BackgroundColor Black;\nGet-AGState | Format-Table;\nWrite-Host -Object \"---------------------------------\" -ForegroundColor Green -BackgroundColor Black;<\/code><\/pre>\n<\/p>\n
Example JSON object<\/h3>\n{\n\t\"Replicas\" : \n\t[\n\t\t{\n\t\t\t\"Name\" : \"V-HAMMER-01\",\n\t\t\t\"AvailabilityMode\" : \"AsynchronousCommit\",\n\t\t\t\"FailoverMode\" : \"Manual\",\n\t\t\t\"isPrimary\" : \"false\"\n\t\t},\n\t\t{\n\t\t\t\"Name\" : \"V-HAMMER-02\",\n\t\t\t\"AvailabilityMode\" : \"AsynchronousCommit\",\n\t\t\t\"FailoverMode\" : \"Manual\",\n\t\t\t\"isPrimary\" : \"false\"\n\t\t},\n\t\t{\n\t\t\t\"Name\" : \"V-HAMMER-03\",\n\t\t\t\"AvailabilityMode\" : \"SynchronousCommit\",\n\t\t\t\"FailoverMode\" : \"Automatic\",\n\t\t\t\"isPrimary\" : \"true\"\n\t\t},\n\t\t{\n\t\t\t\"Name\" : \"V-HAMMER-04\",\n\t\t\t\"AvailabilityMode\" : \"SynchronousCommit\",\n\t\t\t\"FailoverMode\" : \"Automatic\",\n\t\t\t\"isPrimary\" : \"false\"\n\t\t}\n\t]\n}<\/code><\/pre>\n
\n![\"\"](\"\/wp-content\/uploads\/hammer-derik-172x140.png\")
Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\nDerik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\nDerik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\nDerik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\nFor more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"Merry Christmas Eve! A few weeks ago I posted on Availability Group fail-over tests with PowerShell. That post covered the basics of how to check the Availability Group\u2019s state and perform the commands for conducting a zero data loss fail over. As a Christmas Present to all of you,\u00a0I wanted to get a little bit […]<\/p>\n","protected":false},"author":1,"featured_media":530,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-96","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/96","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=96"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/96\/revisions"}],"predecessor-version":[{"id":359,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/96\/revisions\/359"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/530"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=96"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=96"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=96"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- \n
- Does not require the current or target configurations to use synchronous commit in order to achieve zero data loss.<\/li>\n<\/ul>\n
- \n
- Accepts JSON files or strings as input for the target AG state. This is useful for saving configurations for fail-back and for performing scripted tests regularly.<\/li>\n<\/ul>\n
- \n
- It is aware of the AG state, enabling it to move from configuration A to B without stumbling on limitations such as maximum number of synchronous commit replicas.<\/li>\n<\/ul>\n
- \n
- Before and after print outs of the AG state for logging and troubleshooting purposes.<\/li>\n<\/ul>\n
- \n
- Available on GitHub here<\/a>.<\/li>\n<\/ul>\n
Prerequisites<\/h3>\n
- \n
- SQLPS module\u00a0\u2013 this is installed with Microsoft SQL Server 2012 and above, by default. If you don\u2019t have SQL Server installed you can also install the, smaller footprint,\u00a0feature pack here<\/a>.<\/li>\n<\/ul>\n
- \n
- PowerShell v3.0 \u2013 to be honest, this might work in PowerShell v2.0 but I have only tested it in v3.0. v3.0 is packaged with\u00a0Windows Server 2012 and Windows 8 or above but, if you aren\u2019t using one of those operating systems, you can install it yourself by following these instructions<\/a>.<\/li>\n<\/ul>\n
The script<\/h3>\n
[CmdletBinding(SupportsShouldProcess=$true)]\nParam\n(\n [Parameter(Mandatory=$false,\n\t\t\t ValueFromPipeline=$true,\n\t\t\t ValueFromPipelineByPropertyName=$true,\n\t\t\t HelpMessage=\"Path to a file with a json string representing the desired fail-over configuration.\")]\n\t[string]$jsonFilePath,\n [Parameter(Mandatory=$false,\n\t\t\t ValueFromPipeline=$true,\n\t\t\t ValueFromPipelineByPropertyName=$true,\n\t\t\t HelpMessage=\"A json string representing the desired fail-over configuration.\")]\n\t[string]$jsonString\n)\n\nfunction Get-AGState ()\n{\n return Invoke-Sqlcmd -Query \"SELECT replica_server_name\n , AG.name AS [AG_Name], HAGS.primary_replica\n , availability_mode_desc, failover_mode_desc\n FROM sys.availability_replicas AR\n INNER JOIN sys.dm_hadr_availability_group_states HAGS\n INNER JOIN sys.availability_groups AG ON AG.group_id = HAGS.group_id\n ON HAGS.group_id = AR.group_id;\" -ServerInstance \"$selectedNode\";\n}<\/p>\n\n
function Get-DatabaseSyncStates([string]$AG, [string]$Server)\n{\n return Invoke-Sqlcmd -Query \"select DB_NAME(database_id) [Database]\n , ST.synchronization_state_desc [SyncState]\n FROM sys.dm_hadr_database_replica_states ST\n INNER JOIN sys.availability_groups AG ON AG.group_id = ST.group_id\n INNER JOIN sys.availability_replicas AR ON AR.replica_id = ST.replica_id\n WHERE AG.name =
'$AG<\/code>'\n\t AND AR.replica_server_name ='$Server<\/code>';\" -ServerInstance \"$Server\";\n}<\/p>\n\nfunction ValidateTargetReplicaConfiguration()\n{\n [string[]]$primaries = $targetConfig.Replicas | Where-Object {$_.isPrimary -like \"true\"}\n if($primaries.Count -ne 1)\n {\n throw \"There must be one, and only one, primary in the configuration.\";\n }<\/p>\n\n
[string[]]$syncReplicas = $targetConfig.Replicas | Where-Object {$_.AvailabilityMode -like \"SynchronousCommit\"}\n if($syncReplicas.Count -gt 3)\n {\n throw \"The requested configuration exceeds the maximum of three synchronous replicas which can be in an Availability Group configuration.\";\n }<\/p>\n\n
if(($targetConfig.Replicas.Count -gt 5) -and ($majorSQLVersion -lt 12))\n {\n throw \"The requested configuration exceeds the maximum of five replicas which can be in an Availability Group configuration for a SQL Server version below 12.\";\n }\n \n if(($targetConfig.Replicas.Count -gt 9) -and ($majorSQLVersion -ge 12))\n {\n throw \"The requested configuration exceeds the maximum of nine replicas which can be in an Availability Group configuration for a SQL Server version of 12 or greater.\";\n }\n}<\/p>\n\n
#Error handling\n$ErrorActionPreference = \"Stop\";\nTrap \n{\n $err = $_.Exception\n while ( $err.InnerException )\n {\n $err = $err.InnerException\n write-output $err.Message\n };\n}<\/p>\n\n
$totalScriptSteps = 13;\n$currentStep = 0;<\/p>\n\n
#Load dependencies\n$currentStep++;\nWrite-Progress -Activity \"Loading dependencies.\" -Status \"Importing SQLPS module.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\nImport-Module SQLPS -DisableNameChecking;<\/p>\n\n
#Validate input \/ load jsonString\n$currentStep++;\nWrite-Progress -Activity \"Validating input.\" -Status \"Checking parameter requirements.\" -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);<\/p>\n\n
if ((!$jsonString) -and ($jsonFilePath))\n{\n if(!(Test-Path $jsonFilePath))\n {\n throw \"Invalid file path. ($jsonFilePath)\";\n }\n $jsonString = Get-Content $jsonFilePath\n}\nelseif (!$jsonString)\n{\n throw \"You must input a JSON file path or a JSON string.\";\n}<\/p>\n\n
#Load JSON\n$currentStep++;\nWrite-Progress -Activity \"Validating input.\" -Status \"Loading JSON.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\n$targetConfig = $jsonString | ConvertFrom-Json;\n$currentStep++;\nWrite-Progress -Activity \"Validating input.\" -Status \"Validating target replica configuration.\" -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);\nValidateTargetReplicaConfiguration;<\/p>\n\n
#Build server object\n$currentStep++;\nWrite-Progress -Activity \"Validating input.\" -Status \"Testing server connection.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\n$selectedNode = ($targetConfig.Replicas | Where-Object {$_.isPrimary -like \"true\"})[0].Name;\n$newPrimaryReplicaName = $selectedNode;\nif($newPrimaryReplicaName -notcontains \"\\\")\n{\n $newPrimaryReplicaName += \"\\DEFAULT\";\n}<\/p>\n\n
Test connection<\/h1>\n
We check the version number as a light weight method of checking the connection.<\/h1>\n
Error handling will abort script if this command fails.<\/h1>\nInvoke-Sqlcmd -ServerInstance $selectedNode -Query \"SELECT @@Version;\" | Out-Null;\n\n
$currentStep++;\nWrite-Progress -Activity \"Validating input.\" -Status \"Retrieving current Availability Group state.\" -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);\n$AGState = Get-AGState;\nWrite-Host -Object \"Current Availability Group state:\" -ForegroundColor Green -BackgroundColor Black;\n$AGState | Format-Table;\nWrite-Host -Object \"---------------------------------\" -ForegroundColor Green -BackgroundColor Black;<\/p>\n\n
if($AGState -eq $null)\n{\n throw \"Failed to retrieve Availability Group state from $newPrimaryReplicaName\";\n}<\/p>\n\n
[string]$currentPrimaryReplica = $AGState[0].primary_replica;\nif($currentPrimaryReplica -notcontains \"\\\")\n{\n $currentPrimaryReplica += \"\\DEFAULT\";\n}<\/p>\n\n
if($currentPrimaryReplica -eq \"$newPrimaryReplicaName\")\n{\n throw \"$newPrimaryReplicaName is already the primary replica.\";\n}<\/p>\n\n
Failover prep<\/h1>\n
You cannot have more than 3 synchronous commit replicas at one time.<\/h1>\n
But we want to maintain synchronous commit replicas during the fail-over, if possible.<\/h1>\n
So, we set just enough to async so that we can set our fail-over replica to synchronous.<\/h1>\n$currentStep++;\nWrite-Progress -Activity \"Failover preparation.\" -Status \"Pre-configuring async replicas to avoid hitting synchronous commit limit during fail-over.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\n[string]$AGname = $AGState[0].AG_Name;\n[string[]]$syncReplicas = $AGState | Where-Object {$_.availability_mode_desc -like \"SYNCHRONOUS_COMMIT\"};\n$count = 0;\nwhile($syncReplicas.Count -ge 3)\n{\n [string]$asyncReplicaName = ($targetConfig.Replicas | Where-Object {$_.Name -notlike ($currentPrimaryReplica.Replace('\\DEFAULT',''))}
\n | Sort-Object AvailabilityMode, Name)[$count].Name;\n Set-SqlAvailabilityReplica -AvailabilityMode \"AsynchronousCommit\" -FailoverMode \"Manual\" <\/code>\n -Path \"SQLSERVER:\\Sql\\$currentPrimaryReplica\\AvailabilityGroups\\$AGname\\AvailabilityReplicas\\$asyncReplicaName\";\n\n$count++;\n $AGState = Get-AGState;\n [string[]]$syncReplicas = $AGState | Where-Object {$_.availability_mode_desc -like \"SYNCHRONOUS_COMMIT\"};\n}<\/p>\n\n
$currentStep++;\nWrite-Progress -Activity \"Failover preparation.\" -Status \"Setting new primary target replica to synchronous commit to achieve zero data loss during fail-over.\" -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);\n$tempName = $newPrimaryReplicaName.Replace('\\DEFAULT','');\nSet-SqlAvailabilityReplica -AvailabilityMode \"SynchronousCommit\" -FailoverMode \"Manual\"
\n -Path \"SQLSERVER:\\Sql\\$currentPrimaryReplica\\AvailabilityGroups\\$AGname\\AvailabilityReplicas\\$tempName\";<\/p>\n\n#wait for new primary to sync up\n$currentStep++;\nWrite-Progress -Activity \"Failover preparation.\" -Status \"Verifying that data synchronization is complete.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\n[System.Data.DataRow[]]$synchronizingDatabases = (Get-DatabaseSyncStates -AG $AGname -Server $tempName) | Where-Object {$_.SyncState -like \"SYNCHRONIZING\"};\n$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()\n$currentStep++;\nwhile($synchronizingDatabases.Count -gt 0)\n{\n $currentTime = $stopWatch.Elapsed;\n $elapsedTime = [string]::Format(\"Elapsed time: {0:d2}:{1:d2}:{2:d2}\", $CurrentTime.hours, $CurrentTime.minutes, $CurrentTime.seconds)\n Write-Progress -Activity \"Waiting for databases to synchronize.\" -Status $elapsedTime -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);\n Start-Sleep -s 2;\n $synchronizingDatabases = (Get-DatabaseSyncStates -AG $AGname -Server $tempName) | Where-Object {$_.SyncState -like \"SYNCHRONIZING\"};\n} \n$stopWatch.Stop();<\/p>\n\n
Failover<\/h1>\n$to = $newPrimaryReplicaName.Replace('\\DEFAULT','');\n$from = $currentPrimaryReplica.Replace('\\DEFAULT','');\n$currentStep++;\nWrite-Progress -Activity \"Failover.\" -Status \"Failing over, without data loss, from $from to $to.\" -PercentComplete ($currentStep\/$totalScriptSteps100);\nSwitch-SqlAvailabilityGroup -Path \"SQLSERVER:\\Sql\\$newPrimaryReplicaName\\AvailabilityGroups\\$AGname\";\n\n
#Final Configuration<\/p>\n\n
Set availability and failover modes<\/h1>\n
Sort ASC by AvailabilityMode so that Async is before Sync.<\/h1>\n
This prevents us from adding a 4th sync replica before we establish the asyncs<\/h1>\n$currentStep++;\nWrite-Progress -Activity \"Final configuration.\" -Status \"Set all replicas availability mode and failover mode to match target configuration.\" -PercentComplete ($currentStep\/$totalScriptSteps<\/em>100);\nforeach($replica in ($targetConfig.Replicas | Sort-Object AvailabilityMode))\n{\n [string]$newAvailabilityMode = $replica.AvailabilityMode;\n [string]$newFailoverMode = $replica.FailoverMode;\n [string]$replicaName = $replica.Name;\n Set-SqlAvailabilityReplica -AvailabilityMode $newAvailabilityMode -FailoverMode $newFailoverMode <\/code>\n -Path \"SQLSERVER:\\Sql\\$newPrimaryReplicaName\\AvailabilityGroups\\$AGname\\AvailabilityReplicas\\$replicaName\";\n}\n\n
$currentStep++;\nWrite-Progress -Activity \"State review.\" -Status \"Getting Availability Group state for review.\" -PercentComplete ($currentStep\/$totalScriptSteps*100);\nWrite-Host -Object \"Final Availability Group state:\" -ForegroundColor Green -BackgroundColor Black;\nGet-AGState | Format-Table;\nWrite-Host -Object \"---------------------------------\" -ForegroundColor Green -BackgroundColor Black;<\/code><\/pre>\n<\/p>\n
Example JSON object<\/h3>\n
{\n\t\"Replicas\" : \n\t[\n\t\t{\n\t\t\t\"Name\" : \"V-HAMMER-01\",\n\t\t\t\"AvailabilityMode\" : \"AsynchronousCommit\",\n\t\t\t\"FailoverMode\" : \"Manual\",\n\t\t\t\"isPrimary\" : \"false\"\n\t\t},\n\t\t{\n\t\t\t\"Name\" : \"V-HAMMER-02\",\n\t\t\t\"AvailabilityMode\" : \"AsynchronousCommit\",\n\t\t\t\"FailoverMode\" : \"Manual\",\n\t\t\t\"isPrimary\" : \"false\"\n\t\t},\n\t\t{\n\t\t\t\"Name\" : \"V-HAMMER-03\",\n\t\t\t\"AvailabilityMode\" : \"SynchronousCommit\",\n\t\t\t\"FailoverMode\" : \"Automatic\",\n\t\t\t\"isPrimary\" : \"true\"\n\t\t},\n\t\t{\n\t\t\t\"Name\" : \"V-HAMMER-04\",\n\t\t\t\"AvailabilityMode\" : \"SynchronousCommit\",\n\t\t\t\"FailoverMode\" : \"Automatic\",\n\t\t\t\"isPrimary\" : \"false\"\n\t\t}\n\t]\n}<\/code><\/pre>\n
\nWritten by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\nDerik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
Merry Christmas Eve! A few weeks ago I posted on Availability Group fail-over tests with PowerShell. That post covered the basics of how to check the Availability Group\u2019s state and perform the commands for conducting a zero data loss fail over. As a Christmas Present to all of you,\u00a0I wanted to get a little bit […]<\/p>\n","protected":false},"author":1,"featured_media":530,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-96","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/96","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=96"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/96\/revisions"}],"predecessor-version":[{"id":359,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/96\/revisions\/359"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/530"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=96"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=96"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=96"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- PowerShell v3.0 \u2013 to be honest, this might work in PowerShell v2.0 but I have only tested it in v3.0. v3.0 is packaged with\u00a0Windows Server 2012 and Windows 8 or above but, if you aren\u2019t using one of those operating systems, you can install it yourself by following these instructions<\/a>.<\/li>\n<\/ul>\n
- SQLPS module\u00a0\u2013 this is installed with Microsoft SQL Server 2012 and above, by default. If you don\u2019t have SQL Server installed you can also install the, smaller footprint,\u00a0feature pack here<\/a>.<\/li>\n<\/ul>\n
- Available on GitHub here<\/a>.<\/li>\n<\/ul>\n
- Before and after print outs of the AG state for logging and troubleshooting purposes.<\/li>\n<\/ul>\n
- It is aware of the AG state, enabling it to move from configuration A to B without stumbling on limitations such as maximum number of synchronous commit replicas.<\/li>\n<\/ul>\n
- Accepts JSON files or strings as input for the target AG state. This is useful for saving configurations for fail-back and for performing scripted tests regularly.<\/li>\n<\/ul>\n