| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Perform a planned manual failover of an availability group |
This topic describes how to perform a planned manual failover of an Always On availability group. |
MashaMSFT |
mathoma |
10/25/2017 |
sql |
availability-groups |
conceptual |
|
|
[!INCLUDE SQL Server] This topic describes how to perform a manual failover without data loss (a planned manual failover) on an Always On availability group by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], or PowerShell in [!INCLUDEssnoversion]. An availability group fails over at the level of an availability replica. A planned manual failover, like any Always On availability group failover, transitions a secondary replica to primary role. Concurrently, the failover transitions the former primary replica to the secondary role.
A planned manual failover is supported only when the primary replica and the target secondary replica are running in synchronous-commit mode and are currently synchronized. A planned manual failover preserves all the data in the secondary databases that are joined to the availability group on the target secondary replica. After the former primary replica transitions to the secondary role, its databases become secondary databases. Then they begin to synchronize 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, after the secondary replica is synchronized, it also can serve as the target for an automatic failover. For more information, see Availability modes (Always On availability groups).
Important
There are specific procedures to fail over a read-scale availability group with no cluster manager. When an availability group has CLUSTER_TYPE = NONE, follow the procedures under Fail over the primary replica on a read-scale availability group.
-
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 might not be maintained on failover.
[!NOTE] Support for cross-database and distributed transactions vary by SQL Server and operating system versions. For more information, see Cross-database transactions and distributed transactions for Always On availability groups and database mirroring (SQL Server).
-
Both the target secondary replica and the primary replica must be running in synchronous-commit availability mode.
-
Currently, the target secondary replica must be synchronized with the primary replica. All the secondary databases on this secondary replica must be joined to the availability group. They also must be synchronized with their corresponding primary databases (that is, the local secondary databases must be SYNCHRONIZED).
[!TIP] To determine the failover readiness of a secondary replica, query the is_failover_ready column in the sys.dm_hadr_database_replica_cluster_states dynamic management view. Or you can look at the Failover Readiness column of the Always On 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.
The ALTER AVAILABILITY GROUP permission is required on the availability group. The CONTROL AVAILABILITY GROUP permission, the ALTER ANY AVAILABILITY GROUP permission, or the CONTROL SERVER permission also is required.
To manually fail over an availability group:
-
In Object Explorer, connect to a server instance that hosts a secondary replica of the availability group that needs to be failed over. Expand the server tree.
-
Expand the Always On High Availability node and the Availability Groups node.
-
Right-click the availability group to be failed over, and select Failover.
-
The Failover Availability Group wizard starts. For more information, see Use the Failover Availability Group wizard (SQL Server Management Studio).
To manually fail over an availability group:
-
Connect to the server instance that hosts the target secondary replica.
-
Use the ALTER AVAILABILITY GROUP statement, as follows:
ALTER AVAILABILITY GROUP group_name FAILOVER
In the statement, 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;
To manually fail over an availability group:
-
Change the directory (cd) to the server instance that hosts the target secondary replica.
-
Use the Switch-SqlAvailabilityGroup cmdlet.
[!NOTE] To view the syntax of a cmdlet, use the Get-Help cmdlet in the [!INCLUDEssnoversion] PowerShell environment. For more information, see Get help for 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\MyAgTo set up and use the SQL Server PowerShell provider:
If you failed over outside the [!INCLUDEssFosAuto] of the availability group, adjust the quorum votes of the Windows Server failover clustering nodes to reflect your new availability group configuration. For more information, see Windows Server failover clustering (WSFC) with SQL Server.
[!INCLUDEForce failover]