Comparing migration vehicles for SQL Managed Instance - LRS vs. MI link
Published Nov 15 2022 10:58 PM 2,632 Views
Microsoft

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) Comparison commentary
Underlying technology Distributed Availability Groups (AG) Log shipping MI link is using on Always On technology for replication, which is newer and more advanced compared to LRS which uses an older log-shipping technology.

Replication performance

Near real-time

Restores every few minutes

Replication using MI link is considerably more performant than of LRS.

Minimal supported source database

SQL Server 2016 and above

SQL Server 2008 and above

LRS can support much older SQL Server versions than MI link.

Read-only secondary

Supported

Not supported

While replication is in progress, replicated database on Managed Instance using MI link can be used for R/O workloads. This provides possibility to test your migration in R/O mode, or to use the secondary replica without even migrating to Azure. This is not possible at all with LRS.

Replication of TDE encrypted databases

Yes, requires security keys import to SQL MI

Yes, requires security keys import to SQL MI

This is the same for both features.

Network connectivity type

- Private endpoint

- VPN with configured inbound\outbound ports

- Public endpoint

MI link is more secure, as it has additional layers of security, and uses VPN, however it is more difficult to setup networking compared to LRS. As such, LRS is simple to setup networking wise, but it might not satisfy the most demanding security requirements as it uses publicly exposed Azure blob storage as an intermediary to save data before restoring to Managed Instance.

Data encryption in transmission

- Data encrypted with AES, and
- SSL for transmission

SSL for transmission

MI link uses additional data AES encryption layer. SSL is used for transmission for both products.

Authentication for the replication

Certificates signed by a trusted authority (CA)

- SAS tokens, and

- Managed identities (SQL Server 2022 and above)

MI link is using singed CA for authentication which are more secure than shared SAS tokens. On the other hand, starting with SQL Server 2022, LRS uses managed identities which provides a much stronger level of authentication.

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

MI link is resilient to Managed Instance failovers. On the other hand, LRS is impacted and delayed by failovers on Managed Instance.

Replication duration

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

LRS job can run up to 30 days

MI link can run for an unlimited amount of time. LRS is limited to max. of 30 days, after which the migration cannot be continued and needs to start from the scratch.

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.

MI link is the only solution offering minimum downtime solution (<1 minute). Compared to LRS, when the last backup file is being restored, depending on how large the last backup file is, this can take minutes. In case of SQL MI Business Critical service tier, on the migration cutover, using LRS will also take additional time to replicate data from the primary to secondary nodes. As such LRS is considerably slower in making database available on Managed Instance compared to MI link which is almost instantaneous.

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

MI link requires maintenance of on the source SQL Server by making sure regular backups of log are performed. No maintenance is required for LRS.

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.

MI link is more resilient than LRS. MI link automatically resumes after issues have been resolved. In case of a missing backup chain for LRS, or wrongly specifying the last backup file on auto-complete, LRS might get stuck.

Reverse migration from SQL MI back to SQL Server

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

Not supported.

MI link is the only solution offering online and offline reverse migration (e.g. going back) to SQL Server 2022 (older versions of SQL Server are not supported). This provides an "insurance ticket" for migrations to SQL MI, as you can always reverse and go back.

 

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.

2 Comments
Co-Authors
Version history
Last update:
‎Jan 26 2023 06:40 AM
Updated by: