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

  • Makale

This topic describes how to perform a manual failover without data loss (a planned manual failover) on an AlwaysOn availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2014. An availability group fails over at the level of an availability replica. A planned manual failover, like any Always On Availability Groups failover, transitions a secondary replica to primary role and, concurrently, transitions the former primary replica to the secondary role.

A planned manual failover, which is supported only when the primary replica and the target secondary replica are running in synchronous-commit mode and are currently synchronized, preserves all the data in the secondary databases that are joined to the availability group on the target secondary replica. Once the former primary replica transitions to the secondary role, its databases become secondary databases and begin synchronizing with the new primary databases. After they all transition into the SYNCHRONIZED state, the new secondary replica becomes eligible to serve as the target of a future planned manual failover.

Note

If the secondary and primary replicas are both configured for automatic failover mode, once the secondary replica is synchronized, it can also serve as the target for an automatic failover. For more information, see Availability Modes (AlwaysOn Availability Groups).

Before You Begin

Limitations and Restrictions

  • A failover command returns as soon as the target secondary replica 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 Always On Availability Groups. For more information, see Cross-Database Transactions Not Supported For Database Mirroring or AlwaysOn Availability Groups (SQL Server).

Prerequisites and Restrictions

  • The target secondary replica and the primary replica must both be running in synchronous-commit availability mode.

  • The target secondary replica must currently be synchronized with the primary replica. This requires that all the secondary databases on this secondary replica must have been joined to the availability group and be synchronized with their corresponding primary databases (that is, the local secondary databases must be SYNCHRONIZED).

    Tip

    To determine the failover readiness of an secondary replica, query the is_failover_ready column in the sys.dm_hadr_database_cluster_states dynamic management view, or look at the Failover Readiness column of the AlwaysOn Group Dashboard.

  • This task is supported only on the target secondary replica. You must be connected to the server instance that hosts the target secondary replica.

Security

Permissions

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

Using SQL Server Management Studio

To manually fail over an availability group

  1. In Object Explorer, connect to a server instance that hosts a secondary replica of the availability group that needs to be failed over, and expand the server tree.

  2. Expand the AlwaysOn High Availability node and the Availability Groups node.

  3. Right-click the availability group to be failed over, and select the Failover command.

  4. This launches the Failover Availability Group Wizard. For more information, see Use the Fail Over Availability Group Wizard (SQL Server Management Studio).

Using Transact-SQL

To manually fail over an availability group

  1. Connect to the server instance that hosts the target secondary replica.

  2. Use the ALTER AVAILABILITY GROUP statement, as follows:

    ALTER AVAILABILITY GROUP group_name FAILOVER

    where group_name is the name of the availability group.

    The following example manually fails over the MyAg availability group to the connected secondary replica.

    ALTER AVAILABILITY GROUP MyAg FAILOVER;

Using PowerShell

To manually fail over an availability group

  1. Change directory (cd) to the server instance that hosts the target secondary replica.

  2. Use the Switch-SqlAvailabilityGroup cmdlet.

    Note

    To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server 2014 PowerShell environment. For more information, see Get Help SQL Server PowerShell.

    The following example manually fails over the MyAg availability group to the secondary replica with the specified path.

    Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\SecondaryServer\InstanceName\AvailabilityGroups\MyAg

To set up and use the SQL Server PowerShell provider

Follow Up: After Manually Failing Over an Availability Group

If you failed over outside of the automatic failover set of the availability group, adjust the quorum votes of the WSFC nodes to reflect your new availability group configuration. For more information, see Windows Server Failover Clustering (WSFC) with SQL Server.

See Also

Overview of AlwaysOn Availability Groups (SQL Server)
Failover and Failover Modes (AlwaysOn Availability Groups)
Perform a Forced Manual Failover of an Availability Group (SQL Server)