Choosing Your Azure SQL Migration Target for SQL Server
Title: Choosing Your Azure SQL Migration Target for SQL Server
When you migrate SQL Server to Azure, pick Azure SQL Managed Instance as the default target: it gives you the closest SQL Server surface area with a fully managed platform, so a lift-and-shift usually needs minimal changes. Choose Azure SQL Database only if you are refactoring around a single database and can drop instance-scoped features, and choose SQL Server on an Azure VM only when you need OS-level access, a pinned SQL Server version, or a feature Managed Instance still does not support. Decide this before you touch anything, because the Azure SQL migration target you pick sets your tooling, your downtime story, and years of operational cost.
This is a decision that is expensive to reverse. Moving from Managed Instance to SQL Database later means re-architecting cross-database dependencies; moving off a VM to a PaaS tier later means re-running the whole assessment. Get it right once.
Check compatibility before you change anything
The first job is not the move, it is the assessment. You need to know which features your databases actually use and which of those the target does not support. Do this before you provision a single Azure resource.
One trap to avoid: the old Data Migration Assistant is gone. Microsoft states that Data Migration Assistant (DMA) is retired, so any runbook or blog post that tells you to start with DMA is out of date. Use the Azure SQL Migration extension for Azure Data Studio for a single estate, and Azure Migrate when you need to assess many instances at once.
What the assessment must surface
Run the assessment against production, not a stale dev copy, and read it for blocking issues rather than warnings. The features that most often force a target change are the instance-scoped ones, such as FILESTREAM and FileTable and PolyBase, which Microsoft's list of T-SQL differences between SQL Server and Managed Instance lists as not supported on Managed Instance. These are architectural constraints, not bugs that will be fixed later.
Two concrete examples worth checking by hand:
- Database mirroring is unsupported on Managed Instance. The same reference confirms
CREATE ENDPOINT ... FOR DATABASE_MIRRORINGis not allowed, so if your HA or ETL relies on mirroring endpoints, that logic has to change. - CLR behaves differently across tiers. CLR is not supported in SQL Database but is supported in Managed Instance. A single CLR assembly is enough to rule out SQL Database.
The three targets and what each one costs you later
Azure gives you two PaaS options and one IaaS option. The right choice is a trade between how little you want to manage and how much of your existing SQL Server behaviour you need to keep.
| Target | Best when | Main constraint | What it costs you later |
|---|---|---|---|
| SQL Managed Instance | Lift-and-shift of an existing instance with minimal change | Some instance features unsupported (mirroring, cross-instance transactions) | You inherit PaaS behaviour differences you cannot turn off |
| SQL Database | New or refactored app scoped to one database | No CLR, no instance-level surface, most app changes | Re-architecting anything that assumed instance scope |
| SQL Server on Azure VM | You need OS access, a pinned version, or an unsupported feature | You still own patching, backups, and HA | Ongoing VM and SQL Server administration forever |
Managed Instance: the default for existing workloads
Managed Instance is built for migration. Microsoft describes it as supporting migration from on-premises with minimal to no database changes, with full SQL Server access and native virtual network support. It carries a 99.99 percent availability guarantee, and high availability is built in and not something you configure.
The cost you accept is loss of control over a few things. High availability cannot be tuned by you, auditing runs at the server level with logs in Blob storage, and some platform overhead cannot be disabled. These are fine trade-offs for most line-of-business databases, but you should know them going in rather than discovering them during a performance incident.
SQL Database: only when you are refactoring
SQL Database is the most managed option and the furthest from a plain SQL Server instance. It is scoped to a single database, and it drops instance-level surface area, including CLR. Treat it as a target for applications you are willing to change, not for a straight lift-and-shift. If your app opens cross-database queries, relies on SQL Server Agent jobs, or uses CLR, moving to SQL Database is a re-engineering project, not a migration.
SQL Server on Azure VM: the escape hatch
The VM option is a full SQL Server install on infrastructure Azure manages, and the migration overview lists clear reasons to pick it. Microsoft says to consider a SQL Server VM when you need direct OS or file-system access, have a strict dependency on features still unsupported in Managed Instance such as FileStream, FileTable, PolyBase, or cross-instance transactions, must stay on a specific SQL Server version, or need compute much smaller than a Managed Instance offers.
The honest downside is that IaaS hands the work back to you. The Azure SQL overview notes that with a VM you attain higher availability by adding a second SQL VM and running an Always On availability group yourself. Patching, backups, and HA become your team's standing responsibility. Pick the VM when you genuinely need it, not to avoid an afternoon of assessment work.
If you want a second opinion on target selection tied to your actual constraints, that is the kind of thing our cloud migration engineering practice exists to de-risk before cutover.
How the target decision changes your migration path
The target you pick decides the tool and the mechanics. For Managed Instance and VM targets, the current path is the Azure SQL Migration extension for Azure Data Studio, which runs on Azure Database Migration Service underneath.
An important behaviour to plan around: the extension does not create backups for you. Microsoft states the service uses your existing database backup files rather than taking or initiating backups. You are responsible for producing a clean backup chain. For on-premises sources, you configure a self-hosted integration runtime so the service can reach your backup files.
Do not forget the instance-level objects
A database restore is not a complete migration. The migration overview reminds you to plan for instance-level objects: logins, credentials, SQL Agent jobs and operators, and server-level triggers, plus every database that must be colocated on the same instance. If your application authenticates with SQL logins, orphaned users after the move are a classic go-live failure. Script these objects out and test them on the target before cutover, not during it.
When you produce backups for a Managed Instance restore, watch the blob size limit. The T-SQL differences reference notes the maximum backup stripe size for the BACKUP command is 195 GB, and you increase the number of stripes to stay under it. A single-file backup of a large database will fail late, so stripe from the start:
BACKUP DATABASE [SalesDB]
TO URL = 'https://acct.blob.core.windows.net/backups/SalesDB_1.bak',
URL = 'https://acct.blob.core.windows.net/backups/SalesDB_2.bak',
URL = 'https://acct.blob.core.windows.net/backups/SalesDB_3.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM, FORMAT;COPY_ONLY matters because Managed Instance takes its own automatic backups, and the differences reference notes users can create COPY_ONLY full backups so you do not break the platform-managed log chain.
Online versus offline cutover
The last trade-off is downtime against complexity. The migration extension offers both modes, and the difference is concrete. Microsoft describes online migration as continuously uploading backups and restoring them to the target until you perform the final cutover, and offline migration as a single restore that does not require a cutover step.
Offline is simpler and predictable: you take the downtime, restore, and validate. Online keeps the source live and shrinks the outage to the final cutover window, at the cost of a longer-running, more fragile process that you must monitor. Managed Instance also supports Log Replay Service as an online option when you manage the backup chain yourself.
The right answer depends on your outage budget. If the business can take a maintenance window, offline is less to go wrong. If it cannot, online is worth the extra operational care. Either way, decide who owns the post-migration availability target before go-live; if that is a gap on your team, our reliability and SRE practice covers SLOs and on-call for the new estate.
Frequently asked questions
Is Azure SQL Managed Instance or Azure SQL Database the better migration target?
Managed Instance is the better default for migrating an existing SQL Server instance, because it keeps the most SQL Server surface area and needs minimal changes. SQL Database is the better target only when you are building or refactoring around a single database and can live without instance-scoped features such as CLR and SQL Server Agent. Run an assessment first: a single blocking feature often makes the decision for you.
What replaced Data Migration Assistant for SQL Server assessments?
Data Migration Assistant is retired. For a single database estate, use the Azure SQL Migration extension for Azure Data Studio, which assesses compatibility and recommends a target SKU. For assessing many instances at scale, use Azure Migrate, which also produces target sizing and cost estimates.
Does Azure Database Migration Service take my backups for me?
No. The Azure SQL Migration extension, which runs on Azure Database Migration Service, uses backup files you already have rather than creating them. You are responsible for producing a valid backup chain, and for on-premises sources you configure a self-hosted integration runtime so the service can reach those files.
When should I choose SQL Server on an Azure VM instead of a PaaS option?
Choose a VM when you need OS or file-system access, depend on features still unsupported in Managed Instance such as FileStream, FileTable, PolyBase, or cross-instance transactions, must stay on a specific SQL Server version, or need compute far smaller than a Managed Instance offers. The trade is that you keep owning patching, backups, and high availability. Do not pick the VM just to skip the assessment work.
Can I migrate to Azure SQL with near-zero downtime?
Yes, using online migration mode, which continuously uploads and restores backups to the target until you perform the final cutover. It shrinks the outage to the cutover window but is a longer, more fragile process to monitor. If the business can tolerate a maintenance window, offline migration is simpler and has fewer moving parts.