Azure Database Migration Service (DMS) is a fully managed service designed to enable seamless migrations from multiple database sources to Azure data platforms with minimal downtime. It powers the “Azure SQL Migration” extension for Azure Data Studio, can be used via Azure portal, PowerShell and Azure CLI.

This article explains architecture diagram of Azure Database Migration Service and how the DMS perform migration for different scenarios.

How does a normal migration work?

There are two ways to perform migration for a database from source to Azure SQL Targets as mentioned below:

Physical migration: In Physical migration, Backups from the source databases are used to perform restore on the Azure SQL Target databases. DMS uses Physical migration for migrating to Target Azure SQL VM and Azure SQL MI.

Physical Migration

Logical migration: In logical migration, data rows are read from the source database tables and then inserted into the Target Azure SQL’s database tables. DMS uses Logical migration for migrating to Azure SQL DB.

Note:

In case of logical migration, example for Target Azure SQL DB, Schema migration is a prerequisite before proceeding with data migration.

 Logical Migration

How does DMS architecture work?

Overview

Azure Database Migration Service (DMS) is an Azure service that orchestrates migration pipelines to do data movement activities from an on-premises environment to Azure. When a customer creates a Database Migration Service instance (in customer’s subscription), it associates itself with the Azure Data Factory pipeline (in Microsoft’s subscription).

Using this DMS instance customers can start, monitor, and complete / cancel the migration. While DMS functions as an orchestrator, it uses the Azure Data Factory’s Self-hosted Integration Runtime (Migration agent) in case backup files are placed on local SMB file share to copy them to Azure blob container or to perform logical migration to Azure SQL DB (bulk reading data from source and writing on Target). Customer can install the SHIR on a local machine (near to source), then register it using DMS provided authentication keys and associate SHIR with DMS.

Once backups are copied to or let’s say are already present on Azure blob storage container, DMS communicate with Target (Azure SQL VM or Azure SQL MI) resource provider’s restore service to restore these backups from Azure blob storage to respective Target.

Architecture components:

The following list describes the various components of DMS architecture:

Source SQL Server: An on-premises instance of SQL Server that is in a private cloud or an instance of SQL Server on a virtual machine in a public cloud. SQL Server 2008 and later versions on Windows or Linux are supported.

Target Azure SQL: Supported Azure SQL targets are Azure SQL Managed Instance, SQL Server on Azure Virtual Machines (registered with the SQL infrastructure as a service extension in full management mode), and Azure SQL Database.

SMB (backup) file share: A Server Message Block (SMB) network file share where backup files are stored for the databases to be migrated. Azure storage blob containers and Azure storage file share also are supported.

Client Tools: Azure DMS can be used by different client tools like “Azure SQL Migration” extension for Azure Data Studio, Azure portal, PowerShell and Azure CLI.

Azure Storage Account: The blob storage account in customer’s subscription is used to write the backup files from the source and to read from this blob storage container for restoring it on Target.

Azure Data factory: Database Migration Service is associated with the Azure Data Factory’s pipeline. ADF pipeline acts as a placeholder of activities trigger by the DMS’s migration workflow and provides the capability to register and monitor the self-hosted integration runtime.

Self-hosted integration runtime (SHIR): Install a Self-hosted integration runtime on a computer that can connect to the source SQL Server instance and the location of the backup file along with connectivity to Azure blob storage / resources. Database Migration Service provides the authentication keys and registers the self-hosted integration runtime. SHIR act as a compute of the DMS migration’s data movement and can be scaled upto 4 nodes maximum.

Note:

If customers have backup files on local SMB file share or want to perform logical migration to Azure SQL DB, then only Self-hosted integration runtime is needed.

Target resource providers and Restore Service: Target resource provider is a collection of REST operations that enables functionality for an Azure service like restore service on the Target Azure SQL VM or MI. This restore service is responsible for scanning the backups in the Azure blob container, validate, creating a restoration plan, and then performing the restore of these backups on the Target. In case of Azure SQL VM, it is part of the SQL Server IaaS Agent extension and for Azure SQL MI, it is the Log replay service (LRS).

Architecture of Azure Database Migration Service

The following diagram illustrates the DMS architecture workflow:

DMS architecture

The number labels against the workflow defines the migration sequence w.r.t a given scenario (as stated in the key on right side bottom).

And below is the detailed description of architecture workflows based upon the migration scenarios:

     A. Migration to Azure SQL VM or SQL MI from Azure blob storage:

      1. Customer can use different client tools like – “Azure SQL Migration” extension for Azure Data Studio, Azure portal, PowerShell and Azure CLI to create and migrate using Azure Database migration Service. They can also perform Pre-migration activities like Assessment and SKU recommendations using Azure SQL Migration extension, PowerShell and Azure CLI.

      2. Once a customer creates the DMS instance in their subscription, they can use it to perform Azure DB migrations. DMS also creates an ADF instance in the background which runs in Microsoft’s subscription.

      3a. Since in this scenario, the customer has already placed the database backups (Full, differential and Tlog) in the Azure blob storage, DMS communication these details to the Target Resource provider’s restore service.

      4a. Restore service scans the Azure blob storage container, validate, and creates a restore plan based upon the available backups files.

Note:

It is recommended not to delete any backup from blob container until migration completes as it impacts the restore plan.
It is required to keep the single database backup in a folder and the folder should be in the root directory of the blob container. Alternatively, if the backups are stored in the root directory, they should also correspond to one database only.

      5a. Once it creates the restore plan, the Target’s restore service only takes the required backup files and skips the backup files which are not relevant. Example if there are two full backups of same database, restore service skip the older full backup.

      6a. After that, backups are restored to the Target with NORECOVERY option. In case of online migration, once customer initiates the cutover, restore service looks for any remaining backup, restore those last backup with RECOVERY and bring the Target DB online. In case of offline migration, as soon as it finds the “last restore file” mentioned by customer (during configuration of DMS migration), it performs restore with RECOVERY and bring the Target DB online.

     B. Migration to Azure SQL VM or SQL MI from local SMB backup file share:

      1. Customer can use different client tools like – “Azure SQL Migration” extension for Azure Data Studio, Azure portal, PowerShell and Azure CLI to create and migrate using Azure Database migration Service. They can also perform Pre-migration activities like Assessment and SKU recommendations using Azure SQL Migration extension, PowerShell and Azure CLI.

      2. Once a customer creates the DMS instance in their subscription, they can use it to perform migrations. DMS also creates an ADF instance in the background which runs in Microsoft’s subscription.

      3b. To copy the backup files from local SMB backup file share to Azure blob storage container, DMS inform the ADF factory pipeline and request for a copy activity.

      4b. This copy activity request is carried out by SHIR, which acts as a Migration agent, running on a nearby computer w.r.t to the source. To do so, SHIR node should be able to communicate with source and Azure resources.

Note:

The connectivity required for SHIR to function is mentioned here.
For SHIR related best practices, refer here.

      5b. SHIR then read the meta data of backup files from the local SMB backup file share, ensuring the backup files should belong to the mentioned database. After copying the backup files, SHIR keeps on scanning the file share for new backup files after every 1 min interval.

      6b. These backup files are then transferred to an Azure blob storage container.

      7b. Restore service scans the Azure blob storage container, validate, and creates a retore plan based upon the available backup files.

Note:

It is recommended not to delete any backup from blob container until migration completes as it impacts the restore plan.
It is required to keep the single database backup in a folder and the folder should be in the root directory of the blob container. Alternatively, if the backups are stored in the root directory, they should also correspond to one database only.

8b. DMS informs the Target resource provider’s restore service after copying finishes. As per the restore plan, Target’s restore service only restores the required backup files and skips the backup files which are not relevant. Example if there are two full backups of same database, restore service skip the older full backup.

      9b. After that, backups restored to the Target with NORECOVERY option. In case of online migration, once customer initiates the cutover, restore service looks for any remaining backup, then restore those last backup with RECOVERY and bring the Target DB online. In case of offline migration, as soon as it finds the “last restore file” mentioned by customer (during configuration of DMS migration), it performs restore with RECOVERY and bring the Target DB online.

     C. Migration to Azure SQL DB:

      1. Customer can use different client tools like – “Azure SQL Migration” extension for Azure Data Studio, Azure portal, PowerShell and Azure CLI to create and migrate using Azure Database migration Service. They can also perform Pre-migration activities like Assessment and SKU recommendations using Azure SQL Migration extension, PowerShell and Azure CLI.

      2. Once a customer creates the DMS instance in their subscription, they can use it to perform migrations. DMS also creates an ADF instance in the background which runs in Microsoft’s subscription.

      3c. Since it is a logical migration, DMS inform the ADF pipeline to initiate migrate schema and/or copy data rows activity from the selected tables based upon the configuration provided by the customer.

      4c. This activity request is executed by the SHIR, acting as a Migration agent and running on a local computer near to source. To do so, SHIR node should be able to communicate with source and Azure resources.

Note:

The connectivity required for SHIR to function is mentioned here.
For SHIR related best practices, refer here.

5c. SHIR reads the schema / data rows from the selected tables. It performs SQL bulk copy to read the data.

Note:

To improve the Azure SQL DB migration performance, refer here.

6c. It is also responsible to write this data on the Target Azure SQL DB using bulk insert. On Target Azure SQL DB, first schema migration is performed (if configured by customer) and then the data row movement starts. Once all the data rows are inserted on the Target, DMS perform post copying activities like creation of indexes etc. before finishing the migration.

Note:

In case of Target Azure SQL DB, Schema migration is a prerequisite before proceeding with data migration.
In case of any error while migrating schema, if error does not belong to the selected table, then schema migration continues later followed by data row migration.

Supported migration mode

Below are the supported migration modes – Offline and Online migration based upon the Azure SQL Targets.

Azure SQL Targets

Offline migration

Online migration

Azure SQL Database

Yes

No

Azure SQL Managed Instance

Yes

Yes

SQL Server on Azure VM

Yes

Yes

Supported SQL server sources

Azure Database Migration service supports below listed SQL server sources and with version of SQL server 2008 or onwards. 

SQL Sources

Supported

On-prem SQL Server instance

Yes

SQL Server on Private cloud

Yes

SQL Server on VM in Public cloud

Yes

AWS RDS SQL Server

Yes

Resources

For more information about the extension and Azure Database Migration Service, see the following resources.

Azure Database Migration Service documentation
Migrate databases using the Azure SQL Migration extension
One-click SQL Migration PoC environment
Ports and Firewall considerations for SHIR

​Microsoft Tech Community – Latest Blogs –Read More