Scenario.
After spending the weekend wrestling with a real-world SQL Server migration, I finally got my on-premises database from a VM to Azure SQL Database using Azure Database Migration Service (DMS). This wasn’t textbook smooth — I hit connectivity issues, collation mismatches, etc., that took hours to debug. Here’s my complete battle-tested guide, including the screenshots I captured along the way.
Why I Chose Azure DMS Over Backup & Restore?
I’ve done plenty of SQL migrations with backup/restore over the years — it’s the classic sysadmin approach. But for this VM → Azure SQL Database migration, I deliberately picked Azure DMS offline mode instead. Here’s my real-world reasoning:
✅ Native SQL Server → Azure SQL schema conversion
✅ Automatic table mapping + data type handling
✅ Built-in compatibility assessment upfront
✅ Progress dashboard with row counts per table
✅ FK relationships preserved automatically
✅ One-click retry if validation fails
✅ Production-ready validation reports
Understanding Azure DMS Migration Modes: Offline vs Online
Before jumping into the actual migration steps, it’s important to understand the two migration modes Azure DMS offers. Choosing the right one upfront saves a lot of pain later — I learned this firsthand.
Offline Migration
With offline migration, application downtime starts the moment migration begins. Think of it as a full freeze — you stop your app, DMS copies the schema and data, and once the transfer completes, you point your app to the new Azure SQL target.
How it works:
1. Stop application writes to source
2. DMS copies full schema → target
3. DMS transfers all table data → target
4. Validate row counts + integrity
5. Switch app connection string → done
Online Migration
With online migration, your source database stays fully operational during the entire migration. Downtime is limited only to the final cutover moment — typically a few seconds to a few minutes.
How it works under the hood:
Phase 1 — Initial Load: DMS copies full schema + existing data to target (same as offline, source still running).
Phase 2 — Change Data Capture (CDC): DMS reads source transaction log continuously. Every INSERT, UPDATE, DELETE replicated to target
Source and target stay in near real-time sync. Continues migration until cut-over.
Phase 3 — Cut-over: You stop app writes.
Replicate final transactions.
Switch connection string → seconds of downtime.
How to perform migration?
- Create Azure Database Migration Service.
2. Select Migration scenario.
3. Select target
4. Start Migration.
5. Select source & target environments.
6. Deploy & Connect SHIR.
Azure Database Migration Service (DMS) requires a Self-Hosted Integration Runtime (SHIR) VM because DMS runs as a public Azure service that cannot directly access private Azure VM resources. The SHIR acts as a secure proxy—DMS communicates with SHIR over Azure’s private backbone, then SHIR reaches your SQL VM. The SHIR VM can be deployed on Azure or on-prem, but connectivity should be allowed with the source & on-prem database.
7. Configure integration runtime.
Download the runtime agent & register the agent with DMS using one of the key given
8. Install & configure Agent on Server.
9. Insert the key to register.
9. Provide source infrastructure details.
The free SQL Server instance is automatically deployed by Azure DMS as a migration tracking and metadata database when you select “Virtual Machine” as the source infrastructure type. Provide name for Free SQL server instace here.
10. Connect the source server by providing credentials.
11. Select on-prem database for migration.
12. Connect to the target database.
13. Map the databases.
14. Select the tables to migrate.
In this window, you can select the tables to be migrated to the target database. Empty tables can’t be migrated.
15. Start the migration.
When you are doing a production database migration, stop the application to stop data writes to the source database. The migration time will depends on,
Data volume (row count, LOB data)
Network throughput (same subnet vs cross-region)
Source VM specs (CPU, IOPS, memory)
Schema complexity (indexes, FKs, triggers)
Target Azure SQL tier (GP vs Hyperscale)
Migration mode (offline vs online)
Concurrent workload (source/target load)
Validation overhead (row counts, integrity checks)
16. Monitor migration status.
17. Migration status can be seen in detail. Once completed, you can connect the Azure SQL database connection string to the application.
Conclusion
Azure DMS made this SQL Server to Azure SQL migration surprisingly straightforward with automated schema handling and validation. Once networking and collation were sorted, it was point-and-click simple. Perfect tool for both test runs and production migrations.
#laughingcloud.io #AzureDMS #SQLMigration #AzureSQL #DatabaseMigration #CloudMigration #AzureDatabase #SQLServer