0

Assuming I have AG in a 4 node cluster, with 2 nodes on primary site and 2 nodes on DR site, 1 cloud witness. Failover mode is manual.

Now for some reason, suppose primary site network goes down, specifically I cannot connect to the primary data center.

Case 1: primary has quorum as it is still able to communicate to cloud witness. As there is some network issue at primary site, the SQL is still running on primary and thinks it's the primary.

Case 2: primary cannot communicate to cloud witness and has lost quorum. Dynamic witness and dynamic quorum may kick in.

Suppose I now connect to one of the DR nodes and invoke a forced manual failover.

SQL on DR site will think that it's the primary as I invoked manual failover. Technically this is a split cluster scenario.

Split scenario in case 1 is obvious. In case 2 it could be due to dynamic witness and dynamic quorum kicking in.

When the network on primary gets re-established, what do I need to do to handle this scenario? Because if what I'm thinking is right, then both the sites will think it has the primary SQL server.

3
  • In your example, the primary site will not stay online. The cluster has 5 votes (4 nodes and the cloud witness), and if it loses network connectivity then it only has visibility over 2/5 votes - i.e. it cannot form quorum.
    – Dan
    Commented Mar 25 at 3:01
  • 1. For my sanity, assume primary site is able to communicate to the witness and therefore has quorum. Can you elaborate on this scenario please.
    – variable
    Commented Mar 25 at 5:01
  • 2. Assuming primary site is unable to communicate to witness and therefore lost quorum, won't the concept of dynamic witness and dynamic quorum kick in to ensure man standing?
    – variable
    Commented Mar 25 at 5:02

1 Answer 1

2

Suppose I now connect to one of the DR nodes and invoke a forced manual failover. [...] When the network on primary gets re-established, what do I need to do to handle this scenario?

Case 1: primary has quorum as it is still able to communicate to cloud witness. As there is some network issue at primary site, the SQL is still running on primary and thinks it's the primary.

You're split brain yourself on purpose. Since the DR side won't have quorum, it would need to be forced, which means when these nodes all talk to each other again the original primary side will see the forced quorum and the primary side will immediately go down and restart to sync with the new cluster databases from the DR side which was forced. This will have the effect of taking the AG down on the primary side. The problem is that now you have the potential (although it's almost a guarantee) that the original primary has data that the DR side doesn't due to the split brain you've forced.

This means you'll need to manually reconcile the data and put it into the DR database, from the original primary database. No one can tell you the operations needed as it'll be specific to your database and company.

Once the data has been (or decided not to be) reconciled, the replicas will most likely need reinitialized because the recovery forks are too far apart and the last common fork will be from the date and time the issue occurred. Since force failover was used, the databases will need to have data movement resumed or be removed completely in order to automatically seed (or you can manually seed). You won't be able to magically to log restores or anything here as the databases have forked in their recovery paths.

Case 2: primary cannot communicate to cloud witness and has lost quorum. Dynamic witness and dynamic quorum may kick in.

The primary side won't have the votes, it won't stay up. If the DR side is able to gain access to the witness then it should have the votes and quorum should be achieved. If automatic failover is set for one replica in the DR location then this should automatically fail over, if not it'll need to be either failed over normally (if it's a synchronous commit partner) or forced (asynchronous commit partner). You'll then need to resume data movement on each replica still alive on the DR side. Depending on how long their are out of communications you may need to drop the replicas on the original primary side.

Once the AG is forced over, when communications return the AG will see if it's been forced or not. If it has, all communications will the databases on each replica on the original primary side assuming the replicas weren't dropped. If they were, you'll need to add them back into the AG.

Your Answer

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

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