Skip to content

perform-a-planned-manual-failover-of-an-availability-group-sql-server.md

Latest commit

 

History

History
111 lines (73 loc) · 7.78 KB

perform-a-planned-manual-failover-of-an-availability-group-sql-server.md

File metadata and controls

111 lines (73 loc) · 7.78 KB
title Perform a Planned Manual Failover of an Availability Group (SQL Server) | Microsoft Docs
ms.custom
ms.date 06/13/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology high-availability
ms.topic conceptual
f1_keywords
sql12.swb.availabilitygroup.manualfailover.f1
helpviewer_keywords
Availability Groups [SQL Server], failover
failover [SQL Server], AlwaysOn Availability Groups
ms.assetid 419f655d-3f9a-4e7d-90b9-f0bab47b3178
author MashaMSFT
ms.author mathoma
manager craigg

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

This topic describes how to perform a manual failover without data loss (a planned manual failover) on an AlwaysOn availability group by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], or PowerShell in [!INCLUDEssCurrent]. An availability group fails over at the level of an availability replica. A planned manual failover, like any [!INCLUDEssHADR] 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

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 [!INCLUDEssCurrent] 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 [!INCLUDEssFosAuto] 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)