The Engineer’s Guide to SQL Migration: Stopping the Analysis Paralysis

The hardest part of moving SQL Server to Azure isn’t the technical migration; it’s the decision on where to land.

A glance at the Microsoft documentation reveals a confusing alphabet soup of options: SQL on Azure VM (IaaS), Azure SQL Managed Instance (PaaS), and Azure SQL Database (PaaS), not to mention elastic pools and hyperscale tiers.

For the on-prem sysadmin or DBA used to having full control over the OS and the instance, this shift can be paralyzing. Choose wrong, and you end up either overpaying for infrastructure you don’t manage or refactoring code for features that don’t exist.

This guide is designed to cut through the marketing fluff and provide a practical decision framework and migration methodology for moving on-prem SQL to Azure.

Phase 1: The Decision Framework (IaaS vs. PaaS)

Before you touch a migration tool, you must choose your target. The decision always comes down to a trade-off between Control and Convenience.

Figure 1: A simplified flowchart for choosing your Azure SQL target based on technical requirements.

1. SQL Server on Azure VM (IaaS)

This is “lift and shift.” It is just a VM running Windows or Linux in Azure with SQL installed.

• The Vibe: It feels exactly like on-prem. You RDP in. You patch the OS. You manage High Availability (Always On Availability Groups).
• When to choose it:
  • You need OS-level access (e.g., installing third-party backup agents or custom applications on the same server).
  • You are on an unsupported, ancient version of SQL Server (e.g., 2008 R2) and just need to get out of your datacenter quickly.
  • You have massive, complex setups that exceed PaaS limits.

2. Azure SQL Managed Instance (MI) (PaaS “Lite”)

This is the sweet spot for most enterprise migrations. It is a PaaS service, meaning Microsoft handles patching, high availability, and backups, but it is architected to look and feel nearly identical to an on-prem instance.

• The Vibe: You connect via SSMS, and it looks like a normal SQL instance. You have SQL Agent jobs. You can run cross-database queries. You use CLR.
• When to choose it:
  • You want to stop patching servers but cannot afford to refactor your application code.
  • Your application relies on instance-level features like Service Broker, SQL Agent, or cross-database queries.

3. Azure SQL Database (Pure PaaS)

This is a modern, single database scoped service. It is designed for cloud-native applications.

• The Vibe: It is highly abstracted. There is no “instance.” Many traditional SQL features (like cross-database queries or SQL Agent) are missing or require different approaches (like Elastic Jobs).
• When to choose it:
  • You are building a new, modern application.
  • Your existing database is simple, self-contained, and doesn’t rely on instance-level dependencies.

The Cost Warning: Moving to PaaS (MI or SQL DB) changes your cost model significantly. Instead of buying cores once, you rent them forever. Before finalizing your target, ensure you understand the financial implications. Internal Link: Read our guide on The “Lift and Shift” Cost Trap: A Sysadmin’s Guide to FinOps to avoid a surprise bill.

Phase 2: The Pre-Migration Assessment

You cannot migrate what you don’t understand. If you try to move a complex on-prem database directly to Azure SQL DB, it will likely fail because of incompatible features.

The tool for this is the Data Migration Assistant (DMA).

Run DMA against your on-prem source and select your desired Azure target. It will generate a report detailing:

1. Migration Blockers: Features you are using that simply do not exist in the target.
2. Behavior Changes: Things that will work but might act differently in Azure.

Do not skip this step. The output of DMA determines if your Phase 1 decision was correct.

Phase 3: The Migration Methodology

Once you have chosen your target and remediated any DMA findings, it’s time to move the data. While there are many ways to skin this cat, these are the two most common for enterprise workloads.

Method A: The “Old School” Backup & Restore (Offline)

If you can tolerate downtime (hours or a weekend), this is the simplest method.

1. Create a storage account in Azure Blob Storage.
2. Take a full backup of your on-prem database with the target set to URL (pointing to the Azure storage blob).
3. Connect to your target Azure SQL instance (VM or MI) and run a RESTORE DATABASE FROM URL command.

Note: This method does not work for Azure SQL Database (single DB), which requires deploying a BACPAC file.

Method B: Database Migration Service (DMS) (Online/Minimal Downtime)

For mission-critical apps where you only have minutes of cutover window, you need DMS.

Figure 2: The architecture for a minimal-downtime migration using Azure DMS and a Self-Hosted Integration Runtime.

DMS works by taking an initial snapshot of your data and then continuously replicating ongoing changes (transaction logs) from on-prem to Azure until you are ready to cut over.

Key Components:

• Self-Hosted Integration Runtime (SHIR): A small piece of software you install on an on-prem server that can see your source SQL server. It acts as the bridge to Azure.
• Azure VNet: Your target Managed Instance must sit in an Azure VNet that has connectivity (VPN or ExpressRoute) back to your on-prem network so the SHIR can talk to it.

Internal Link: Setting up the networking plumbing for Managed Instance can be tricky. Ensure your foundation is solid by reviewing our Azure Landing Zone for Beginners guide.

Conclusion

Migrating to Azure SQL is less about the act of moving data and more about choosing the right destination for your operational maturity level. If you want zero change, choose a VM. If you want modernized operations with minimal code change, choose Managed Instance. If you are building for the future, choose Azure SQL Database. Choose wisely, assess thoroughly with DMA, and the migration itself will be the easy part.

Additional Resources: