# Migrating SQL Server on VM to Azure SQL Database: My Hands-On Experience with Azure DMS.

- Link: https://laughingcloud.io/migrating-sql-server-on-vm-to-azure-sql-database-my-hands-on-experience-with-azure-dms
- Published: 2026-04-12T23:18:45+05:30
- Author: Visitha

## 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 → target3. DMS transfers
all table data → target 4. Validate row counts + integrity5. 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?

 1. Create Azure Database Migration Service.

![](https://laughingcloud.io/wp-content/uploads/2026/04/1-1024x437.png)

2. Select Migration scenario. 

![](https://laughingcloud.io/wp-content/uploads/2026/04/2-1024x327.png)

3. Select target

![](https://laughingcloud.io/wp-content/uploads/2026/04/3-1024x460.png)

![](https://laughingcloud.io/wp-content/uploads/2026/04/4-1024x755.png)

![](https://laughingcloud.io/wp-content/uploads/2026/04/5-1024x697.png)

4. Start Migration.

![](https://laughingcloud.io/wp-content/uploads/2026/04/7.1-1024x411.png)

5. Select source & target environments.

![](https://laughingcloud.io/wp-content/uploads/2026/04/8-987x1024.png)

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

![](https://laughingcloud.io/wp-content/uploads/2026/04/9-795x1024.png)

8. Install & configure Agent on Server.

![](https://laughingcloud.io/wp-content/uploads/2026/04/10.png)

![](https://laughingcloud.io/wp-content/uploads/2026/04/11.png)

9. Insert the key to register.

![](https://laughingcloud.io/wp-content/uploads/2026/04/12-1024x702.png)

![](https://laughingcloud.io/wp-content/uploads/2026/04/14-1024x704.png)

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.

![](https://laughingcloud.io/wp-content/uploads/2026/04/16-1024x850.png)

10. Connect the source server by providing credentials.

![](https://laughingcloud.io/wp-content/uploads/2026/04/17-1024x832.png)

11. Select on-prem database for migration.

![](https://laughingcloud.io/wp-content/uploads/2026/04/18-1024x771.png)

12. Connect to the target database.

![](https://laughingcloud.io/wp-content/uploads/2026/04/19-1024x826.png)

13. Map the databases.

![](https://laughingcloud.io/wp-content/uploads/2026/04/20-1024x503.png)

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.

![](https://laughingcloud.io/wp-content/uploads/2026/04/21-1024x691.png)

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)

![](https://laughingcloud.io/wp-content/uploads/2026/04/22-1009x1024.png)

16. Monitor migration status.

![](https://laughingcloud.io/wp-content/uploads/2026/04/23-1024x349.png)

17. Migration status can be seen in detail. Once completed, you can connect the 
Azure SQL database connection string to the application. 

![](https://laughingcloud.io/wp-content/uploads/2026/04/24-1024x552.png)

## 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`