Data Migration: Database Migration Service & AzCopy

Enterprise data migration strategies with DMS, AzCopy, and Azure Data Box

Migration Architecture

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────┐
│                    MIGRATION ARCHITECTURE                            │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  SOURCE                 MIGRATION              TARGET                │
│  ┌──────────┐         ┌──────────────┐       ┌──────────────┐     │
│  │ SQL      │────────>│ Database     │──────>│ Azure SQL    │     │
│  │ Server   │         │ Migration    │       │ Managed Inst │     │
│  │ On-Prem  │         │ Service      │       │              │     │
│  └──────────┘         └──────────────┘       └──────────────┘     │
│                                                                     │
│  ┌──────────┐         ┌──────────────┐       ┌──────────────┐     │
│  │ Files/   │────────>│ AzCopy /     │──────>│ ADLS Gen2    │     │
│  │ Folders  │         │ Data Box     │       │              │     │
│  │ On-Prem  │         │              │       │              │     │
│  └──────────┘         └──────────────┘       └──────────────┘     │
│                                                                     │
│  MIGRATION PHASES:                                                  │
│  ┌─────────────────────────────────────────────────────────────┐   │
│  │ 1. ASSESS    2. MIGRATE    3. VALIDATE    4. CUTOVER       │   │
│  │ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐      │   │
│  │ │Schema    │ │Full/     │ │Data      │ │Switch    │      │   │
│  │ │Analysis  │ │Incremental│ │Validation│ │Traffic   │      │   │
│  │ │          │ │Load      │ │          │ │          │      │   │
│  │ │Perf      │ │CDC       │ │Row       │ │DNS       │      │   │
│  │ │Baseline  │ │Sync      │ │Counts    │ │Update    │      │   │
│  │ └──────────┘ └──────────┘ └──────────┘ └──────────┘      │   │
│  └─────────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘

AzCopy Commands

# Copy from on-prem to ADLS Gen2
azcopy copy "C:\data\sales\*" "https://stdatalake001.dfs.core.windows.net/raw/sales/?<SAS>" --recursive

# Sync directory (incremental)
azcopy sync "https://stdatalake001.dfs.core.windows.net/raw/sales/?<SAS>" "C:\backup\sales" --delete-destination

# Copy with access tier
azcopy copy "https://source.blob.core.windows.net/container/*" "https://stdatalake001.blob.core.windows.net/archive/?<SAS>" --block-blob-tier=Cool

# Parallel transfer
azcopy copy "https://source/*" "https://stdatalake001.dfs.core.windows.net/raw/?<SAS>" --recursive --parallel=32

DMS Migration

{
  "properties": {
    "sourceConnectionInfo": {
      "dataSource": "sqlserver",
      "connectionDetails": {
        "dataSource": "onprem-sql.company.com",
        "authentication": "Windows",
        "encryptConnection": true
      }
    },
    "targetConnectionInfo": {
      "dataSource": "sql-managed-instance.database.windows.net",
      "connectionDetails": {
        "dataSource": "sql-managed-instance.database.windows.net",
        "authentication": "SQL",
        "encryptConnection": true
      }
    },
    "databaseSettings": {
      "selectedDatabases": ["sales_db", "inventory_db"],
      "databasesOfflineMigration": false
    }
  }
}

ℹ️

Pro Tip: Use AzCopy for file-based migrations (terabytes). Use DMS for database migrations with ongoing sync. Use Data Box for petabyte-scale offline migrations.

Interview Questions

Q1: How do you validate data after migration? A: 1) Compare row counts, 2) Validate checksums/hashes for critical columns, 3) Run business validation queries, 4) Compare aggregate metrics (sums, counts), 5) Test application functionality against target.

Q2: What is the difference between online and offline migration? A: Online migration keeps source running during migration (minimal downtime). Offline migration requires source downtime. Use online for production systems; offline for non-critical or batch workloads.

Q3: How do you handle schema changes during migration? A: Use DMS schema synchronization for ongoing migrations. For AzCopy/file migrations, implement schema validation checks and transformation pipelines post-migration.