Log Replay Service for SQL MI is now Generally Available for SQL MI
Published Nov 16 2022 08:00 AM 1,030 Views
Microsoft

Log Replay Service (LRS) is our implementation of log-shipping technology used to migrate databases from SQL Server 2008-2022 to the fully managed PaaS service Azure SQL Managed Instance is now generally available (GA). In this blogpost we are reflecting on the past, present and future of the feature.

  

The evolution of LRS

 

LRS has been a powerhouse of all migrations since Managed Instance service general availability (GA) in November of 2018. LRS was powering Azure Database Migration Service (DMS) which was used to automatically orchestrate all of our initial service migrations. In some cases, DMS however was not meeting needs of customers with specific use cases where for example DMS executable could not be installed or executed in restrictive customer environments, when there existed a little tolerance for downtime on migration cutover, or when customers needed to use differential backups – amongst others. This was the main driver to expose the underlying LRS service in February of 2021 in a public preview empowering customers to manually orchestrate their migrations using built-in Azure PowerShell scripts. Within the past four years of its existence, LRS was improved and upgraded from its initial v1 to v2 based on tens of thousands databases being migrated to Azure and experiences we have gained. The improved LRS API v2 is now powering Azure SQL migration extension for Azure Data Studio.

 

Improvements delivered for GA release

 

In reaching our general availability milestone, the following improvements were made since the public preview of LRS (in February of 2021):

 

Improvement Description
No CHECKSUM requirement Backups files made on SQL Server no longer need to be created with what was earlier a mandatory CHECKSUM option.
Resumable restore In case of impactful system patches and updates, migrations are now suspended and resumed automatically, removing the earlier 36 hrs. window to migrate. See Resumable restore improves SQL Managed Instance database migrations experience blogpost.
Detailed progress report Based on the customer feedback, we are now providing considerably more detailed migration progress status for easier monitoring and troubleshooting
Managed Identity support Backups can now be restored from Azure Blob Storage with managed identities authentication, along with the existing SAS token authentication.

 

Tech talk

 

No Checksum: The reason LRS has initially required all backups to be made with CHECKSUM on SQL Sever was because a backup without CHECKSUM taken could be corrupted, and such corruption would ‘successfully’ be imported to Azure, which subsequently could cause number of incidents with customer data. Some would argue this requirement was very restrictive, however it was in our opinion necessary to ensure that no corrupt databases were restored on SQL MI. In removing this requirement, our engineering team has performed something short of a marvel – we have made improvements to the SQL Server engine to automatically check the physical database consistency while backups without CHECKSUM are being restored in Azure. In case that there is a corruption detected in-flight, such backup would be rejected, and only backups whose integrity checks out would be restored on SQL MI. This improvement opens new opportunities for customers who were limited in not being able to take backups with CHECKSUM, such is for example on Amazon RDS for SQL Server.

 

Resumable restore: In restoring backup files using log shipping on SQL MI impactful upgrades requiring failover would interrupt such backup, especially if during the process there is a node failover from one machine to the other. To prevent impactful system updates to interrupt migrations in flights, we have initially implemented a mechanism that would halt system upgrades for 36hrs since the migration has started. This gave customers an opportunity to have a finite window of 36hrs to complete their migrations to SQL MI. As SQL MI grew in capacity, now at 16TB of max storage space for all service tiers, this migration window started becoming too tight. On the other hand, if there were important security updates, it was not wise to hold them off. Our engineering has performed another engineering marvel by implementing the resumable restore technology for General Purpose service tier by making modifications to SQL MI service layer, and SQL Server engine. In this new world, we are prioritizing applying security patches immediately once they are available, and we are suspending in-flight restores. Once patching is done, and failover completed, our mechanism is able now to automatically resume the restore operation, even if this is on a new node. This made the service more secure, and has removed the 36hrs migration window requirement, allowing customers to enjoy now 30 days of a timeframe to complete their migrations once the LRS has started.

 

Detailed progress report: In our public preview release of LRS we have offered migration progress report showing the last backup file restored. Customers have told us that they would like to see more in terms of detailed map of files restored, their progress and size. We have listened and in the new version of LRS we are returning a list of files processed or to be processed for restore, skipped, queued, or invalid, along with the total number of files detected, files to the backup plan restore, their sizes, and progress in terms of total MB to restore, and MVs restored thus far. For continuous migration mode where you can add additional log or differential backups, the system automatically updates the stats for the new files added.

 

Managed Identity support: As we are modernizing the SQL Server for the cloud-powered age, starting with SQL Server 2022 there is a native Azure Active Directory (AAD) support built-in. We are welcoming this newcomer to the SQL family and are extending LRS to now natively support managed identities for authentication and backup of database files to Azure Blog Storage. Both SQL Server 2022 and SQL Managed Instance can access backup stored on Azure Blob Storage using managed identities as a new authentication mechanism for LRS.

 

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 and new-ten technology for you to migrate to managed instance. In case that your migration requires near real-time replication, database accessible as read-only secondary as soon the migration has started, or if you require the best possible minimum downtime on cutover, or require a mechanism to reverse-migrate back to SQL Server, alternatively consider using our new migration option  .

 

Our engineering team sees LRS and MI link complementing each other in capabilities. Depending on your specific circumstances, in some cases using LRS or MI link might be better suited for your needs. See this article for comparison of MI link and LRS as migration vehicles.

 

Getting started - migrate databases to SQL MI

 

To get started migrating databases from SQL Server to Managed Instance, choose:

 

 

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 Feedback.

 

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: https://aka.ms/mi-lrs-ga-announcement.

 

Co-Authors
Version history
Last update:
‎Nov 15 2022 10:43 PM
Updated by: