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 as a more performant migration vehicle using Always On technology, with the promise of the best possible minimum downtime, and also ability of online failback from SQL Managed Instance to SQL Server 2022, as a migration insurance policy.
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 procedure for both migration options. |
|
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 simpler to use as it can be used over public endpoit. However, it perhaps might not satisfy some of 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 | 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 | MI link is using singed CA (Certificate Authority) for authentication. For LRS using managed identity is much more secure than using self-generated SAS tokens. |
|
Impacted by system updates or failover |
No, other than a minimum interruption of a short failover |
- For MI GP pauses and resumes the migration process for MI GP automatically - For MI BC restarts the migration process from the scratch | MI link is resilient and not impacted by Managed Instance failovers. On the other hand, LRS is impacted and migrations are 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 of continuous log-shipping, after which the migration is automatically stopped, and needs to be restarted from the scratch. |
|
Type of migration |
True online migration with only a short failover (measured in seconds) |
- Online migration with expected downtime on the cutover for the duration of time it takes to restore the last backup file. As such, it is strongly recommended to make the last backup file as small as possible when using LRS. - Cutover time takes considerably longer for SQL MI Business Critical service tier for the time it takes to replicate the entire database size from the primary to all three secondary notes of SQL MI BC (possible workarounds - migrate to SQL MI GP Next-Gen and perform online upgrade from MI GP to BC) . |
MI link is the only solution offering minimum downtime solution (<1 minute) for all SQL MI service tiers. Compared to LRS, when the cutover is made the last backup file is being restored - so depending on the size of the last backup file size and the time it takes to restore it, there could be a significant wait until the database comes online on SQL MI. In case of using LRS to migrate to SQL MI Business Critical service tier, on the migration cutover the downtime can be significantly log - as only then the entire database is being replicates from the primary to secondary nodes. Depending on the overall database size, in some cases this replication, and hence downtime, could take hours. 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. - Does not support backup files from multiple databases in the same folder (migration fill fail). | MI link is more resilient than LRS. MI link automatically resumes after issues (server down, loss of network connectivity, and may others) have been resolved. In addition, MI link is resilient to any of SQL MI failovers, or service updates. In case of a missing backup chain for LRS, or wrongly specifying the last backup file on auto-complete, LRS might get stuck, or migration could be aborted. When LRS is used to migrate to SQL MI GP, in case of SQL MI service updates, the restore will be paused until service update is complete, and then it will resume. When LRS is used to migrate to SQL MI BC, in case of SQL MI service updates, the service update will take the precedence, but the migration job will be restarted from scratch therefore prolonging the time to migrate. |
|
Reverse migration from SQL MI back to SQL Server |
Offline and online migration back to SQL Server 2022 and above is supported. |
Not supported. | MI link is the only solution offering online and offline reverse migration (e.g. going back) to SQL Server 2022 and above (note: older versions of SQL Server are not supported). This provides an "insurance policy" for migrations to SQL MI using MI link, 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.
Microsoft
