January 7, 2014 at 12:09 pm
Howdy,
I'm trying to apply some MS articles I've read to a SQL DR design and I am not quite sure I understand the technology well enough to make an appropriate design. In particular I'm trying to decide if it's a good idea to use a file-share witness or not.
We have two datacenter sites. Let's label them Texas (main site) and Virginia (DR site). Each site has a different subnet, with a ping latency greater than 10 ms. We have a SAN at each site with enough storage to accommodate our needs.
In Texas, I have a 4 node MS cluster with 5 different SQL 2012 FCIs running in that cluster. I have 2 nodes in Virginia also with 5 matching standalone SQL 2012 instances split among those two instances.
So in Texas I have a cluster containing 4 nodes TexasNode1 through TexasNode4 and 5 Clustered SQL Instances SqlFciA\Apple through SqlFciE\Elephant. In Virginia I have 2 servers VirginiaNode1 and VirginiaNode2 with 5 standalone SQL instances VirginiaNode1\Apple through VirginiaNode2\Elephant.
I'd like to setup SQL 2012 HA groups for my SQL instances.
In order to do that I need to add my two nodes VirginiaNode1 and VirginiaNode2 to the Texas cluster making it a multi-site cluster. I've read in Technet and other places that when you have an even number of nodes in the multi-site cluster, that you need a witness to create a quorum for the cluster. I have an even number of nodes, but they are not evenly distributed. Does that make a difference?
I've also think I want my witness to be a file share, although I do not have a third site for the file-share witness, which seems ideally the best design. If I do use a file-share witness and it lives in the Texas datacenter, and then Texas goes down. I'm trying to understand what would happen. Would the cluster be down on the nodes in Virginia as well? If so, do I need the file-share witness available in Virginia? If so we can use SAN replication to accomplish that, although it would be a manual process to "turn up" the file-share witness in Virginia. Does that mean the cluster will come up in Virginia at that point?
Now if we only have a break in the communication between the two sites, then I'd want the cluster to be up on the Texas nodes, and then down on the Virginia nodes. It seems like that would be the behavior regardless of whether or not I'm using a file-share witness, given that my nodes are not evenly distributed between the two sites?
Any insight would be helpful.
...thanks!
January 8, 2014 at 12:02 am
If you chose to use a file share witness, total votes is 7 with 4 being the minimum votes required for quorum.
If you hosted the file share in Texas and lose the site completely including the 4 nodes, Virginia will not stay available as it does not have a majority vote to form quorum (only has 3 votes).
If you host the file share in Virginia and that site goes down, Texas will have majority vote and stay available. If you only lose any 2 nodes in Texas and the WAN link, the cluster will go down completely as it will not be able to form a majority vote.
The standard recommendation is to host the fileshare at a 3rd site or an Azure/EC2 cloud box if you only have 2 physical locations.
Either that, or setup a separate 2 node multi-site cluster to host the file share with a more sensitive threshold so it fails over before the SQL cluster.
You could also consider removing the votes from the DR site so that it only matters when Texas has an issue. eg:
January 8, 2014 at 10:18 am
Thank you very much for your responses this has helped clear up and put together better what I have read. I liked the article links as well they've also been a good help.
So given my 4 Texas node and 2 Virginia node design, if the Texas site goes down, then the cluster on the Virginia site will also go down. This would happen regardless of whether or not I'm using a witness that is not down (file-share or otherwise) as there would not be a quorum. For a no witness implementation there would be only 2 out of 6 voting members available. For a witness implementation there would only be 3 out of 7 voting members available. In either case there is not a quorum.
This is all what I'll call built-in and automated behavior.
In that scenario I understand that I could then manually force the cluster up in Virginia (if we determine that the outage in Texas was going to be "lengthy"). That is assuming that we have enough SQL resources on those two nodes in Virginia to run what we want to run.
What are the implications of forcing the cluster up in Virginia? It sounds like if the 4 Texas nodes then come up they will automatically re-join the cluster. According to this article http://technet.microsoft.com/en-us/library/hh270275.aspx before I bring those 4 Texas nodes online, I'll want to "re-evaluate and reconfigure NodeWeight values". I don't understand the implication there.
Does that mean I'll want to set their NodeWeights to 0 then bring them online and then set their NodeWeights back to 1? Is what you are doing then updating the cluster configuration on those 4 Texas nodes to the current operating cluster configuration on the 2 Virginia nodes before they become voting members?
...thanks
January 8, 2014 at 8:00 pm
If you forced quorum in Virginia and the 4 Texas nodes were to become available without access over the WAN, then they will form quorum and you will have a split brain scenario with both sites having an active primary AG.
However, if the WAN is available and each Texas node becomes available one by one, then they will join the existing cluster quorum. According to the process for recovering from forced quorum http://technet.microsoft.com/en-us/library/hh270277.aspx , step 2 notes "The forced quorum setting has a cluster-wide affect to block quorum checks until the logical WSFC cluster achieves a majority of votes and automatically transitions to a regular quorum mode of operation." Which you will need to test.. what will happen as each node joins and you get 3 votes in Virginia (the cluster could shut down, but not according to that article).
My understanding of "re-evaluate and reconfigure NodeWeight values" from /hh270275.aspx is an attempt to explain how to avoid a split brain when equal numbers of votes exist at each site. It's also suggested in /hh270277.aspx step 4 " Apply new quorum mode and node vote configuration. If forcing quorum successfully restarted all the nodes in the cluster and the root cause of the quorum failure has been corrected, changes to the original quorum mode and node vote configuration are unnecessary.
Otherwise, you should evaluate the newly recovered cluster node and availability replica topology, and change the quorum mode and vote assignments for each node as appropriate. Un-recovered nodes should be set offline or have their node votes set to zero."
http://technet.microsoft.com/en-us/library/hh270280.aspx gives an overview of quorum under "Voting and Non-Voting Nodes" and "Recommended Adjustments to Quorum Voting" it suggests removing the vote from your asynchronous secondary replicas which I assume will be both nodes at the Virginia site. That would leave 4 votes + 1 witness, meaning you need 3 votes for majority to form quorum. You could lose 2 nodes or (1 + witness) in Texas and still have a working cluster. You would not have auto failover to Virginia, but that site is only for your DR scenario. HA is covered by the 4 at Texas?
Another article for recovering the AG replicas
http://technet.microsoft.com/en-us/library/ff877957.aspx#FailureResponse
January 9, 2014 at 7:53 am
Thanks for your replies, I'll read through the articles, they sound very helpful. I see what you are saying about how if the primary site comes up, but network between the two site is not, and that resulting in a split-brain scenario.
Yes, the current setup has HA in Texas and Virginia for DR.
We are also now going through a design process where the 2 datacenters will be hosted so that there is redundancy for basic operations (eletricity, HVAC, etc.). When we do that our latency between the two datacenters would be around 3 ms, so I may be able to go to "snychronous" mode for the replicas.
Given that, longer term we may going to more of a co-location and split processing between the two sites. Where some apps run in Datacenter1 and some in Datacenter2 and we have the ability to failover and run on either site.
January 14, 2014 at 2:37 pm
I have added a file witness to the cluster and am now testing the various functions and features. Thanks again for the help and advice!
January 14, 2014 at 7:33 pm
Just curious, where did you end up hosting the fileshare?
Be sure to test all possible failure scenarios and make sure everyone in your team understands the implications of forced quorum.
Sounds like your company is pretty serious about HA. Would the business consider moving one of the nodes from Texas to Virginia? Or better yet, adding 2 nodes to Virginia and making it synchronous with auto failover and basically gauranteed HA?
Also be careful with Microsoft licencing if any of the passive cluster nodes become active.
January 15, 2014 at 3:23 am
Ted, don't forget about the new Dynamic weight quroum configuration in Windows 2012, this can help to maintain quorum during node shutdowns but on a catastrophic would likely be ineffective, even so, the feature is there in Windows 2012 clusters
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply