Fail over a database by using the link in SSMS - Azure SQL Managed Instance

Article
01/06/2023
3 minutes to read

Applies to: Azure SQL Managed Instance

This article teaches you how to fail over a database from SQL Server to Azure SQL Managed Instance by using the link feature in SQL Server Management Studio (SSMS).

Failing over your database from SQL Server 2019 or earlier to SQL Managed Instance breaks the link between the two databases. It stops replication and leaves both databases in an independent state, ready for individual read/write workloads. Failing over from SQL Server 2022 does not break the link, and fail back to SQL Server 2022 is supported - this is currently in preview.

Note

Some functionality of the link is generally available, while some is currently in preview. Review the prerequisites to learn more.
You can also use a T-SQL and PowerShell to failover a database with the link.

Prerequisites

To fail over your databases to SQL Managed Instance, you need the following prerequisites:

An active Azure subscription. If you don't have one, create a free account.
Supported version of SQL Server with required service update installed.
Azure SQL Managed Instance. Get started if you don't have it.
SQL Server Management Studio v19.0 or later.
An environment that's prepared for replication.
Setup of the link feature and replication of your database to your managed instance in Azure.

Fail over a database

In the following steps, you use the Failover database to Managed Instance wizard in SSMS to fail over your database from SQL Server to SQL Managed Instance. The wizard takes you through failing over your database, breaking the link between the two instances in the process if you're on SQL Server 2019 or earlier.

Caution

If you're performing a planned manual failover, stop the workload on the source SQL Server database to allow the SQL Managed Instance replicated database to completely catch up and failover without data loss. If you're performing a forced failover, you might lose data.

Open SSMS and connect to your SQL Server instance.
In Object Explorer, right-click your database, hover over Azure SQL Managed Instance link, and select Failover database to open the Failover database to Managed Instance wizard.
On the Introduction page of the Failover database to Managed Instance wizard, select Next.
On the Log in to Azure page, select Sign-in to provide your credentials and sign in to your Azure account. Select the subscription that's hosting SQL Managed Instance from the dropdown list, and then select Next.
On the Failover Type page, choose the type of failover you're performing. Select the box to confirm that you've stopped the workload for a planned failover, or you understand that you might lose data if using a forced failover. Select Next.
On the Clean-up (optional) page, choose to drop the availability group if you created it solely for the purpose of migrating your database to Azure and you no longer need it. If you want to keep the availability group, leave the boxes cleared. Select Next.
On the Summary page, review the actions that will be performed for your failover. Optionally, select Script to create a script that you can run at a later time. When you're ready to proceed with the failover, select Finish.
The Executing actions page displays the progress of each action.
After all steps finish, the Results page shows check marks next to the successfully completed actions. You can now close the window.

On successful execution of the failover process, the link is dropped and no longer exists. The source SQL Server database and the target SQL Managed Instance database can both execute a read/write workload. They're completely independent. Repoint your application connection string to managed instance to complete the migration process.

Important

On successful failover, manually repoint your application(s) connection string to managed instance FQDN to continue running in Azure, and to complete the migration process.