Is it possible to log ship from on-premise SQL server to Azure SQL Managed Instance?

Question

From the docs, it would seem that restoring a full database backup file is possible, but can one also restore transaction logs?

(the idea is to minimize downtime in a migration from on-prem to MI).

Thanks.

Answer 1

No, log shipping to Managed Instance is not possible; it only supports the restore of full backups. See this thread:

• Lesson Learned #82: Azure SQL Database Managed Instance supports only COPY_ONLY restoring a database backup (was here before)

And the limited syntax diagram for RESTORE in the documentation for Managed Instance:

• RESTORE Statements (Transact-SQL)

The docs imply that RESTORE ... FROM URL will implicitly add WITH RECOVERY and so there will be no opportunity for logs to be applied after the initial restore. So your downtime will be >= the time it takes to take a COPY_ONLY full backup, put it somewhere MI can access, restore it, and re-point your app(s). You might also want to read through this tutorial, for a different approach.

Answer 2

I work in the SQL Managed Instance Product Group. We have now (Feb 2021) enabled log shipping as a part of an external service called Log Replay Service (LRS). The reason we could not enable log shipping directly from T-SQL was due to security concerns.

The LRS service when invoked will execute the log-shipping in NORECOVERY mode behind the scenes on Managed Instance. More about this release in the documentation:

Migrate databases from SQL Server to SQL Managed Instance by using Log Replay Service (Preview).

---

DMS and LRS are both the same tech using log shipping to move data to Managed Instance. Here are the differences between DMS and LRS for your migration options:

• Azure DMS is using LRS (log shipping) behind the scenes. The difference between two migration options (DMS and LRS) is that you would use DMS as an easy to use migration with no experience required. DMS is basically tooling built on top of LRS that simplifies the things for you in just a few clicks.
• On the other hand, if you want full customization and control of your log-shipping migration experience, and in case you cannot use DMS (for various technical reasons, such as for example cannot run .exe locally, no admin permissions, or cannot open network ports), you could use LRS yourself with PowerShell and CLI commands to manually orchestrate log shipping to Managed Instance.

• Paul White: I edited the content from your second answer into this answer. The answer and content is still yours, the card above simply shows I was last to edit. See dba.stackexchange.com/help/editing for more.

Answer 3

If you want to minimize downtime in a migration from on-prem to MI Microsoft suggest to use the built in feature that you can find on Azure Migrate called Data Migration Assistant:

https://learn.microsoft.com/en-us/azure/dms/tutorial-sql-server-managed-instance-online

https://azure.microsoft.com/en-us/resources/videos/online-migrations-using-azure-dms/