Now that we have a Managed Instance built, the next question is how do we get data across? I will break this up into separate posts but the lesson for this blog post is ANALYSIS FIRST!
Honestly, there will probably be less work needed to make the data compatible for Managed Instance, this is very true if comparing to the true PaaS offering of Azure SQL DB, but we should always check before migrating.
High level approach is:
- Analyse the on-premises database using DMA tool (https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15)
- Make relevant changes so that it is ready for migration.
- Typically I backup and restore.
Get DMA installed first, find the installer here: https://www.microsoft.com/en-us/download/details.aspx?id=53595
And go to assessment and the relevant source and target types.
Select the source.
And review the results once analysis is done.
So, the assessment says it is ready for the migration to Managed Instance.
So what can catch you out from moving the databases on-prem to Managed Instance? Well some details such as:
- For SQL Agent – The merge replication job step isn’t supported.
- Queue Reader isn’t supported.
- SQL Managed Instance can’t access external resources, for example, network shares via robocopy.
- Distributed transactions are supported BUT only if the instances are part of a server trust group (more on that another time).
This is why we need to analysis on the current setup. Next time, we will move the databases.