multiple instance sql cluster with different windows configuration

  • We have a two node cluster. In order to utilize the max resources we are planning to add more instances to this cluster by adding few more nodes. Let us assume--

    server A - cluster node--100 GB ram and 24 core cpu (active)

    server B - cluster node--100 GB ram and 24 core cpu( active)

    server C - new node -- 50 GB ram and 12 core cpu( passive)

    server D - new node -- 50 GB ram and 12 core cpu (passive)

    What we are planning to do is to run sql instance of server C and D on server B and make use of the resources. When server A fails we need it to failover to server B and if this happens we can manually failover the two instances running on server B to node C and node D respective. Is this possible? I know in cluster all the nodes should have same resources. Need suggestions?

  • Not sure. Wait for Perry's reply.

  • SQL Cluster 2008 need not the exact same hardware but rather compatible hardware. I'm currently running a 2 node cluster with Node A (64proc, 256GB) the primary and Node B (24proc, 128GB) as the passive. Now where this becomes a problem is if Node A crashes can Node B handle the load? In our environment it can but queries do become slower and its okay for us because it is only temporary until we get Node A back up.

    It all depends on your load and can the weakest link (Node in this case) hold all your resources?

    -king

  • deep_kkumar (4/1/2013)


    We have a two node cluster. In order to utilize the max resources we are planning to add more instances to this cluster by adding few more nodes. Let us assume--

    server A - cluster node--100 GB ram and 24 core cpu (active)

    server B - cluster node--100 GB ram and 24 core cpu( active)

    server C - new node -- 50 GB ram and 12 core cpu( passive)

    server D - new node -- 50 GB ram and 12 core cpu (passive)

    What we are planning to do is to run sql instance of server C and D on server B and make use of the resources. When server A fails we need it to failover to server B and if this happens we can manually failover the two instances running on server B to node C and node D respective. Is this possible? I know in cluster all the nodes should have same resources. Need suggestions?

    In theory, yes all nodes should have the same configuration. This is especially important to the following

    CPU family

    Base hardware (i.e. server model)

    Patch level

    Network configuration (including the NIC type)

    Now, in my current role we actually have a 6 node cluster that has 2 "high spec" nodes and 4 lower spec nodes (but all 4 the same). The key to remember here is how the clustered instances will be distributed, just because you install a clustered instance into a multi node cluster it doesn't mean you should then install it across all the cluster nodes.

    Only install the instance across nodes where you want the resources to be highly available.

    So, having 2 nodes that are slightly lower spec does not present an issue but you should abide by the above regarding CPU family, network configuration and more importantly the patch level. The software version and level are the most important here.

    muthyala_51 (4/2/2013)


    Not sure. Wait for Perry's reply.

    Lol, thank you

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

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

  • Thanks Sailorking and Perry!

  • Dropping my 2 cents here.

    This may not be true for 2+ node Cluster, but I always try to put more RAM on server than the total amount of SQL instances I have if, hypothetical scenario, I need to run those on one node only.

    For instance:

    RAM on Server A: X GB

    RAM on Server B: Y GB

    So... RAM Instance C + RAM Instance D < X + Y

    That's in order to avoid the situation that two instances have more allocated RAM than actual server's RAM.

  • Perry,

    I was not able to follow this statement, can you please elaborate--

    The key to remember here is how the clustered instances will be distributed, just because you install a clustered instance into a multi node cluster it doesn't mean you should then install it across all the cluster nodes.

    Only install the instance across nodes where you want the resources to be highly available.

  • muthyala_51 (4/4/2013)


    Perry,

    I was not able to follow this statement, can you please elaborate--

    The key to remember here is how the clustered instances will be distributed, just because you install a clustered instance into a multi node cluster it doesn't mean you should then install it across all the cluster nodes.

    Only install the instance across nodes where you want the resources to be highly available.

    Say you have a cluster with 4 nodes ServerA, ServerB, ServerC and ServerD. You install a clustered instance to ServerA, you don't necessarily install it to the remaining 3 nodes.

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

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

  • What is the purpose of additional server nodes if you're not going to extend the SQL cluster there? If the servers with SQL go down, you're down. (Unless the server is being used for other things, like a network fileshare.)

  • dan-572483 (4/10/2013)


    What is the purpose of additional server nodes if you're not going to extend the SQL cluster there?

    The whole idea of creating a multi node cluster is to make cluster administration easier. One 6 node cluster is easier to manage and uses less network name and IP resources than 3 x 2 node clusters.

    Just because you have 6 nodes you do not necessarily want to extend your instance across all of the nodes, you'd generally leave at least one or as passive failover partners on hot standby for key instances.

    dan-572483 (4/10/2013)


    If the servers with SQL go down, you're down. (Unless the server is being used for other things, like a network fileshare.)

    If i have 6 nodes with standard quorum setup of node majority with witness and 4 nodes fail then the cluster will be offline anyway. Whats the likelihood of 4 servers failing all at once, are they on a separate geographic site. you have to factor all this into your cluster design. Also, install 12 instances across 6 nodes and see how long the SP installer takes to run 😉

    The SQL Server installation quotes the following

    Installing a SQL Server 2008 R2 Failover Cluster


    Then, you run Setup again for each node you want to add to the cluster.

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

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

  • Perry,

    Do we need to have multiple witness disks if we want to go with this type of setting?

  • deep_kkumar (4/12/2013)


    Perry,

    Do we need to have multiple witness disks if we want to go with this type of setting?

    A cluster will have only 1 witness if one is required, this can be either a disk or fileshare.

    A cluster with an odd number of nodes does not traditionally have a witness.

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

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

  • Thanks Perry. Too many questions. I have setup a two node cluster and now adding a third node to it. I have asked for 3 drives for system db files, used db data files and user db log files for second instance. So we need to share the other drive volumes existing on other two nodes and these three new volumes to be shared between on all three nodes. Right? So that we can failover to any node we want to if needed. Correct me if I am wrong.

    And also we have to select the same MSDTC and Quorum drive used in other two node cluster while installing the failover cluster on the third node.

  • deep_kkumar (4/12/2013)


    Thanks Perry. Too many questions.

    You're welcome, i don't mind, really.

    deep_kkumar (4/12/2013)


    I have setup a two node cluster and now adding a third node to it. I have asked for 3 drives for system db files, used db data files and user db log files for second instance. So we need to share the other drive volumes existing on other two nodes and these three new volumes to be shared between on all three nodes. Right? So that we can failover to any node we want to if needed. Correct me if I am wrong.

    It all depends on how you want your cluster to operate and respond to failures.

    Say you have a 3 node cluster NodeA,NodeB,NodeC.

    Instance 1 is installed on NodeA and NodeC

    Instance 2 is installed on NodeB and NodeC

    You would typically mask or zone the storage for instance1 from NodeB and likewise instance2 from NodeA.

    This design may not suit you at all, there are many ways of achieving a highly available SQL Server system, how do you want it to respond?

    deep_kkumar (4/12/2013)


    And also we have to select the same MSDTC and Quorum drive used in other two node cluster while installing the failover cluster on the third node.

    If you're using a disk witness this storage device obviously has to be available on every node in the cluster. The following services are in my opinion the common resources you would fail on any node in a multi node cluster

    Msdtc

    File

    Print

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

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

  • Thanks a lot Perry. I really appreciate your help.

    If possible, I want to make all the disks available on all three nodes and introduce the affinity rule so that one instance running on node B and node c never fails over to node A. I also like your idea of masking the disks. Which one you suggest ?

    Actually my requirement is to setup 4 node cluster with instance 1 to run on node A and node B. Instance 2 runnig on node B and node C. Instance3 running on node D and node B.

Viewing 15 posts - 1 through 15 (of 17 total)

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