Restore to SQL Managed Instance

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:

  1. Analyse the on-premises database using DMA tool (https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15)
  2. Make relevant changes so that it is ready for migration.
  3. Migrate:
    1. 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:

Limitations?

  • 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.
  • Filetables.
  • 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.

4 thoughts on “Restore to SQL Managed Instance

  1. Pingback: Dew Drop – October 8, 2021 (#3533) – Morning Dew by Alvin Ashcraft

  2. Pingback: ➧Dew Drop – October 8, 2021 (#3533) • Softbranchdevelopers

  3. Pingback: Dew Drop – October 8, 2021 (#3533) - Online Code Generator

  4. Pingback: Restoring a Database to Azure SQL Managed Instance – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s