SQL Server Always On Node and File Share Majority I am looking for some guidance on architecting our SQL Server 2016 SP1 Always On Availability Groups HADR solution on Windows Server 2012 R2. 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. 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.

We are attempting to do a Node and File Share Majority configuration per https://technet.microsoft.com/en-us/library/cc731739(v=ws.11).aspx and this diagram:



This diagram shows that when one node and the "disk" / file share witness are in communication the cluster runs, but in our testing of this situation, the cluster fails due to a loss of quorum of the WSFC. The automatic AG failover works if we test a failure of one server at a time by disabling the NIC in vmWare since SQL Server 2016 supports two automatic failover target replicas. However, it does not work if we fail both servers at site A at the same time to simulate a point-to-point network failure or a site power failure.

The following approach with manual intervention to force the quorum will work but it is not automatic, which is what we want in the ideal situation:

$node = "SQLServerC"
Stop-ClusterNode –Name $node
Start-ClusterNode –Name $node –FixQuorum

ALTER AVAILABILITY GROUP SQLServerAO FORCE_FAILOVER_ALLOW_DATA_LOSS;

$node = "SQLServerC"
Stop-ClusterNode –Name $node
Start-ClusterNode –Name $node


I appreciate any suggestions you can offer and thank you in advance!
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.


Except for a very specific scenario, this will not work. In all other scenarios you'll lose quorum before you'd ever be able to automatically fail over (which requires... quorum).

The best answer would be to have the DR side be manual failover (it could still be synchronous) with the proper documentation on how to force quorum and bring the AG online.

You could also invest more into VMWare and use their technologies but that assumes the infrastructure, licensing, and ability to implement those products for a specific service such as this.