Sql-server – SQL Server Mirroring kicks in before cluster has time to fail over

Clustering is complex, and there are lots of moving parts (no pun intended). Let me try to break this down into more manageable chunks:

From a terminology perspective, there's your Windows Server Failover Cluster (WSFC), and your SQL Server Failover Cluster Instances (FCI). I try to avoid saying "Cluster" and use these acronyms to avoid ambiguity.

Quorum: The quorum is the number of votes necessary to transact business on your WSFC. Depending on your WSFC configuration, voters can be nodes (servers), a drive, or a file share. You need more than 50% of your votes in order for the WSFC to be online. If you lose 50% or more of your voters, then the WSFC and all clustered services (including your FCI) will go offline and not come back until you have (or force) quorum.

In your configuration, you have two nodes, and one file share for a total of three votes. Any one of those voters can go offline. When you lost the file share, you still had two nodes online, so your WSFC and all clustered services stayed online.

Cluster Owner/Host Server: When you say that "Node2 was now specified as the active node by Windows", I suspect you are referring to the "Current Host Server" for the cluster. So what is that?

Your WSFC has a network name and an IP address. That name & IP has to be tied to a machine that is part of your cluster. More specifically, it can be tied to any one machine in your cluster. This is part of your WSFC, but not your FCI.

In your scenario, you have three FCIs on a two-node WSFC. It would be a perfectly valid to have one FCI on Node1, and two FCIs on Node2. And the "Current Host Server" for the WSFC could be either node. SQL Server won't care.

So what happened: As you said, there were no adverse effects on the databases. I'd expect that, because SQL Server isn't tied to that WSFC host server. I don't think I wouldn't have expected the host server to move when the file share failed--but I'd let your Windows guys dig into that more. From a SQL perspective, everything worked as expected.

Firstly, having 4 separate 2 node clusters with one FCI deployment in each seems to me to be a little extreme. The second model you describe does not really make sense to me. Instead of either deployment I would suggest running 2 Clusters of 4 nodes (you would need a witness for each) and consider one node in each as a dedicated failover node (i.e. that you install 3 SQL FCIs to each cluster).

Also you should try to deploy on Windows 2012 R2 because of its dynamic quorum and dynamic witness capabilities (2012 only had dynamic quorum) -which will help to maintain maximum uptime upon node failure.

As you will be aware, the biggest issue with not having enough dedicated failover nodes is that ultimately you are exposed to SQL co-existence considerations (such as setting the correct max-memory upon failover if shared with other SQL FCIs amongst other things). Furthermore in truly Highly Available environments you probably would not want to fail-back post failover (otherwise it is a further addition of down-time), so sharing dedicated failover nodes would probably necessitate failback of one instance (assuming two had failed over).

However, that said, all system deployments are a compromise and provisioning too many redundant failover nodes does not make sense and it is important to find the right balance between HA requirement and cost effectiveness.

As a general rule of thumb you should consider an approximate 1 dedicated (failover node) per (2 SQL FCI) 3 node cluster, 2 dedicated per (5 FCI) 7 node cluster and 3 dedicated per (8 FCI) 11 node cluster. These figures are generous approximations from nearly 20 years personal experience of SQL Failover Clustering but obviously there are many other factors that can affect your design strategy.

p.s. There are some technical Clustering reasons why it makes sense not to deploy a huge number of cluster nodes so I would probably not go beyond a 5 node cluster anyway (and in that case I would probably still be happy with 1 dedicated failover node).