September 16, 2016 at 2:08 am
Hi all,
I have setup a WSFC cluster (2 nodes).
I wanted to setup an availability group with primary replica on node1 and secondary on node2.
I do not want to have automatic failover.
In such scenario above, how will having a quorum help since each replica is acting as a standalone and no automatic failover is required.
Is the only cluster resource the alwayson listener ?
Regards,
Noob
September 16, 2016 at 10:02 am
Well if you don't have quorum then your AG will be down. It requires quorum to function (otherwise how would it know which was the primary). In your situation you should probably go with a file share witness on a third server that is not a part of the WSFC.
September 16, 2016 at 12:09 pm
Nicholas Cain (9/16/2016)
Well if you don't have quorum then your AG will be down. It requires quorum to function (otherwise how would it know which was the primary). In your situation you should probably go with a file share witness on a third server that is not a part of the WSFC.
Hi Nicholas,
I do get what you mean (that AG require WSFC to work). But i am thinking of it this way-> i got a 2 node cluster (without any witness/share).
It has quorum because both nodes are up. In each node, primary and secondary replica will be working.
But if either node is down -> so what ? I do not want any automatic failover, how does a split brain occur here?
What's the point of trying to achieve quorum then (other then to prevent split-brain which doesn't apply here)?
Regards,
Noob
September 16, 2016 at 1:42 pm
The worst case isn't whether a node is down or not, that's not what causes split brain scenarios. Rather the issue is when both nodes are up, and able to accept traffic, but for whatever reason are unable to communicate with each other. At that point the cluster doesn't know what to do and so shuts everything down.
Depending on the version of Windows you are using (2012 R2) you could use a dynamic witness to handle these kinds of scenarios, however, you must always have the majority of votes up in order to maintain quorum, otherwise you will lose the cluster, and your AG will go offline.
September 18, 2016 at 7:48 am
Nicholas Cain (9/16/2016)
The worst case isn't whether a node is down or not, that's not what causes split brain scenarios. Rather the issue is when both nodes are up, and able to accept traffic, but for whatever reason are unable to communicate with each other. At that point the cluster doesn't know what to do and so shuts everything down.Depending on the version of Windows you are using (2012 R2) you could use a dynamic witness to handle these kinds of scenarios, however, you must always have the majority of votes up in order to maintain quorum, otherwise you will lose the cluster, and your AG will go offline.
Hi Nicholas, thanks for your reply and sorry for getting back late.
Precisely, the purpose of the quorum is to prevent split brain and if quorum can't be achieved, the cluster will have to be down.
However, in 2 nodes scenario as mentioned where 1 node houses the primary replica and another houses the standby replica with noautofailover configured - so what if split brain occurs with both nodes up but thought each other as down ?
No failover is required. Primary still remains as primary, secondary still remains as secondary.
So why the need to have a quorum over here where there is no shared resources, no failover required.
Regards,
Noob
September 18, 2016 at 1:38 pm
That's the way that WSFC is designed and functions. There must always be a quorum of votes.
Ultimately, should you desire, you could configure the node weight of each node so that the second node does not have a vote. This way only your primary server will. I wouldn't recommend doing this, but it's certainly an option for you.
September 19, 2016 at 10:03 am
Nicholas Cain (9/18/2016)
That's the way that WSFC is designed and functions. There must always be a quorum of votes.Ultimately, should you desire, you could configure the node weight of each node so that the second node does not have a vote. This way only your primary server will. I wouldn't recommend doing this, but it's certainly an option for you.
Hi Nicholas,
Thanks for your reply. I just came to also realize, if I have a 5 node cluster with only replicas in 2 of the nodes (e.g. node1 and node2),if node3,4,5 is down (meaning a quorum cannot be obtain), the AG still goes down even if the replicas are not on the affected nodes.
Is there any other reasons that an AG must goes down when a quorum cannot be obtained other then preventing split brain (which don't quite apply in a no failover environment) ?
Or the simple answer is -> AG depends on WSFC for the monitoring of the replicas' status and health and WSFC work as a cluster service and require quorum to function.
Hence, if there is no quorum -> no cluster -> no WSFC -> no AG.
Regards,
Noob
September 19, 2016 at 2:02 pm
Correct, no quorum, no cluster, no AG.
It's all about carefully selecting what nodes do, and do not have votes within your cluster to ensure that you maintain the maximum uptime for your environment. I would stick with the same number of nodes in your cluster as you need to support the AG, and do not mix things within that cluster. Down that path there be dragons.
September 19, 2016 at 11:03 pm
Thanks for your reply. I just came to also realize, if I have a 5 node cluster with only replicas in 2 of the nodes (e.g. node1 and node2),if node3,4,5 is down (meaning a quorum cannot be obtain), the AG still goes down even if the replicas are not on the affected nodes.
If you have a 5 node cluster, as long as cluster resource is residing on either node 1 or node 2 and the rest of other nodes did not go down at the same time, AG will still be up and running.
Since you have only 2 nodes in your cluster, you can run the command below in powershell to check which node actually is holding the quorum vote. This will be indicated by DynamicWeight.
Get-ClusterNode | ft ID, NodeName, NodeWeight, DynamicWeight, State -AutoSize
Shutting down the node which holds the SQL secondary replica would not kill your AG because WSFC can stay up until the last node. Hope the article below helps to understand this behaviour a bit more.
The combination of scenarios are limitless, but cluster quorum on Win 2012 R2 are no longer based on the initial configuration layout (this is due to dynamic quorum). I.e If you're stopping the cluster node which also host the primary SQL replica, then AG will go down. I think its best to test your scenarios in DEV to decide the best configuration for your organisation.
Simon Liew
Microsoft Certified Master: SQL Server 2008
September 20, 2016 at 10:23 am
thanks nicholas and simon for the wonderful replies and thoughts
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply