SQL Server 2016 – AG Group Failover and Quorum Explained

availability-groupsclusteringsql-server-2016

We have a 4 node AG set up across two data centers using 4 replicas with a file share witness. Nodes 1 and 2 are in data center 1 and Nodes 3 and 4 in data center 2. Node 1 is primary and is set to synchronous commit with automatic failover to node 2 in the same datacenter. Node 1 is set to asynchronous commit to nodes 3 and 4 in data center 2. Node weighting is set to one for nodes 1 and 3 (main node in each data center) and zero for nodes 2 and 4.

Please, can someone tell me what the behavior would be if we had an unexpected SQL Server shut down at node 1?

Would the AG attempt to failover to node 3 in data center 2 due to the node weighting, fail and not attempt to fail over to any other replicas. Or would the AG group ignore node 3 due to it being async and try to fail over to the first synchronous replica with automatic failover based on the preferred owner's list?

Best Answer

Please, can someone tell me what the behavior would be if we had an unexpected SQL Server shut down at node 1?

The failover would happen and the failover would be according to order in which nodes were added to Availability groups and configured for automatic failover. Like below(All Images taken from This MSDN Blog

enter image description here

This is SQL Server 2016 where you have option to set 3 replicas with automatic failover. Now user added replicas such that Node 2 and Node 3 where added one after other. Now in this case if Node1 goes down it will failover to Node 2. The exact failover sequence can be seen from WSFC GUI. For this AG it would be like below

enter image description here

This tell you sequence of automatic failover. if node 1 goes down it automatically failsover to node 2 and if node 2 goes down it automatically failover to node 3 assuming WSFC was up and did the failover correctly.

Would the AG attempt to failover to node 3 in data center two due to the node weighting, fail and not attempt to fail over to any other replicas. Or would the AG group ignore node 3 due to it being async and try to fail over to the first synchronous replica with automatic failover based on the preferred owner's list?

The node weight would not come into picture here. I little idea how you ahve configured the AG so go ahead look into cluster GUI and you can see the order in which it would failover.

Since we talked about node weight I would suggest you to keep node weight for DR replicas as 0 this will make ODD number of votes in cluster. 2 nodes at DC and 1 FS witness. You would not want DR to take part in voting which is sitting out far somewhere and hardly have any idea

Related Question