[edit - Dec 2022 - backup-restore is a new way to move databases from managed instance to SQL Server 2022]
[edit - Dec 2023 - Online disaster recovery between SQL Server 2022 and SQL Managed Instance]
Introduction
Let's move our database to the cloud! This is one of the common scenarios for solution modernization and database migrations. There are many ways of migrating SQL Server database to Microsoft Azure and there are lots of materials covering this topic. This blog is about the ability to go back from Azure SQL Managed Instance to physical/virtual SQL Server environment. At this stage, there are at least two questions that could come up. First, what's the challenge of going back, as it's a matter of backup and restore, isn’t it? Well, it isn’t, because backup-restore strategy does not work today in this case, and you will find more about this below. Second, why should anyone go back from the Azure, or cloud in general to any other place? Our customer and partner cases shed some light on this. One of the reasons for example is a policy or general requirement of having a "cloud exit" plan.
Why backup-restore is not a way to go?
[edit - Dec 2022 - Backup restore from Azure SQL Managed Instance to SQL Server 2022 is a way to go!]
To better understand this problem, first of all, let’s see what is Managed Instance? It’s a database product in a form of a platform as a service (PaaS), meaning that Microsoft as a service provider covers for you various platform aspects such as:
- Providing resources (compute, memory, networking) and setting up the system.
- Built in high availability and disaster recovery capabilities.
- Regular OS and SQL upgrades.
- Automatic backups, monitoring, etc.
As SQL bits are being upgraded regularly (every few months), Managed Instance is running on “evergreen” SQL bits. This means that current Managed Instance version is most of the time actually newer than the latest SQL Server version. As SQL Server backups are not backwards compatible, a backup from Managed Instance cannot be restored to e.g., SQL Server 2019. It's for the same reason as why backup from SQL Server 2019 cannot be restored to SQL Server 2017. Specifically, internal database version mismatch is one of the main technical reasons why a database from Managed Instance cannot be backed up and then restored to e.g., SQL Server 2019. Upgrading is of course working but downgrading internal database version has never been supported in SQL Server. So by design, backups from lower versions of the SQL Server can be restored to higher version, but vice versa is not supported.
Note: You can use this query to check internal database version of the server instances you’re using:
select name as database_name, version as internal_database_version from sys.sysdatabases
Lack of backup-restore functionality from higher to lower version is what makes moving a database from Managed Instance to SQL Server a non-trivial effort, and that’s why migration to the Managed Instance is often considered a one-way journey. Making Managed Instance backup portable to on-premises SQL is one of the popular Managed Instance feature requests on Azure Feedback. The team is investigating ways to provide the solution for this request and make the two-way migration, both to and from Managed Instance easy and simple. Until a solution arrives, we’ll talk more about currently available options for migration of user databases from Managed Instance to SQL Server: Export/Import and Transactional Replication.
Why moving away from Managed Instance?
While we’re striving to build Azure SQL Managed Instance to be the best PaaS database solution, with full compatibility with SQL Server and want you to like it and use it for your databases, sometimes having an option to easily migrate from Managed Instance to another database system is a necessity. Motivation can come from compliance or legal reasons. Sometimes our customers have contract obligation to provide database backups to their clients, who might not want to use our cloud solutions. In other cases, development practices require using schemas and data from production back in development environment that are often purely on-premises. Finally, you might find out that you require more flexibility than what PaaS can offer, so you need to go back to IaaS (e.g. managed VMs) or on-premises solutions.
Available options
There are four main SQL technologies that can be used today for moving data from Managed Instance to SQL Server. Those are:
All technologies have different options and some limitations, and these will be discussed later, or in subsequent blog posts.
Here is a brief comparison of the two technologies and their pros and cons.
Link feature for Azure SQL Managed Instance with online disaster recovery with SQL Server 2022
Online disaster recovery between SQL Server 2022 and SQL Managed Instance - Preview - Microsoft Comm...
This capability of the link feature enables an easy and cost-effective hybrid disaster recovery solution for workloads running in SQL Server 2022 and provides a convenient way for creating a near real-time replica of SQL Managed Instance database outside of Azure.
At the same time, the new release of SQL Server Management Studio (SSMS) brings new wizards for creating links both from SQL Server and from SQL Managed Instance, and for performing failovers in both directions to further simplify the entire experience.
Backup-restore
Restoring a SQL Managed Instance database backup to SQL Server 2022 now GA
Export/Import
Using export/import is very simple, either through SSMS or SqlPackage. It’s best to be used for offline migration of small and medium databases.
This technology has some limitations:
- It does not handle databases that reference external objects (schemas with three or four-part names).
- Its performance degrades when working with large databases or databases with large number of objects (hundreds of GBs or tens of thousands of objects in a database).
- Produced BACPAC is not transactionally consistent.
If you'd like to read more about this option, see how to migrate database from Managed Instance to SQL Server with export/import.
Transactional replication
This is a more robust way to migrate databases from Managed Instance to SQL Server. It’s best to be used for online or offline migration of large and complex databases.
Some outstanding limitations that apply to transactional replication are:
- Tables must have primary keys.
- Setup is not simple.
To find out more about this, see how to migrate database from Managed Instance to SQL Server with transactional replication.
