Edit

Share via

Migration guide: SQL Server to Azure SQL Managed Instance

  • Article

Applies to: Azure SQL Managed Instance

In this guide, you learn how to migrate your user databases from SQL Server to Azure SQL Managed Instance.

Complete pre-migration steps, before continuing.

Migrate

After you complete the steps for the pre-migration stage, you're ready to perform the schema and data migration.

Migrate your data using your chosen migration method.

This section provides general migration steps for the following recommended migration options:

  • Managed instance link
  • Log Replay Service (LRS)
  • Azure SQL migration extension for Azure Data Studio - migration with near-zero downtime.
  • Native RESTORE DATABASE FROM URL - uses native backups from SQL Server and requires some downtime.

SQL Managed Instance targets user scenarios requiring mass database migration from on-premises or Azure VM database implementations. These are the optimal choice when you need to lift and shift the back end of the applications that regularly use instance level and/or cross-database functionalities. If this is your scenario, you can move an entire instance to a corresponding environment in Azure without the need to rearchitect your applications.

To move SQL instances, you need to plan carefully:

  • The migration of all databases that need to be collocated (ones running on the same instance).
  • The migration of instance-level objects that your application depends on, including logins, credentials, SQL Agent jobs and operators, and server-level triggers.

SQL Managed Instance is a managed service that allows you to delegate some of the regular DBA activities to the platform as they're built in. Therefore, some instance-level data doesn't need to be migrated, such as maintenance jobs for regular backups or Always On configuration, as high availability is built in.

Azure Data Studio

This section provides high-level steps to migrate from SQL Server to Azure SQL Managed Instance with minimal downtime by using the Azure SQL migration extension in Azure Data Studio. For detailed instructions, see Tutorial: Migrate SQL Server to Azure SQL Managed Instance online in Azure Data Studio.

To migrate with Azure Data Studio, follow these steps:

  1. Download and install Azure Data Studio and the Azure SQL migration extension for Azure Data Studio.
  2. Launch the Migrate to Azure SQL Migration wizard in the extension in Azure Data Studio.
  3. Select databases for assessment and view migration readiness or issues (if any). Additionally, collect performance data and get right-sized Azure recommendation.
  4. Select your Azure account and your target Azure SQL Managed Instance from your subscription.
  5. Select the location of your database backups. Your database backups can either be located on an on-premises network share or in Azure Blob Storage container.
  6. Create a new Azure Database Migration Service using the wizard in Azure Data Studio. If you previously created an Azure Database Migration Service using Azure Data Studio, you can reuse the same if desired.
  7. Optional: If your backups are on an on-premises network share, download and install self-hosted integration runtime on a machine that can connect to the source SQL Server, and the location containing the backup files.
  8. Start the database migration and monitor the progress in Azure Data Studio. You can also monitor the progress under the Azure Database Migration Service resource in Azure portal.
  9. Complete the cutover.
    1. Stop all incoming transactions to the source database.
    2. Make application configuration changes to point to the target database in Azure SQL Managed Instance.
    3. Take any tail log backups for the source database in the backup location specified.
    4. Ensure all database backups have the status Restored in the monitoring details page.
    5. Select Complete cutover in the monitoring details page.

This section provides high-level steps to migrate from SQL Server to Azure SQL Managed Instance with minimal downtime by using the Managed Instance link. For detailed instructions, review Migrate with the link.

To migrate with the link, follow these steps:

  1. Create your target SQL Managed Instance: Azure portal, PowerShell, Azure CLI.
  2. Prepare your environment for the link.
  3. Configure the link with SSMS or scripts.
  4. Stop the workload.
  5. Validate data on the target instance.
  6. Fail over the link.

Log Replay Service (LRS)

This section provides high-level steps to migrate from SQL Server to Azure SQL Managed Instance with minimal downtime by using the Log Replay Service (LRS). For detailed instructions, review Migrate databases from SQL Server by using Log Replay Service.

To migrate with LRS, follow these steps:

  1. Create an Azure storage account with a blob container.
  2. Authenticate to your Blob Storage storage account using an SAS token or a managed identity and validate access.
  3. Be sure to configure your folder structure correctly if you plan to migrate multiple databases.
  4. Upload your backups to your storage account by either copying your backups, or taking backups directly by using the BACKUP TO URL.
  5. Determine if you want to run LRS in autocomplete or continuous mode.
  6. Start LRS.
  7. Monitor migration progress.
  8. Complete the migration (if in continuous mode).

Back up and restore

One key capability of Azure SQL Managed Instance that enables quick and easy database migration is the native restore to SQL managed instance of database backup (.bak) files stored in Azure Storage. Backing up and restoring are asynchronous operations based on the size of your database.

The following diagram provides a high-level overview of the process:

Diagram shows SQL Server with an arrow labeled BACKUP / Upload to URL flowing to Azure Storage and a second arrow labeled RESTORE from URL flowing from Azure Storage to a SQL Managed Instance.

Note

The time to take the backup, upload it to Azure storage, and perform a native restore operation to Azure SQL Managed Instance is based on the size of the database. Factor a sufficient downtime to accommodate the operation for large databases.

The following table provides more information regarding the methods you can use depending on source SQL Server version you're running:

Step SQL Engine and version Backup/restore method
Put backup to Azure Storage Before 2012 SP1 CU2 Upload .bak file directly to Azure Storage
2012 SP1 CU2 - 2016 Direct backup using deprecated WITH CREDENTIAL syntax
2016 and later versions Direct backup using WITH SAS CREDENTIAL
Restore from Azure Storage to a managed instance RESTORE FROM URL with SAS CREDENTIAL

Important

When you're migrating a database protected with transparent data encryption to a managed instance using native restore option, the corresponding certificate from the on-premises or Azure VM SQL Server needs to be migrated before database restore. For detailed steps, see Migrate a certificate of a TDE-protected database to Azure SQL Managed Instance.

Restore of system databases isn't supported. To migrate instance-level objects (stored in master or msdb databases), we recommend to script them out and run T-SQL scripts on the destination instance.

To migrate using backup and restore, follow these steps:

  1. Back up your database to Azure Blob Storage. For example, use backup to url in SQL Server Management Studio. Use the Microsoft Azure Tool to support databases earlier than SQL Server 2012 SP1 CU2.

  2. Connect to your Azure SQL Managed Instance using SQL Server Management Studio.

  3. Create a credential using a Shared Access Signature to access your Azure Blob storage account with your database backups. For example:

    CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/databases]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
        SECRET = '<secret>'

  4. Restore the backup from the Azure storage blob container. For example:

    RESTORE DATABASE [TargetDatabaseName]
FROM URL = 'https://mitutorials.blob.core.windows.net/databases/WideWorldImporters-Standard.bak'

  5. Once restore completes, view the database in Object Explorer within SQL Server Management Studio.

To learn more about this migration option, see Quickstart: Restore a database to Azure SQL Managed Instance with SSMS.

Note

A database restore operation is asynchronous and can be retried. You might get an error in SQL Server Management Studio if the connection breaks or a time-out expires. Azure SQL Database will keep trying to restore database in the background, and you can track the progress of the restore using the sys.dm_exec_requests and sys.dm_operation_status views.

Data sync and cutover

When using migration options that continuously replicate / sync data changes from source to the target, the source data and schema can change and drift from the target. During data sync, ensure that all changes on the source are captured and applied to the target during the migration process.

After you verify that data is the same on both source and target, you can cut over from the source to the target environment. It's important to plan the cutover process with business / application teams to ensure minimal interruption during cutover doesn't affect business continuity.

Important

For details on the specific steps associated with performing a cutover as part of migrations using DMS, see Performing migration cutover.

Post-migration

After you successfully complete the migration stage, go through a series of post-migration tasks to ensure that everything is functioning smoothly and efficiently.

The post-migration phase is crucial for reconciling any data accuracy issues and verifying completeness, and addressing performance issues with the workload.

Monitor and remediate applications

Once you complete the migration to a managed instance, you should track the application behavior and performance of your workload. This process includes the following activities:

Perform tests

The test approach for database migration consists of the following activities:

  1. Develop validation tests: To test database migration, you need to use SQL queries. You must create the validation queries to run against both the source and the target databases. Your validation queries should cover the scope you defined.
  2. Set up test environment: The test environment should contain a copy of the source database and the target database. Be sure to isolate the test environment.
  3. Run validation tests: Run the validation tests against the source and the target, and then analyze the results.
  4. Run performance tests: Run performance test against the source and the target, and then analyze and compare the results.

Use advanced features

You can take advantage of the advanced cloud-based features offered by SQL Managed Instance, such as built-in high availability, threat detection, and monitoring and tuning your workload.

Azure SQL Analytics allows you to monitor a large set of managed instances in a centralized manner.

Some SQL Server features are only available once the database compatibility level is changed to the latest compatibility level (150).

Related content