Disaster recovery with Managed Instance link - Azure SQL Managed Instance

Article
11/04/2024

Applies to: Azure SQL Managed Instance

This article teaches you to configure a hybrid disaster recovery solution between SQL Server hosted anywhere and Azure SQL Managed Instance by using the Managed Instance link, and how to save on licensing costs by activating the Hybrid failover benefit on a license-free DR replica.

Overview

The Managed Instance link enables disaster recovery, where, in the event of a disaster, you can manually fail over your workload from your primary to your secondary.

With SQL Server 2022, either SQL Server or Azure SQL Managed Instance can be the primary and you can establish the link initially from either SQL Server or SQL Managed Instance. You can fail over between SQL Server and Azure SQL Managed Instance in either direction, as needed.

When failing back to SQL Server 2022, you can choose to fail back:

online by using the Managed Instance link directly.
offline by taking a backup of your database from SQL Managed Instance and restoring it to your SQL Server 2022 instance.

Diagram showing the disaster recovery scenario.

With SQL Server 2016, and SQL Server 2019, the primary is always SQL Server and failover to the secondary managed instance is one-directional. Reversing roles by failing back to SQL Server and making SQL Managed Instance primary isn't supported. However, it's possible to recover your data to SQL Server using data movement options such as transactional replication or exporting a bacpac.

Important

After successful fail over to SQL Managed Instance, manually repoint your application(s) connection string to the SQL managed instance FQDN to complete the fail over process and continue running in Azure.

Prerequisites

To use the link with Azure SQL Managed Instance for disaster recovery, you need the following prerequisites:

An active Azure subscription. If you don't have one, create a trial account.
Supported version of SQL Server) with the required service update installed.
Azure SQL Managed Instance. Get started if you don't have an instance.
A configured Managed Instance link between SQL Server and Azure SQL Managed Instance.

Permissions

For SQL Server, you should have sysadmin permissions.

For Azure SQL Managed Instance, you should be a member of the SQL Managed Instance Contributor, or have the following custom role permissions:

Microsoft.Sql/ resource	Necessary permissions
Microsoft.Sql/managedInstances	/read, /write
Microsoft.Sql/managedInstances/hybridCertificate	/action
Microsoft.Sql/managedInstances/databases	/read, /delete, /write, /completeRestore/action, /readBackups/action, /restoreDetails/read
Microsoft.Sql/managedInstances/distributedAvailabilityGroups	/read, /write, /delete, /setRole/action
Microsoft.Sql/managedInstances/endpointCertificates	/read
Microsoft.Sql/managedInstances/hybridLink	/read, /write, /delete
Microsoft.Sql/managedInstances/serverTrustCertificates	/write, /delete, /read

One-way failover (SQL Server 2016 - 2022)

For SQL Server 2016 and SQL Server 2019, failover to Azure SQL Managed Instance from SQL Server is one way. Failing back, or restoring your database to SQL Server isn't possible. However, you can recover your data back to SQL Server by using data movement options such as transactional replication or exporting a bacpac. Failing over to Azure SQL Managed Instance breaks the link and drops the distributed availability group.

With SQL Server 2022, you can choose to perform a one-way failover, such as for migration, by breaking the link in the process of failover. Be sure to choose the appropriate option for your business when you fail over your SQL Server 2022 database.

To fail over, review Fail over the link.

Two-way failover (SQL Server 2022)

SQL Server 2022 introduces online failover with fail back, which allows you to seamlessly failover to Azure SQL Managed Instance and then fail back online to SQL Server by using the Managed Instance link, with minimal down time.

To fail over, review Fail over the link.

Offline fail back (SQL Server 2022)

With SQL Server 2022, after the disaster is mitigated, you can choose to fail back to SQL Server from SQL Managed Instance offline by taking a backup of your database on your managed instance, and then restoring it to SQL Server. This option is generally available.

To get started, review Restore database to SQL Server 2022.

License-free passive DR replica

You can save on licensing costs by activating the Hybrid failover benefit for your passive secondary SQL managed instance when it's used only for disaster recovery. The Hybrid failover benefit can be activated for new and existing instances.

Note

The Hybrid failover benefit is only applicable when you configure a secondary instance as a passive in a hybrid environment between SQL Server and SQL Managed Instance. For failover benefits between two instances in a failover group, use the failover benefit instead.

New instances

To activate the Hybrid failover benefit for a new instance, follow these steps:

Go to the SQL managed instances page in the Azure portal.
Select + Create to open the Create Azure SQL Managed Instance page.
On the Basics tab, select Configure Managed Instance under Compute + Storage to open the Compute + Storage page:
Choose Hybrid failover rights under SQL Server License.
Check the box to confirm that you'll use this instance as a passive replica.
Select Apply to save your changes.

Existing instances

To activate the Hybrid failover benefit for an existing instance, follow these steps:

Go to your SQL managed instance in the Azure portal.
Select Compute + storage under Settings in the resource menu.
Choose Hybrid failover rights under SQL Server License and then check the box to confirm that you'll use this instance as a passive replica:
Select Apply to save your changes.

To use the link:

To learn more about the link:

Managed Instance link overview

For other replication and migration scenarios, consider: