Every migration to Azure SQL starts the same way: someone in a planning meeting says “let’s just lift and shift it, how hard can it be,” and someone else who’s actually done a few of these quietly starts making a list of everything that phrase glosses over. Both people are right, in a way. Migrating to Azure SQL genuinely doesn’t have to be hard – but “not hard” and “not planned” are two very different things, and the gap between them is exactly where 2 a.m. incident calls come from.
This guide walks through the real decision points: which Azure SQL target actually fits your workload, which migration method fits your downtime tolerance, and what the step-by-step process looks like once you’ve made those calls. No hand-waving, no “it depends” without an explanation of what it depends on.
Step 1: Pick the Right Target – This Decision Drives Everything Else
Before you touch a migration tool, you need to know what you’re migrating to, because “Azure SQL” isn’t one thing.
Azure SQL Database is a fully managed, single-database (or elastic pool) PaaS offering. You don’t manage the OS, you don’t manage SQL Server instance-level features, and things like cross-database queries or SQL Agent jobs either don’t exist or exist in a limited form. It’s the right target when your application is (or can be) database-per-tenant or reasonably self-contained, and you want Microsoft handling patching, backups, and HA.
Azure SQL Managed Instance (MI) gives you near-complete SQL Server surface area – SQL Agent, cross-database queries, linked servers, CLR, Service Broker – while still being PaaS underneath. It’s the target for “we have 15 years of SQL Server baggage and we’re not rewriting it, but we also don’t want to manage VMs.” Most enterprise migrations with legacy stored procedure logic and SQL Agent jobs land here.
SQL Server on Azure VMs (IaaS) is exactly what it sounds like – your own SQL Server install, on a VM, in Azure. You keep full control (and full responsibility) – every feature works exactly like on-prem, but you’re back to patching and managing the OS. This is the fallback for anything that genuinely can’t run on PaaS: unsupported legacy features, third-party software with strict on-instance requirements, or specific OS-level dependencies.
A quick way to decide: if you can honestly answer “does our app use SQL Agent, linked servers, or cross-database queries,” and the answer is yes, you’re almost certainly looking at MI, not SQL DB, unless you’re willing to re-architect. If the answer is no and the app is reasonably contained, SQL DB is usually the cheaper, lower-maintenance choice. VMs are for “everything else,” and it’s a smaller bucket than most people initially assume – a lot of workloads that feel like they need a VM actually just need MI.
Step 2: Assess Before You Migrate – Don’t Skip This
This is the step that gets rushed under deadline pressure, and it’s the step that causes the most post-migration surprises. Before picking a migration method, run an actual assessment against the current database:
- Data Migration Assistant (DMA) – scans for compatibility issues, deprecated features, and breaking changes between your source version and the target. It’ll flag things like unsupported linked server types for SQL DB, or CLR assemblies that need review.
- Azure Migrate – for a broader inventory when you’re migrating a whole estate (multiple servers, mixed workloads), not just one database.
- Database size and growth rate – this determines whether an offline migration window is even realistic. A 50 GB database can be backed up and restored in minutes. A 5 TB database cannot, and that single fact eliminates several of the methods below before you’ve even considered downtime tolerance.
- Peak transaction volume – tells you whether a minimal-downtime method (which needs to catch up on changes during cutover) can realistically keep pace, or whether the change backlog will keep growing faster than it drains.
The output of this step should be two numbers you can put in a planning document: acceptable downtime window (from the business) and realistic migration duration for the method you’re considering (from the assessment). If the second number is bigger than the first, you don’t have a downtime problem to negotiate – you have a method problem to solve, and no amount of “let’s just do it over a weekend” changes that math.
Step 3: Choose Your Migration Method
This is where most of the actual decision-making happens, and it splits cleanly along one axis: how much downtime can the business tolerate.
Method 1: Backup and Restore (Offline)
The simplest possible approach – take a native SQL Server backup, restore it to the target. For Azure SQL DB, you’d typically export to a BACPAC and import it; for Managed Instance, native RESTORE FROM URL (backup file staged in Azure Blob Storage) works almost exactly like an on-prem restore.
-- Managed Instance native restore example
RESTORE DATABASE [ProductionDB]
FROM URL = 'https://mystorageaccount.blob.core.windows.net/backups/ProductionDB.bak'
WITH MOVE 'ProductionDB' TO 'ProductionDB.mdf',
MOVE 'ProductionDB_log' TO 'ProductionDB.ldf';
Use this when: the database is small enough that backup + upload + restore fits inside your downtime window, and you can afford to take the application offline for that window. It’s the least operationally complex method by a wide margin – no ongoing replication to monitor, no sync process that can silently fall behind. For a 20 GB reporting database with a weekend maintenance window, this is often the right call precisely because it’s boring.
Don’t use this when: your downtime window is measured in minutes, not hours, or your database is large enough that the backup/restore itself blows past the window before you’ve even started testing the cutover.
Method 2: Azure Database Migration Service (DMS) – Offline Mode
DMS offline mode automates essentially the same backup-and-restore flow but adds schema conversion, some validation, and a managed migration project you can track, rather than a manual set of scripts. It’s the same downtime profile as Method 1, but with less manual scripting and better visibility into what’s happening.
Use this when: you want Method 1’s simplicity but you’re migrating more than a handful of databases and want a repeatable, trackable process rather than a folder of ad-hoc scripts per database.
Method 3: Azure Database Migration Service – Online Mode (Continuous Sync)
This is the one people mean when they say “minimal downtime migration.” DMS online mode does an initial full data load, then continuously replicates ongoing changes (via transaction log reading, similar in concept to log shipping or CDC) from source to target while the source stays live and serving production traffic. When you’re ready to cut over, you pause writes on the source briefly, let the last bit of replication drain, and flip the connection string.
Use this when: downtime tolerance is measured in minutes, the database is too large for an offline window, or the application simply can’t be down during business hours and there’s no acceptable maintenance window at all (24/7 global operations, for instance).
The catch, and it’s a real one: you have to actively monitor replication lag throughout the sync period. If your source database’s write volume is high enough, or the network path to Azure is constrained enough, the replication can fail to keep pace – the gap grows instead of shrinking, and your “minimal downtime” cutover window quietly becomes “we can’t cut over at all until we fix the throughput problem.” This is exactly why Step 2’s peak transaction volume assessment matters – it’s the number that tells you, before you start, whether online mode is realistic for your workload or whether you’re setting yourself up to watch a lag counter climb for three days.
Method 4: Log Shipping / Always On Availability Groups (to VMs or MI)
For SQL Server-to-SQL Server style migrations – particularly to Managed Instance or IaaS VMs – you can use the same HA/DR mechanisms you’d use for a disaster recovery setup, and simply treat the migration as “fail over to the new location and never fail back.” Set up an Availability Group with the Azure target as a replica, let it synchronize, then perform a planned failover during a short window.
Use this when: you’re already running Always On in your source environment (so the tooling and team familiarity already exist), or you’re migrating to MI/VMs specifically and want an approach that mirrors your existing DR runbook rather than introducing a new tool.
Method 5: Transactional Replication
Similar in spirit to DMS online mode but using SQL Server’s native replication engine, publishing changes from the source to a subscriber in Azure. It predates DMS and is more hands-on to configure and maintain, but it remains useful for specific scenarios – like migrating only a subset of tables, or needing bidirectional sync during an extended transition period where both source and target need to stay writable.
Use this when: you need partial migration (not the whole database) or a genuinely bidirectional sync during a long coexistence period – scenarios DMS online mode doesn’t handle as naturally.
Step 4: A Quick Decision Table
| Your Situation | Recommended Method |
|---|---|
| Small DB, weekend maintenance window available | Backup & Restore |
| Multiple DBs, want a repeatable/trackable process, offline OK | DMS Offline |
| Large DB, near-zero downtime required | DMS Online |
| Already running Always On, migrating to MI/VMs | AG-based cutover |
| Need partial table migration or bidirectional sync | Transactional Replication |
Step 5: Execute – The Actual Runbook
Regardless of which method you picked, the execution steps rhyme:
- Pre-migration validation – run DMA one more time against the current state, not the state from three weeks ago when you first assessed it. Schemas drift.
- Provision the target – right-size it based on real DTU/vCore usage from the source (don’t guess; pull actual utilization metrics), not based on the source server’s total capacity, which is almost always over-provisioned.
- Initial data load – via your chosen method.
- Validation pass – row counts, checksums on key tables, and critically, run your application’s actual test suite against the migrated target, not just a manual spot-check of a few tables.
- Cutover -for offline methods, this is the restore completing. For online methods, this is the freeze-drain-redirect sequence. Have the rollback plan written down before this step, not improvised during it.
- Post-cutover monitoring – watch Query Store and DTU/vCore utilization closely for the first 48–72 hours. Plans that looked fine in testing can behave differently under real production concurrency, and this is genuinely the first time you’ll see that.
- Decommission the source – only after a defined bake-in period, not immediately. Keep it available (even if just powered off, not deleted) until you’re confident there’s no need to roll back.
The Part Nobody Puts in the Slide Deck
Migration tooling has gotten very good, and it’s tempting to let that lull you into treating this as a mechanical, low-risk exercise. The tools mostly aren’t where migrations go wrong. Migrations go wrong in the assumptions nobody checked out loud: nobody realized the application had a hardcoded connection string in three different config files, nobody tested the SSIS package that only runs on the last business day of the month, nobody noticed the client’s reporting tool uses a linked server that doesn’t exist on the new MI instance. None of that shows up in a compatibility scan. It shows up in Step 2, if you actually do the inventory work, and it shows up in production at 2 a.m. if you don’t.
The methods above are all mature, well-documented, and genuinely reliable when matched correctly to the workload. The step-by-step part is easy. The part worth taking seriously is Steps 1 and 2 – picking the right target and doing the honest assessment – because that’s where a migration is actually won or lost, long before anyone runs a single migration command.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
