Azure Database Migration Service - Migrate SQL Server Databases to Azure

DMS moves SQL Server databases to Azure. It has two versions because Microsoft couldn't get it right the first time. The classic version gets killed March 15, 2026 because it randomly failed on large databases and the error messages were useless.

The current version works with Azure Data Studio's migration extension, which is actually decent tooling. The assessment feature tells you if your database will migrate cleanly or if you're fucked. Spoiler: you're usually fucked by something stupid like deprecated features, compatibility level issues, or that one stored procedure from 2012 that nobody remembers writing but everything depends on. Microsoft's migration best practices guide mentions none of the real problems you'll encounter.

How DMS Actually Works (When It Works)

DMS uses a Self-Hosted Integration Runtime (SHIR) that you install on a machine that can talk to both your source database and Azure. Version compatibility is a nightmare - I've seen migrations fail because someone installed SHIR 5.35 instead of 5.37+. Always grab the latest version or prepare for cryptic error messages.

Online migrations keep your database running while DMS copies data in the background. This works great until:

• Network timeouts kill the connection (happens more than Microsoft admits)
• Transaction log fills up because replication can't keep up
• Authentication tokens expire mid-migration
• Azure decides to "maintenance" your target during the sync

Offline migrations are simpler - database goes down, data copies over, database comes back up. Takes longer but fewer things can break randomly. The offline migration process is documented well, but real-world timelines are 2-5x longer than Microsoft's estimates.

What DMS Actually Migrates (And What It Breaks)

DMS can move schemas, data, logins, and SQL Agent jobs. The schema migration feature actually works well now - it copies tables, indexes, stored procedures, and most other objects without destroying them.

But here's what will bite you:

• Linked servers - DMS doesn't migrate these, your apps will break
• SQL Mail/Database Mail - Not supported in Azure SQL Database, only works on Managed Instance
• Full-text catalogs - Sometimes work, sometimes don't, no one knows why
• Custom .NET assemblies - Azure SQL Database doesn't support these at all
• Cross-database queries - Works on Managed Instance, breaks everywhere else

The schema migration rollout was supposed to fix most compatibility issues, but I still see stored procedures breaking because of slight T-SQL differences between on-premises and Azure SQL. Check the feature comparison matrix before you start - it'll save you hours of debugging later.

DMS migrations follow a simple pattern: assess what will break, try to migrate it, fix what actually breaks, repeat until it works. Microsoft calls this "best practices" but it's really just trial and error with better logging.

Step 1: Run Assessment and Pray

Start with Azure Migrate to scan your databases. The assessment will tell you everything looks good, then half your stored procedures will break during migration. But run it anyway because it catches the obvious stuff like:

• SQL Server 2008 compatibility (won't work, upgrade first)
• SQL Agent jobs using xp_cmdshell (Azure SQL Database will reject these)
• Cross-database queries (only work on Managed Instance)
• Deprecated features (BACKUP/RESTORE statements, file tables, etc.)

The SKU recommendations are always oversized. If it recommends 8 vCores, you probably need 4. If it recommends 4, you probably need 2. Microsoft wants your money. Use the Azure SQL sizing guide and performance recommendations to right-size your target.

Step 2: Pick Your Migration Pain Level

Offline migrations are the safe choice:

• Database goes down, data copies over, database comes back up
• Takes 2-8 hours depending on size and network speed
• Simple to troubleshoot when it breaks
• Use this for anything under 100GB or where downtime is acceptable

Online migrations keep your database running but add complexity:

• Initial full backup/restore, then continuous log shipping
• Works great until network hiccups kill the sync
• Final cutover still requires brief downtime (2-5 minutes if lucky)
• Use this for large databases where downtime costs more than the headache

Step 3: Network and Authentication Hell

90% of DMS failures are network or auth issues. Before you start:

Install SHIR correctly:

• Download latest version from Microsoft
• Install on a machine that can reach both source DB and Azure
• Test connectivity before starting migration (seriously, test everything)
• SHIR versions are picky - 5.37+ required for new features

Fix authentication early:

• Source needs `db_owner` permissions minimum - db_datareader won't cut it
• Target needs proper Azure SQL roles (the error messages won't tell you which ones)
• Service accounts need login rights, not just database access
• Password authentication is easier than Windows auth through SHIR
• Pro tip: Create dedicated migration accounts with explicit permissions - using sa or admin accounts causes weird permission failures later

Step 4: When It Breaks (Not If)

Common failure modes and fixes:

• Connection timeouts: Increase timeout values, check firewalls, blame Azure networking
• Transaction log full: Shrink logs on source, increase log backup frequency
• Authentication expired: Refresh credentials in Azure portal, restart SHIR service
• Schema migration fails: Check for dependencies, migrate objects in dependency order
• Data validation errors: Usually character encoding or data type mismatches

Nuclear options when everything fails:

• Delete the migration service and start over
• Export to .bacpac file and import (slow but reliable)
• Use SqlPackage.exe directly (bypasses DMS entirely)
• Call Microsoft support and wait 3 days for them to tell you to try #1

PowerShell Automation (For When You Hate Yourself)

PowerShell cmdlets work well for bulk migrations. Script everything because clicking through the portal 50 times will drive you insane.

## Basic migration workflow
$migration = New-AzDataMigrationToSqlManagedInstance -Parameters $params
Start-AzDataMigrationToSqlManagedInstance -ResourceGroupName $rg -ManagedInstanceName $mi
## Then wait and pray it doesn't fail

The cmdlets have decent error handling, but expect to spend a day figuring out the parameter syntax. Microsoft's documentation examples never work as written.