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?