How to restore a replica to the primary role after the quorum is lost in SQL Server 2012

Article translations Article translations
Article ID: 2792138 - View products that this article applies to.
Expand all | Collapse all

On This Page

Summary

This article describes how to restore a replica to the primary role after the quorum for a cluster is lost in Microsoft SQL Server 2012.

More information

Assume that quorum is lost and that all availability group replicas of a SQL Server 2012 failover cluster go into the RESOLVING state. The availability SQL Server databases are inaccessible from any replica, and you cannot reestablish the quorum. To restore a replica to the primary role, you may have to force quorum on one of the Windows cluster nodes that are hosting a SQL Server availability group replica, and then fail over to that replica in order to return the availability databases to the ONLINE state. To do this, follow these steps. 

Step 1: Force quorum in your selected Windows failover cluster node

To bring the Windows cluster node online, run the following command at the command prompt:
net.exe start clussvc /forcequorum

Step 2: Restore the replica to the primary role guaranteeing minimal or no data loss

Follow the instructions for whichever scenario is most applicable. 
  • Scenario 1: Quorum is lost, and the Windows cluster node that is hosting the primary replica is brought online by using the command in step 1.

    If you can force quorum on the cluster node that is hosting the primary replica, then the replica does not return to the primary role. The primary replica remains in the RESOLVING state until full quorum is restored in the cluster. If this replica was originally the primary role, then you can return the primary replica to the primary role by using the force_failover_allow_data_loss parameter. To do this, connect to the primary replica and then execute the following SQL script:
    alter availability group <group_name> force_failover_allow_data_loss

    Note 'Group_name' is a placeholder for the name of the availability group.
  • Scenario 2: Quorum is lost, and the Windows cluster node that is hosting the automatic failover partner secondary replica is brought online by using the command in step 1.

    If the primary replica is unavailable, and you can force the quorum on the cluster node that is hosting the automatic failover secondary replica. As in scenario 1 for the primary replica, you can force failover against this secondary replica. To do this, connect to the automatic failover synchronous secondary replica and then execute the following SQL script:
    alter availability group <group_name> force_failover_allow_data_loss

    Note Before you do this, you have to query the is_failover_ready column status of the sys.dm_hadr_database_replica_cluster_states table to make sure that the data is not lost when you force failover. When all availability databases are reported as is_failover_ready=1, you can start the failover by using the force_failover_allow_data_loss parameter. To query the status of a database, execute the following SQL script:
    select database_name, is_failover_ready from sys.dm_hadr_database_replica_cluster_states where replica_id=(select replica_id from sys.availability_replicas where replica_server_name ='<Replica server name>')

    If the database is ready, the script will return the following result:
    database_name is_failover_ready


    ========== ===========

    ag1 1

    ag2 1

References

For more information about how to bring a Windows cluster node online as part of availability group disaster-recovery, refer the following white paper at the Microsoft Download Center:
AlwaysOn architecture guide: Building a high availability and failure recovery solution by using failover cluster instances and availability groups

Properties

Article ID: 2792138 - Last Review: January 14, 2013 - Revision: 1.0
Applies to
  • SQL Server 2012 Enterprise Core
  • Microsoft SQL Server 2012 Web
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Developer
Keywords: 
kbsurveynew kbtshoot kbexpertiseadvanced KB2792138

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com