1

I am doing some testing on SQL 2017 Availability Groups and have the following setup:

  • 1 SQL Clustered instance on a 2-node WSFC in DC1
  • 1 standalone SQL node in DC2

There is 1 AG, containing 3 DBs, and the clustered instance in DC1 is the primary replica.

The premise for this config is if DC1 goes down in its entirety, then we could failover to DC2 and be up and running in x time period. Therefore, I got one of our Infrastructure team to drop the NICs on the clustered nodes to simulate the DC being trashed. I was able to failover to DC2 and the secondary replica without issue.

However, when I asked my colleague to bring the clustered nodes back online, when they came up, they think they're the Primary replica too. I assume this is because that is the state it knew it was last in when it "crashed" and during the failover (via the Failover Wizard), I was not able to tell it to be the secondary.

Therefore, my question is - is there something I can do to make DC1 aware it's now the secondary OR in a scenario like mine, would I trash the AG once DC2 was the Primary and if/when DC1 came back online, I'd recreate the AG and when ready, fail back to DC1?

Hope that makes some sense to someone. Thanks.

Improve this question
1
  • What is your cluster's quorum configuration? Windows clusters require a "quorum" with more than half of eligible voters online in order for the cluster to be up. If you are using "Node Majority" with only 2 nodes as voters, it's impossible to maintain "more than half" because if a node goes down you have "only half" (and not more), which combined with forcing the cluster online, can cause problems.
    – AMtwo
    Dec 17, 2021 at 17:02

1 Answer 1

Reset to default
2

However, when I asked my colleague to bring the clustered nodes back online, when they came up, they think they're the Primary replica too.

That's expected if the nodes coming online are able to achieve quorum and thus form the cluster, and can't talk to the partitioned nodes currently online. The partitioned nodes (in this case your DC2 instance) is primary because it was forced quorum. If the two were able to talk, the forced quorum side would win and it would stay primary with the non-forced side (your DC1) obtaining the latest updates from the previously partitioned side (DC2). This is all at the cluster level.

The other part of this is the SQL level. Since the AG had to be forced online (due to the forced quorum), when the instances connect back to each other the replicas on the non-forced side (your DC1 side) should be paused due to the forced failover at the AG level. Also, due to the FCI in the environment, there will be no automatic failover of the AG.

Therefore, my question is - is there something I can do to make DC1 aware it's now the secondary [...]

If connectivity is correctly restored, this will all be automatic. I want you to be aware that by "testing" this way, the cluster and SQL were running in a split-brain scenario, which is not something that should be done.

Improve this answer
3
  • Thanks for your reply Sean. Would I therefore be correct in thinking (and I did wonder this at the time because currently the "fail back" process is entirely non-existent) that the second part of my test is invalid/inappropriate as we wouldn't bring DC1 back up in this manner anyway if/when we had to?
    – Ric
    Jan 4, 2022 at 12:13
  • @Ric It entirely depends on the scenario, it may be a valid scenario. For example, let's say that (this really happened to me) you have two different ISPs at a datacenter. One ISP is doing work on their lines. A drunk driver hits the other line, now you have no internet connection from DC1. In so far as DC1 knows, everything is fine with DC1, it's DC2 that's having the problem. DC2 says the exact opposite. Since no one can contact DC1 from the outside world (but everything inside still works internally) then it might make sense to fail to DC2. In that case you should gracefully take down DC1.
    – Sean Gallardy
    Jan 4, 2022 at 16:02
  • Wow, that's definitely not a scenario I'd considered! I'll make a note of that one for when we to put the fail-back plan together.
    – Ric
    Jan 4, 2022 at 16:13

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.