Migration guide: SQL Server to Azure SQL Managed Instance

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)
• Native RESTORE DATABASE FROM URL, which uses native backups from SQL Server and requires some downtime
• Azure Database Migration Service (DMS), which offers migration with near-zero downtime
• SQL Server migration in Azure Arc

SQL Managed Instance targets user scenarios that require mass database migration from on-premises or SQL Server on Azure Virtual Machines implementations. It's the optimal choice when you need to lift and shift the back end of applications that regularly use instance level and 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 Server 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 Server Agent jobs and operators, and server-level triggers.

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

Database migration

Migrate your SQL Server enabled by Azure Arc instance to Azure SQL Managed Instance directly through the Azure portal. For detailed instructions, see Migrate SQL Server instance to Azure SQL Managed Instance.

Database migration provides a built-in migration experience, using the Managed Instance link or Log Replay Service (LRS) methods behind the scenes, while simplifying configuration, management, and monitoring of the migration process.

Managed Instance link

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, see 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 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 a 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 using 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

A key capability of SQL Managed Instance is the ability to natively restore database backup (.bak) files stored in Azure Storage. This feature makes database migration straightforward. 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:

Note

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

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

Step	SQL Engine and version	Backup/restore method
Put backup to Azure Storage	Before 2012 with Service Pack 1 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 migrate a database protected with transparent data encryption (TDE) to a SQL managed instance using the native restore option, you need to migrate the corresponding certificate from the SQL Server instance (on-premises, or SQL Server on an Azure VM) before restoring the database. For detailed information, see Migrate a certificate of a TDE-protected database to Azure SQL Managed Instance.

Restoring system databases isn't supported. To migrate instance-level objects (stored in master or msdb databases), script them out and run Transact-SQL (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 with Service Pack 1 CU2.

2. Connect to your SQL managed instance using SQL Server Management Studio (SSMS).

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. When the restore completes, view the database in Object Explorer within SSMS.

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 SSMS if the connection breaks, or a timeout expires. Azure SQL Database keeps trying to restore the database in the background, and you can track the progress of the restore by using the sys.dm_exec_requests and sys.dm_operation_status views.

Azure Database Migration Service (Azure DMS)

This section provides high-level steps to migrate from SQL Server to SQL Managed Instance with minimal downtime by using Azure DMS. For detailed information, see Tutorial: Migrate SQL Server to Azure SQL Managed Instance online.

To migrate using DMS from the Azure portal, follow these steps:

1. Open the Azure portal.

2. Open Azure DMS and either select the DMS instance if you already created one, or create a new one.

3. On the DMS instance dashboard, select Start migration, choose your source server type, set your target server type to Azure SQL Managed Instance, and select the migration backup file storage location and migration mode.

4. Provide the source SQL Server tracking details for Azure, such as subscription, resource group, location, and SQL Server instance name. This step creates a SQL Server instance enabled by Azure Arc.

5. Provide the target subscription and resource group, then choose the target SQL managed instance.

6. Provide the backup location details, such as resource group, storage account, blob container, folder, last backup file (for offline migration mode), and target database.

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.

   1. You might have to provide source SQL Server instance details and credentials to connect to it.

   2. Additionally, select the databases and location of the network SMB file share where backup files are kept and credentials to connect to it.

8. Start the database migration and monitor the progress in the Azure portal from your DMS instance monitoring dashboard.

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 you specify.

   4. Ensure all database backups have the status Restored in the monitoring details page.

   5. Select Complete cutover in the monitoring details page.

      For detailed instructions, see Tutorial: Migrate SQL Server to Azure SQL Managed Instance with DMS.

To migrate using DMS 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 DMS instance using the wizard in Azure Data Studio. If you previously created a DMS instance using Azure Data Studio, you can reuse the same instance 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 instance, 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 DMS resource in the 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 you specify.

   4. Ensure all database backups have the status Restored in the monitoring details page.

   5. Select Complete cutover in the monitoring details page.

SQL Server migration in Azure Arc

Migrate SQL Server instances enabled by Azure Arc to SQL Managed Instance through the Azure portal. SQL Managed Instance provides a fully managed PaaS solution for lift-and-shift migrations. The process includes assessing readiness, selecting a target, migrating data, and monitoring progress.

Two integrated methods are available:

• Managed Instance link for near real-time replication with minimal downtime,

• Log Replay Service for continuous backup and restore.

Microsoft Copilot assists throughout the migration. Migration supports SQL Server 2012 and later versions, and automates most steps.

For more information, see Migration to Azure SQL Managed Instance - SQL Server migration in Azure Arc.

Data sync and cutover

When you use migration options that continuously replicate or 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 the migration process captures and applies all changes on the source to the target.

After you verify that data is the same on both source and target, you can cut over from the source to the target environment. Plan the cutover process with business and application teams to ensure minimal interruption during cutover and that it 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, verifying completeness, and addressing performance issues with the workload.

Monitor and remediate applications

After you migrate to a SQL managed instance, track the application behavior and performance of your workload. This process includes the following activities:

• Compare performance of the workload running on the managed instance with the performance baseline that you created on the source SQL Server instance.
• Continuously monitor performance of your workload to identify potential issues and improvements.

Perform tests

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

1. Develop validation tests: To test database migration, use T-SQL queries. 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, then analyze the results.

4. Run performance tests: Run performance tests against the source and the target, then analyze and compare the results.

Use advanced features

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 SQL managed instances in a centralized manner.

Some SQL Server features are only available when you change the database compatibility level to the latest compatibility level.

Related content

• Services and tools available for data migration scenarios
• Service Tiers in Azure SQL Managed Instance
• T-SQL differences between SQL Server & Azure SQL Managed Instance
• Migrate databases with Azure SQL migration extension for Azure Data Studio
• Tutorial: Migrate SQL Server to Azure SQL Managed Instance with DMS
• Cloud Adoption Framework for Azure
• Best practices for costing and sizing workloads migrate to Azure