We have a primary site A with a primary replica and a secondary
replica and a disaster recovery (DR) site B with a secondary replica
and a file share witness.
Okay, you have 3 node cluster, 2 nodes are hosted in Site A and one hosted on Site B.
Site A has a primary replica (node1) and a secondary replica (node2)
Site B has a secondary replica (node3)
And your quorum model is: Node and File Share Majority.
By default, each node will have 1 vote and file share witness will have 1 vote.
The total quorum votes will be node1 + node2 + node3 + file share witness = 1 + 1 + 1 + 1 = 4
and the current quorum majority will be 4 out of 4
, i.e. 100% survival.
Let’s assume there is network connectivity issue between subset (2,2) of the cluster nodes, now the quorum majority will be 2 out 4, i.e. 50 % chance for survival. The problem here is, each subset will think, it has majority and it will try to own the resource groups
to keep the cluster up and running, but **Windows Server Failover Cluster (WSFC)**
supports only one active nodes at a time, two subset of nodes trying to access to same resource end up in conflicting issue, this is called split-brain scenario
.
So having even number of nodes in cluster
is not the right approach.
SQL Server 2016 SP1 Always On Availability Groups HADR solution on
Windows Server 2012 R2
To avoid the above mentioned issue, Windows Server 2012 R2 introduced a new feature called Dynamic Witness.
In your case, (starting from Windows Server 2012 R2) the default voting will be as follows:
node1 + node2 + node3 = 1 + 1 + 1 = 3 & File Share Witness = 0
Here, the total quorum votes will be 3 and majority is 3 out of 3. There is no voting for File Share Witness to keep the total voting as odd number.
If there is a failure of node, the cluster will assign a vote to the File Share Witness dynamically. The process of assigning or revoking quorum vote to witness dynamically called as Dynamic Witness.
And the new quorum will be calculated dynamically post failure of node. For example, if a node is failed, and witness has a vote, the quorum will be 3 out of 3, i.e. node2 + node3 + File Share Witness. The process of adopting quorum after subsequent failure is called Dynamic Quorum.
Our goal is that if the primary replica server 1 at site A is down,
the Always On Availability Group (AG) fails over to the secondary
replica server 2 at site A, and if both servers at site A are down,
the AG fails over to site B.
So let see the failure scenario for your case:
Default:
node1 node2 node3 File Share Witness Quorum Majority
1 1 1 0 3 out of 3 100 %
1 node failure:
node1 node2 node3 File Share Witness Quorum Majority
0 1 1 1 3 out of 3 100 %
--> **`Dynamic Quorum and Dynamic Witness`**
In this case, the cluster become, 2 node cluster with witness
.
Here, the default quorum configuration will be node2 + node3 + File Share Witness = 1 + 1 + 1 = 3 out of 3 (because dynamically quorum updated as 3)
Let’s assume another node fails (node2)
node2 node3 File Share Witness Quorum Majority
0 1 1 2 out of 3 66.66 %
--> It has majority - more than 50% - the cluster will survive
Now the new cluster will be:
node3 File Share Witness Quorum Majority
1 1 2 out of 2 --> Dynamically quorum updated as 2
In case of another node failure, the cluster will be down. I hope this is clear. Any node may fail in any order, but the quorum majority is calculated same as above.
Having cloud witness will be better option, but it will not solve your problem, still it is just an option. Please check my page for deeper understanding of Windows Server Failover Cluster Quorum
To setting up DR, I suggest a simple solution:
On Site A set up Always On Availability Group (AG)
with 2 node cluster
and configure quorum model as Node and File Share Majority.
On Site B set up SQL Server Standalone instance
and configure - automated backup and restoration / log shipping from Site A.
There will be different options, this was the one, I have used earlier. In this case, failover should be performed manually. Consider your business requirement and design the DR strategy.
Few points to be noted while designing HADR solutions
:
- Recovery Time Objective (RTO)
- Recovery Point Objective (RPO)
- Scalability
- Retention Policy,
- Failover, etc...
I recommend you to check my page for better understanding. I hope this answer helps, thank you.