Failover and Failover Modes (AlwaysOn Availability Groups)

When you configure an availability replica as synchronous commit with automatic failover, the availability replica becomes part of the automatic failover set. However whether the set takes effect depends the current primary. The forms of failover that are actually possible at a given time depends on what failover sets are currently in effect.

For example, consider an availability group that has four availability replicas, as follows:

The failover behavior for each secondary replica depends on which availability replica is currently the primary replica. Basically, for a given secondary replica, the failover behavior is the worst case given the current primary replica. The following figure illustrates how the failover behavior of secondary replicas varies depending on the current primary replica, and whether it is configured for asynchronous-commit mode (with only forced manual failover) or synchronous-commit mode (with or without automatic failover).

[Top]

Automatic failover occurs only under the following conditions:

• Both the primary replica and a secondary replica (the automatic failover target) are configured for synchronous-commit mode and are both set to AUTOMATIC failover. If either the primary or secondary replica is set MANUAL failover, automatic failover cannot occur.

• The automatic failover target has a healthy synchronization state (this indicates that every secondary database on the failover target is synchronized with its corresponding primary database).

  For more information, see Availability Modes (AlwaysOn Availability Groups).

• The Windows Server Failover Clustering (WSFC) cluster has quorum. For more information, see WSFC Quorum Modes and Voting Configuration (SQL Server).

• The primary replica has become unavailable, and the failover-condition levels defined by your the flexible failover policy have been met. For information about failover-condition levels, see Flexible Failover Policy for Automatic Failover of an Availability Group (SQL Server).

An automatic failover initiates the following sequence of actions:

1. If the server instance that is hosting the current primary replica is still running, it changes the state of the primary databases to DISCONNECTED and disconnects all clients.

2. If any log records are waiting in recovery queues on the target secondary replica, the secondary replica applies the remaining log records to finish rolling forward the secondary databases.

   Note
   The amount of time required to apply the log to a given database depends on the speed of the system, the recent work load, and the amount of log in the recovery queue.

3. The former secondary replica transitions to the primary role. Its databases become the primary databases. The new primary replica rolls back any uncommitted transactions (the undo phase of recovery) as quickly as possible. Locks isolate these uncommitted transactions, allowing roll back to occur in the background while clients use the database. This process does not roll back any committed transactions.

   Until a given secondary database is connected, it is briefly marked as NOT_SYNCHRONIZED. Before the rollback recovery starts, secondary databases can connect to the new primary databases and quickly transition to the SYNCHRONIZED state. The best case is usually for a third synchronous-commit replica that remains in the secondary role after the failover.

4. Later, when the server instance that is hosting the former primary replica restarts, it recognizes that another availability replica now owns the primary role. The former primary replica transitions to the secondary role, and its databases become secondary databases. The new secondary replica connects to the current primary replica and catches its database up to the current primary databases as quickly as possible. As soon as the new secondary replica has resynchronized its databases, failover is again possible, in the reverse direction.

An availability replica can be configured to support automatic failover at any point.

To configure automatic failover

1. Ensure that the secondary replica is configured to use the synchronous-commit availability mode. For more information, see Change the Availability Mode of an Availability Replica (SQL Server).

2. Set the failover mode to automatic. For more information, see Change the Failover Mode of an Availability Replica (SQL Server).

3. Optionally, change the flexible failover policy of the availability group to specify the sorts of failures that can cause an automatic failover to occur. For more information, see Configure the Flexible Failover Policy to Control Conditions for Automatic Failover (AlwaysOn Availability Groups) and Failover Policy for Failover Cluster Instances.

[Top]

To support a manual failover, the current primary replica must be set to synchronous-commit mode and a secondary replica must be:

• Configured for synchronous-commit mode.

• Currently synchronized with the primary replica.

To manually fail over an availability group, you must be connected to the secondary replica that is to become the new primary replica.

A planned manual failover, which must be initiated on the target secondary replica, initiates the following sequence of actions:

1. To ensure that no new user transactions occur on the original primary databases, the WSFC cluster sends a request to the primary replica to go offline.

2. If any log is waiting in the recovery queue of any secondary database, the secondary replica finishes rolling forward that secondary database. The amount of time required depends on the speed of the system, the recent workload, and the amount of log in the recovery queue. To learn the current size of the recovery queue, use the Recovery Queue performance counter. For more information, see SQL Server, Database Replica.

   Note
   The failover time can be regulated by limiting the size of the recovery queue. However, this can cause the primary replica to slow down to allow the secondary replica to keep up.

3. The secondary replica becomes the new primary replica, and the former primary replica becomes the new secondary replica.

4. The new primary replica rolls back any uncommitted transactions and brings its databases online as the primary databases. All secondary databases are briefly marked as NOT SYNCHRONIZED until they connect and resynchronize to the new primary databases. This process does not roll back any committed transactions.

5. When the former primary replica comes back online, it takes on the secondary role, and the former primary database becomes the secondary database. The new secondary replica quickly resynchronizes the new secondary databases with the corresponding primary databases.

   Note
   As soon as the new secondary replica has resynchronized the databases, failover is again possible, but in the reverse direction.

After failover, clients must reconnect to the current primary database. For more information, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

[Top]

The database administrator for your availability groups can use manual failovers to maintain database availability when you upgrade hardware or software. To use an availability group for software upgrades, the server instance and/or computer node that hosts the target secondary replica must have already received the upgrades.

Note
Failing over availability groups should support a rolling upgrade, but this is not guaranteed.

[Top]

Forcing failover initiates a smooth transition of the primary role to the target secondary replica which becomes the new primary replica and immediately serves its copies of the databases to clients. When the former primary replica becomes available, it will transition to the secondary role and its databases will become secondary databases.

Every secondary database (including the former primary databases, when they become available). Depending on the previous data synchronization state of a suspended secondary database it might be suitable for salvaging missing committed data for that primary database. On a secondary replica that is configured for read-only access, you can query the secondary databases to manually discover missing data. Then you can issue Transact-SQL statements on the new primary databases to make any necessary changes.

It is essential to understand that forcing failover can cause data loss. Data loss is possible because the secondary replica cannot communicate with the primary replica and, therefore, cannot guarantee that the databases are synchronized. Forcing failover starts a new recovery fork. Because the original primary databases and secondary databases are on different recovery forks, each of them now contains data that the other database does not contain: each original primary database contains whatever changes were not yet sent from its send queue to the former secondary database (the unsent log); the former secondary databases contain whatever changes occur after failover was forced.

If failover is forced because the primary replica has failed, potential data loss is depends on whether any transaction logs had not been sent to the secondary replica before the failure. Under the asynchronous-commit mode, accumulated unsent log is always a possibility. Under synchronous-commit mode, this is possible only until the secondary databases becomes synchronized.

The following table summarizes the possibility of data loss for a particular database on a secondary replica to which you force failover.

Secondary databases track only two recovery forks, so if you perform multiple forced failovers, any secondary database that did start data synchronization with the previous force failover might not be able to resume. If this occurs, any secondary databases that cannot be resumed will need to be removed from the availability group, restored to the correct point in time, and rejoined to the availability group. A restore will not work across multiple recovery forks, therefore, be sure to perform a log backup after performing more than one forced failover.

After failover is forced, once the former primary replica is available, assuming that its databases are undamaged, you can attempt to manage the potential data loss. The available approach for managing potential data loss depends on whether the original primary replica has connected to the new primary replica. Assuming that the original primary replica can access the new primary instance, reconnecting occurs automatically and transparently.

The Original Primary Replica Has Reconnected

The Original Primary Replica Has Not Reconnected