Resumable restore improves SQL Managed Instance database migrations experience
Published Jul 14 2022 12:55 PM 5,332 Views

Today we are announcing improvements that 1) Enable resumable database backup restores for Azure SQL Managed Instance in case of impactful system updates, and 2) Removal of the 36 hrs. limitation to hold off system updates once a migration from SQL Server to managed instance has started. These improvements apply to Log Replay Service (LRS), Azure SQL migration extension for Azure Data Studio, and Azure Database Migration Services (DMS).


About LRS


Log Replay Service (LRS) is our implementation of the SQL Server log shipping to the cloud, and perhaps the most used feature for orchestration of migration jobs from SQL Server 2008-2019 to Azure SQL Managed Instance. LRS infrastructure powers Azure SQL migration extension for Azure Data Studio, and Azure Database Migration Services (DMS). Azure Blob Storage is used as an intermediary to store backup files from SQL Server, and LRS is used to restore these backup files on managed instance in NORECOVERY mode. Customers can add differential and log backups continuously to Azure Blob Storage, and these will be continuously restored on Managed instance. Once the last backup file has been restored, and manual or automated cutover initiated, the migration is complete.


How did it work before


When we initially launched LRS service a few years ago, we’ve implemented a limitation that halts all impactful system updates on managed instance for 36 hours. This was made so to prevent the migration jobs from being interrupted. Once 36 hrs. window has expired, and in case there was an impactful system update required, migration jobs would be interrupted and restarted from scratch by design.


As managed instance was upgraded to support up to 16TB of data storage in 2021 (increase from 8TB for GP service tier when we originally launched the service), this made it possible for some very large workloads to be migrated. At the same time migrating a very large database of several terabytes to this service tier could go well beyond 36 hrs. due to which our system limitation in interrupting the migration because of the system update could be especially impactful in these cases. In addition, we have also introduced a maintenance window feature that allows scheduling of planned system updates, which provides a predictability of when the planned system updates will be applied.


Improvements introduced


With the recent updates to managed instance, we have made restore of backups on General Purpose service tier resumable in case of impactful system updates. Any backup restores in progress on this service tier will be suspended and automatically resumed following a system update. On Business Critical instances, backup restores will be suspended and automatically restarted following a system update. In addition, we have also removed the limitation to hold off impactful system updates for 36 hrs. once LRS migration has started. Customers can use managed instance maintenance window to predictably schedule planned system updates. The upper limit to continuously run a single LRS migration job has now been increased to 30 days.


How does this benefit you


This improvement provides the following new benefits:


  • Improved flexibility
    • Backup restores are not interrupted, but are automatically suspended and resumed after impactful system updates on General Purpose managed instance, and restarted after impactful system updates for Business Critical managed instance.
    • Planned system updates can be scheduled outside your migration window using the maintenance window feature.
    • LRS can run in continuous mode up to 30 days per single database migration job.
  • Improved security 
    • Critical (non-planned) system updates are applied as soon as available for improved security.


Considerations and best practices


LRS migration in progress being suspended because of a system update might result in longer migration times. To make your migration time more predictable use maintenance window to configure a specific day/time for the system update to occur. Plan your migration start and completion outside of the scheduled maintenance window.


Note that critical system updates, such are security patches, cannot be scheduled within the maintenance window. Although a rare event, deployment of critical system updates will result in prolonged migration times by design.


More choices to migrate to Managed Instance


As we are evolving Azure SQL Managed Instance as a fully managed database platform, we are introducing even more options for you to migrate to managed instance. In case that your migration requires databases on managed instance to be accessible in R/O mode as soon the migration has started, if you have a considerably lengthy migration for which maintenance window schedule is not appropriate, or if you require the best possible minimum downtime migration, alternatively consider using our new migration option MI link.


Closing remarks


We hope that our improvements in this space have further improved our services to you. For any comments or feedbacks, use the comments section below, or post your feature request at Azure Feedbacks.


If you find this article useful, please like it on this page and share through social media. To share this article, you can use the Share button below, or this short link:

1 Comment
Version history
Last update:
‎Jul 15 2022 03:19 AM
Updated by: