Perform a planned manual failover of an availability group (SQL Server)

THIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

This topic describes how to perform a manual failover without data loss (a planned manual failover) on an AlwaysOn availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2017. An availability group fails over at the level of an availability replica. A planned manual failover, like any AlwaysOn 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 failver 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 (AlwaysOn availability groups).

Before you begin

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.

Limitations and restrictions

Prerequisites and restrictions

  • 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_cluster_states dynamic management view. Or you can 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

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.

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

  4. The Failover Availability Group wizard starts. For more information, see Use the Failover Availability Group wizard (SQL Server Management Studio).

Use 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

    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;  
    

Use PowerShell

To manually fail over an availability group:

  1. Change the 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 SQL Server 2017 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\MyAg  
    

    To set up and use the SQL Server PowerShell provider:

Follow up: After you manually fail over an availability group

If you failed over outside the automatic failover set 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.

Fail over the primary replica on a read-scale availability group

Each availability group has only one primary replica. The primary replica allows reads and writes. To change which replica is primary, you can fail over. In an availability group for high availability, the cluster manager automates the failover process. In a read-scale availability group, the failover process is manual.

There are two ways to fail over the primary replica in a read-scale availability group:

  • Forced manual failover with data loss
  • Manual failover without data loss

Forced manual failover with data loss

Use this method when the primary replica isn't available and can't be recovered.

To force failover with data loss, connect to the SQL Server instance that hosts the target secondary replica and run:

ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Manual failover without data loss

Use this method when the primary replica is available, but you need to temporarily or permanently change the configuration and change the SQL Server instance that hosts the primary replica. Before you issue the manual failover, ensure that the target secondary replica is up to date to avoid potential data loss.

To manually fail over without data loss:

  1. Make the target secondary replica SYNCHRONOUS_COMMIT.

    ALTER AVAILABILITY GROUP [ag1] 
         MODIFY REPLICA ON N'**<node2>*' 
         WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
    
  2. Run the following query to identify that active transactions are committed to the primary replica and at least one synchronous secondary replica:

    SELECT ag.name, 
       drs.database_id, 
       drs.group_id, 
       drs.replica_id, 
       drs.synchronization_state_desc, 
       ag.sequence_number
    FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
    WHERE drs.group_id = ag.group_id; 
    

    The secondary replica is synchronized when synchronization_state_desc is SYNCHRONIZED.

  3. Update REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1.

    The following script sets REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1 on an availability group named ag1. Before you run the following script, replace ag1 with the name of your availability group:

    ALTER AVAILABILITY GROUP [ag1] 
         SET REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1;
    

    This setting ensures that every active transaction is committed to the primary replica and at least one synchronous secondary replica.

  4. Demote the primary replica to a secondary replica. After the primary replica is demoted, it's read-only. Run this command on the SQL Server instance that hosts the primary replica to update the role to SECONDARY:

    ALTER AVAILABILITY GROUP [ag1] 
         SET (ROLE = SECONDARY); 
    
  5. Promote the target secondary replica to primary.

    ALTER AVAILABILITY GROUP ag1 FORCE_FAILOVER_ALLOW_DATA_LOSS; 
    

    Note

    To delete an availability group, use DROP AVAILABILITY GROUP. For an availability group created with CLUSTER_TYPE NONE or EXTERNAL, the command must be executed on all replicas that are part of the availability group.

See also