none
AlwaysOn Automatic Failover Using Two Nodes with SQL Standalone Instances

    Question

  • We are building a new SQL 2012 cluster for our web environment. We decided to use two nodes and utilize AlwaysOn Availability Groups for High Availability. Server01 and Server02 have had a Standalone Instance of SQL installed and both have been joined to Cluster01. An availability group has been created. Server01 and Server02 are nodes in the availability group, and are configured for Synchronous-commit replicas.

    However I noticed the other day when Server01 required a reboot for patching that it brought down the entire cluster. When I open Windows Failover Cluster Management mmc on Server02 I see that the cluster is down and requires the service be restarted on Server02 (not sure if this is related to what we ultimately want to achieve). I open up SQL Management Studio on Server02 and the availability group is showing a state of Resolving (doesn't indicate that Server02 has become the primary node). If I expand the Database section in SSMS it shows the databases in a Recovery Pending state. If I try to expand one of the databases (not the system databases ie. master, model, etc.) I get the following error:

    Error:

    The database SiteAdmin is not accessible. (ObjectExplorer)

    When I expand Availability Groups in SSMS and then expand Replicas I only see Server02 listed. After Server01 comes back up I open Windows Failover Cluster Management locally on the server. Server01 shows that it is attempting to connect to the cluster. However Server02 shows that the cluster is still down. However I still need to manually start the cluster service on both the nodes. After I do this the Availability Group comes back online showing Server02 still being the Secondary node.

    After seeing this I checked the current Quorum settings for Cluster01. Currently it is set to Node Majority which indicates with the two node configuration we have that it cannot sustain a lost of even one node. I believe that if we stood up a third server with another standalone SQL instance that node majority would work. However we do not have the resources. Based on these settings I think that we need to change the quorum settings for the cluster, but not sure if AlwaysOn supports the use of using a Disk or File Share witness for automatic failover.

    Wednesday, March 27, 2013 5:29 PM

Answers

  • Yes, AlwaysOn supports both disk and file share witness for quorum (not for automatic failover). You're on the right track in that you need a third node in the failover cluster, but it doesn't need to run SQL Server. It just needs to be participating in the cluster quorum (3 votes) so that when a node fails you still have node majority in the cluster.

    For more information, see http://msdn.microsoft.com/en-us/library/hh270280.aspx.


    Cephas Lin This posting is provided "AS IS" with no warranties.

    • Marked as answer by Echo8413 Wednesday, March 27, 2013 5:59 PM
    Wednesday, March 27, 2013 5:39 PM
  • I agree with Cephas that you can use a disk or file share witness.  However, you do NOT need a 3rd NODE in the cluster, just a 3rd VOTE which is exactly what the disk or file share witness will provide.

    David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

    • Marked as answer by Echo8413 Wednesday, March 27, 2013 5:59 PM
    Wednesday, March 27, 2013 5:46 PM
  • Right ;). Exactly what I meant but I realized my response wasn't that clear. The file share witness or the disk counts as 1 node vote in the cluster. This is why when you have an even number of nodes, you want use the "node majority and file share" or the node majority and disk" mode to make it an odd number, and when you have an odd number of nodes already then you're good to go with the "node majority" mode.

    Cephas Lin This posting is provided "AS IS" with no warranties.

    • Marked as answer by Echo8413 Wednesday, March 27, 2013 6:00 PM
    Wednesday, March 27, 2013 5:51 PM

All replies

  • These were two articles I was looking at, but they unfortunately didn't help me get to the answer I was looking for:

    Failover and Failover Modes (AlwaysOn Availability Groups)

    http://msdn.microsoft.com/en-us/library/hh213151.aspx

    AlwaysOn Failover Cluster Instances (SQL Server)

    http://msdn.microsoft.com/en-us/library/ms189134.aspx

    Wednesday, March 27, 2013 5:37 PM
  • Yes, AlwaysOn supports both disk and file share witness for quorum (not for automatic failover). You're on the right track in that you need a third node in the failover cluster, but it doesn't need to run SQL Server. It just needs to be participating in the cluster quorum (3 votes) so that when a node fails you still have node majority in the cluster.

    For more information, see http://msdn.microsoft.com/en-us/library/hh270280.aspx.


    Cephas Lin This posting is provided "AS IS" with no warranties.

    • Marked as answer by Echo8413 Wednesday, March 27, 2013 5:59 PM
    Wednesday, March 27, 2013 5:39 PM
  • I agree with Cephas that you can use a disk or file share witness.  However, you do NOT need a 3rd NODE in the cluster, just a 3rd VOTE which is exactly what the disk or file share witness will provide.

    David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

    • Marked as answer by Echo8413 Wednesday, March 27, 2013 5:59 PM
    Wednesday, March 27, 2013 5:46 PM
  • Right ;). Exactly what I meant but I realized my response wasn't that clear. The file share witness or the disk counts as 1 node vote in the cluster. This is why when you have an even number of nodes, you want use the "node majority and file share" or the node majority and disk" mode to make it an odd number, and when you have an odd number of nodes already then you're good to go with the "node majority" mode.

    Cephas Lin This posting is provided "AS IS" with no warranties.

    • Marked as answer by Echo8413 Wednesday, March 27, 2013 6:00 PM
    Wednesday, March 27, 2013 5:51 PM
  • Thank you guys very much for your responses. I think this information will help us immensely :)

    • Edited by Echo8413 Wednesday, March 27, 2013 6:01 PM
    Wednesday, March 27, 2013 6:01 PM