TechNet
Export (0) Print
Expand All

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

 

Updated: May 17, 2016

Applies To: SQL Server 2016

This topic describes how to perform a manual failover without data loss (a planned manual failover) on an Always On 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. A planned manual failover, like any Always On 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.

System_CAPS_ICON_note.jpg 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 (Always On Availability Groups).

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

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

Security

Permissions

Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

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

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;  
    
    

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.

    System_CAPS_ICON_note.jpg Note


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

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.

Overview of Always On Availability Groups (SQL Server)
Failover and Failover Modes (Always On Availability Groups)
Perform a Forced Manual Failover of an Availability Group (SQL Server)

Community Additions

ADD
Show:
© 2016 Microsoft