Use the Fail Over Availability Group Wizard (SQL Server Management Studio)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
This topic describes how to perform a planned manual failover or forced manual failover (forced failover) on an AlwaysOn availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2016. An availability group fails over at the level of an availability replica. If you fail over to a secondary replica in the SYNCHRONIZED state, the wizard performs a planned manual failover (without data loss). If you fail over to a secondary replica in the UNSYNCHRONIZED or NOT SYNCHRONIZING state, the wizard performs a forced manual failover—also known as a forced failover (with possible data loss). Both forms of manual failover transition the secondary replica to which you are connected to the primary role. A planned manual failover currently transitions the former primary replica to the secondary role. After a forced failover, when the former primary replica comes online, it transitions to the secondary role.
Before you begin:
To fail over an availability group, using:
Fail Over Availability Group Wizard pages:
Select New Primary Replica page (later in this topic)
Connect to Replica page (later in this topic)
Confirm Potential Data Loss page (later in this topic)
Before your first planned manual failover, see the "Before You Begin" section in Perform a Planned Manual Failover of an Availability Group (SQL Server).
Before your first forced failover, see the "Before You Begin" and "Follow Up: Essential Tasks After a Forced Failover" sections in Perform a Forced Manual Failover of an Availability Group (SQL Server).
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 might not be maintained on failover.
Support for cross-database and distributed transactions vary by SQL Server and operating system versions. For more information, see Cross-Database Transactions Support For Database Mirroring or AlwaysOn Availability Groups (SQL Server).
Prerequisites for Using the Failover Availability Group Wizard
You must be connected to the server instance that hosts an availability replica that is currently available.
Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
To Use the Failover Availability Group Wizard
In Object Explorer, connect to the server instance that hosts a secondary replica of the availability group that needs to be failed over, and expand the server tree.
Expand the AlwaysOn High Availability node and the Availability Groups node.
To launch the Failover Availability Group Wizard, right-click the availability group that you are going to fail over, and select Failover.
The information presented by the Introduction page depends on whether any secondary replica is eligible for a planned failover. If this page says, "Perform a planned failover for this availability group", you can failover the availability group without data loss.
On the Select New Primary Replica page, you can view the status of the current primary replica and of the WSFC quorum, before you choose the secondary replica that will become the new primary replica (the failover target). For a planned manual failover, be sure to select a secondary replica whose Failover Readiness value is "No data loss". For a forced failover, for all the possible failover targets, this value will be “Data loss, Warnings(#)”, where # indicates the number of warnings that exist for a given secondary replica. To view the warnings for a given failover target, click its “Failover Readiness” value.
For more information, see Select New Primary Replica page, later in this topic.
On the Connect to Replica page, connect to the failover target. For more information, see Connect to Replica page, later in this topic.
If you are performing a forced failover, the wizard displays the Confirm Potential Data Loss page. To proceed with the failover, you must select Click here to confirm failover with potential data loss. For more information, see .Confirm Potential Data Loss page, later in this topic.
On the Summary page, review the implications of failing over to the selected secondary replica.
If you are satisfied with your selections, optionally click Script to create a script of the steps the wizard will execute. Then, to failover the availability group to the selected secondary replica, click Finish.
The Progress page displays the progress of failing over the availability group.
When the failover operation finishes, the Results page displays the result. When the wizard completes, click Close to exit.
For more information, see Results Page (AlwaysOn Availability Group Wizards).
After a forced failover, see the "Follow Up: After a Forced Failover" section in the Perform a Forced Manual Failover of an Availability Group (SQL Server).
This section describes the pages that are unique to the Fail Over Availability Group Wizard.
In This Section
The other pages of this wizard share help with one or more of the other AlwaysOn Availability Groups wizards and are documented in separate F1 help topics.
Select New Primary Replica Page
This section describes the options of the Select New Primary Replica page. Use this page to select the secondary replica (failover target) to which the availability group will fail over. This replica will become the new primary replica.
Confirm Potential Data Loss Page
This section describes the options of the Confirm Potential Data Loss page, which is displayed only if you are performing a forced failover. This topic is used only by the Fail Over Availability Group Wizard. Use this page to indicate whether you are willing to risk possible data loss in order to force the availability group to fail over.
Confirm Potential Data Loss Options
If the selected secondary replica is not synchronized with the primary replica, the wizard displays a warning that failing over to this secondary replica could cause data loss on one or more databases.
Connect to Replica Page
This section describes the options of the Connect to Replica page of the Fail Over Availability Group Wizard. This page is displayed only if you are not connected to the target secondary replica. Use this page to connect to the secondary replica that you have selected as the new primary replica.