As I was going through the documentation of AlwaysON, I came across the following caveat with SQL Server AlwaysON Automatic Failover set.

While we can have upto synchronous secondary replica,  we can have only one secondary replica to be part of Automatic Failover Set. Now, what that means is only of the synchronous secondary replica can be set for Automatic failover if the primary replica has failed.

Caveat

If the synchronous secondary replica which forms the Automatic Failover set with Primary Replica goes down due to some reason, the Primary Replica is exposed until the same secondary replica node is up again.

Scenario

I am taking the following Example from Msdn

Replica Availability   Mode and Failover Mode Settings
A (Current Primary) Synchronous   commit with automatic failover
B Synchronous   commit with automatic failover
C Synchronous   commit with manual failover only


if the Secondary B fails due to some reason and during the same time, if the primary replica does down, the server cannot perform the Automatic Failover inspite of having another synchronous secondary replica. It will wait for Manual failover in which case availability of the server will be impacted.

While this scenario might be highly unlikely wherein you have the failure of two replicas involved in Automatic Failover Set at the same time and as a result the Product Team had taken this conscious decision to have only 1 secondary replica involved in AlwaysON v1 Automatic Failover set.

However if we have lot of many customer with similar scenario, the Product  Team might consider allowing multiple secondary replicas in the Automatic Failover Set.

I have raised a Connect Item to flag the same as a suggestion to the Product Team and if you think it is valid suggestion, please vote for it so that Product Team takes it with priority

https://connect.microsoft.com/SQLServer/feedback/details/757086/sql-alwayson-allows-only-a-single-secondary-to-form-an-automatic-failover-set

However currently, if you think you might be vulnerable to hit this scenario you can use custom Powershell scripts to Test whether the Secondary Replica part of the Automatic Failover set is online and if goes down, you can switch the other synchronous secondary replica as the Failover target for automatic Failover.

To develop the above script, you can use the new Test-SqlAvailabilityReplica Powershell cmdlet to test the secondary replica and switch another secondary replica for automatic failover using Set-SqlAvailabilityReplica cmdlet.

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *