February 21, 2012 at 8:57 am
Hello Everyone,
I hope all is well.
I have already implemented a 2 node Active passive clustering and tested all the possible scenarios. Now,
I am planning on testing a 3 node Active/Active/Passive clustering with SQL Server 2008 R2 on windows server 2008 r2. I was wondering how the cluster would appear in my scenario (behavior of passive node(Node C)). On one active node(Node A) I want to have instance1 and on the other active node(Node B) I want to have instance2. So when Node A goes down it has to failover to Node C. And Similarly if Node B goes down it has to failover to Node C.
So my question is what happens if both Node A and Node B goes down? will Node C come up with both the instances?
Is this kind of set up supported in SQL Server 2008 R2 clustering?
I need your valuable inputs. Please input some links if you have any.
Thanks a ton.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
February 21, 2012 at 9:11 am
Yes, if node C is allowed to host all the resources, it will host the two instances. Be sure that node C has enough umph to handle running two instances (RAM, CPU, etc).
February 21, 2012 at 9:25 am
Thanks for the reply Calvo. In this scenario how should I configure my shared storage. I meant can I use a single storage for both the instances? what is the best practice. Do you know of any references for the setup?
Thanks Again
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
February 21, 2012 at 9:32 am
I can't tell you what the "best practice" would be but you'd share the same storage with the third node and add it to the windows cluster. You would then add a node to the SQL Server failover cluster just like you did for node B, assuming you installed node A first.
February 21, 2012 at 9:35 am
oh, same shared disks for two instances... I'm going to say no. Someone with more knowledge might say differently.
The reason I say no is because the shared disks are removed from the available storage resource group when the failover cluster instance is installed. Therefore they wouldn't be available to install another failover instance on those disks, you'd need a new shared disk(s).
February 21, 2012 at 9:47 am
Thanks for the reply. One last thing is about the licensing. DO I need to have 2 licenses one for each active node... in my case i am using 2 different instances so i am wondering if i need two seperate licenses or one license shd be fine?
i am using sql server 2008 r2 standard edition on windows server 2008 r2 enterprise edition.
Thanks again.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
February 21, 2012 at 10:06 am
Depending on your licensing you should only need to license the active instances. Now there are rules governing this process so be sure to consult microsoft documentation or give them a call. You don't normally have to have a license for a passive node in a cluster so long as the specs are the same (number of CPUs for example). But again, rules are in place that determine how long a cluster can be failed over to a "passive" node before it is failed back etc.
Instance1 running on Node A(active) - license
Instance2 running on Node B(active) - license
Node C(passive) - no license required.
February 21, 2012 at 11:24 am
Cool Thanks..
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
February 21, 2012 at 2:51 pm
Sapen (2/21/2012)
Thanks for the reply Calvo. In this scenario how should I configure my shared storage. I meant can I use a single storage for both the instances? what is the best practice. Do you know of any references for the setup?Thanks Again
No. Unless you want the two SQL instances in the same cluster group which makes the active/active/passive setup impossible since you need to fail the group over as a whole.
Each group will need its own NAME, IP and storage as minimum. Generally people would suggest each group follows their best practice.. i.e. a drive for logs, data and tempdb. This equates to 6 volumes (3 in each group) and another potential one for the quorum but this is not required for a majority nodeset of 3 nodes.
You will end up with CLUSTERA\Instance1 and CLUSTERB\Instance2 for an active\active\passive setup.
Its a good idea to preconfigure the failover node to double RAM of the primary nodes just in case it takes both nodes. Or I setup an automatic procedure to reset the MAX memory upon failover depending on the node.
February 21, 2012 at 9:31 pm
I think you need to understand the cluster quorum settings because for a three node cluster the only way to allow a 2 of 3 failure is the No Majority Quorum setting.
1. Node Majority - Purely the number of nodes running, for 3 nodes you can lose 1, a loss of two means cluster down.
2. Node and Disk Majority - Number of nodes running plus a disk quorum, would not be recommended because as far as counts go it is still basically the same as #1. Why, because 1 node plus disk quorum is just 50% you need over 50% to retain the cluster.
3. Node and File Share Majority Basically #2 but with a file share. Same problem.
4. No Majority - Just a disk quorum, can handle an all but one failure but a failure of the Quorum drive for ANY reason brings the whole cluster down.
Short answer, You can sustain a single node failure in a 3 node cluster in the recommended setup. #2 is the recommended setting for an even number of node cluster. #1 is the recommended setting for an odd number of node clusters. I don't think #4 is ever recommended. #3 is typically used in a geographically dispersed cluster, which is rarely set up due to costs.
I had a three node cluster running active/active/active and struggled with how many nodes could be lost and still keep everything running..
CEWII
February 21, 2012 at 9:35 pm
calvo (2/21/2012)
Depending on your licensing you should only need to license the active instances. Now there are rules governing this process so be sure to consult microsoft documentation or give them a call. You don't normally have to have a license for a passive node in a cluster so long as the specs are the same (number of CPUs for example). But again, rules are in place that determine how long a cluster can be failed over to a "passive" node before it is failed back etc.Instance1 running on Node A(active) - license
Instance2 running on Node B(active) - license
Node C(passive) - no license required.
I believe the rule is 30 days. And you only have to license active nodes. And standard SQL is ok on enterprise windows. It will never allow a clustered SQL instance to allow for 3 possible nodes.. It checks during the install..
CEWII
February 22, 2012 at 7:11 am
yeah...I am upgrading to enterprise and per my call with MS the grace period has increased to 90 days now.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
February 22, 2012 at 7:44 am
90 days. good deal. I haven't seen that printed yet..
I try not to go to enterprise unless I need to, basically due to costs. But if you need to be able to failover to either of the other nodes then thats your only path..
Good luck.
CEWII
February 22, 2012 at 8:03 am
Sapen (2/21/2012)
I was wondering how the cluster would appear in my scenario (behavior of passive node(Node C)). On one active node(Node A) I want to have instance1 and on the other active node(Node B) I want to have instance2. So when Node A goes down it has to failover to Node C. And Similarly if Node B goes down it has to failover to Node C
Perfectly valid, as already stated just bear in mind you will not be able to add nodeb to sql instance 1 and nodea to sql instance 2
1. Node Majority
designed for an odd number of nodes, can sustain the failure of 1 node in a 3 node cluster or 2 nodes in a 5 node cluster, etc.
2. Node and Disk Majority
Designed for an even number of nodes, can sustain the failure of half the nodes in the cluster as long as the disk witness stays online.
3. Node and File Share Majority
Designed for clusters that span datacenters
4. No Majority
This is the old Windows 2003 traditional quorum model, the disk is a single point of failure and so, not recommended
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 22, 2012 at 8:54 am
Isn't that what I said? 😛
CEWII
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply