Cloud & IT Cert Prep · Lesson

Database Migration Best Practices

Use the Azure Database Migration Service to migrate a SQL Server database to Azure SQL with minimal downtime, and resolve common schema and compatibility issues.

Database Migration Best Practices is a free Cloud & IT Cert Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Cloud & IT Cert Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Database Migration Is Different

Migrating a database is more complex than lifting a VM because databases are stateful and often have active connections 24/7. A naïve copy-and-restore approach requires hours of downtime, which is unacceptable for production systems. Best-practice database migration uses continuous data replication to keep source and target in sync while the target is validated, then performs a brief, planned cutover when traffic is redirected to the new Azure database.

Azure Database Migration Service

Azure Database Migration Service (DMS) is a fully managed service that orchestrates online (near-zero-downtime) and offline (downtime-based) migrations from popular on-premises engines to Azure managed database services. DMS supports sources including SQL Server, MySQL, PostgreSQL, MongoDB, Oracle, and targets including Azure SQL Database, Azure SQL Managed Instance, Azure Database for MySQL, and Azure Cosmos DB. The service integrates with Data Migration Assistant (DMA) for pre-migration compatibility checks.

# Create a DMS instance
az dms create \
  --service-name myDMS \
  --resource-group myRG \
  --location eastus \
  --sku-name Premium_4vCores \
  --vnet myVnet \
  --subnet mySubnet

Pre-Migration Assessment with DMA

Before migrating SQL Server, run the Data Migration Assistant (DMA) against the source database. DMA identifies: compatibility issues (features used in your database that are not supported by the Azure SQL target), breaking changes, deprecated features, and performance recommendations. It generates a detailed HTML report that prioritises issues by severity so your team can fix them before the migration window begins.

# Run DMA assessment from CLI (Windows only)
# DmaCmd.exe /AssessmentName='SQL2022toAzureSQL' \
#   /AssessmentSourcePlatform='SqlOnPrem' \
#   /AssessmentTargetPlatform='AzureSqlDatabase' \
#   /AssessmentDatabases='Server=myServer;Initial Catalog=AdventureWorks;Integrated Security=true'

Online vs. Offline Migration

Offline migration takes the source database offline for the duration of the migration — acceptable for small databases or non-critical systems where a maintenance window is feasible. Online migration uses change data capture (CDC) or transaction log shipping to replicate changes continuously while the new database is being loaded, minimising downtime to just the final cutover step. Online migration is recommended for databases larger than 1 GB or with SLAs requiring less than 1 hour of downtime.

Configuring a DMS Migration Project

A DMS migration project specifies the source connection (server name, authentication, database names), the target connection (Azure SQL connection string), and the migration mode (online or offline). You also select which databases and tables to migrate. DMS validates connectivity before starting. The migration project persists in the Azure portal so you can monitor progress, retry failed tables, and view detailed activity logs.

# Create a DMS project for SQL Server to Azure SQL Database
az dms project create \
  --service-name myDMS \
  --resource-group myRG \
  --name SQL2AzureSQL \
  --source-platform SQL \
  --target-platform SQLDB \
  --location eastus

Schema Migration First

Always migrate the schema before data. Use DMA or SQL Server Management Studio (SSMS) to script and deploy tables, views, stored procedures, functions, and indexes to the target Azure SQL Database. Verify that all schema objects exist and compile without errors before allowing DMS to begin data transfer. Schema errors discovered mid-migration can corrupt the migration run and force you to restart from scratch.

# Generate schema scripts using sqlpackage
sqlpackage /Action:Script \
  /SourceServerName:myOnPremServer \
  /SourceDatabaseName:AdventureWorks \
  /TargetServerName:mysqlserver.database.windows.net \
  /TargetDatabaseName:AdventureWorksAzure \
  /OutputPath:/tmp/schema.sql

Initial Data Load

DMS performs a full data load by reading the source database table-by-table and writing rows to the Azure SQL target using bulk insert. During the full load, the service handles large tables by chunking them into batches. Indexes on the target are disabled during load to maximise throughput, then rebuilt afterwards. For very large databases (TB-scale), consider using Azure Data Factory or bacpac exports to pre-load data and reduce DMS run time.

Change Data Capture for Online Sync

For online migrations, DMS enables SQL Server Change Data Capture (CDC) on the source to capture INSERT, UPDATE, and DELETE operations that occur while the full load is in progress. Once the full load completes, DMS replays these captured changes on the target, gradually closing the gap. When the latency drops to near zero, the database is ready for cutover. CDC requires the SQL Server Agent to be running and the source database to be in full recovery mode.

-- Enable CDC on source database
EXEC sys.sp_cdc_enable_db;

-- Enable CDC on specific table
EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name = N'Orders',
  @role_name = NULL;

Cutover and Validation

When CDC lag is minimal, initiate the cutover in the DMS portal. DMS stops accepting new CDC events, applies any remaining changes, and marks the migration complete. At this point you redirect application connection strings to the Azure SQL endpoint. Run data validation queries comparing row counts and checksum aggregates between source and target for critical tables. Keep the source database in read-only mode for 24–48 hours post-cutover in case a rollback is needed.

-- Validate row counts post-cutover
SELECT TABLE_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME;

Performance Tuning Post-Migration

After migration, Azure SQL Database may perform differently from on-premises SQL Server because of different query plan caches, statistics, and hardware. Run the Database Experimentation Assistant (DEA) to replay workload traces on the Azure target and compare execution plans. Enable Automatic Tuning in Azure SQL to let the service automatically create or drop indexes and force regression-free query plans, improving performance over time without manual intervention.

-- Enable Automatic Tuning on Azure SQL Database
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (
  FORCE_LAST_GOOD_PLAN = ON,
  CREATE_INDEX = ON,
  DROP_INDEX = OFF
);

Migration of Other Database Engines

DMS also migrates open-source engines: MySQL to Azure Database for MySQL using the mysqldump + binlog replication pattern, and PostgreSQL to Azure Database for PostgreSQL using logical replication slots. For MongoDB to Cosmos DB migrations, use the Cosmos DB for MongoDB API and the native mongodump / mongorestore pipeline or Azure Data Factory's MongoDB connector for incremental CDC. Always test the migration on a non-production copy before running against live data.

# Offline MySQL migration using mysqldump
mysqldump -h source-server -u admin -p \
  --single-transaction --routines --triggers \
  myDatabase > /tmp/myDatabase.sql

# Restore to Azure Database for MySQL
mysql -h myserver.mysql.database.azure.com \
  -u adminUser@myserver -p myDatabase < /tmp/myDatabase.sql

Quick Check

Test your understanding of Microsoft Azure Fundamentals (AZ-900) concepts from this lesson.

Lesson Recap

In this lesson you learned: Azure Database Migration Service orchestrates online and offline database migrations, pre-migration assessment with DMA catches compatibility issues before the migration window, and change data capture enables near-zero-downtime cutover for production databases. Next up we shift focus to optimising Azure costs.

Frequently asked questions

Is the “Database Migration Best Practices” lesson free?

Yes — the full text of “Database Migration Best Practices” is free to read here on the web, and the Cloud & IT Cert Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Cloud & IT Cert Prep course, upgrade to CoddyKit PRO.

What will I learn in “Database Migration Best Practices”?

Use the Azure Database Migration Service to migrate a SQL Server database to Azure SQL with minimal downtime, and resolve common schema and compatibility issues. You practise Cloud & IT Cert Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Cloud & IT Cert Prep?

No prior experience is required. Cloud & IT Cert Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Database Migration Best Practices” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Cloud & IT Cert Prep lesson?

Yes. Every Cloud & IT Cert Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

The 6-Rs Migration Framework
Azure Migrate: Discovery and Assessment
Rehost with Azure Migrate (Lift and Shift)
Database Migration Best Practices

← Back to Cloud & IT Cert Prep