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.
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.
There are four main SQL technologies that can be used today for moving data from Managed Instance to SQL Server. Those are:
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.