I have a number of SQL Server Availability Groups in my organization. Of them, most are multi-site configurations for disaster recovery. Naturally, the latency that a separate data center implies is more than my application can handle for its writes. For this reason, we use asynchronous commit with manual fail-over. We also conduct regular fail-over tests. During a real fail-over we will accept a certain amount of data loss due to non-synchronized transactions, but during a test this is unacceptable.
Here I will demonstrate how to fail-over an Availability Group which starts off in asynchronous commit, without any data loss. First, I wrote a quick function to verify the state of the replicas so that I can avoid a certain amount of typing during the process of testing and demonstrating. This function can be run from any client and connect to any of the nodes of the Availability Group.
Get-AGState function
[sql]Import-Module SQLPS -DisableNameChecking
function Get-AGState ([string]$server)
{
$state = Invoke-Sqlcmd -Query "SELECT replica_server_name
, HAGS.primary_replica, endpoint_url
, availability_mode_desc, failover_mode_desc
FROM sys.availability_replicas AR
INNER JOIN sys.dm_hadr_availability_group_states HAGS
ON HAGS.group_id = AR.group_id;" -ServerInstance "$server";
$state | Format-Table -AutoSize;
}[/sql]
As shown above, in this test environment I have four replicas. Two are using synchronous commit representing local data center high availability. The other two are using asynchronous commit representing two nodes that would exist in a separate data center. Our fail over test is going to move from V-HAMMER-04 to V-HAMMER-01. First the availability modes must be changed to synchronous commit and all transactions must be synchronized. This way a clean fail over can occur and we can revert the settings back to normal operation.
Changing availability modes
Changing a replica to synchronous commit is as easy as executing one PowerShell command.
[sql]Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" `
-FailoverMode "Manual" `
-Path SQLSERVER:\Sql\V-HAMMER-04\DEFAULT\AvailabilityGroups\AG_Test\AvailabilityReplicas\V-HAMMER-01[/sql]
NOTE: The first server name (V-HAMMER-04) in the -PATH is the primary replica of the AG. This cmdlet must always use the primary replica here. The primary replica was identified in our Get-AGState function result set above. The final server name (V-HAMMER-01) is the replica that we are modifying.
Once complete, checking the AG state again will show that the availability_mode_desc did update with the change.
At this point there is a high likely-hood that your system is in a state of functional outage. Writes to your database are having to commit in a secondary data center before committing locally and this latency could be causing timeouts. To minimize this effect, all of the commands demonstrated here can be run in one custom script.
Failing over
To fail over to our secondary replica (V-HAMMER-01) PowerShell has, once again, graced us with a single cmdlet to do the work for us. T-SQL also only requires one statement, but we’re not competing right?
[sql]
Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\V-HAMMER-01\DEFAULT\AvailabilityGroups\AG_Test
[/sql]
NOTE: The server name here (V-HAMMER-01) is the destination secondary replica, not the primary.
Wa-la! The Availability Group has just failed over to your secondary data center without any data loss. Next we need to normalize the configuration and remove that functional outage that we induced.
Normalizing the availability replica configuration
In order to normalize the replicas we need to make our secondary data center have high-availability with synchronous commit and automatic fail over. We also need to put our primary data center into asynchronous commits with manual fail over.
Remove latency induced outage
[sql]Set-SqlAvailabilityReplica -AvailabilityMode "AsynchronousCommit" `
-FailoverMode "Manual" `
-Path SQLSERVER:\Sql\V-HAMMER-01\DEFAULT\AvailabilityGroups\AG_Test\AvailabilityReplicas\V-HAMMER-03
Set-SqlAvailabilityReplica -AvailabilityMode "AsynchronousCommit" `
-FailoverMode "Manual" `
-Path SQLSERVER:\Sql\V-HAMMER-01\DEFAULT\AvailabilityGroups\AG_Test\AvailabilityReplicas\V-HAMMER-04[/sql]
Re-establish high-availability
[sql]Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" `
-FailoverMode "Automatic" `
-Path SQLSERVER:\Sql\V-HAMMER-01\DEFAULT\AvailabilityGroups\AG_Test\AvailabilityReplicas\V-HAMMER-01
Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" `
-FailoverMode "Automatic" `
-Path SQLSERVER:\Sql\V-HAMMER-01\DEFAULT\AvailabilityGroups\AG_Test\AvailabilityReplicas\V-HAMMER-01[/sql]
Final availability group state
Leave a Reply