Perform a planned manual failover of an availability group

Article
11/18/2022
7 minutes to read

Applies to: 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 SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server. 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).

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

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
(Video) SQL Server Always On Series - Always On Planned Manual Failover #jbswiki #alwayson
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).

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).
See Also
Perform operations on data - Azure Logic Apps Perform inner joins (LINQ in C#)Get started with formulas in canvas apps - Power Apps Review access to groups & applications in access reviews - Microsoft Entra
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.

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:

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

Use Transact-SQL

To manually fail over an availability group:

Connect to the server instance that hosts the target secondary replica.
(Video) How to Manually Failover an AlwaysOn Availability Group | XTIVIA Video Blog
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:

Change the directory (cd) to the server instance that hosts the target secondary replica.
See Also
Azure Synapse RBAC roles - Azure Synapse Analytics Run a message trace and view the results in the Exchange admin center in Exchange Online Azure AD Connect sync: Scheduler - Microsoft Entra
Use the Switch-SqlAvailabilityGroup cmdlet.
Note
To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server 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:
- SQL Server PowerShell provider
- Get help for SQL Server PowerShell

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 a typical availability group, the cluster manager automates the failover process. In an availability group with cluster type NONE, the failover process is manual.

There are two ways to fail over the primary replica in an availability group with cluster type NONE:

(Video) SQL Server 2019 Always On Availability Group Configuration Step by Step Guide

Manual failover without data loss
Forced manual failover with data loss

Manual failover without data loss

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

To manually fail over without data loss:

Make the current primary and target secondary replica SYNCHRONOUS_COMMIT.

ALTER AVAILABILITY GROUP [AGRScale] MODIFY REPLICA ON N'<node2>' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

To identify that active transactions are committed to the primary replica and at least one synchronous secondary replica, run the following query:
```
SELECT ag.name, drs.database_id, drs.group_id, drs.replica_id, drs.synchronization_state_desc, ag.sequence_numberFROM sys.dm_hadr_database_replica_states drs, sys.availability_groups agWHERE drs.group_id = ag.group_id; 
```
The secondary replica is synchronized when synchronization_state_desc is SYNCHRONIZED.
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 [AGRScale] 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.
Note
This setting is not specific to failover and should be set based on the requirements of the environment.
Set the primary replica and the secondary replica(s) not participating in the failover offline to prepare for the role change:
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```

Promote the target secondary replica to primary.

ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS;

Update the role of the old primary and other secondaries to SECONDARY, run the following command on the SQL Server instance that hosts the old primary replica:
```
ALTER AVAILABILITY GROUP [AGRScale] SET (ROLE = SECONDARY); 
```
Note
To delete an availability group, use DROP AVAILABILITY GROUP. For an availability group that's created with cluster type NONE or EXTERNAL, execute the command on all replicas that are part of the availability group.
(Video) SQL Server Always On Series - Always On Availability Group Automatic Failover to a Particular Node
Resume data movement, run the following command for every database in the availability group on the SQL Server instance that hosts the primary replica:
```
ALTER DATABASE [db1] SET HADR RESUME
```
Re-create any listener you created for read-scale purposes and that isn't managed by a cluster manager. If the original listener points to the old primary, drop it and re-create it to point to the new primary.

Forced manual failover with data loss

If the primary replica is not available and can't immediately be recovered, then you need to force a failover to the secondary replica with data loss. However, if the original primary replica recovers after failover, it will assume the primary role. To avoid having each replica be in a different state, remove the original primary from the availability group after a forced failover with data loss. Once the original primary comes back online, remove the availability group from it entirely.

To force a manual failover with data loss from primary replica N1 to secondary replica N2, follow these steps:

On the secondary replica (N2), initiate the forced failover:

ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;

On the new primary replica (N2), remove the original primary (N1):
```
ALTER AVAILABILITY GROUP [AGRScale]REMOVE REPLICA ON N'N1';
```
Validate that all application traffic is pointed to the listener and/or the new primary replica.
If the original primary (N1) comes online, immediately take availability group AGRScale offline on the original primary (N1):
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
If there is data or unsynchronized changes, preserve this data via backups or other data replicating options that suit your business needs.
Next, remove the availability group from the original primary (N1):
```
DROP AVAILABILITY GROUP [AGRScale];
```
Drop the availability group database on original primary replica (N1):
```
USE [master]GODROP DATABASE [AGDBRScale]GO
```
(Optional) If desired, you can now add N1 back as a new secondary replica to the availability group AGRScale.

FAQs

How do you always perform on failover? ›

Right-click on the availability group to failover from the primary instance and go to failover. Go through the failover wizard and perform an AG failover. Alternatively, you can run the Alter Availability Group command to initiate a failover from the primary to the secondary replica.

Explore More ›

How automatic failover happens in AlwaysOn availability groups? ›

Always On Availability Groups monitors the health of both replicas in an automatic failover set. If either replica fails, the availability group's health state is set to CRITICAL. If the secondary replica fails, automatic failover is not possible because the automatic failover target is unavailable.

Tell Me More ›

How do I manually failover an Azure SQL database? ›

In the Azure portal, browse to the primary database in the geo-replication partnership. Scroll to Data management, and then select Replicas. In the Geo replicas list, select the database you want to become the new primary, select the ellipsis, and then select Forced failover. Select Yes to begin the failover.

What is the disadvantage of always on SQL Server? ›

Disadvantages. You cannot safeguard system databases (Master, Model, and MSDB) from instance or database level failure. Always On does not support adding them to the availability groups. Always On does not synchronize SQL Server logins, linked servers, and Agent jobs to the secondary databases.

Get More Info ›

How to create a failover group in SQL Server? ›

Select the name of the server under Server name to open the settings for the server. Select Failover groups under the Settings pane, and then select Add group to create a new failover group. On the Failover Group page, enter or select the required values, and then select Create.

Read The Full Story ›

How does SQL Server failover work? ›

In the event of a failover, the WSFC service transfers ownership of instance's resources to a designated failover node. The SQL Server instance is then re-started on the failover node, and databases are recovered as usual. At any given moment, only a single node in the cluster can host the FCI and underlying resources.

How do you set up a failover server? ›

Here are the detailed steps.

Select Configure Failover. Right-click the selected DHCP scope, and select Configure Failover from the context menu. ...
Specify the partner server. ...
Create new failover relationship. ...
Select Finish to complete configuration.

Explore More ›

What are 3 requirements that must be met in order to build a failover cluster using Windows Server 2016? ›

All servers must use the same version of Windows Server 2016. All servers must be joined to an Active Directory domain. All servers must use identical hardware components.

Learn More ›

Whats the difference between a planned failover and the failover option when right clicking on the virtual machine and replica? ›

In the case of a “Planned Failover”, the Hyper-V Primary Server is notified of the operation/action so it takes necessary actions for the primary Virtual Machine. On the other hand, “Unplanned Failover”, which is performed at the Replica Server, does not provide any notification to the Replica Server.

Discover More Details ›

What is the difference between database mirroring and failover cluster in SQL? ›

Failover clusters provide high-availability support for an entire Microsoft SQL Server instance, in contrast to database mirroring, which provides high-availability support for a single database. Database mirroring works between failover clusters and, also, between a failover cluster and a nonclustered host.

Read The Full Story ›

What is always on availability mode in SQL Server? ›

Always On SQL Server is a High Availability and Disaster Recovery solution introduced in SQL Server 2012. It increases the availability by providing a failover environment for Availability Databases that failover together.

How long does SQL failover take? ›

Somewhere between 15 seconds and a minute usually. That said, there are a few reasons why failover may be on the slower side: Virtual Log File Count (VLFs)

View Details ›

How do I restore a SQL Server database Alwayson availability group? ›

Always ON - Restore Database to Secondary

Connect to Primary -> Go to Availabilty groups --> Availabilty Databases and Remove Secondary Database OR T-Sql. ...
Take FULL Backup from Primary and Restore into Secondary. ...
Take T-Log Backup from Primary and Restore into Secondary.

More items...

Feb 3, 2022

What is the difference between planned failover and failover? ›

Failover Now = Turns on the latest replica. workload from source VM is not migrated so we actually have some data loss depends on when the last replication was done. Planned Failover = Moves all workload in from source vm to replica. Powers off source vm and the replica is now acting same as the source VM.

Discover More Details ›

What is failover procedure? ›

What is failover? Failover is the process of switching to a redundant or standby computer server, system, hardware component or network. Other terms also used to describe this capability include role-swap or switching.

Discover More Details ›

What is failover and example? ›

Failover can apply to any aspect of a system. Within a personal computer, for example, failover might be a mechanism to protect against a failed processor. Within a network, failover can apply to any network component or system of components, such as a connection path, storage device or Web server.

Discover More Details ›

What are the 3 major principles to ensure high availability? ›

The following three principles are used when designing HA systems to ensure high availability:

Single points of failure. A single point of failure is a component that would cause the whole system to fail if it fails. ...
Reliable crossover. Building redundancy into these systems is also important. ...
Failure detectability.

How do you ensure high availability on a server? ›

Here are some of the key resources you can implement to make high availability possible:

Implement multiple application servers. ...
Scaling and slaves matters. ...
Spread out physically. ...
Maintain a recurring online backup system along with hardware. ...
Use of a virtualized server for zero-downtime recovery.

View Details ›

What is the difference between failover and failback? ›

The failover operation is the process of switching production to a backup facility (normally your recovery site). A failback operation is the process of returning production to its original location after a disaster or a scheduled maintenance period.

Read The Full Story ›

What are the top reasons that cause an unsuccessful failover? ›

Summary.
Symptoms if an automatic failover is triggered successfully.
Symptoms if an automatic failover is unsuccessful.
Case 1: "Maximum Failures in the Specified Period" value is exhausted.
Case 2: Insufficient NT Authority\SYSTEM account permissions.
Case 3: The availability databases aren't in a SYNCHRONIZED state.

More items...

Jan 27, 2023

How to enable or disable Always On Availability Group feature? ›

In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (<instance name>), where <instance name> is the name of a local server instance for which you want to enable Always On Availability Groups, and click Properties. Select the Always On High Availability tab.

Learn More Now ›

How to check availability group in SQL Server? ›

Right-click the availability group whose properties you want to view, and select the Properties command. In the Availability Group Properties dialog box, use the General and Backup Preferences pages to view and, in some cases, change properties of the selected availability group.

Know More ›

What is difference between replication and always on SQL Server? ›

AlwaysOn/Mirroring reads the log on the master server and transfers all the commands to the DR servers where it essentially restores the t-log from a backup. Replication reads the log and translates the transactions and commands into individual commands to insert/updated/delete one row at a time on the subscribers.

Learn More Now ›

Does SQL AlwaysOn require clustering? ›

Deploying Always On availability groups requires a Windows Server Failover Cluster (WSFC). To be enabled for Always On availability groups, an instance of SQL Server must reside on a WSFC node, and the WSFC and node must be online.

Learn More Now ›

How do I know if SQL Server is always on status? ›

Use ISHADRENABLED property to check the status of "Always On" availability of groups. You can use Windows PowerShell to invoke SQL command on a reachable server within the network using Invoke-Sqlcmd cmdlet as the following, Open Windows PowerShell as Administrator.

Get More Info Here ›

How do you manually failover Alwayson availability groups? ›

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.

How do I change SQL Server failover mode? ›

Using SQL Server Management Studio

Right-click the replica, and click Properties. In the Availability Replica Properties dialog box, use the Failover mode drop list to change the failover mode of this replica.

Keep Reading ›

What are the different types of server failover? ›

Three forms of failover exist: automatic failover (without data loss), planned manual failover (without data loss), and forced manual failover (with possible data loss), typically called forced failover.

How to configure failover cluster step by step? ›

Verify the prerequisites.
Install the Failover Clustering feature.
Validate the configuration.
Create the failover cluster.
Create clustered roles.
Create a failover cluster by using Windows PowerShell.
More information.

Feb 11, 2022

Learn More Now ›

What is failover servers for high availability? ›

Failover servers for high availability – Allows customers to install and run passive SQL Server instances in a separate operating system environment (OSE) or server for high availability on-premises in anticipation of a failover event.

What's the difference between a planned failover and the failover option when right clicking on the virtual machine and replica? ›

Explore More ›

How do you failover a physical standby database? ›

Oracle 12c-Step by Step Manual Data Guard Failover

Failover:-
Step:2 Cancel the MRP process. alter database recover managed standby database cancel;
Step:3 The below commands will help to bring up standby as primary.
Step:5 Bounce your database and verify database name its open mode and its role.

Get More Info Here ›

How do you manually failover the cluster node? ›

Manual Failover Cluster

Right click the service and hover your cursor in move this service or application to another node, It give you an option Move to Node 2 click on the options as shown in the image.
You will be prompted to confirm. Select to Move. ...
And your Node2 will take ownership.

Nov 2, 2015

Get More Info ›

How do you configure replication with Always On Availability Groups? ›

In this article

Configure the Database Publications and Subscriptions.
Configure the Always On Availability Group.
Ensure that all of the Secondary Replica Hosts are Configured for Replication.
Configure the Secondary Replica Hosts as Replication Publishers.
Redirect the Original Publisher to the AG Listener Name.

More items...

Nov 18, 2022

Get More Info ›

When should a planned failover be conducted? ›

When should I use Planned Failover? I want to perform host maintenance on my primary and would like to run from the replica site. My primary site is expecting some power outage – I want to move over to the replica site. There's an impending typhoon – I want to proactively take action to ensure business continuity.

Keep Reading ›

What is failover and why is it useful? ›

Failover is the ability to switch automatically and seamlessly to a reliable backup system. When a component or primary system fails, either a standby operational mode or redundancy should achieve failover and lessen or eliminate negative impact on users.

Read On ›

What triggers an unscheduled failover? ›

An unplanned failover is a failover performed when an unexpected failure occurs resulting in a critical VM or the entire primary site going offline. The failure can be caused by any of a number of natural disasters, accidents (a power outage), a malware attack, or any other incident.

What is the difference between switchover and failover? ›

Answer: In general, a failover is a production emergency, where the primary database has failed and you need to failover to the standby database. Conversely, a switchover is a planned switch from a standby back to the primary database, a non-emergency operation.

Read On ›

What is the difference between Dataguard switchover and failover? ›

The main difference between switchover and failover Oracle Data Guard is that Switchover requires human intervention, while failover occurs automatically without warning. In brief, switchover converts to the standby database manually while failover converts to the standby database automatically during a failure.

Get More Info Here ›

How do you failover a cluster in SQL Server? ›

To do so, launch Add Roles and Feature Wizard from the server manager. Enable the Failover Clustering feature in both the SQL nodes. Confirm the failover cluster installation for SQL Server Always On Availability Groups.

Show Me More ›

How do you simulate a failover? ›

To simulate the failure of a service, perform the following steps:

Open the Failover Cluster Manager on the Primary Cluster Node. For steps to open the Failover Cluster Manager, see Open and View the Failover Cluster Manager. ...
Right-click a Resource and select More Actions, Simulate failure of this resource.

Jul 27, 2022

Get More Info ›

How does SQL Server failover cluster work? ›

In the event of a failover, the WSFC service transfers ownership of instance's resources to a designated failover node. The SQL Server instance is then re-started on the failover node, and databases are recovered as usual. At any given moment, only a single node in the cluster can host the FCI and underlying resources.

Perform a planned manual failover of an availability group - SQL Server Always On (2023)

Before you begin

Limitations and restrictions

Prerequisites and restrictions

Security

Permissions

Use SQL Server Management Studio

Use Transact-SQL

Use PowerShell

Follow up: After you manually fail over an availability group

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

Manual failover without data loss

Forced manual failover with data loss

See also

FAQs

How do you always perform on failover? ›

What is the disadvantage of always on SQL Server? ›

What is the difference between planned failover and failover? ›

How do you ensure high availability on a server? ›

What are the different types of server failover? ›

What is the difference between switchover and failover? ›

Videos