We are using an AlwaysOn Availability Group in SQL 2017 on Windows Server 2016 which consists of two servers on our primary site and one DR server on a remote site.

We recently did an internal IT DR test as proof-of-concept during which the rest of the company continued using the primary site. We isolated our remote DR network to simulate a catastrophic failure of our main site and tested the DR site to make sure we could bring it up and use it successfully. The test went very well up to this point, but we had a major issue when setting things back to normal.

Because the main site was still in use during the test, my main concern was to ensure that the live database would not be corrupted by the DR database which had also been used during the test, so I dropped the DR database before we opened the VPN and I expected the main site to start automatic seeding back to the DR site. But, because I had to force failover on the DR site, as soon as the network link was re-established, the primary site detected that the DR server had been started with the Force Quorum switch ( /fq) and it basically shut down the two live servers. I could not force failover back to the Live servers and had to rebuild the cluster.

Question: How do I prevent the Live servers from detecting that a forced failover was performed on the DR site?

I did consider shutting down the DR server before opening the network link, but am still not sure what would happen when it’s brought online. In any case, the remote database would need reseeding from Live.

  • A few questions: 1. Did you intentionally start the DR server with the Force Quorum switch? Or did that somehow happen on its own? 2. What exactly did you do to perform the forced failover? Did you use ALTER AVAILABILITY GROUP MyAg FAILOVER; ? Or something else? 3. After you did the manual failover, did you make sure that the availability group had green statuses in the AG dashboard? May 26 '19 at 12:41
  • 1 of 2 We isolated the DR server and then intentionally forced failover. At that point, the DR server was acting as though there was no cluster and no quorum so it had to be forced to start as the primary with the Force Quorum switch which I did through the Failover Cluster Manager. Once the server was up, the status of the AG was "Resolving" with the other servers showing as unavailable and I used the AG dashboard to failover with potential data loss. After failing over, the status was showing all green on the DR server and down on the other servers.
    – FreedToFly
    May 26 '19 at 15:10
  • 2 of 2 I was also monitoring the Live servers throughout which were both up and being used as normal. They were showing the AG was good apart from the DR server showing as being down. This behaviour was as expected and was part of the test.
    – FreedToFly
    May 26 '19 at 15:10
  • Since you have to reseed your database(s) anyway, I would evict your DR replica, open the network link, then re-add the replica and reseed the database(s). This seems like the lowest risk approach in regards to experiencing an unwanted failover out outage due to quorum loss. May 26 '19 at 15:45
  • Thanks John, evicting the DR replica would make sense. Is that the only way of doing it?
    – FreedToFly
    May 26 '19 at 17:57

Assuming the use of VMs, take a snapshot of the DR server after the source site is disconnected. I might recommend shutting down the server first just so the snapshot is in a good, clean state. After the DR testing is done, shut down the DR server and then revert it to the snapshot. This is easy and cheap. The only downsides are that the source site is unprotected during the test, and the DR node may be a little slower due to the snapshot depending on your storage infrastructure. Oh, and your primary will have to have enough disk space to queue database replication until the sites are reconnected.

Use a Clone
Assuming that you're using VMs and have adequate disk space, clone the DR node, shut down the original DR node, and do the testing on the clone. When done, delete the clone and bring the original DR node online. This solves two problems: 1) You don't have to resynchronize the entire database(s), and 2) You don't have to evict/join cluster nodes.

The downside of this approach is that during the time of the testing, the primary server has to queue all of the data, so transaction logs may get quite large. In the test you performed, this was occurring so you apparently had enough disk space for your logs that it wasn't a problem.

Use Even More Clones
Some organizations create a "bubble" network at the DR site that cannot communicate with the production DR network. Then infrastructure servers (domain controllers, DNS, etc.) are cloned and the clones are brought online in the bubble. Then the other servers that need to be part of the DR test are cloned into the bubble. This allows you to keep your real DR servers online and functional during the DR test. Other than truly failing over everything, this is about the safest, because what happens if the primary site gets destroyed 5 hours after the DR test starts? If you've disconnected the sites, you lose 5 hours of data.

The downside of this, of course, is that it can become a massive undertaking and require a lot of staff time, hardware, etc.

Evict and Rejoin
As mentioned in the comments above, you can evict the DR node from the cluster before the DR site is reconnected, and then rejoin it back to the cluster and add the node back to the availability group. This is fairly simple and requires the least hardware, but it does have the downside of leaving your primary site unprotected during the time of the DR test.

  • Hi Tony, thanks for your detailed answer. I like the idea of cloning the DR Server and using that to test. Log space isn’t an issue, but I do monitor it. However, as you say, this would leave the primary site vulnerable to possible data loss during the test. The bubble network is also an excellent idea and I will discuss that with respect to the infrastructure it will require. This solution is clearly preferable if the resources can be made available. Many thanks!
    – FreedToFly
    May 31 '19 at 21:47

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.