Fail over link - Azure SQL Managed Instance

Applies to: Azure SQL Managed Instance

This article teaches you how to fail over a database linked between SQL Server and Azure SQL Managed Instance by using SQL Server Management Studio (SSMS) or PowerShell for the purpose of disaster recovery or migration.

Prerequisites

To fail over your databases to your secondary replica through the link, you need the following prerequisites:

Stop workload

If you're ready to fail over your database to the secondary replica, first stop any application workloads on the primary replica during your maintenance hours. This enables database replication to catch up on the secondary so you can fail over to the secondary without data loss. Ensure your applications aren't committing transactions to the primary before failing over.

Fail over a database

You can fail over a linked database by using Transact-SQL (T-SQL), SQL Server Management Studio, or PowerShell.

You can fail over the link by using Transact-SQL starting with SQL Server 2022 CU13 (KB5036432).

To perform a planned failover for a link, use the following T-SQL command on the primary replica:

ALTER AVAILABILITY GROUP [<DAGname>] FAILOVER

To perform a forced failover, use the following T-SQL command on the secondary replica:

ALTER AVAILABILITY GROUP [<DAGname>] FORCE_FAILOVER_ALLOW_DATA_LOSS

View database after failover

For SQL Server 2022, if you chose to maintain the link, you can check that the distributed availability group exists under Availability Groups in Object Explorer in SQL Server Management Studio.

If you dropped the link during failover, you can use Object Explorer to confirm the distributed availability group no longer exists. If you chose to keep the availability group, the database will still be Synchronized.

Clean up after failover

Unless Remove link after successful failover is selected, failing over with SQL Server 2022 doesn't break the link. You can maintain the link after failover, which leaves the availability group, and distributed availability group active. No further action is needed.

Dropping the link only drops the distributed availability group, and leaves the availability group active. You can decide to keep the availability group, or drop it.

If you decide to drop your availability group, replace the following value and then run the sample T-SQL code:

  • <AGName> with the name of the availability group on SQL Server (used to create the link).
-- Run on SQL Server
USE MASTER
GO
DROP AVAILABILITY GROUP <AGName> 
GO

Inconsistent state after forced failover

Following a forced failover, you might encounter a split-brain scenario where both replicas are in the primary role, leaving the link in an inconsistent state. This can happen if you fail over to the secondary replica during a disaster, and then the primary replica comes back online.

First, confirm you're in a split-brain scenario. You can do so by using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).

Connect to both SQL Server and SQL managed instance in SSMS, and then in Object Explorer, expand Availability replicas under the Availability group node in Always On High Availability. If two different replicas are listed as (Primary), you're in a split-brain scenario.

Alternatively, you can run the following T-SQL script on both SQL Server and SQL Managed Instance to check the role of the replicas:

-- Execute on SQL Server and SQL Managed Instance 

declare @link_name varchar(max) = '<DAGName>' 
USE MASTER 
GO

SELECT
   ag.name [Link name], 
   rs.role_desc [Link role] 
FROM
   sys.availability_groups ag 
   join sys.dm_hadr_availability_replica_states rs 
   on ag.group_id = rs.group_id 
WHERE 
   rs.is_local = 1 and ag.name = @link_name 
GO

If both instances list a different Primary in the Link role column, you're in a split-brain scenario.

To resolve the split brain state, first take a backup on whichever replica was the original primary. If the original primary was SQL Server, then take a tail log backup. If the original primary was SQL Managed Instance, then take a copy-only full backup. After the backup completes, set the distributed availability group to the secondary role for the replica that used to be the original primary but will now be the new secondary.

For example, in the event of a true disaster, assuming you've forced a failover of your SQL Server workload to Azure SQL Managed Instance, and you intend to continue running your workload on SQL Managed Instance, take a tail log backup on SQL Server, and then set the distributed availability group to the secondary role on SQL Server such as the following example:

--Execute on SQL Server 
USE MASTER

ALTER availability group [<DAGName>] 
SET (role = secondary) 
GO 

Next, execute a planned manual failover from SQL Managed Instance to SQL Server by using the link, such as the following example:

--Execute on SQL Managed Instance 
USE MASTER

ALTER availability group [<DAGName>] FAILOVER 
GO 

To use the link:

To learn more about the link:

For other replication and migration scenarios, consider: