Azure Database Migration Service

Azure Database Migration Service (DMS) is a fully managed Azure service designed to simplify and accelerate the migration of databases from on-premises, other clouds, or virtual machines to Azure data platforms. It supports both offline (one-time, full data movement) and online (minimal downtime, continuous sync) migrations. DMS automates the tedious tasks of schema conversion, data transfer, and synchronization, reducing manual effort and risk of errors.

Traditionally, database migrations were complex, time-consuming, and error-prone. They required manual scripting, extended downtime, and deep expertise in both source and target platforms. DMS addresses these challenges by providing: - Automated assessment: Identifies compatibility issues and suggests remediation. - Minimal downtime: Online migration mode allows applications to remain operational during the bulk of the migration. - Integrated tooling: Works with Azure Data Studio, Azure Portal, PowerShell, and CLI. - Support for heterogeneous migrations: From Oracle, PostgreSQL, MySQL, MongoDB, and more.

DMS uses a migration pipeline that consists of several phases:

1. Assessment: DMS first performs a pre-migration assessment using the Data Migration Assistant (DMA) or Azure Migrate. This checks for feature parity, blocking issues, and performance recommendations. 2. Schema Migration: DMS converts the source schema to the target schema. For homogeneous migrations (e.g., SQL Server to Azure SQL), this is straightforward. For heterogeneous migrations (e.g., Oracle to Azure SQL), DMS uses the Data Migration Assistant to map data types and objects. 3. Data Migration: - Offline mode: DMS takes a full snapshot of the source database and restores it to the target. The source database must be offline during this process. - Online mode: DMS performs an initial full load, then continuously syncs changes from the source to the target using change data capture (CDC) or transaction log replication. The source remains operational until cutover. 4. Cutover: For online migrations, after the initial sync catches up, you perform a final sync to bring the target fully up to date, then switch the application connection string to the target. The source is taken offline only at this point.

- SQL Server (on-premises, Azure VM) - Oracle (versions 10g, 11g, 12c, 18c, 19c) - PostgreSQL (on-premises, Azure VM) - MySQL (on-premises, Azure VM) - MongoDB (up to version 4.0) - Supported Targets:

- Azure SQL Database (single, elastic pool) - Azure SQL Managed Instance - Azure SQL Server on Azure VM - Azure Database for PostgreSQL (Single Server, Flexible Server) - Azure Database for MySQL (Single Server, Flexible Server) - Azure Cosmos DB (for MongoDB API) - Default Timeout: The default timeout for migration activities is 24 hours. This can be extended via REST API or PowerShell. - Bandwidth Requirement: For online migrations, Microsoft recommends at least 10 Mbps network bandwidth between source and Azure. - Database Size Limits: DMS can handle databases up to 2 TB for offline migrations and 1 TB for online migrations (for SQL Server). Larger databases may require splitting or using Azure Data Factory.

You can create and manage DMS using Azure CLI, PowerShell, or Azure Portal. Here are key commands:

Azure CLI

Create a DMS instance (Standard tier for production, Premium for high throughput):

az dms create \
  --resource-group myResourceGroup \
  --name myDMS \
  --location eastus \
  --sku-name Standard_1vCore

Create a migration project:

az dms project create \
  --resource-group myResourceGroup \
  --service-name myDMS \
  --name myProject \
  --source-platform SQL \
  --target-platform SQLDB

Create a migration activity (offline):

az dms project task create \
  --resource-group myResourceGroup \
  --service-name myDMS \
  --project-name myProject \
  --name myTask \
  --task-type OfflineConfiguration

Monitor migration status:

az dms project task show \
  --resource-group myResourceGroup \
  --service-name myDMS \
  --project-name myProject \
  --name myTask \
  --query 'properties.state'

PowerShell

New-AzDataMigrationService -ResourceGroupName myResourceGroup -Name myDMS -Location eastus -Sku Standard_1vCore
New-AzDataMigrationProject -ResourceGroupName myResourceGroup -ServiceName myDMS -ProjectName myProject -SourceType SQL -TargetType SQLDB
New-AzDataMigrationTask -ResourceGroupName myResourceGroup -ServiceName myDMS -ProjectName myProject -TaskName myTask -TaskType OfflineConfiguration

A large financial services company needs to migrate a 5-TB SQL Server 2017 database to Azure SQL Managed Instance to leverage PaaS benefits while maintaining SQL Server Agent and cross-database dependencies. They use DMS in online mode to minimize downtime. The source is a SQL Server Always On availability group with multiple replicas. DMS is configured to use a secondary replica as the source to reduce impact on the primary. The migration takes two weeks for the initial full load due to network bandwidth constraints (50 Mbps). During continuous sync, replication lag stays under 5 seconds. At cutover, the application is paused for 2 minutes while the final sync completes. Key lesson: Always test cutover procedures in a staging environment.

A retail company is migrating its Oracle 12c database (2 TB) to Azure SQL Database to reduce licensing costs. This is a heterogeneous migration requiring schema conversion. DMS uses the Data Migration Assistant to convert Oracle schemas, including PL/SQL packages, to T-SQL. However, some complex stored procedures require manual refactoring. The assessment phase identifies these issues early. The migration is performed offline because the application can tolerate 4 hours of downtime during a maintenance window. DMS completes the full load in 3 hours. After migration, the team runs compatibility tests. Common pitfalls: Oracle sequences may not map directly to SQL Server identity columns, and NULL handling differs.