Restoring a backup file is the easiest way to copy a database to another instance. We are excited to announce that you can now restore a copy-only full backup of an Azure SQL Managed Instance (SQL MI) database to an instance of SQL Server 2022 hosted anywhere.
Now generally available, this capability significantly improves the freedom of data movement across SQL estates. It enables you to provide a copy of a SQL MI database to your end users or eligible third parties for regulatory compliance and other purposes; to use it as a light-weight business continuity and disaster recovery solution for restoring functionality on another instance of SQL Server; or simply for seeding or refreshing your other SQL Server environment.
How it works
Copy-only backups of SQL MI database are stored in Azure Blob Storage account that you provide as part of the backup command. Once a backup file is created, you can download it locally or to a shared location before restoring it, or you can simply restore it directly from the storage account if the storage account is accessible from the destination SQL Server:
For detailed instructions check the how-to article.
Restoring a backup of an encrypted database
For databases encrypted at rest by using Transparent Data Encryption (TDE), the destination SQL Server 2022 instance must have access to the protector key used on the source managed instance to be able to read the data from the backup file. If you’re using a service-managed key for data protection at rest, the key is not available outside of the instance and taking copy-only full backup is preventively blocked.
Use customer-managed key stored in Azure Key Vault (AKV) instead to protect the database and enable access to the same key from the destination SQL Server 2022 instance. Step-by-step instructions for enabling access to AKV from SQL Server are available here.
Figure 2: Restoring backup of an encrypted SQL MI to database to SQL Server 2022 with AKV integration
Database format compatibility
Ability to restore backup relies on the database format alignment between the source and the destination instance. Historically, SQL Server and Azure SQL Managed Instance were following different upgrade policies: SQL Server releases have maintained database format unchanged with each new cumulative update within the same major version. SQL MI could have database format upgraded at times with some of the new features introduced. Such upgrades made new features instantly available to customers, but also rendered database format of SQL MI unreadable by SQL Server.
The introduction of a new major release of SQL Server happens once in few years and it is the perfect time to realign database format with SQL MI and make backups of SQL MI databases restorable to the latest SQL Server. We used the opportunity with SQL Server 2022 and enabled restorability of SQL MI copy-only full backups to SQL Server 2022 for all the existing and newly created managed instances.
Restorability of backups is not the only benefit of the database format alignment between SQL Server 2022 and SQL MI. It also enables hybrid, fully managed disaster recovery solution for SQL Server 2022 with the link feature of SQL MI.
Maintaining database format alignment
You don’t need to perform any extra steps to maintain SQL MI database backup restorability to the latest SQL Server major release going forward. By default, your managed instances will keep database format aligned with SQL Server 2022 until the end of the SQL Server 2022 mainstream support period. At that time, the instances will be automatically upgraded to the database format compatible with the next Generally Available version of SQL Server.
You can also initiate the database format upgrade to the next available major release of SQL Server whenever you are ready during the SQL Server 2022 mainstream support period. Be aware that, once upgraded, the database format cannot be downgraded. After the upgrade new backups of your instances will not be restorable to SQL Server 2022 instances anymore, but you will be able to restore them on the instances running the next major version of SQL Server.
Note that your managed instances will continue to receive upgrades and new features that do not change the database format. As an example, all the instances may benefit from the upcoming November 2022 feature wave since none of the features in the wave bring changes to the database format.
What if I prefer enjoying all the latest features to database format compatibility?
No worries, you are not missing out on benefitting from any new features. We expect the first new features requiring database format changes to become available during the calendar year 2023. At that time, you will be able to choose whether you want to opt in for all the new exclusive features going forward or keep the database format alignment with SQL Server 2022. We are going to publish a dedicated blog post and Azure portal notification to let you know once the choice is available. You don’t need to rush with the decision, as you will be able to opt in for the early adoption of the new features at any time.
Please note that opting in will give you access to the additional new features but will also upgrade database format making backups of your managed instance not restorable to SQL Server 2022, and your instance not suitable for establishing 2-way link for database synchronization with SQL Server 2022. Once database format is upgraded, you cannot switch back to the alignment mode with SQL Server 2022.
You may decide to opt in for the additional new features on a subset of your instances, depending on the opportunities or needs of the concrete solution. You may also want to opt in on the test instances first, to get early access and evaluate new exclusive features.
Get started today
- Check the step-by-step instructions for restoring your SQL MI database backup to SQL Server 2022.
- Sign up for the preview of hybrid disaster recovery solution for SQL Server 2022 using the link feature of SQL MI.
- Check out all the SQL MI innovations that have been just announced.
- Contact us and let us know your thoughts on SQL Managed Instance and how we can empower you achieve more.