You may also like...

  • Alberto

    Good morning … I am super confused about adding a second instance, to an existing sql cluster. Here is where the confusing exists, why does the new instance need a new set of disk for the data and logs? We have a stand alone slq server, and there we have many instances, and we are using the data d: and l: drives for the secondary instances. Can you please clear my confusion?
    Thank you so much in advance.

  • Sysadmin

    In clustered setup, you need separate disks if you have more than one SQL network name, e.g. I have SYS01SQLINS01 and SYS02SQLINS01, both can’t share same disks but you can have multiple named instances sharing same disks. Hope this clears your question.

  • amohammed2726

    I have a 5 node A/A/A/A/P SQL 2008 R2 cluster on Windows 2008 R2 cluster. As of today, Node 1 has a named SQL Instance running on it and is set to Failover to Node2 and they both are the preferred owners/failover partners. Next, Node 3 has a named SQL instance installed on it and is set to failover to Node 5 and they both are preferred owners/failover partners in cluster. Node 4 doesn’t have anything running on it but it is in the windows cluster.
    The task I was given is to now first change the failover partner(preferred owners) of Node 1 to Node 5(this is Node 2 at present) and then install a new named SQL cluster instance on the Node 2 and set it to failover to Node 5. Next thing I need to do is, install another named SQL instance on the Node 4 and set it to failover to Node 5. The ultimate goal here is all the first 4 nodes will have once single named instance running on each of them and they will all be set to failover to ONLY Node 5 in case any one goes down. Yeah Node 5 has all the resources like memory, CPU to handle the load of 4 nodes at a time.
    In order to accomplish this here is my plan…
    1. On Node 5, I run the SQL setup and do “Add node to the SQL failover cluster”. This is in preparation to make the failover partner for instance on Node 1 to Node 5. While doing this step in “Cluster Node Configuration” I will select the name of instance on Node 1 in “SQL Server Instance name” option.
    2. Now I change the Failover partner instance on Node 1 to Node 5 and make Node 1 and Node 5 preferred owners.
    3. Now I run the set up on Node 2 and select the “New SQL Server failover cluster Installation” and install new instance on Node 2.
    4. On Node 5, I run the SQL setup again and do “Add node to the SQL failover cluster”. This is in preparation to make the failover partner/preferred owners for instance on Node 2 to Node 5. While doing this step in “Cluster Node Configuration” I will select the name of instance on Node 2 in “SQL Server Instance name” option.
    5. Now I run the set up on Node 4 and select the “New SQL Server failover cluster Installation” and install new instance on Node 4.
    6. On Node 5, I run the SQL setup again and do “Add node to the SQL failover cluster”. This is in preparation to make the failover partner for instance on Node 4 to Node 5. While doing this step in “Cluster Node Configuration” I will select the name of instance on Node 4 in “SQL Server Instance name” option.
    7. Finally I will have to remove the Node 2 which was being used by Instance 1 on Node 1 earlier as a failover partner. I will perform this by following the Maintenance tab of install media and select the option to remove the node.

    Please let me know if these steps are correct or if I am missing anything here. Any help will be very appreciated and will make my install smooth as I already have 2 production instances up and running in this cluster and I would like to make sure I don’t mess those during the install.

    • Sysadmin

      Interesting question :) I’ll get back to you on this.

      • Sysadmin

        @amohammed
        I have reviewed your steps and I must say your steps are good. I do not see you running into any issues if you follow the steps correctly. The only thing which I am sure you already know is:

        1. All the new instances must have a unique network name and assigned IP address
        2. Each new instances would require its own drives both for Database and Log files
        3. Ensure all instances have same sp/patch level installed on all nodes so you don’t run into issues during failover

    • rkm

      @amohammed2726
      Hi Mohammed,

      Do you have detailed steps how you accomplished this task, this could be very useful for someone like me.

      Many Thanks
      Rkm

  • amohammed2726

    Thank you sir, appreciate your time on this.

  • amohammed2726

    Another quick question
    When changing the failover partner of Instance 1 on Node 1 to Node 5(at present it is set to failover to node 2), I initially planned to add the node for instance 1 on Node 5 and then do remove node from node 2 for instance 1. But all my instances on cluster are going to be standard edition(allows only 2 nodes in cluster) so I am left with choice of doing this other way by removing the instance from Node 2 first and do Add node on Node 5. Just trying to play safe here as instance 1 is already in production.

    Do you believe is there any other way to accomplish this without removing the Instance from Node 2 first?

  • Sysadmin

    Yes, SQL standard supports only two nodes per instances and enterprise up to 16 as at the last time I checked. In your situation, your only best option is to remove node2 from the cluster first but ensure you disable the preferred owner option first and ensure node1 is the current owner of instance1.

    Hope this helps

  • amohammed2726

    Yep, thank you for the quick response.

  • SohelNax

    I am testing Multi-Instance SQL Server 2012 Failover Clustering in Windows Server 2012 in Active/Passive or Active/Active scenarios. My second named instance got another Unique IP address, why not i can connect to my second instance with that second IP address other then calling SecondClusterNetworkName\SQLInstance2. Is there any way to connect my second or third instance by calling the provided IP address not with IP\Instance Name. It will be highly appropriated in this regards…….

    • admin

      @SohelNax

      Sorry for responding late. Been busy with my day to day job :).

      I am a bit confused as to what your actual question is here. Extracts from your comment:

      why not i can connect to my second instance with that second IP address other then calling SecondClusterNetworkName\SQLInstance2

      Is there any way to connect my second or third instance by calling the provided IP address not with IP\Instance Name

      Your first statement says connect to second instance as IP\Instance Name but second statement says not with IP\Instance Name

      Please clarify your question.

  • SajidYousuf

    Hi, Can you please clarify the rational of having second Network Name & IP as I can’t get this point. Is it means to attach new Network card to the servers or what?

    Appreciate your help in this regards.

    • admin

      @SajidYousuf, having a second Network Name & IP has nothing to do with attaching new Network card to the servers. Remember before you install a Microsoft failover cluster, you will need each servers to have 2 network cards, one to allow both SQL servers to communicate with the network while the other network is to act as the heartbeat between both servers.

      Note: SQL Server depends on distinct registry keys and service names within the failover cluster to ensure that SQL Server functionality continues after a failover. Therefore, the name you provide for the instance of SQL Server, including the default instance, must be unique across all nodes in the failover cluster. Using unique instance names ensures that instances of SQL Server that are configured to fail over to a single server have distinct registry keys and service names.

      In simple term, see each instance name as a machine on the network meaning you can’t have two machines on the network with the same hostname and IP address else there will be conflict.

      The beauty of doing this is having a requirement to install different SQL Server versions to support different applications or solutions or testing.

      Hope this makes sense :)

      • SajidYousuf

        Greetings,
        Thanks for your detailed response but I want to clarify it further that the Networkname/instance name we will require in Instance Configuration Screen. Here I define a different NetworkName and Instance (e.g. TestNetworkName2 & Instance02 respectively).

        whether it will work or I need to define the TestNetworkName2 in network / dns first as well.

        regards,
        Sajid

        • admin

          @Sajid, like I had explained, you need to ensure that the network name ‘TestNetworkName2′ is configured in the DNS and assigned an IP Address so it can be accessible.

          Feel free to reply back if you run into any issues following the outlined steps above.

  • rkm

    Hi,

    Thanks for your time in explaining the technicalities of the subject, much appreciated.
    I seek your advice and valuable feedback on the setup I am having currently.

    2 Node Cluster (in active/passive mode) (both have same configuration in terms of hardware and software)

    3 named server instance up and running at any point of time on active Node.
    Say for example Server1 with 3 named instance (server1ainst1, server1binst2, server1cinst3) and we can switch/failover to server2 including all 3 named instance automatically/manually as and when required.

    Enterprise SQLSERVER2008R2 SP1 installed on both the nodes in the cluster

    Aim: To give more processing and churning power to this setup we ideally need to add one more node in Active/Active/Passive Mode).I need to add extra node in active mode and rest remains the same.(2 active and 1 passive in place of 1 active and 1 passive what we have currently)

    I am sure passive node can able to take load of both active servers at any point of time(planned and unplanned).

    Could you please suggest the steps how I can achieve the same with minimum risk and downtime as it is already setup and running as part of production setup. I am not an expert and don’t have lot of experience of running the SQLSERVICES AS PART OF windows cluster.

    Many Thanks,
    rkm

    • admin

      You are welcome.

      What you simply need to do is:

      – Follow through same process of adding another node to an existing cluster
      – Run the cluster validation check and ensure it passes (You might need to ignore volume checks that are currently in-use to avoid it taking it off-line)
      – Manually migrate one of the instance to the 2nd node while the second instance remains on first node
      – Then for a 3node cluster, the cluster quorum settings will change. Read up more on this article by Microsoft on Understanding Quorum Configurations in a Failover Cluster (http://technet.microsoft.com/en-us/library/cc731739.aspx). I believe the Node Majority set will be the preferred option since its recommended for clusters with odd number nodes, in this case 3
      – You might also take into account, the memory resource allocation for the 3rd node that would act as the passive node. Ensure the resource can handle all the 3 instances.

      Hope this makes sense as I am responding back from office.

      • rkm

        Hi Admin,

        Thanks for your prompt response. Idea at this point of time is to add another instance to this new node which is getting introduced into the cluster having 2 nodes in active/passive mode already.

        In short I want this new node to host one or 2 named instance and as and when required failover to passive node which also servers to existing active node hosting 3 named instance.

        Node 1 –Active (Already existing with 3 Named Instance)
        Node 2 – Passive (Already existing and serving Node 1)

        Planned Changes
        Add Node 3 – New active node which will host 1 Named instance as of now
        Node 2 – Still remains Passive but will also serve new Node 3 along with node 1

        No failover between Node 1 and Node 1 and they will act independent of each other.(Active/Active)

        Do I need create a new cluster on this Node 3 independent of existing cluster or I need to add node to already existing cluster, bit confused!!!

        Best Regards,
        Rkm

  • rkm

    No failover between Node 1 and Node 2 and they will act independent of each other.(Active/Active), sorry there was typo in my previous comment.

    Cheers,

    Rkm

    • admin

      Hi rkm

      Lets break this down. You have Node 1 hosting 3 named instances and node 2 waiting for failover. To introduce a 3rd node, simply add the new node to the existing cluster as earlier explained.

      So now you have 3 node cluster with node 1 still hosting 3 instances while nodes 2 & 3 are waiting for failover.

      Next manually failover over instance 1 or 2 or 3 to node 3 which means you will have node 1 hosting just 2 named instances while node 3 will host just 1 instance but node 2 remains on standby.

      Next if I recall, you need to go into the properties of each instances, click on Advanced Policies tab and set possible owners meaning:

      – all instances hosted by node 1 will have only node 1 and 3 checked. This means anytime node 1 goes offline, all the 2 instances will failover to node 3 and when its back online, because of the set preferred owner the instances will failback

      – then instance hosted by node 3 will have all the nodes checked meaning when node 3 goes offline, its instance will obviously failover to node 2 since its got no instance.

      I really hope I have been clear with my explanation. Sorry that I do not have my SQL lab online at the moment to do more checks but I believe it all boils down to your desired failover scenario and expected recovery