Availability groups across sites: what happens if severs can't contact each other but both still can reach file share witness?

Question

I'm currently planning a new SQL Server 2019 Enterprise Edition setup with HA/DR, possibly with availability groups.

Let say we have the following setup:

• Site A: one SQL Server instance (SQL1)
• Site B: one SQL Server instance (SQL2)
• Site C: file share witness for the Windows Failover Cluster
• Availability groups: AG1 (SQL1 is primary, SQL2 is secondary), AG2 (SQL2 is primary, SQL1 is secondary)

If network connection between Site A and Site B is lost, but both sites are still able to reach the file share witness, how availability groups will react?

• Will SQL1 stays primary replica of AG1 and SQL2, primary replica for AG2?
• Will one of the server go down and the other one become primary replica for all availability groups?
• If only one server stays online, which one wins? The one currently owning the cluster core resource group?
• Another scenario?

So far, all articles I found only talk about 1 server acting as primary replica for all availability groups. Unfortunately, I don't have the infrastructure available yet to test it myself.

Does anyone have experience with this scenario?

Thank you

Answer 1

If network connection between Site A and Site B is lost, but both sites are still able to reach the file share witness, how availability groups will react?

This depends on the settings of the cluster. Since most people don't setup a cluster with advanced settings (which is generally a good thing because it's very easy to do it incorrectly and really muck things up) I'm going to assume a vanilla cluster install and setup.

Given the above assumption, depending on the latency between the sites to the witness and assuming the communication to the file share has no issues from either site, then the site that stays up will generically most of the time be the site that owns the core cluster resource group. This is due to the witness backoff setting in the cluster as the partition that owns the core resources will not have to wait the delayed backoff timer before attempting to arbitrate.

Whichever side achieves quorum (which, depending on various other items could be either side [or no side if things go very very poorly]) will be the side that all cluster groups move to, whether or not they can come up is another story.

Availability groups: AG1 (SQL1 is primary, SQL2 is secondary), AG2 (SQL2 is primary, SQL1 is secondary)

As an aside since your question wasn't about this but I must point it out, this is not a great idea. Depending on the load, there can be performance issues which creep in from various places. If there is enough hardware available that both can be primary on the same node then it might work out to do this. If there isn't enough hardware available that both can be primary on the same node then this is a massive disaster waiting for ignition.