none
Quorum voting in our SQL 2016 AAG cluster environment RRS feed

  • Question

  • Hello, We have SQL 2016 Alwayson AAG setup. There are 2 nodes in Primary Data center. 1 Node in DR data center. And one file share witness. All are participating in the quorum voting. I know DR node should not be in the voting. Before we remove DR node from voting, would like to find out if there is any downside doing that. Any ideas please? Thanks.

    sqldev

    Thursday, January 23, 2020 2:56 AM

All replies

  • hi,

    any replica that is set with "manual" failover must not vote.

    in your case, since your quorum is ok with 2 node + share witness, you must remove the vote from the dr replica.

    because the quorum must be an odd number, in your case 3. (2 replicas + 1 share)

    that's an online operation without any outage.

    https://docs.microsoft.com/en-us/windows-server/failover-clustering/manage-cluster-quorum

    Thursday, January 23, 2020 4:18 PM
  • What version of Windows Server operating system are you running? Different versions have different ways to configure voting options to achieve quorum.

    Edwin M SarmientoMicrosoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Thursday, January 23, 2020 9:04 PM
    Moderator
  • > any replica that is set with "manual" failover must not vote.

    This is not correct. The Availability Group configuration has nothing to do with whether or not the Windows Server Failover Cluster (WSFC) stays online. The role of the WSFC is to provide high availability, fault detection, automatic failover, etc. to the Availability Group. It doesn't matter if the replicas are configured with either automatic or manual failover or if the Availability Group is healthy. If the WSFC loses quorum, the Availability Group will be taken offline.

    Also, due to the different quorum behavior between versions of the Windows Server operating system, simply saying "remove the vote from the DR replica" would be catastrophic without seeing the bigger picture.


    Edwin M SarmientoMicrosoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Thursday, January 23, 2020 9:10 PM
    Moderator
  • https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/wsfc-quorum-modes-and-voting-configuration-sql-server?view=sql-server-ver15#RecommendedAdjustmentstoQuorumVoting

    No vote by default. Assume that each node should not vote without explicit justification.

    Include all primary replicas. Each WSFC node that hosts an availability group primary replica or is the preferred owner of an FCI should have a vote.

    Exclude secondary site nodes. In general, do not give votes to WSFC nodes that reside at a secondary disaster recovery site. You do not want nodes in the secondary site to contribute to a decision to take the cluster offline when there is nothing wrong with the primary site.


    Thursday, January 23, 2020 9:42 PM
  • All are participating in the quorum voting.

    If you're on Windows Server 2012R2 or above, that's not technically correct due to dynamic quorum and dynamic witness. In this case you'd have 3 votes and the witness would not actually have a vote, though it could should an issue occur though I don't see this as a requirement given your statement.

     Before we remove DR node from voting, would like to find out if there is any downside doing that.

    In your specific setup, no there really isn't for most major scenarios. This doesn't mean it is correct for setups or implementations. I think you're spot on with your expectations of what a DR location is used.

    any replica that is set with "manual" failover must not vote.

    Much as Edwin has already pointed out, this is not true.


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Thursday, January 23, 2020 9:53 PM
    Answerer
  • maybe i did not understand but with "replica that is set with "manual" failover must not vote". i mean Exclude secondary site nodes. In general, do not give votes to WSFC nodes that reside at a secondary disaster recovery site. You do not want nodes in the secondary site to contribute to a decision to take the cluster offline when there is nothing wrong with the primary site.

    Thursday, January 23, 2020 10:05 PM
  • That entirely depends on the configuration and expectations of different issues that may hit the environment.

    It would not make sense to disable votes on any system, for example, if the topology were 2 nodes in one site, two nodes in another, and an azure witness with the expectation of automatic failover between sites.

    There are also other mechanisms that could be at play which are dynamically adding and removing votes based on your configured preferences such as dynamic quorum, dynamic witness, and preferred sites/fault domains.

    In the OPs extremely simplistic example where one site is considered truly DR and the expectation is to force quorum, the OPs choice is fine. However not everyone is going to fall into that bucket.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.


    Friday, January 24, 2020 12:44 AM
    Answerer
  • Thanks for the response. We have the following windows OS versions:

    windows server 2016 standard
    windows server 2012 R2 standard



    sqldev

    Friday, January 24, 2020 1:12 AM

  • you are right, it depends. but all i said it was in the context of this question

    2 nodes in Primary Data center. 1 Node in DR data center. And one file share witness.

    , i do not set the vote for the dr. since the dr replica is unable to give opeartional access without manual intervention, so i prefere it do not partecipate in the HA flow.

    by the way, i have been to much generic, thank for having shed light on that.




    • Edited by baleng Monday, January 27, 2020 11:18 AM
    Friday, January 24, 2020 8:47 AM
  • Hopefully you are not combining different versions of Windows Server in one failover cluster.

    Windows Server 2012 R2 and 2016 both have dynamic quorum and dynamic witness enabled. This means that the failover cluster will automatically disregard a vote from either a node or a witness based on the number of available voting members. Because the goal is to achieve majority (typically odd number of votes), your architecture having 4 voting members (2 in primary data center, 1 in DR and a file share witness), means the cluster will automatically and dynamically remove the voting option from one of the members. By default, it's the file share witness because of the dynamic witness feature. Verify the voting options by running the following PowerShell commands below.

    Get-ClusterNode | Select Name, NodeWeight, DynamicWeight, State

    Get-Cluster | Select WitnessDynamicWeight

    However, as Sean already mentioned, this does not include Windows Server 2016 failover cluster capabilities such as site awareness. If the failover cluster is Windows Server 2016 and site awareness has been configured, there other mechanisms that could be at play in addition to dynamic quorum and dynamic witness. Check to see if your Windows administrators have configured site awareness and defined fault domains and preferred sites. Or you can run the PowerShell command below.

    Get-ClusterFaultDomain


    Edwin M SarmientoMicrosoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Saturday, January 25, 2020 6:00 PM
    Moderator
  • Thanks for the detailed information. See my response to your question below. 

    Hopefully you are not combining different versions of Windows Server in one failover cluster. -  No, we are not combining different windows versions in one failover cluster.  


    sqldev

    Sunday, January 26, 2020 4:44 AM