0

vSphere and Hyper-v are meant to complement AGs by adding additional HA. However, if I lose a VM hosting a primary sync replica, wouldn't vSphere/Hyper-v both migrate the failed VM to another host at the sane time as SQL Server automatically fails over to the Sync secondary replica?

Questions Who would alway win? Can this screw up SQL? Wouldn't it be easier to just use SQL for HA (and DR) and migrate the failed VM over to a surviving host after SQL has switched over to a secondary replica?

Any comments/experience appreciated.

0

It depends on what you mean by "lose a VM." If you have a hardware failure and the VM is effectively powered off, a failover will occur if the failed VM was the primary. When the failed server is moved and rebooted, it will join the cluster before trying to become primary again, and when it does it will discover that the other node is primary, and it then resolves its own state to secondary.

If there is no power off situation, but the VM disappears from the network for several seconds--this can be bad as it can cause failover to occur, but since the VM that is moving is effectively paused, it does rejoin the cluster to resolve its role--it just picks up where it left off. The cluster will quickly detect that there are two primaries and the VM that moved will be forced into a secondary role, but if there was any data committed from the transaction logs, you can end up with the databases out of sync. If this happens, you have to remove the replica database that's out of sync, restore from backup, etc., and then join it back in. See How to remove a secondary database from an availability group and rejoin it.

Another thing with VMs that can result in this mess is VM snapshot backups, or VMotioning--anything that can cause the server to be paused. For servers with 100 GB storage--usually not a problem. But when you start getting into terrabytes, the server can be paused for several seconds to do these operations. So you may need to adjust your backup strategy to accomodate--see this for more.

2
  • Manhy Thanks Tony, I agree with your statement and in an ideal world, it may be best to use Hyper-V HA and AG's. However, for simplicity and reliabaility (which is want you need for HA, I thuink it would be better to simply let SQL Server handle 'failures' and perform the failover to the secondary replica. It means we have to migrate the failed VM to another node manually, but I think the simplicity/reliability/easier troubleshooting more than makes up for this... Dec 5 '19 at 17:41
  • @GaryWright I agree--if you only have one mechanism for failover that certainly makes things easier to unwind when there's a problem. My conclusion based on over 20 years working with clusters is that they cause more downtime than they prevent due to the extra layers of software and hardware that add more complexity. And it's not always Microsoft issues--when network admins put in new equipment and disable gratuitous ARP, the result is an outage caused by the requirements of the HA setup. Dec 5 '19 at 18:08

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.