0

I am planning to implement a two-node Windows Server Failover Cluster (WSFC) for SQL Server Always On Availability Group in our main site, and the third node in our DR site. All application clients connect from remote branches, and I plan to put the FS witness on one of them.

We want to be able to automatically FO to DR if the main site is completely down (like communication to the main site is down).

The scenario I described above will leave us with an even number of voters, and the option of failing the 2 main site nodes at once will not work.

  1. Can I set 2 witnesses? (Guess not)
  2. If I set the primary node (in the main site) as a nonvoter, I will remain with a 3 voting cluster. Any downsides here?
Improve this question
2
  • 1
    Did you read Configure and manage quorum? Please add your windows and SQL version because they can make a difference in the answer.
    – SqlWorldWide
    Dec 20, 2021 at 11:38
  • Just met the client again. It is WINDOWS 2016 and SQL 2017, but can be upgraded if needed.
    – Oky
    Dec 30, 2021 at 20:55

2 Answers 2

Reset to default
2

We want to be able to automatically FO to DR if the main site is completely down (like communication to the main site is down).

Disaster recovery locations are generally for disaster recovery and not high availability. It might just be a naming thing, but I'd change to calling it by datacenter or location name as disaster recovery doesn't fit here. Just fyi.

When you say "communication to the main site is down" as shown by whom? This will change how the setup would need to be configured. For example, If communication goes down between the primary site and the secondary site, what should happen? If communication goes down between the primary site, secondary site, and filesahre location, what should happen?

Can I set 2 witnesses? (Guess not)

You're correct, though you can change the witness at any time. If you would move to another location, it is possible to set a new witness.

If I set the primary node (in the main site) as a nonvoter, I will remain with a 3 voting cluster. Any downsides here?

There are various situations where this would cause the whole cluster to be down. I would focus on the primary goal, if it's truly to only have automatic HA between the sites then this would work for most scenarios. If it's to have automatic HA in the priamry site and then if that fails, automatic HA to the secondary site then you're not going to really be able to achieve what you're looking to do out of the box. There are various situations that need to be checked, such as where the issues occurs, how it manifests, what it affects, etc., and this will change what ends up occuring to the cluster.

Improve this answer
1
  • So, yes, he wants DR and HA in one pack. "communication to the main site is down" - All branches are routed to see the application servers in the main site. The application servers are mirrored to the DR site. Let's take an extreeme scenario, where there is no comunication to the main site from the branches and from the DR site. File share goes bundled with application servers. The mirrored Servers have the same IP as in the main site. So in case of a major failure everyone sees only the DR site.
    – Oky
    Dec 30, 2021 at 21:12
1

I don’t think you’re going to be able to accomplish what you want to, nor that you should try.

Considering you have a failover cluster that spans two data centers, and the witness is in prod. Nothing about a connectivity issue to the data center would cause a failover, assuming connectivity within the data center is intact. Both AG nodes will still be talking to each other and the witness as if nothing has changed. What would trigger them to think they should fail over?

Further, to have automatic failover to DR you’d need to be running synchronous commit to DR. Doing so would increase latency of every transaction in prod. Would your users be ok with that?

Finally, assuming you consider the above limitations and leave DR as a manual failover, I’d consider using Distributed AG between prod and DR. This would isolate your prod failover cluster config from that of DR. You should be able to create a clusterless AG in DR and add that to your DAG between prod and DR.

Improve this answer
4
  • "and the witness is in prod" - oh no, the witness is in one of the branches, client side. "Doing so would increase latency of every transaction" - They have many different Applications on these servers. They behave differently, but none are resource intensive. If I understand right, read actions are not effected by Sync AO AG. We tested bulk inserts today with SYNC and ASYNC modes. They have optic fiber connection, so for 1000 Inserts in Sync mode it took 5 seconds. In case I am wrong and in the future we will find an intensive update (Online) app, I will create an alternative Async AG.
    – Oky
    Dec 31, 2021 at 0:33
  • I rather not go the Distributed AG path as the customer wants an automatic Failover
    – Oky
    Dec 31, 2021 at 0:35
  • Sometimes customer aren’t always right, or don’t fully understand the limitations of the technology they’re using. In this case, they may need to be made aware of the limitations, and why that would not be ideal. If they’re ok with the latency, that’s only one challenge. But they do need to understand there will always be added latency due to the way synchronous commit works (commits happen on secondaries before the primary).
    – Brendan McCaffrey
    Dec 31, 2021 at 0:50
  • As for auto failover though, I can’t see a configuration where the failover cluster would know to failover to DR, simply because the WAN link to prod is down. The site being down would only isolate the Prod portion of the cluster from DR, but not produce a failover event. Additionally, this config could trigger false-positive failover events at inopportune times. And if their clients aren’t multi-subnet aware, they’ll have down time until their DNS records age out. That duration can be decreased though by decreasing the TTL.
    – Brendan McCaffrey
    Dec 31, 2021 at 0:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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