Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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

SQL Server 2012

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 2012. An availability group fails over at the level of an availability replica. A planned manual failover, like any AlwaysOn 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 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).

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 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 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 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.

Arrow icon used with Back to Top link [Top]

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).

Arrow icon used with Back to Top link [Top]

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;
    

Arrow icon used with Back to Top link [Top]

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 Note

    To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server 2012 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

Arrow icon used with Back to Top link [Top]

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.

Arrow icon used with Back to Top link [Top]

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.