Use the Fail Over Availability Group Wizard (SQL Server Management Studio)

  • Article

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

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 is not maintained on failover.

    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 for Using the Failover Availability Group Wizard

  • You must be connected to the server instance that hosts an availability replica that is currently available.

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]

Using SQL Server Management Studio

To Use the Failover Availability Group Wizard

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

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

  3. To launch the Failover Availability Group Wizard, right-click the availability group that you are going to fail over, and select Failover.

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

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

  6. On the Connect to Replica page, connect to the failover target. For more information, see Connect to Replica page, later in this topic.

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

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

  9. The Progress page displays the progress of failing over the availability group.

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

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

Arrow icon used with Back to Top link[Top]

Help for Pages that are Exclusive to This Wizard

This section describes the pages that are unique to the Fail Over Availability Group Wizard.

In This Section

  • Select New Primary Replica page

  • Connect to Replica page

  • Confirm Potential Data Loss page

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.

Page Options

  • Current Primary Replica
    Displays the name of the current primary replica, if it is online.

  • Primary Replica Status
    Displays the status of the current primary replica, if it is online.

  • Quorum Status
    Displays the WSFC quorum status for the availability replica, one of:

    Value

    Description

    Normal quorum

    The cluster has started with normal quorum.

    Forced quorum

    The cluster has started with forced quorum.

    Unknown quorum

    The cluster quorum status is unavailable.

    Not applicable

    The node that hosts the availability replica has no quorum.

    For more information, see WSFC Quorum Modes and Voting Configuration (SQL Server).

  • Choose a new primary replica
    Use this grid to select a secondary replica to become the new primary replica. The columns in this grid are as follows:

    • Server Instance
      Displays the name of a server instance that hosts a secondary replica.

    • Availability Mode
      Displays the availability mode of the server instance, one of:

      Value

      Description

      Synchronous commit

      Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected.

      Asynchronous commit

      Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.

      For more information, see Availability Modes (AlwaysOn Availability Groups).

    • Failover Mode
      Displays the failover mode of the server instance, one of:

      Value

      Description

      Automatic

      A secondary replica that is configured for automatic failover also supports planned manual failover whenever the secondary replica is synchronized with the primary replica.

      Manual

      Two types of manual failover exist: planned (without data loss) and forced (with possible data loss). For a given secondary replica, only one of these is supported, depending on the availability mode and, for synchronous-commit mode, the synchronization state of the secondary replica. To determine which form of manual failover is currently supported by a given secondary replica, see the Failover Readiness column of this grid.

      For more information, see Failover and Failover Modes (AlwaysOn Availability Groups).

    • Failover Readiness
      Displays failover readiness of the secondary replica, one of:

      Value

      Description

      No data loss

      This secondary replica currently supports planned failover. This value occurs only when a synchronous-commit mode secondary replica is currently synchronized with the primary replica.

      Data loss, Warnings(#)

      This secondary replica currently supports forced failover (with possible data loss). This value occurs whenever the secondary replica is not synchronized with the primary replica. Click the data-loss warnings link for information about the possible data loss.

  • Refresh
    Click to update the grid.

  • Cancel
    Click to cancel the wizard. On the Select New Primary Replica page, cancelling the wizard cause it to exit without performing any actions.

Arrow icon used with Back to Top link[Top]

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.

  • Click here to confirm failover with potential data loss.
    If you are willing to risk data loss in order to make the databases in this availability group available to users, click this checkbox. If you are not willing to risk data loss, you can either click Previous to return to the Select New Primary Replica page, or click Cancel to exit the wizard without failing over the availability group.

  • Cancel
    Click to cancel the wizard. On the Confirm Potential Data Loss page, cancelling the wizard cause it to exit without performing any actions.

Arrow icon used with Back to Top link[Top]

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.

Page Options

  • Grid columns:

    • Server Instance
      Displays the name of the server instance that will host the availability replica.

    • Connected As
      Displays the account that is connected to the server instance, once the connection has been established. If this column displays "Not Connected" for a given server instance, you will need to click the Connect button.

    • Connect
      Click if this server instance is running under a different account than other server instances to which you need to connect.

  • Cancel
    Click to cancel the wizard. On the Connect to Replica page, cancelling the wizard cause it to exit without performing any actions.

Arrow icon used with Back to Top link[Top]

See Also

Concepts

Overview of AlwaysOn Availability Groups (SQL Server)

Availability Modes (AlwaysOn Availability Groups)

Failover and Failover Modes (AlwaysOn Availability Groups)

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

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

WSFC Disaster Recovery through Forced Quorum (SQL Server)