I have a database server setup where there are three SQL Server database instances set up on three nodes of a Windows Server Failover Cluster (WSFC).

The sysadmin, who is responsible for the Windows Cluster, is accusing me of manually setting the primary cluster node as something other than what it is.

Can failing Availability Groups over to secondary replicas, to include during reboots when they automatically take over, also cause the underlying Windows Server Failover Cluster to switch the primary node and not switch it back?

This has the potential to become a distracting issue, keeping me from getting work done. I'm hoping to be pointed in the right direction to keep this from happening again.


Converted from comments:

Are you referring to the core cluster resources? If yes, the answer is no, failover of the cluster role for the availability group would not impact the core cluster resources. The core cluster resources will of course fail over to another node if the current owner is rebooted or loses connectivity, but that behavior is not impacted by what anything that happens with the AG resources. – Tony Hinkle

  • Thank you. What about a reboot of the primary node? When the primary node reboots, the cluster fails over to one of the secondaries because that's what it's supposed to do. Where would I properly ask about how to ensure that it fails back over to the primary when it comes up? – J.D. Walker Dec 3 '19 at 14:39

In order to have the failover cluster role fall-back to the "primary" node, you need to:

  1. Configure the primary node. Right-click the cluster role. Click "Properties". On the "General" tab, configure the "Preferred Owners" such that the desired primary node is the only "preferred" owner.

  2. Switch to the "Failover" tab, and choose the option for "Allow Fallback", and set the desired options about when to fallback.

  • SQL Server overwrites anything done in the Failover Cluster Manager, though. – J.D. Walker Dec 3 '19 at 17:17
  • Without documentation showing that, you're going to have a hard time convincing me. I manage about 40 failover clusters. – Max Vernon Dec 3 '19 at 19:25
  • I've got SQL Server experts telling me that SQL Server overwrites anything done with an availability group. It isn't that A is no longer primary in the WSFC after a reboot that's got my sysadmin in a state, it's that the AGs (which he can see in the WSFC) don't automatically fail back over to A. Compromise has been achieved, however. – J.D. Walker Dec 4 '19 at 2:29

Compromise achieved, after a wasted morning. King Sysadmin decided that nothing could ever be on C as a primary, not even for a moment. Even my compromise of automatic jobs that would email all of us was refused.

Having said that, I set C as manual failover, so now when A reboots, all AGs (currently five of them) fail over to B. He's okay with me manually failing back over to A when making sure that the databases are okay.

  • What's the point of having C as part of the cluster, then? – Max Vernon Dec 3 '19 at 19:24
  • It's a synchronous replica, it just has manual failover, so if A fails and B fails, I have to manually fail the AGs over to C. – J.D. Walker Dec 4 '19 at 2:28

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.