0

I created a 2 node AG setup as a POC today and when I tried to do an automatic failover by stopping the sql server service on the primary node, the one in the secondary went into resolving state and it took a long time for it to become a primary AG.

Does it have to do with networking issues? I also want to know if it is a good practice to have a separate network interface for the heartbeat network. When I go to the networks in WFSC I only see "cluster and client" and I assume it also includes the heartbeat.

  • What is your quorum configuration of the WSFC? – AMtwo Apr 20 '17 at 22:46
  • Node and file share majority. It's a 2 node setup – user2923332 Apr 20 '17 at 22:46
  • And is there anything in the cluster logs? – AMtwo Apr 20 '17 at 22:47
  • 1
    What do you consider a long time? Stopping SQL Server is not a reliable way to trigger a failover. It's not considered a fatal error because it assumes you know what you are doing. Nothing like what would happen if the service crashed. – Robert L Davis Apr 20 '17 at 22:50
  • Is there another way to test the automatic failover? – user2923332 Apr 21 '17 at 1:00
1

I created a 2 node AG setup as a POC today and when I tried to do an automatic failover by stopping the sql server service on the primary node ...

Just as others have pointed out in the comments section, this isn't a good way of testing as you've nicely and cleanly taken the service down. I mean, it is a great test if your disaster case is "someone shutdown SQL Server nicely", however outside of that it doesn't help much.

Having a two-node cluster also isn't very helpful for testing HA (or production) as there are just two possible nodes. While you can argue the usefulness of this setup one way or another based on certain factors, you won't really get to experience AGs until you have 3 or more nodes. This is mainly to do with 2-node clusters being special cases since there are so little resources.

... the one in the secondary went into resolving state and it took a long time for it to become a primary AG.

Resolving, in AG terms, means it is trying to find and connect to the primary. Since you've shut it down nicely, it'll take some time (assuming the cluster has quorum and the failover policy has been met) to fail.

Does it have to do with networking issues?

Nope.

I also want to know if it is a good practice to have a separate network interface for the heartbeat network.

No such thing as a "heartbeat" network since Windows Server 2008+. The cluster will decide which route to take.

What do you really want?

If you want to see how long automatic failover takes then you'll first need to figure out what scenarios you'd like to test. Here are some examples:

  1. OS Disk fails
  2. Cluster partitioning
  3. Data disk fails
  4. Out of memory
  5. Bugcheck
  6. log disk fails
  7. Overwhelmed disk
  8. slow networking
  9. windows patching
  10. sql patching
  11. server power failure

You can add more, but for each possible issue there will be a different amount of time it takes.

The amount of time is roughly:

total = Detection + Mitigation + Redo Queue drain + Undo

Each of the above will have different detection and mitigation times, but the redo and undo should be roughly the same for each test. So, at the end of the day, really you're testing detection times.

If you only care about how long it takes to do a failover, then run: ALTER AVAILABILIY GROUP MyAG FAILOVER on the new primary. Time it. That's how long it takes.

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.