Comparing migration vehicles for SQL Managed Instance – LRS vs. MI link

Posted by

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Log Replay Service (LRS) has been our powerhouse powering majority of migrations to SQL MI since the service launched in November of 2018. LRS is our implementation of log shipping, which also powers Azure Database Migration Service (DMS), and Azure SQL migration extension for Azure Data Studio under the hood. As we are evolving SQL Server for the cloud age, in March of 2022 we have released MI link in public preview as a more performant migration vehicle using Always On technology, with the promise of the best possible minimum downtime, and also a return ticket from SQL Managed Instance to SQL Server 2022.

 

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. This article provides a comparison of LRS and MI link such that you can make the best choice which migration vehicle would be best suited for your needs.

 

Comparing LRS vs. MI link

 

Perhaps one of the most fundamental differences between LRS and MI link is in its core technology. LRS is based on log shipping – taking log and diff backups continuously on SQL Server, uploading them to Azure Blog Storage, and restoring on them on SQL MI. This process is not real-time as it time is spend on backing up files, uploading them, and restoring on SQL MI. The performance is based on how big or small your backup chunks are. On the other hand, MI link uses Always On technology to replicate databases near real-time from SQL Server to Managed Instance, and it is a considerably more performant migration solution. However, setup of MI link requires initial networking setup in configuring a VPN between SQL Server and Managed Instance, and opening appropriate ports on the firewall, while LRS can work out of the box using the public endpoint. LRS can be used for SQL Server 2008 and up, while MI link can be used for SQL Server 2016 and up. One major benefit of MI link is its new capability for reverse migrations from SQL MI to SQL Server 2022.

 

The below table compares both technologies in more detail.

 

Functionality

Managed Instance link

Log Replay Service (LRS)

Underlying technology

Distributed availability groups

Log shipping

Replication performance

Near real-time

Restores every few minutes

Minimal supported source database

SQL Server 2016 and above

SQL Server 2008 and above

Read-only secondary

Supported

Not supported

Replication of TDE encrypted databases

Yes, requires security keys import to SQL MI

Yes, requires security keys import to SQL MI

Network connectivity type

Private endpoint, VPN with configured inbound\outbound ports

Public endpoint

Data encryption in transmission

Data encrypted with AES, and
SSL for transmission

 

Authentication for the replication

Certificates signed by a trusted authority (CA)

SAS tokens, and managed identities (SQL Server 2022 and above)

Impacted by system updates or failover

No, other than a minimum interruption of a short failover

Yes, restarts the migration process for MI BC, pauses and resumes the migration process for MI GP

Replication duration

Unlimited replication time using the link (months and years at a time)

LRS job can run up to 30 days

Type of migration

True online migration with only a short failover

Online migration with expected downtime on the cutover to restore the last backup file. Cutover time takes longer for SQL MI Business Critical service tier.

Maintenance required on source

Yes, logs must be backed up to prevent filling up the SQL Server disk in case there is an issue with the replication

No

Resiliency

Automatically resumes the link replication on SQL Server reboot, patching\upgrade, downtime, or network connectivity loss

In case of a broken backup chain, or wrongly specified last backup file, the migration will stall.

Reverse migration from SQL MI back to SQL Server

Offline and online migration back to SQL Server 2022 is supported.

Not supported.

 

When does it make sense to use LRS vs MI link?

 

It all depends on your circumstances and particular business needs. Below are some of our suggestions. Most notable difference between the two is in performance of the two solutions. LRS has a much simpler initial setup, and it will get you to migrate quickly, whereas MI link’s initial configuration takes more time, but then it provides many more options.

 

Another notable difference between LRS and MI link is the cutover time. MI link offers a considerably better cutover time (<1 min), whereas LRS cutover time is in minutes. In case of SQL MI Business Critical tier the cutover downtime with LRS might take considerably longer as on the cutover with LRS databases yet need to be seeded from primary to the secondary notes, which is not the case with MI link – this is why for MI link we call this the “true online migration to MI BC”.

 

Perhaps there are some other scenarios where LRS vs MI link is better suited for your needs. Let us know in the comments below!

 

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-migration-comparision.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.