locked
Failover clusters and SQL Server standard edition query RRS feed

  • Question

  • Hello, 

    Could anyone help me with, probably the simple, question about failover cluster instances (FC/FCI) please?

    Imagine I have 10 current instances of SQL Server on 10 separate virtual machines (so 1 instance per machine). These are very busy machines and 'fairly resource intensive' so I would like to move them to a FC so that if the primary server they fails, the failover server picks up. 

    We are currently licensed for SQL Server Standard 2016 core licensing. On standard edition I believe you can only create a single two node FCI?? I'm not sure I want to put 10 instances on a single FC because of issues like shared storage, competition for machine resources. So, what is the alternative? Can/should I create multiple FCIs? For example 5 FCIs - with two instances in each of the FCIs?

    Have I got the right idea how this works?

    Thanks v much, 

    OC


    Friday, October 20, 2017 2:31 PM

Answers

  • This is really an "it depends" case. It depends on

    • Availability of hardware resources on your WSFC. You can move all 10 instances on to a well-provisioned single-server node and just have one standby node
    • Availability of skilled operations staff to properly handle a failover of one of the FCIs. In almost all cases, all of the FCIs will automatically failover to the other node. But there can be some cases where the Flexible Failover Policy in the SQL Server FCI can kick in, forcing one of the FCIs to be in the standby node while the rest are on the active node
    • Availability of operational policies and procedures to handle a failover
    • Risk acceptance. Your point on "putting all eggs in one basket" is a real risk. But if you define your recover objectives and service level agreements, you can define your operational procedures, HA/DR solutions and skillset of your operations staff to meet those requirements. Note that a 2-node SQL Server FCI is mainly for HA, not DR.

    There really is no "best way" other than what is best for your organization given the recovery objectives, service level agreements and the other considerations I mentioned above. Besides, you will be the one maintaining this solution down the road.


    Edwin M Sarmiento Microsoft Data Platform MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    • Marked as answer by orange-crush Sunday, October 22, 2017 7:06 PM
    Saturday, October 21, 2017 3:30 PM
  • > I presume there is nothing stopping sql server standard users configuring a WSFC cluster of 10 computers and creating 5 pairs of two-node failover cluster instances?

    That is correct.


    Edwin M Sarmiento Microsoft Data Platform MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    • Marked as answer by orange-crush Sunday, October 22, 2017 7:06 PM
    Sunday, October 22, 2017 4:11 AM

All replies

  • You can have multiple FCIs in a 2-node Windows Server Failover Cluster. Just make sure that you allocate appropriate CPU and memory resources. Depending on the version of SQL Server that you are using, both CPU and memory resources are capped in Standard Edition.

    You also need to have the proper policies in place in case one of the FCI fails over to a node that already has majority of the load.

    You can probably have a 4-node WSFC and install a 2-node FCI on some pairs so you can distribute the load. An FCI can only have 2 of the nodes in the WSFC as Possible Owners.


    Edwin M Sarmiento Microsoft Data Platform MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Friday, October 20, 2017 7:24 PM
  • Hi Edwin,

    Thanks for your reply. I’m still not sure I 100% understand - so can I just confirm a couple of things please?

    1) Re: your first point - are you suggesting that I move all 10 instances on to a well-provisioned single server node (A) that can be failed over to a single well-provisioned backup server node (B)? If so, I think this would be a hard sell to our management due to worries about ‘putting all our eggs in one basket’.

    2) RE: your second point - in principle I prefer the sound of ‘spreading the risk’ by configuring a WSFC of 10 machines and dividing these into five pairs of two-node failover cluster instances (with each FCI having 2 sql instances (databases) installed on them). Is this typical practice?

    Essentially I guess I need to know what is ‘typically the best way’ of managing High availability in an environment with multiple sql server-based applications out of the two approaches above.

    Thanks a lot

    OC

    Saturday, October 21, 2017 7:04 AM
  • This is really an "it depends" case. It depends on

    • Availability of hardware resources on your WSFC. You can move all 10 instances on to a well-provisioned single-server node and just have one standby node
    • Availability of skilled operations staff to properly handle a failover of one of the FCIs. In almost all cases, all of the FCIs will automatically failover to the other node. But there can be some cases where the Flexible Failover Policy in the SQL Server FCI can kick in, forcing one of the FCIs to be in the standby node while the rest are on the active node
    • Availability of operational policies and procedures to handle a failover
    • Risk acceptance. Your point on "putting all eggs in one basket" is a real risk. But if you define your recover objectives and service level agreements, you can define your operational procedures, HA/DR solutions and skillset of your operations staff to meet those requirements. Note that a 2-node SQL Server FCI is mainly for HA, not DR.

    There really is no "best way" other than what is best for your organization given the recovery objectives, service level agreements and the other considerations I mentioned above. Besides, you will be the one maintaining this solution down the road.


    Edwin M Sarmiento Microsoft Data Platform MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    • Marked as answer by orange-crush Sunday, October 22, 2017 7:06 PM
    Saturday, October 21, 2017 3:30 PM
  • Hi Edwin, 

    Thanks very much for your thoughtful response - I very much concur with your perspective. 

    In some respects I concur too much... the organisation I work for is relatively young (skills and process mapping-wise) which makes me feel like too much dependence on a single two-node failover cluster instance is 'too risky' at the moment. 

    I presume there is nothing stopping sql server standard users configuring a WSFC cluster of 10 computers and creating 5 pairs of two-node failover cluster instances? 

    Thanks again, 

    OC

    Saturday, October 21, 2017 5:17 PM
  • > I presume there is nothing stopping sql server standard users configuring a WSFC cluster of 10 computers and creating 5 pairs of two-node failover cluster instances?

    That is correct.


    Edwin M Sarmiento Microsoft Data Platform MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    • Marked as answer by orange-crush Sunday, October 22, 2017 7:06 PM
    Sunday, October 22, 2017 4:11 AM
  • Thanks very much Edwin. Much appreciated.
    Sunday, October 22, 2017 7:07 PM