July 25, 2013 at 12:08 pm
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
July 25, 2013 at 12:33 pm
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
July 25, 2013 at 12:46 pm
Thanks for the suggestion. I checked the article but it doesnt have any information regarding my desired set up.
July 25, 2013 at 12:52 pm
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.
July 25, 2013 at 2:13 pm
I am not very familiar with Hyper-V system. My environment needs to run 3 physical windows servers.
July 25, 2013 at 2:31 pm
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
July 25, 2013 at 3:05 pm
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
July 25, 2013 at 3:42 pm
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
July 26, 2013 at 1:53 am
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" 😉
July 26, 2013 at 7:21 am
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.
July 26, 2013 at 8:33 am
My current environment is 2 physical widows 2008 R2 servers. Both have identical hardware and memory. And they are clustered. I have 64GB RAM and SAN storage. 3rd server will also be identical to the other 2 in terms of hardware and memory.
January 21, 2014 at 1:36 pm
Hi,
Can anyone please add the steps to add the 2nd active node on already running active/passive clsuter mode please
Thanks
Rkm
January 21, 2014 at 2:01 pm
r.mishra (1/21/2014)
Hi,Can anyone please add the steps to add the 2nd active node on already running active/passive clsuter mode please
Thanks
Rkm
Create a new thread detailing the current setup and I'll be able to help.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply