Azure Database Migration Service (DMS) - AI-Optimized Technical Reference

Executive Summary

Azure Database Migration Service migrates SQL Server databases to Azure with a 70% first-attempt success rate. Classic DMS retires March 15, 2026. Current version requires Azure Data Studio extension and Self-Hosted Integration Runtime (SHIR) 5.37+.

Critical Decision Factors

Migration Path Selection

• Offline: 2-8 hours downtime, 95% success rate, databases <100GB
• Online: Continuous operation, 60% success rate due to network failures, databases >500GB
• Real timeline multiplier: 2-5x Microsoft estimates

Target Platform Comparison

Platform	Cost Level	Restrictions	Use Case
Azure SQL Database	Lowest	High (no linked servers, cross-DB queries)	New applications
SQL Managed Instance	Medium	Low	Legacy applications
SQL VM	Highest	None	Complex legacy systems

Critical Failure Modes

Network Connectivity (90% of failures)

• Symptom: Connection timeouts during migration
• Root causes: Firewall rules, SHIR version incompatibility, authentication token expiration
• Resolution time: 2-4 hours average
• Prevention: Test connectivity before migration, use SHIR 5.37.8767.4+

Authentication Issues

• Required permissions: db_owner on source (minimum), proper Azure SQL roles on target
• Common failure: Using db_datareader insufficient permissions
• Password migration: Passwords do NOT migrate, manual reset required
• Service accounts: Need login rights, not just database access

Schema Compatibility

• Assessment accuracy: 70% (30% false negatives)
• Breaking changes: Custom assemblies, linked servers, xp_cmdshell, cross-database queries
• Migration success: Schema migration works, but dependencies break applications

Resource Requirements

Time Investment

• 10GB database: 30 minutes - 2 hours
• 100GB database: 4-12 hours (not 2-3 hours as documented)
• 500GB database: 12-48 hours
• 1TB+ database: 2-3 days
• Troubleshooting overhead: Add 25-50% to timeline

Cost Components (Hidden charges)

• DMS service: Free
• Data transfer: $0.02-0.09 per GB
• Target compute: Ongoing Azure SQL costs
• Storage: Database, backup, and log charges
• Failed attempts: Each retry burns additional compute time
• Real cost: 150-200% of calculator estimates

Expertise Requirements

• Basic migration: 1-2 days learning curve
• Complex troubleshooting: Windows networking, SQL Server internals knowledge
• PowerShell automation: 1 day to understand cmdlet syntax quirks

Configuration That Actually Works

SHIR Setup

Version: 5.37.8767.4 or newer (critical)
Installation: Clean install, don't upgrade in place
Network: Machine must reach both source DB and Azure
Authentication: Dedicated migration accounts with explicit permissions

Source Database Requirements

Permissions: db_owner minimum (db_datareader fails)
Transaction log: Adequate space for log shipping
Network: Firewall rules for port 1433
Backup: Recent full backup for validation

Target Azure SQL Configuration

SKU sizing: Use 50% of DMS recommendations
Authentication: SQL authentication easier than Windows
Firewall: Allow Azure services and SHIR IP
Collation: Match source to avoid application failures

Known Breaking Points

Feature Incompatibilities

• Linked servers: Not migrated, applications break immediately
• SQL Mail/Database Mail: Only works on Managed Instance
• Custom .NET assemblies: Azure SQL Database rejects completely
• xp_cmdshell usage: Blocked in Azure SQL Database
• Cross-database queries: Managed Instance only

Performance Thresholds

• UI breaks: >1000 spans makes debugging impossible
• Transaction log full: Common with databases >100GB during online migration
• Network timeout threshold: Default 30 seconds too short for large transactions
• Memory pressure: SHIR requires 4GB+ RAM for large databases

Data Integrity Issues

• Character encoding: Unicode vs ANSI mismatches cause data corruption
• Data type mapping: Money, datetime2 precision differences
• Constraint violations: Foreign key timing during bulk insert
• Case sensitivity: Azure SQL Database case-sensitive by default

Critical Warnings

What Official Documentation Omits

• Classic DMS randomly fails on databases >100GB with useless error messages
• Online migrations fail 40% of time due to network instability
• Assessment tool misses 30% of compatibility issues
• SKU recommendations consistently oversized by 100%
• Password migration doesn't work despite documentation claims

Production Failure Scenarios

• Scenario: Large database online migration
• Failure mode: Transaction log fills up, sync breaks, manual intervention required
• Impact: Extended downtime, potential data loss
• Frequency: 30-40% of online migrations >200GB

Breaking Changes

• Classic DMS support ends March 15, 2026 (hard cutoff)
• SHIR versions <5.37 incompatible with schema migration
• PowerShell cmdlet syntax changes between versions
• Azure portal UI changes break saved migration configurations

Troubleshooting Decision Tree

Connection Failures

1. Restart SHIR service (fixes 60% of auth timeouts)
2. Verify firewall rules both directions
3. Test with telnet [server] 1433
4. Check SHIR version compatibility
5. Nuclear option: Delete and recreate migration service

Schema Migration Failures

1. Check dependency order in stored procedures
2. Validate custom data types compatibility
3. Remove deprecated features before migration
4. Use SqlPackage.exe for complex schemas
5. Manual schema recreation as last resort

Performance Issues

1. Increase timeout values from 30s default
2. Shrink transaction logs on source
3. Reduce concurrent connection limits
4. Switch to offline migration
5. Break large databases into smaller chunks

Alternative Approaches

When DMS Fails Completely

• SqlPackage.exe: Slower but more reliable for <50GB databases
• Backup/Restore: Manual process, works for Managed Instance
• SSIS packages: Custom ETL for complex transformations
• Third-party tools: Redgate, Quest alternatives

Emergency Recovery

• Export to .bacpac file as fallback
• Keep source database until migration validated
• Plan rollback procedures before starting
• Document all custom configurations for rebuild

Automation Scripts

PowerShell Prerequisites

# Install required modules
Install-Module Az.DataMigration -Force
# Version compatibility check
Get-Module Az.DataMigration -ListAvailable

Bulk Migration Pattern

# Basic migration workflow (actual working syntax)
$migration = New-AzDataMigrationToSqlManagedInstance -Parameters $params
Start-AzDataMigrationToSqlManagedInstance -ResourceGroupName $rg -ManagedInstanceName $mi
# Error handling required - cmdlets fail silently

Success Metrics

Migration Success Indicators

• Schema migration completes without dependency errors
• Data validation shows 100% row count match
• Application connection tests pass
• SQL Agent jobs execute successfully
• Performance baseline matches source system

Failure Recovery Time

• Network issues: 2-4 hours average resolution
• Authentication problems: 1-2 hours with proper documentation
• Schema conflicts: 4-8 hours depending on complexity
• Complete migration restart: 6-12 hours for medium databases

Cost Optimization

Reducing Migration Costs

• Use offline migration to minimize compute time
• Right-size target SKU to 50% of recommendations
• Batch multiple small databases
• Use reserved instances for target resources
• Monitor data transfer costs during migration

Hidden Cost Avoidance

• Plan for 150% of calculator estimates
• Factor in failed attempt costs
• Account for extended troubleshooting time
• Budget for additional Azure services needed
• Include post-migration optimization costs