Before you touch anything with Always On in the name, you need to understand how cluster quorum works and how it can bring your SQL Server down. Learn how dynamic quorum and dynamic witness should be configured and see 3 real-world scenarios.

Note: this class is from 2017, and I no longer update it. I’ve made it freely available since I no longer do production DBA work, and I don’t intend to keep this course up to date. You’re welcome to leave comments for other viewers here, but I don’t answer questions on this material, nor do I update it to reflect changes in Microsoft SQL Server. Thanks for understanding, and enjoy the free videos! Hope they help.

17 Comments. Leave new

  • Great info. Just need to know how would i go about configuring below:
    Current set up= 2 Node cluster [DataCenter1] [DB Principal]+ 2 Node cluster [Datacenter2] [DB mirror]: + @ node cluster [LS set up for reporting] ::Mirroring is set up as DR due to Quarter fail over events or you can say DR test+ All above are Windows 2012 standard edition ,Shared storage and disk quorum

    Keeping the same above set up with 2node cluster in Dc1 and 2 Node in other Data center would setting up Always ON be complicated and bigger challenge? Management wants to keep the same looks and replacing mirroring with always on due to many benefits available specially reporting ? Any suggestions , appreciate your advise, thanks!

    Reply
    • Kapil – wow, lots of information in there. Unfortunately, architecture design like that is pretty far beyond what I can do fast in a comment. That’s where our consulting comes in. If you’d like to hire us for a consulting engagement, click Consulting at the top of the screen. Thanks!

      Reply
  • Hi Brent, I have a Always On AG 2016, configured on Windows Server 2012 R2 – 2 node cluster. Quorum configured as 2 nodes + Fileshare witness, totally 3 votes.

    Scenario 1:
    Now, either 1 node fails – remaining vote = 66.66 (majority) and cluster is up.

    Scenario 2:
    Now, fileshare witness fails – remaining = 66.66 (majority) and cluster is up

    Kindly help me understand the dynamic quorum and dynamic witness on scenario 2.

    My doubts, post scenario 2:

    Will it automatically take away 1 vote from either one of the nodes? If yes, remaining vote will be 33% not majority and single point of failure.

    What will be the role of dynamic witness in this scenario?

    Thanks in advance!.

    Reply
    • I’m not sure if you’re saying scenario 1 & 2 are related. If you lose both the node AND the file share witness, you’re down to just 1 node – in that case, you’re screwed regardless of what happens. Single point of failure is not good.

      Can you rephrase your question, maybe?

      Reply
      • Question is:

        What is the role of dynamic quorum and dynamic witness in 2 node cluster?

        However, I understood from the SE question, post scenario 2 (ie, FS is down), we have only 2 nodes and Dynamic quorum will zero down either one of the node and assign complete vote (100%) to other node. So now, quorum will be 1/1, ie 100% majority.

        And there is no role of dynamic witness in this case. When we even even number nodes (example 4 nodes) with FS witness, dynamic witness will add one more vote to FS, make it odd and majority. Also, when we have even odd number of nodes with FS witness, dynamic witness will zero down the vote for FS and keep it as odd and majority quorum.

        Kindly, correct me, if I am missing something. Thanks!

        Reply
  • Another great video! Curious why you mentioned that you do not want to use a cloud witness for SQL?

    Reply
    • When you’re doing failover clusters, adding a remote resource over the Internet doesn’t usually make your cluster more reliable (as opposed to a nearby resource).

      Reply
  • prashant.singh
    November 9, 2020 8:10 am

    Hi Brent ,
    In Oracle we have Voting Disk configured in Odd Number’s to avoid split brain – in the similar manner can we configure Multiple redundant copies of Quorum in Windows accessible to each node ?

    Regards,
    Prashant

    Reply
  • Hi Brent! Thank you very much for this beautiful video.
    I created a cluster with two nodes (node1 and node2).
    The operating system on each machine is windows server 2016 data center edition.
    There is no shared storage.
    Both nodes use local storage.
    I did not configure quorum witness.
    There are two nodes and one voter (The current vote for node2 is zero).
    The cluster is up and running when node1 is on and node2 is off (That means the computer is turned off).
    Also, the cluster is up and running when node1 is off and node2 is on.
    Kind regards and stay healthy.

    Reply
  • Hi Brent!
    Is the following sentence correct ?
    It is most appropriate to not configure a witness, where there are an odd number of
    nodes in a single data center.

    Reply
    • No, you’ll always want to configure a witness. Windows dynamic quorum will automatically add or remove a vote from the witness based on the availability of the other nodes.

      Reply
  • Thank you Brent!

    Reply
  • Hi Brent!

    What is cluster ownership?
    What is the difference between cluster ownership and other nodes in the cluster?

    Thank you for answering my questions!

    Reply

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.