2

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

3

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.

2
  • I appreciate your concerns about "active-active" setup. 1 server will definitely be able to handle the load. If some applications run primarily on site B instead of site A, then it would make sense to have the primary replica for these databases on the same site, no? Dec 1 '21 at 15:11
  • @EricPrévost if the hosts can handle it and the latency isn't too bad. If the latency is bad enough that one AG should be hosted at one site, then it'd make more sense to have these are two separate clusters and then something like a distributed across. Again, I have no idea what the expectations are, the infrastructure, or the requirements so I'm just giving advice is all. Dec 1 '21 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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