SQL Server 2016 AlwaysOn Availability Group Enhancements: Multiple Automatic Failover Targets
Overview
SQL Server 2104 and SQL Server 2012 can have two synchronous secondary replicas. One synchronous secondary replica can serve the role as automatic failover partner with the primary replica.
In SQL Server 2016 both synchronous secondary replicas can be configured as automatic failover partners with the primary replica.
Benefits
This will allow following benefits
- This increases the chances that high availability can be maintained if one of the automatic failover partners is lost.
- Reduces the need to manually manage failovers or to reconfigure the availability group with another automatic failover partner in the event that one of the automatic failover partners is unavailable.
Configure Multiple Automatic Failover Targets
You can configure three replicas as automatic failover partners using Transact-SQL or SQL Server Management Studio.
USE [master]GO ALTER AVAILABILITY GROUP [AG]MODIFY REPLICA ON N'SQLN1' WITH (FAILOVER_MODE = AUTOMATIC)GO ALTER AVAILABILITY GROUP [AG]MODIFY REPLICA ON N'SQLN2' WITH (FAILOVER_MODE = AUTOMATIC)GO ALTER AVAILABILITY GROUP [AG]MODIFY REPLICA ON N'SQLN3' WITH (FAILOVER_MODE = AUTOMATIC)GO |
 |
Troubleshooting Automatic Failover Problems
Troubleshooting multiple automatic failover targets would be similar to the steps of the following article used to troubleshoot automatic failover issues with a single automatic failover target:
Troubleshooting automatic failover problems in SQL Server 2012 AlwaysOn environments
Useful Query
The following query may be used to check replica properties for multiple automatic failover partners and AG database synchronization health:
select replica_server_name, availability_mode_desc,failover_mode_desc, database_id, synchronization_health_desc,synchronization_state_desc,* from sys.availability_replicas as AR inner join sys.dm_hadr_database_replica_states as DRS on DRS.replica_id = AR.replica_id
FAQs
If both the secondary replicas are SYNCHRONIZED and healthy, which replica with Cluster choose to failover to?
Windows Cluster will attempt to failover to the next replica in the preferred owner list of the availability group, which dictates attempted failover order. The preferred owner list of the availability group role in Cluster will list all the availability group replicas defined for automatic failover.
For example replicas SQL16N1 (primary), SQL16N2 (secondary) and SQL16N3 (secondary replica) are all defined for automatic failover and are in that order in the preferred owner list. In the event that a health issue is detected in SQL16N1, Cluster will attempt to failover to SQL16N2 next. In the event that SQL16N2 is not healthy (for example, an availability database was not SYNCHRONIZED) Cluster will attempt to failover to SQL16N3.
Here is a picture of the availability group role properties from Failover Cluster Manager, note the Up and Down buttons for setting priority of these nodes as owner of the primary replica.
Can we set the automatic failover priority, choosing a particular replica for a preferred failover target in SQL Server or by using Failover Cluster Manager?
There is no setting in SQL Server for configuring the preferred owner list settings of the availability group role. In addition, resetting the priority of the nodes in the preferred owner list of the availability group role in Failover Cluster Manager is also not recommended because SQL Server manages Preferred and Possible Owner Properties for AlwaysOn Availability Group/Role. When a failover occurs, SQL Server resets the Preferred Owner list, over-writing any change made through Failover Cluster Manager.
Furthermore, modifying AG resource properties in the cluster manager is not recommended.
Tests have shown you can change the Preferred Owner list in Failover Cluster, and the next automatic failover will abide by the modified preferred owner list. However, once SQL Server resets the preferred owner list, subsequent automatic failovers will proceed with the preferred owner priority set by SQL Server.
Workaround: Configure automatic failover priority by adding replicas to availability group in preferred automatic failover priority
SQL Server will set the preferred owner list priority based on the order in which your availability group replicas are added to the availability group. This can allow you some control over what automatic failover partner Cluster attempts to failover to first.
For example, you have defined an availability group with primary replica SQL16N1 and add SQL16N2 and SQL16N3 as automatic failover partners. If you add SQL16N2 and then SQL16N3 using the Add Replica to Availability Group wizard, SQL Server will add them in the same order to the preferred owner list:
 |
 |
When a health event is detected in SQL16N1 (in this example, I shut down SQL16N1 through SSMS Object Explorer), Cluster checks the next node in the preferred owner list which is SQL16N2. Assuming it is healthy and SYNCHRONIZED, Cluster will successfully failover to SQL16N2.
Now, try adding the availability replicas in the reverse order to the availability group, like this. After adding the replicas, check the properties of the availability group role to confirm the order of the preferred owner list:
 |
 |
When a health issue is detected on SQL16N1 (again shut down SQL Server on SQL16N1) we can see the preferred owner list priority is honored and Cluster attempts to failover to SQL16N3 first and successfully.
