Perform a Forced Manual Failover of an Availability Group (SQL Server)

Artikel
02/14/2014

This topic describes how to perform a forced failover (with possible data loss) on an AlwaysOn availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2012. A forced failover is a form of manual failover that is intended strictly for disaster recovery, when a planned manual failover is not possible. If you force failover to an unsynchronized secondary replica, some data loss is possible. Therefore, we strongly recommend that you force failover only if you must restore service to the availability group immediately and you are willing to risk losing data.

After a forced failover, the failover target to which the availability group was failed over becomes the new primary replica. The secondary databases in the remaining secondary replicas are suspended and must be manually resumed. When the former primary replica becomes available, it transitions to the secondary role, causing the former primary databases to become secondary databases and transition into the SUSPENDED state. Before you resume a given secondary database, you might be able to recover lost data from it. However, notice that transaction log truncation is delayed on a given primary database while any of its secondary databases is suspended.

Important

Data synchronization with the primary database will not occur until the secondary database is resumed. For information about resuming a secondary database, see Essential Tasks After a Forced Failover later in this article.

Performing a forced failover is necessary in the following emergency situations:

After forcing quorum on the WSFC cluster (forced quorum), you need to force failover each availability group (with possible data loss). Forcing failover is required because the real state of the WSFC cluster values might have been lost. However, you can avoid data loss, if are able to force failover on the server instance that was hosting the replica that was the primary replica before you forced quorum or to a secondary replica that was synchronized before you forced quorum. For more information, see Potential Ways to Avoid Data Loss After Quorum is Forced, later in this topic.

Important

If quorum is regained by natural means instead of being forced, the availability replicas will go through normal recovery. If the primary replica is still unavailable after quorum is regained, you can perform a planned manual failover to a synchronized secondary replica.

For information about forcing quorum, see WSFC Disaster Recovery through Forced Quorum (SQL Server). For information about why forcing failover is required after forcing quorum, see Failover and Failover Modes (AlwaysOn Availability Groups).
If the primary replica becomes unavailable when the WSFC cluster has a healthy quorum, you can force failover (with possible data loss), to any replica whose role is in the SECONDARY or RESOLVING state. If possible, force failover to a synchronous-commit secondary replica that was synchronized when the primary replica was lost.

Tip

When the WSFC cluster has a healthy quorum, if you issue a force failover command on a synchronized secondary replica, the replica actually performs a planned manual failover.

Note

For more information about the prerequisites and recommendations for forcing failover and for an example scenario that uses a forced failover to recover from a catastrophic failure, see Example Scenario: Using a Forced Failover to Recover from a Catastrophic Failure, later in this topic.

Before you begin:

Limitations and Restrictions

Prerequisites

Recommendations

Potential Ways to Avoid Data Loss After Quorum is Forced

Security
To force failover (with possible data loss), using:

SQL Server Management Studio

Transact-SQL

PowerShell
Follow Up: Essential Tasks After a Forced Failover
Example Scenario: Using a Forced Failover to Recover from a Catastrophic Failure
Related Tasks
Related Content

Before You Begin

Limitations and Restrictions

The only time that you cannot perform a forced failover is when Windows Server Failover Clustering (WSFC) cluster lacks quorum.
Data loss is possible during the forced failover of an availability group. In addition, if the primary replica is running when you initiate a forced failover, clients might still be connected to former primary databases. Therefore, we strongly recommend that you force failover only if the primary replica is no longer running and if you are willing to risk losing data in order to restore access to databases in the availability group.
When a secondary database in the REVERTING or INITIALIZING state, forcing failover would cause the database to fail to start as a primary database. If the database was in the INTIAILIZGING state the you will need to apply the missing log records from a database backup or fully restore the database from scratch. If the database was in the REVERTING state you will need to fully restore the database from backups.
A failover command returns as soon as the failover target has accepted the command. However, database recovery occurs asynchronously after the availability group has finished failing over.
Cross-database consistency across databases within the availability group is not maintained on failover.

Note

Cross-database transactions and distributed transactions are not supported by AlwaysOn Availability Groups. For more information, see Cross-Database Transactions Not Supported For Database Mirroring or AlwaysOn Availability Groups (SQL Server).

Prerequisites

The WSFC cluster has quorum. If the cluster lacks quorum, see WSFC Disaster Recovery through Forced Quorum (SQL Server).
You must be able to connect to a server instance that hosts a replica whose role is in the SECONDARY or RESOLVING state.

Recommendations

Do not force failover while the primary replica is still running.
If possible, force failover only to a failover target whose secondary databases are either in the NOT SYNCHRONIZED, SYNCHRONIZED, or SYNCHRONIZING state. For information about the implications of forcing failover when a secondary database is in the INTIAILIZGING or REVERTING state, see Limitations and Restrictions, earlier in this topic.
Typically, the latency of a given secondary database, relative to the primary database, should be similar on different asynchronous-commit secondary replicas. However, when forcing failover, data loss can be a significant concern. Therefore, consider taking time to determine the relative latency of the copies of the databases on different secondary replicas. To determine which copy of a given secondary database has the least latency, compare their end-of-log LSNs. A higher the end-of-log LSN indicates less latency.

Tip

To compare end-of-log LSNs, connect to each online secondary replica, in turn, and query sys.dm_hadr_database_replica_states for the end_of_log_lsn value of each local secondary database. Then, compare the end-of-log LSNs of the different copies of each database. Note that different databases might have their highest LSNs on different secondary replicas. In this case, the most appropriate failover target depends on the relative importance that you place on the data in the different databases. That is, for which of these databases would you most want to minimize possible data loss?
If clients are able to connect to the original primary, a forced failover incurs some risk of split brain behavior. Before you force failover, we strongly recommend that you prevent clients from accessing the original primary replica. Otherwise, after failover is forced, the original primary databases and the current primary databases could be updated independently of the other.

Potential Ways to Avoid Data Loss After Quorum is Forced

Under some failure conditions after quorum is lost, you can avoid prevent data loss, as follows:

If the original primary replica comes online

If quorum is lost and forcing WSFC quorum restores the cluster node that hosts the primary replica of an availability group, you can prevent data loss for this availability group. Connect to the primary replica and perform a forced failover (FAILOVER_ALLOW_DATA_LOSS). This brings the primary replica back online. Because you perform the forced failover to the original primary replica, there is no data loss.
If a synchronized synchronous-commit secondary replica comes online

If quorum is lost and forcing WSFC quorum restores a cluster node that hosts a synchronized secondary replica for an availability group, you should be able to prevent data loss for this availability group. If the restored node was up at the time that quorum was lost, you can determine whether data loss could occur on a given database by querying the is_failover_ready column of the sys.dm_hadr_database_replica_cluster_states dynamic management view. For example, for a server instance named sql108w2k8r22, issue the following query:
```
SELECT * FROM sys.dm_hadr_database_replica_cluster_states
   WHERE replica_id=(SELECT replica_id FROM sys.availability_replicas 
      WHERE replica_server_name ='sql108w2k8r22')
```
Warning

If the restored node was not up at the time quorum was lost, is_failover_ready may not reflect the cluster's actual state at the time the primary replica went offline. Therefore, the is_failover_ready value is only good if the host node at the time of the failure. For more information, see "Why Forced Failover is Required After Forcing Quorum" in Failover and Failover Modes (AlwaysOn Availability Groups).

If is_failover_ready = 1, the database is marked as synchronized in the cluster and is ready for a failover. If is_failover_ready = 1 on every database on a given secondary replica, you can perform a forced failover (FORCE_FAILOVER_ALLOW_DATA_LOSS) without data loss on this secondary replica. The synchronized secondary replica comes online in the primary role, that is, as the new primary replica, with all the data intact.

If is_failover_ready = 0, the database is not marked as synchronized in the cluster and is not ready for a planned manual failover. If you force failover to the host secondary replica, data will be lost on this database.

Note

When you force failover to a secondary replica, the amount of data loss will depend on how far the failover target is lagging behind the primary replica. Unfortunately, when the WSFC cluster lacks quorum or quorum has been forced, you cannot assess the amount of potential data loss. Note, however, that once the WSFC cluster regains a healthy quorum, you could begin to track potential data loss. For more information, see "Tracking Potential Data Loss" in Failover and Failover Modes (AlwaysOn Availability Groups).

Security

Permissions

Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Arrow icon used with Back to Top link [Top]

Using SQL Server Management Studio

To force failover (with possible data loss)

In Object Explorer, connect to a server instance that hosts a replica whose role is in the SECONDARY or RESOLVING state in the availability group that needs to be failed over, and expand the server tree.
Expand the AlwaysOn High Availability node and the Availability Groups node.
Right-click the availability group to be failed over, and select the Failover command.
This launches the Failover Availability Group Wizard. For more information, see Use the Fail Over Availability Group Wizard (SQL Server Management Studio).
After forcing an availability group to fail over, complete the necessary follow-up steps. For more information, see Follow Up: Essential Tasks After a Forced Failover, later in this topic.

Arrow icon used with Back to Top link [Top]

Using Transact-SQL

To force failover (with possible data loss)

Connect to a server instance that hosts a replica whose role is in the SECONDARY or RESOLVING state in the availability group that that needs to be failed over.
Use the ALTER AVAILABILITY GROUP statement, as follows:

ALTER AVAILABILITY GROUP group_name FORCE_FAILOVER_ALLOW_DATA_LOSS

where group_name is the name of the availability group.

The following example forces the AccountsAG availability group to fail over to the local secondary replica.
```
ALTER AVAILABILITY GROUP AccountsAG FORCE_FAILOVER_ALLOW_DATA_LOSS;
```
After forcing an availability group to fail over, complete the necessary follow-up steps. For more information, see Follow Up: Essential Tasks After a Forced Failover, later in this topic.

Arrow icon used with Back to Top link [Top]

Using PowerShell

To force failover (with possible data loss)

Change directory (cd) to a server instance that hosts a replica whose role is in the SECONDARY or RESOLVING state in the availability group that that needs to be failed over.
Use the Switch-SqlAvailabilityGroup cmdlet with the AllowDataLoss parameter in one of the following forms:
- -AllowDataLoss
  
  By default -AllowDataLoss parameter causes Switch-SqlAvailabilityGroup to prompt you to remind you that forcing failover might result in the loss of uncommitted transactions and to request confirmation. To continue, enter Y; to cancel the operation, enter N.
  
  The following example performs a forced failover (with possible data loss) of the availability group MyAg to the secondary replica on the server instance named SecondaryServer\InstanceName. You will be prompted to confirm this operation.
```
Switch-SqlAvailabilityGroup `
   -Path SQLSERVER:\Sql\SecondaryServer\InstanceName\AvailabilityGroups\MyAg `
   -AllowDataLoss
```
- -AllowDataLoss -Force
  
  To initiate a forced failover without confirmation, specify both the -AllowDataLoss and -Force parameters. This is useful if you want to include the command in a script and run it without user interaction. However, use the -Force option with caution, because a forced failover might result in the loss of data from databases participating the availability group.
  
  The following example performs a forced failover (with possible data loss) of the availability group MyAg to the server instance named SecondaryServer\InstanceName. The -Force option suppresses confirmation of this operation.
```
Switch-SqlAvailabilityGroup `
   -Path SQLSERVER:\Sql\SecondaryServer\InstanceName\AvailabilityGroups\MyAg `
   -AllowDataLoss -Force
```
Note

To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.
After forcing an availability group to fail over, complete the necessary follow-up steps. For more information, see Follow Up: Essential Tasks After a Forced Failover, later in this topic.

To set up and use the SQL Server PowerShell provider

SQL Server PowerShell Provider

Arrow icon used with Back to Top link [Top]

Follow Up: Essential Tasks After a Forced Failover

After a forced failover, the secondary replica to which you failed over becomes the new primary replica. However, to make that availability replica accessible to clients, you might need to reconfigure the WSFC quorum or adjust the availability-mode configuration of the availability group, as follows:
- If you failed over outside of the automatic failover set: Adjust the quorum votes of the WSFC nodes to reflect your new availability group configuration. If the WSFC node that hosts the target secondary replica does not have a WSFC quorum vote, you might need to force WSFC quorum.
  
  Note
  
  An automatic failover set exists only if two availability replicas (including the previous primary replica) are configured for synchronous-commit mode with automatic failover.
  
  To adjust quorum votes
- If you failed over outside of the synchronous-commit failover set: We recommend that you consider adjusting the availability mode and failover mode on the new primary replica and on remaining secondary replicas to reflect your desired synchronous-commit and automatic failover configuration.
  
  Note
  
  A synchronous-commit failover set exists only if the current primary replica is configured for synchronous-commit mode.
  
  To change the availability mode and failover mode
  - Change the Availability Mode of an Availability Replica (SQL Server)
  - Change the Failover Mode of an Availability Replica (SQL Server)
After a forced failover, all secondary databases are suspended. This includes the former primary databases, after the former primary replica comes back online and discovers that it is now a secondary replica. You must manually resume each suspended database individually on each secondary replica.

When a secondary database is resumed, it initiates data synchronization with the corresponding primary database. The secondary database rolls back any log records that were never committed on the new primary database. Therefore, if you are concerned about possible data loss on the post-failover primary databases, you should attempt to create a database snapshot on the suspended databases on one of the synchronous-commit secondary databases.

Important

Transaction log truncation is delayed on a primary database while any of its secondary databases is suspended. Also the synchronization health of a synchronous-commit secondary replica cannot transition to HEALTHY as long as any local database remains suspended.

To create a database snapshot
- Create a Database Snapshot (Transact-SQL)
To resume an availability database
- Resume an Availability Database (SQL Server)
Warning

After resuming all the secondary databases, before attempting to fail over the group again, wait for every secondary database on the next failover target to enter the SYNCHRONIZING state. If any database is not yet SYNCHRONIZING, that database will be prevented from coming online as a primary database, and re-establishing data synchronization for the database might require restoring transaction logs, restoring a full database backup, or failing over back to the previous primary replica.
If an availability replica that failed will not be returning to the availability replica or will return too late for you to delay transaction log truncation on the new primary database, consider removing the failed replica from the availability group to avoid running out of disk space for your log files.

To remove a secondary replica
- Remove a Secondary Replica from an Availability Group (SQL Server)
If you follow a forced failover with one or more additional forced failovers, perform a log backup after each additional forced failover in the series. For information about the reason for this, see "Risks of Forcing Failover" in the "Forced Manual Failover (with Possible Data Loss)" section of Failover and Failover Modes (AlwaysOn Availability Groups).

To perform a log backup
- Back Up a Transaction Log (SQL Server)

Arrow icon used with Back to Top link [Top]

Example Scenario: Using a Forced Failover to Recover from a Catastrophic Failure

If the primary replica fails and no synchronized secondary replica is available, forcing the availability group to fail over might be an appropriate response. The appropriateness of forcing a failover depends on: (1) whether you expect the primary replica to be offline for longer than your service level agreement (SLA) tolerates, and (2) whether you are willing to risk potential data loss in order to make primary databases available quickly. If you decide that an availability group requires a forced failover, the actual forced failover is but one step of a multi-step process.

To illustrate the steps that are required to use a forced failover to recover from a catastrophic failure, this topic presents one possible disaster recovery scenario. The example scenario considers an availability group whose original topology consists of a main data center that hosts three synchronous-commit availability replicas, including the primary replica, and a remote data center that hosts two asynchronous-commit secondary replicas. The following figure illustrates the original topology of this example availability group. The availability group is hosted by a multi-subnet WSFC cluster with three nodes in the main data center (Node 01, Node 02, and Node 03) and two nodes in a remote data center (Node 04 and Node 05).

Original topology of availability group

The main data center shuts down unexpectedly. Its three availability replicas to go offline, and their databases become unavailable. The following figure illustrates the impact of this failure on the topology of the availability group.

Topology after failure of main data center

The database administrator (DBA) determines that the best possible response is to force failover of the availability group to one of the remote, asynchronous-commit secondary replicas. This example illustrates the typical steps involved when you force failover of the availability group to a remote replica and, eventually, return the availability group to its original topology.

The failure-response presented here consists of the following two phases:

Responding to the catastrophic failure of the main data center
Returning the Availability Group to its Original Topology

Responding to the Catastrophic Failure of the Main Data Center

The following figure illustrates the series of actions performed at the remote data center in response a catastrophic failure at the main data center.

Steps for responding to failure of main data cente

The steps in this figure indicate the following steps:

Step	Action	Links
1.	The DBA or network administrator ensures that the WSFC cluster has a healthy quorum. In this example, quorum needs to be forced.	WSFC Quorum Modes and Voting Configuration (SQL Server) WSFC Disaster Recovery through Forced Quorum (SQL Server)
2.	The DBA connects to the server instance with the least latency (on Node 04) and performs a forced manual failover. The forced failover transitions this secondary replica to the primary role and suspends the secondary databases on the remaining secondary replica (on Node 05).	sys.dm_hadr_database_replica_states (Transact-SQL) (Query the end_of_log_lsn column. For more information, see Recommendations, earlier in this topic.) Perform a Forced Manual Failover of an Availability Group (SQL Server)
3.	The DBA manually resumes each of the secondary databases on the remaining secondary replica.	Resume an Availability Database (SQL Server)

Arrow icon used with Back to Top link [Top]

Returning the Availability Group to its Original Topology

The following figure illustrates the series of actions that return the availability group to its original topology after the main data center comes back online and the WSFC nodes re-establish communication with the WSFC cluster.

Important

If the WSFC cluster quorum has been forced, as the offline nodes restart they could form a new quorum if the following conditions both exist: (a) there is no network connectivity between any of the nodes in the forced-quorum set, and (b) the restarting nodes are the majority of the cluster nodes. This would result in a "split brain" condition in which the availability group would possess two independent primary replicas, one at each data center. Before forcing quorum to create a minority quorum set, see WSFC Disaster Recovery through Forced Quorum (SQL Server).

Steps to return the group to its original topology

The steps in this figure indicate the following steps:

	Step	Links
1.	The nodes in the main data center come back online and re-establish communication with the WSFC cluster. Their availability replicas come online as secondary replicas with suspended databases, and the DBA will need to manually resume each of these databases soon.	Resume an Availability Database (SQL Server) Tip If you are concerned about possible data loss on the post-failover primary databases, you should attempt to create a database snapshot on the suspended databases on one the synchronous-commit secondary database. Keep in mind that the transaction log truncation is delayed on a primary database while any of its secondary databases is suspended. Also the synchronization health of the synchronous-commit secondary replica cannot transition to HEALTHY as long as any local database remains suspended.
2.	Once the databases are resumed, the DBA changes the new primary replica to synchronous-commit mode temporarily. This involves two steps: Change one offline availability replica to asynchronous-commit mode. Change the new primary replica to synchronous-commit mode. Note This step enables resumed synchronous-commit secondary databases to become SYNCHRONIZED.	Change the Availability Mode of an Availability Replica (SQL Server)
3.	Once the synchronous-commit secondary replica on Node 03 (the original primary replica) enters the HEALTHY synchronization state, the DBA performs a planned manual failover to that replica, to make it the primary replica again. The replica on returns to being a secondary replica.	sys.dm_hadr_database_replica_states (Transact-SQL) Use AlwaysOn Policies to View the Health of an Availability Group (SQL Server) Perform a Planned Manual Failover of an Availability Group (SQL Server)
4.	The DBA connects to the new primary replica and: Changes the former primary replica (in the remote center) back to asynchronous-commit mode. Changes the asynchronous-commit secondary replica in the main data center back to synchronous-commit mode.	Change the Availability Mode of an Availability Replica (SQL Server)

Bagikan melalui

Perform a Forced Manual Failover of an Availability Group (SQL Server)