2

Does it make sense to think about a failover cluster configuration where less than 50% of all included nodes are standby?

For example instead of setting up 4 two-node clusters where one is active and one is on standby on each cluster - doesn't it make more sense to create 1 cluster with 4 active nodes and two on standby/passive for failover? That makes a saving of 2 nodes in total.

Sure, there is a risk in case multiple nodes fail unplanned and have to share one of the previous standby nodes, but in case you decide to take that risk over provisioning the standby hardware resources?

Looking for "multiple node failover clustering" I couldn't find more on this.

3

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).

  • Thanks for sharing your experience. This helps me making progress. However unfortunately I did not realy understand your rules of thumb "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." because I just don't see what belongs to each other... :( Maybe you could you add it as a tablix or so that helps me follow? TY – Magier Apr 20 '16 at 12:54
  • 1
    So what I am suggesting is that if you build a cluster of 3 servers, you should install 2 SQL Server Failover Clustered Instances and have 1 server as a dedicated failover node. A cluster of 7 servers, you'd install 5 SQL FCIs and have 2 servers as dedicated failover nodes.... and so on. Each FCI would run on its own dedicated server. – Mark Broadbent Apr 20 '16 at 14:57

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.