Help with SQL Clustering - complicated example

  • Hi all,

    I have been tasked to consolidate global office SQL Servers into a new data centre and come up with a HA solution. Bear with me, as we have had restrictions placed on us, it's not a money pit and we have 'only' two ESX servers ,each with 32 cores which can be licensed with SQL Ent. Sounds good but not when you look at the servers globally to be consolidated into a VM environment

    Anyway, more to the point, is the design below possible. It has:

    Two ESX Servers will join a single WSFC and act as hosts for the following:

    N+ SQL Server Failover Instances (by geo-location isolation) will be configured, each having a two node Guest Failover Cluster with both nodes running as virtual machines and both nodes on different physical hosts. Combined with dedicated Shared storage solutions and identical VM hardware.

    Something like in the attachment

    summary

    •A single Windows Server Failover Cluster (WSFC) at Host ESX level (Operating system level)

    •Two ESX Hosts in the above single cluster

    •One or more failover cluster instances (FCI). Total = 8 FCIs

    •Two Nodes per FCI (Total = 8 FCIs x 2 nodes per FCI = 16 Nodes)

    So, question, is this possible, can i failover each FCI on it's own without affecting the other FCI or associated nodes? The reason for multiple FCI is for individual failure and at a geo location basis, and patching SQLABDPRIMO1\SQLINSTANCE01 would NOT bring down SQLSHARPROM05\SQLINSTANCE05

    Additionally can the 8 FCIs and 16 Nodes be sat on one single WSFC, what happens on failover, do all fail or is down to individual FCI and their node level?

    The worst thing...we don’t have any dev environment, please don’t shoot the messenger i know and disagree but it's one of those moments where we 'just have to do' and then probably made redundant

  • slk55guy (2/12/2015)


    summary

    •A single Windows Server Failover Cluster (WSFC) at Host ESX level (Operating system level)

    •Two ESX Hosts in the above single cluster

    It won't be a WSFC, the 2 ESX hosts will be in a VMware HA cluster

    slk55guy (2/12/2015)


    •One or more failover cluster instances (FCI). Total = 8 FCIs

    Just remember that the total number of storage paths presented to the ESX hosts could exceed the maximum supported by ESX, depending on how your storage is configured

    slk55guy (2/12/2015)


    •Two Nodes per FCI (Total = 8 FCIs x 2 nodes per FCI = 16 Nodes)

    Starting to sound over engineered, glad it's not my budget being spent

    slk55guy (2/12/2015)


    So, question, is this possible, can i failover each FCI on it's own without affecting the other FCI or associated nodes?

    All nodes could be part of the same WSFC and depending on how you install the FCIs you can fail each over independently without affecting others.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks Perry,

    slk55guy (2/12/2015)

    --------------------------------------------------------------------------------

    • Two Nodes per FCI (Total = 8 FCIs x 2 nodes per FCI = 16 Nodes)

    Starting to sound over engineered, glad it's not my budget being spent

    The budget is spent on the two ESX Servers, with all cores licensed with SQL 2014 Ent, and the rest of the nodes will be VM machines, so apart from our time and the licence cost of the physical servers it shouldn't be that much and believe me, based upon the number of SQL Server Ent editions that have been installed worldwide, we will be saving well over 1.5 million!

    Anyway, the reason behind the 8 FCIs and 16 nodes is to have (hate the terminology) an active\passive arrangement for each FCI. Additionally each FCI will be allocated specific host ESX processors and reserved memory allocations. Further each FCI will serve for a different geographic region i.e Far East, Europe, USA and I wanted to be able to patch these separately by failing over gracefully on each FCI without affecting other regions.

    I do plan to have both ESX hosts working in an ‘active’ manner and it’s open to change based upon any suggestions but the main thing is whether this design works and that if Node 1 went down FCIPRIM01 and failed over to FCIPRIM01\Node2, it wouldn’t failover FCIPRIM09\Node09 to!

    If we could consolidate this further we would but there are hundreds of databases, some tier 1, others tier 2 or 3, split by location and also the issue of patching the servers, we dont want to bring down the East for a Europe piece of patching work and so forth

    slk55guy (2/12/2015)

    --------------------------------------------------------------------------------

    So, question, is this possible, can i failover each FCI on it's own without affecting the other FCI or associated nodes?

    All nodes could be part of the same WSFC and depending on how you install the FCIs you can fail each over independently without affecting others.

    Can you explain please? My assumption and hence the wee post was that they 'could' all be configured under one WSFC but still failover independently , is this not the best scenario?

  • FCIs are created as completely separate cluster roles and failover independently.

    It may pay you to read through my AlwaysOn stairway starting at this link[/url] to get a better understanding of WSFC's and FCI's

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (2/12/2015)


    FCIs are created as completely separate cluster roles and failover independently.

    It may pay you to read through my AlwaysOn stairway starting at this link[/url] to get a better understanding of WSFC's and FCI's

    Yes I understand that. The question is this

    Can I have multiple 2 node FCIs running under ONE WSFC with independent failover?

    Also, some whitepapers i have read have stated to install the WSFC (in a VM environment) at the physical ESX host level and the nodes as VMs

    See my attachment in original post as an example layout.

    I think I've probably now got muddled up a bit, and since we dont have a test environment for this, it makes it a damn site harder:w00t:

  • Actually having had a re-think, perhaps, as you say, i would utilise the VM HA clustering. My wording needs amending and actually I think I need to have a single WSFC for each SQL FCI\Cluster

  • slk55guy (2/12/2015)


    Can I have multiple 2 node FCIs running under ONE WSFC with independent failover?

    Yes, again each clustered role is totally independent

    slk55guy (2/12/2015)


    Also, some whitepapers i have read have stated to install the WSFC (in a VM environment) at the physical ESX host level and the nodes as VMs.

    The WSFC is the nodes. The nodes joined together form a WSFC, again might pay you to read my stairway

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • slk55guy (2/12/2015)


    Actually having had a re-think, perhaps, as you say, i would utilise the VM HA clustering.

    A WSFC does not exist until you join a set of Windows nodes together in a clustered relationship. Read my stairway to understand this.

    slk55guy (2/12/2015)


    actually I think I need to have a single WSFC for each SQL FCI\Cluster

    No, maybe you actually don't. Clustering the nodes as one large WSFC will make them easier to manage and cut down on the number of Virtual IPs and networknames required

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply