Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Adding New Node to exisiting SQL Server 2008 R2 Cluster Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 6:15 PM
Points: 7, Visits: 119
Hi,

I hope someone will be able to assist me. I currently have an active/passive cluster setup on SQL Server 2008 R2. My active node has 2 named instances that are running on the same machine. Both named instances are failing over to my single passive node.

I want to add a new node and move one of the named instances to the new node and then add that node to my cluster. So the end result i would like to have 2 active nodes and one passive node.

So my question is do i need to physically move or re-install one of the instances on the new node and then add that node to the cluster, or do i first add the new node to the cluster and then just use the cluster manager to move one of the instances to run on the new node?

Thanks in advance

Post #1477662
Posted Thursday, July 25, 2013 12:33 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
I am not sure this will work as you would like, but anytime you are going to add a node to an existing cluster the first step is to install SQL on the new cluster node using the "Add new node to existing cluster" set-up options.

http://technet.microsoft.com/en-us/library/ms191545(v=sql.100).aspx
Post #1477672
Posted Thursday, July 25, 2013 12:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 6:15 PM
Points: 7, Visits: 119
Thanks for the suggestion. I checked the article but it doesnt have any information regarding my desired set up.
Post #1477677
Posted Thursday, July 25, 2013 12:52 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
It should give you the needed information on how to add the additional node to your set-up. As far as making a Active/Active/Passive SQL Cluster IDK... I have not heard of this and do not know if it would be supported or possible.

When we used to do performance clusters you had to have four systems for Failover. Each active node needed a passive node. You may want to look at building a Three Node Hyper-V system with the VM's hosting your SQL server instances running across two of the thee possible nodes.
Post #1477681
Posted Thursday, July 25, 2013 2:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 6:15 PM
Points: 7, Visits: 119
I am not very familiar with Hyper-V system. My environment needs to run 3 physical windows servers.
Post #1477734
Posted Thursday, July 25, 2013 2:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
This is a very easy to do, you need to first get the third machine all built up, added to the domain. Once that is done add it to the windows cluster. You can't do anything until it is part of the cluster. Then you install SQL using the add a node feature in setup and get it setup. I also recommend you do this for BOTH SQL instances even if you don't necessarily want to have both here. In a pinch it can save a lot of hassle. Also make sure the SAN will let the third node see the disks and I think you have to add them to the third node, but they would be reserved there.

I do want to point out something important with an A/A/P configuration, your cluster mode is REALLY important. Right now with a 2 node cluster I'm sure it is "Node and Disk Majority", this basically means that the Quorum and one node must be available or the entire cluster is down. Think of it this way, for the cluster to be up 50.1% of the nodes/quorum must be available. So in a 2 node cluster you can have a node die but the quorum is still active and the other node is active, that is 67% of the resources. Think about this with three nodes and a quorum, if one node dies you still have 75% of the nodes/quorum, if two nodes die you have 50%, in that case the ENTIRE cluster is down. The only way to do this is to set the cluster mode at "No Majority", this case allows everything but one node to die and the cluster remains running.

CEWII
Post #1477743
Posted Thursday, July 25, 2013 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 6:15 PM
Points: 7, Visits: 119
Thanks Elliot this is the information that I was looking for. In terms of my current set up how would you suggest that i split up my 2 named instances that are both running on one machine? I would like to have one instance on each node.

After i have added the new node to the cluster should i use the cluster manager to move one instance to the new node, or should i actually re-install a new instance on the new node and move over all the databases from the existing instance?

Thanks
Post #1477754
Posted Thursday, July 25, 2013 3:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
chitandaa 90 (7/25/2013)
Thanks Elliot this is the information that I was looking for. In terms of my current set up how would you suggest that i split up my 2 named instances that are both running on one machine? I would like to have one instance on each node.

After i have added the new node to the cluster should i use the cluster manager to move one instance to the new node, or should i actually re-install a new instance on the new node and move over all the databases from the existing instance?

Thanks
I think you are missing some important facts, you don't really "move" an instance, when you add an instance of SQL it installs ALL of the required binaries to run SQL, these are NOT shared, a copy exists on all nodes where that instance has been added. I generally suggest you add ALL SQL instance to all nodes. You can then change the Resource Group to not use nodes that you don't want but in a pinch it allows you to put them there if needed. A cluster resource group can have node preferences which helps decide where a failover goes to. For SQL there are also available nodes, when you add a node using the SQL install when it is done the node appears in that list, if you don't want this instance to use that node uncheck it. This means that without intervention that instance will NEVER run on that node.

I am concerned by the statement "re-install", you don't re-install, you add a node to an existing sql cluster, this is absolutely important, DO NOT install a stand-alone instance. There is an option in the SQL install that handles this setup you MUST use that. If you try it any other way you are pretty much screwed.

When you failover a clustered SQL server what happens is SQL is shutdown and the resources are taken offline on the node that has them, "moved" to the node that is getting them and then brought online.

I have build a couple of three node clusters and a bunch of two node but I am getting the feeling that you have never even built a two node cluster, I would try this in a dev environment before I tried it on a production server. Once you can install a two node cluster adding a third node is basically the same as the second node. I did it with VMware and VirtualBox on a laptop recently so I know it can be done in a small environment if needed.

CEWII
Post #1477773
Posted Friday, July 26, 2013 1:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 6,490, Visits: 13,967
Elliott Whitlow (7/25/2013)[hrI generally suggest you add ALL SQL instance to all nodes.

I wouldn't advise this, in fact the SQL Server installation documentation directs you to install the clustered instance only to nodes where you wish the instance to be highly available. Just because you have multiple cluster nodes it doesnt mean you should automatically install each instance across every node.


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1477897
Posted Friday, July 26, 2013 7:21 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
At this point we would need some you to post a detailed description of your three physical servers, the shared and local storage they will be using, and how they are connected. That would allow everyone to give you some more detailed information on how this might work for you.
Post #1477995
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse