1

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?

Improve this question
5
  • 1
    You should read Recommended Adjustments to Quorum Voting. Technically, it will failover to node 2 in same data center since it is set to Sync with auto failover. When you adjust node weights, test all the scenarios that you might face.
    – Kin Shah
    Commented Sep 14, 2018 at 16:41
  • 1
    Set the votes to 0 for nodes 3 and 4 and 1 for nodes 1 and 2.
    – Tara Kizer
    Commented Sep 14, 2018 at 17:18
  • Please let me know are you talking about unexpected shutdown of SQL Server on node 1 or planned shutdown by stopping services. if this is latter there would ne NO automatic failover and you would have to do manual failover.
    – Shanky
    Commented Sep 14, 2018 at 17:46
  • Thanks Tara. For full clarity that's actually how we normally confiure our votes, however due to a bug we were left with the above ag and vote config for a week. I just wanted to know what issues we might have faced.
    – Pixelated
    Commented Sep 14, 2018 at 17:49
  • Shanky, unexpected failover.
    – Pixelated
    Commented Sep 14, 2018 at 17:49

2 Answers 2

Reset to default
1

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

Improve this answer
2

The configuration described in the original topic is a bit strange. There are exists two resource groups SYSTEM (CNO, CNO_VIP1, CNO_VIP2, FSW) and AG (LISTENER, LISTENER_VIP1, LISTENER_VIP2, AG).

These two resource groups can be owned by different WSFC nodes. For example SYSTEM is owned by node from DC2 and AG is owned by node from DC1. In this case, if the link between DC1 and DC2 is broken:

  • Then DC1 will have only 1 vote (NODE1)
  • Then DC2 will have only 2 votes (NODE3 + FSW)

Accordingly to the quorum configuration DC2 part will alive (due to majority of votes) and DC1 will die (due to minority of votes). But as soon as synchronization between DC1 and DC2 is asynchronous there won't be automatic failover to DC2.

So you will end up with the situation when DC2 is alive but can not handle DB workload, because AG will wait manual intervention (allow_data_loss).

To avoid such situation, you need to assign votes like that

DC1:

  • NODE1: 1

  • NODE2: 1

ASYNC SYNCHRONIZATION

DC2

  • NODE3: 0

  • NODE4: 0

In that configuration AG will fail over to the NODE2 (DC1).

You'd better to explain the purpose of DC2. Is it disaster recovery DC or you need it for read scale or both?

Improve this answer
2
  • As I mentioned above to Tara, it was actually a bug in my code which left us in the state described in my question. Normally one vote is given to the witness, one given to the primary and the other given to which ever node is hosting the sync replica. My question was to clarify what issues I might have faced, and fail over behaviour whilst the ag was in three described configuration.
    – Pixelated
    Commented Sep 19, 2018 at 13:13
  • 1
    Yes, got it. I highlighted the issue that you guys can encounter with the existing configuration.
    – Alex S
    Commented Sep 19, 2018 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.