Tutorial: Migrate SQL Server to Azure SQL Database (offline)

You can use Azure Database Migration Service via the Azure portal to migrate databases from an on-premises instance of SQL Server to Azure SQL Database (offline).

In this tutorial, learn how to migrate the sample AdventureWorks2019 database from an on-premises instance of SQL Server to an instance of Azure SQL Database, by using Database Migration Service. This tutorial uses offline migration mode, which considers an acceptable downtime during the migration process.

Prerequisites

Before you begin the tutorial:

• Ensure that you can access the Azure portal

• Have an Azure account that's assigned to one of the following built-in roles:

  • Contributor for the target instance of Azure SQL Database
  • Reader role for the Azure resource group that contains the target instance of Azure SQL Database
  • Owner or Contributor role for the Azure subscription (required if you create a new instance of Azure Database Migration Service)

  As an alternative to using one of these built-in roles, you can assign a custom role.

• Create a target instance of Azure SQL Database.

• Make sure that the SQL Server login that connects to the source SQL Server instance is a member of the db_datareader role, and that the login for the target SQL Server instance is a member of the db_owner role.

• To migrate the database Schema from source to target Azure SQL DB by using the Database Migration Service, the minimum supported SHIR version required is 5.37 or above.

• If you're using Database Migration Service for the first time, make sure that the Microsoft.DataMigration resource provider is registered in your subscription.

Create a Database Migration Service instance

Step 1: In the Azure portal, navigate to the Azure Database Migration Service page. Create a new instance of Azure Database Migration Service, or reuse an existing instance that you created earlier.

Use an existing instance of Database Migration Service

To use an existing instance of Database Migration Service:

• On Azure portal, under Azure Database Migration Services, select an existing instance of Database Migration Service that you want to use, ensuring that it's present in right Resource Group and region.

  

Create a new instance of Database Migration Service

To create a new instance of Database Migration Service:

1. On Azure portal, under Azure Database Migration Service, select Create.

   

2. In Select migration scenario and Database Migration Service, select the desired input like Source and Target server type, choose Database Migration Service and choose Select.

   

3. On the next screen Create Data Migration Service, select your subscription and resource group, then select Location, and enter the Database Migration Service name. Select Review + Create. This creates the Azure Database Migration Service.

   

4. If the self-hosted integration runtime (SHIR) is required, on the overview page of your Database Migration Service and under Settings, select Integration runtime, and complete the following steps:

   1. Select Configure integration runtime and choose the Download and install integration runtime link to open the download link in a web browser. Download the integration runtime, and then install it on a computer that meets the prerequisites for connecting to the source SQL Server instance.

      

      When installation is finished, Microsoft Integration Runtime Configuration Manager automatically opens to begin the registration process.

   2. In the Authentication key table, copy one of the authentication keys that are provided in the wizard and paste it in Microsoft Integration Runtime Configuration Manager.

      

      If the authentication key is valid, a green check icon appears in Integration Runtime Configuration Manager. A green check indicates that you can continue to Register.

      After you register the self-hosted integration runtime, close Microsoft Integration Runtime Configuration Manager. It might take several minutes to reflect the Node details on Azure portal for Database Migration Service, under Settings > Integration runtime.

      

      Note

      For more information about the self-hosted integration runtime, see Create and configure a self-hosted integration runtime.

Start a new migration

1. In Step 2 to start a new migration using Database Migration Service from Azure portal, under Azure Database Migration Services, select an existing instance of Database Migration Service that you want to use, and then select either New Migration or Start migrations.

2. Under Select new migration scenario, choose your source, target server type, migration mode and choose Select.

   

3. Now under Azure SQL Database Offline Migration wizard:

   1. Provide below details to connect to source SQL server and select Next:

      • Source server name
      • Authentication type
      • User name and password
      • Connection properties

      

   2. On next page, select databases for migration. This page might take some time to populate the list of databases from source.

      

   3. Assuming you have already provisioned the Target based upon the assessment results, provide the target details on Connect to target Azure SQL Database page, and select Next:

      • Azure subscription
      • Azure resource group
      • Target Azure SQL Database server
      • Authentication type
      • User name and password

      

   4. Under Map source and target databases, map the databases between source and target.

      

   5. Before moving to this step, ensure to migrate the schema from source to target for all selected databases. Then, Select database tables to migrate for each selected database and select the table/s for which you want to migrate the data".

      

   6. Review all the inputs provided on Database migration summary page and select Start migration button to start the database migration.

      

      Note

      In an offline migration, application downtime starts when the migration starts.

      Now, you can migrate database Schema and data both using Database Migration Service. Also, you can use tools like the SQL Server dacpac extension or the SQL Database Projects extension in Azure Data Studio to migrate schema before selecting the list of tables to migrate.

Monitor the database migration

1. In the Database Migration Service instance overview, select Monitor migrations to view the details of your database migrations.

   

2. Under the Migrations tab, you can track migrations that are in progress, completed, and failed (if any), or you can view all database migrations. In the menu bar, select Refresh to update the migration status.

   

   Database Migration Service returns the latest known migration status each time migration status refreshes. The following table describes possible statuses:

   Status	Description
   Preparing for copy	The service is disabling autostats, triggers, and indexes in the target table.
   Copying	Data is being copied from the source database to the target database.
   Copy finished	Data copy is finished. The service is waiting on other tables to finish copying to begin the final steps to return tables to their original schema.
   Rebuilding indexes	The service is rebuilding indexes on target tables.
   Succeeded	All data is copied and the indexes are rebuilt.

3. Under Source name , select a database name to open the table view. In this view, you see the current status of the migration, the number of tables that currently are in that status, and a detailed status of each table.

   

4. When all table data is migrated to the Azure SQL Database target, Database Migration Service updates the migration status from In progress to Succeeded.

   

You've completed the migration to Azure SQL Database. We encourage you to go through a series of post-migration tasks to ensure that everything functions smoothly and efficiently.

Limitations

Azure SQL Database offline migration utilizes Azure Data Factory (ADF) pipelines for data movement and thus abides by ADF limitations. A corresponding ADF is created when a database migration service is also created. Thus factory limits apply per service.

• The machine where the SHIR is installed acts as the compute for migration. Make sure this machine can handle the cpu and memory load of the data copy. To learn more, review SHIR recommendations.
• 100,000 table per database limit.
• 10,000 concurrent database migrations per service.
• Migration speed heavily depends on the target Azure SQL Database SKU and the self-hosted Integration Runtime host.
• Azure SQL Database migration scales poorly with table numbers due to ADF overhead in starting activities. If a database has thousands of tables, the startup process of each table might take a couple of seconds, even if they're composed of one row with 1 bit of data.
• Azure SQL Database table names with double-byte characters currently aren't supported for migration. Mitigation is to rename tables before migration; they can be changed back to their original names after successful migration.
• Tables with large blob columns may fail to migrate due to timeout.
• Database names with SQL Server reserved are currently not supported.
• Database names that include semicolons are currently not supported.
• Computed columns don't get migrated.

Next steps

• Create an Azure SQL database
• Azure SQL Database overview
• Connect apps to Azure SQL Database