9

I was reviewing our company DR procedures and when I looked online for solutions to an Always On Cluster losing quorum, to compare to. I was three pages into google results before finding the first SE post on the subject Clustering vs. transactional replication vs. availability groups which only lightly touches on the subject of lost quorum.

While everyone agrees the losing quorum is bad, and there are some suggestions for decreasing the potential, it can still happen. I am looking for a good peer reviewed answer to the best path to recovery from an Always On cluster loss of quorum.

Improve this question
  • If not on it already, I recommend try to get on Windows Server 2012 R2. The dynamic quorum, dynamic witness, and tie breaker features allow you to achieve "last man standing" in a lot of cases. sqlha.com/2013/06/06/… – SQL Hammer Jan 13 '16 at 19:48
11

AGs are based on Windows Clustering. The WSFC procedures for quorum loss apply.

Once the WSFC is running, you can then force AG, if needed. Perform a Forced Manual Failover of an Availability Group:

After forcing quorum on the WSFC cluster (forced quorum), you need to force failover each availability group (with possible data loss). Forcing failover is required because the real state of the WSFC cluster values might have been lost. However, you can avoid data loss, if are able to force failover on the server instance that was hosting the replica that was the primary replica before you forced quorum or to a secondary replica that was synchronized before you forced quorum. For more information, see Potential Ways to Avoid Data Loss After Quorum is Forced.

Improve this answer
  • How does this work with the new AG setup without a cluster? Is there still a Quorum? – Shaulinator May 25 '18 at 13:13
6

What to do when your AlwaysOn cluster loses quorum?

I have been into this situation especially with Multi-subnet clustering spanning different countries (NY-LD-HK).

How to avoid Quorum Loss in a multi-subnet cluster ?

  • Change the cluster default setting to a more relaxed monitoring state especially Cluster Heartbeat settings using CrossSubnetDelay, or CrossSubnetThreshold property by this hotfix.
  • AG uses WSFC which inturn uses quorum based approach for determining cluster health. Make sure you proper choose and configure the quorum. This blog post dives deeper into Quorum vote configuration for AlwaysON

  • Things change in Windows server 2016 with the introduction of site aware clusters and cloud witness.

    Nodes in stretched clusters can now be grouped based on their physical location (site). Cluster site-awareness enhances key operations during the cluster lifecycle such as failover behavior, placement policies, heartbeating between the nodes and quorum behavior.

    Cloud Witness is a new type of Failover Cluster quorum witness that leverages Microsoft Azure as the arbitration point. It uses Microsoft Azure Blob Storage to read/write a blob file which is then used as an arbitration point in case of split-brain resolution.

What to do when Quorum is lost ?

  • If the cluster goes down due to an unplanned outage/disaster, then manual intervention is required. Either a windows admin or cluster admin has to manually force the quorum (linking back to @Remus's answer as that covers this point) and bring the surviving nodes online.

As always, to do a Root Cause Analysis (RCA), gather your windows cluster logs, for AlwaysON RCA - use SQL Server Failover Cluster Diagnostic Logs. These files in the SQL Server Log directory have the following format: <HOSTNAME>_<INSTANCENAME>_SQLDIAG_X_XXXXXXXXX.xel.

Improve this answer
0

Once I've been involved in an outage where our mirrored servers lost connectivity. One of the things to worry about is making sure your applications are pointed to a single instance. In a network outage you can have all the nodes of an Always On cluster up but unable to communicate with each other. You force a fail over to a secondary and then as long as there is an outage you can have two primary nodes since the original primary won't know about the forced fail over.

Depending on the locations of your application servers, their configuration and their ability to reach a SQL server, then in theory you can have two nodes believing they are primary and having data changed at the same time. Once you fix your network issues and the nodes resume connectivity all the data changed on the original primary will be overwritten from the node where the fail-over was forced to. This can result in the loss of critical data.

I've seen this situation once with SQL 2005 and mirroring. And we decided to not to force the fail over and let it stay unreachable. Reason being that in the worst case if we had to back up and restore to restart mirroring, then it would be a 2 day process for us with risks of the transaction log becoming full and not being able to expand the disk on which it sat.

Improve this answer
  • Mirrroring and AlwaysOn are different. With AlwaysOn you should (hopefully) be pointing to a listener with MultiSubnetFailover=True – James Jenkins May 25 '18 at 15:10
  • I know that but it's possible to have servers geographically separated with a network outage where some apps can reach some servers but not others. And there are java drivers being used that don't support MultiSubnetFailover=True. Probably other third party apps as well. I've seen some people refuse to configure their connection strings for it. Even then you can force a failover without thinking it through for your exact situation and end up with two writable servers not able to communicate. And with applications writing to both because of their ability to communicate across sites. – Alen May 25 '18 at 15:16
  • P.S. I've seen a situation where we could not communicate to our primary site less than a mile away, but connectivity to our DR site 100 miles away worked just fine. – Alen May 25 '18 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.