Home / Guides
Migration guide

SQL Server to Azure SQL Database, Step by Step

By · October 26, 2026 · 11 min read

The short version

A SQL Server to Azure SQL Database migration is two jobs: first remove what Azure SQL Database cannot run (cross-database and linked-server queries, SQL Server Agent jobs, CLR assemblies, FILESTREAM, Service Broker, Database Mail), then move the data. For the move, Microsoft documents three routes: a BACPAC exported and imported with SqlPackage for small databases that can take downtime, Azure Database Migration Service for an offline copy with no file in between, and transactional replication when the database cannot go offline. If the blocker list is long, the better target is Azure SQL Managed Instance.

Is Azure SQL the same as SQL Server?

Same engine, different box. Azure SQL Database "always runs on the latest stable version of the SQL Database Engine", and Microsoft handles patching, backups and upgrades (What is Azure SQL Database). Data types, operators and functions behave the same (T-SQL differences). What you lose is everything scoped to the instance: the database is isolated from the system databases and the operating system, so "most instance-level features aren't supported" (same page). A single database is closest to a contained database: one database, its own users, no msdb, no USE.

That is the whole difficulty. The data moves fine; the code around it is what breaks.

What Azure SQL Database does not support

From Microsoft's feature comparison and T-SQL differences pages, the ones that stop real migrations:

Feature Azure SQL Database What Microsoft points to instead
Cross-database / three-part name queries No Elastic query (read-only, preview)
Four-part names, OPENQUERY, linked servers No Elastic query
Cross-database and distributed (MS DTC) transactions No Elastic transactions
SQL Server Agent No Elastic jobs
CLR integration No -
FILESTREAM / FileTable No -
Service Broker No -
Database Mail No -
BACKUP command No, only automatic backups Point-in-time restore
Windows authentication No Microsoft Entra authentication
USE statement No A new connection per database
Trace flags, sp_configure No ALTER DATABASE SCOPED CONFIGURATION

Two details people miss. Three-part names that point at tempdb or at the current database are allowed (T-SQL differences), so MyDb.dbo.Orders inside MyDb is fine. And elastic query is read-only: it replaces a cross-database SELECT, never a cross-database INSERT.

Find the blockers in your own database

Run these on the source, in the database you are moving. A word on what I did and did not run: I meant to show their output from a SQL Server 2022 container, but the Docker daemon on my machine hung while starting it and never came back. So these are shown as written, checked column by column against the Microsoft Learn reference pages, without output. The next section is what I did run.

Cross-database and cross-server references. referenced_database_name is filled in only for three- and four-part names, referenced_server_name only for four-part names (sys.sql_expression_dependencies):

SELECT OBJECT_SCHEMA_NAME(referencing_id) AS obj_schema,
       OBJECT_NAME(referencing_id)        AS obj_name,
       referenced_server_name,
       referenced_database_name,
       referenced_schema_name,
       referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
  AND referenced_database_name NOT IN (DB_NAME(), 'tempdb');

Agent job steps that run against this database. dbo.sysjobsteps lives in msdb, and database_name is set for T-SQL steps (dbo.sysjobsteps):

SELECT j.name AS job_name, s.step_name, s.subsystem, s.command
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE s.database_name = DB_NAME()
   OR s.command LIKE '%' + DB_NAME() + '%';

User CLR assemblies (is_user_defined = 1 leaves out system ones such as Microsoft.SqlServer.Types, per sys.assemblies) and FILESTREAM files (type = 2, per sys.database_files):

SELECT name, permission_set_desc FROM sys.assemblies WHERE is_user_defined = 1;
SELECT name, type_desc, physical_name FROM sys.database_files WHERE type = 2;

The dependency view has gaps: it tracks names, so OPENQUERY text and dynamic SQL built in strings will not show up. Search module text for those:

SELECT OBJECT_SCHEMA_NAME(object_id) AS obj_schema, OBJECT_NAME(object_id) AS obj_name
FROM sys.sql_modules
WHERE definition LIKE '%OPENQUERY%' OR definition LIKE '%OPENDATASOURCE%'
   OR definition LIKE '%msdb.%'     OR definition LIKE '%sp_send_dbmail%';

The scan I ran on a scripted-out schema

Often you do not get a login to production before the project starts. You get a .sql file from SSMS "Generate Scripts". So I wrote a small offline check for that case: split the script on GO, parse each batch with sqlglot's T-SQL dialect, flag every table reference that carries a database part, and add regex checks for the things a parser does not model. The test schema is a typical legacy sales database: a view joining a Reference database, a nightly procedure writing to Warehouse, reading a linked server and sending Database Mail, an OPENQUERY for exchange rates, a FILESTREAM column and a CLR assembly.

for i, batch in enumerate(batches, 1):
    hits = [name for name, rx in PATTERNS.items() if re.search(rx, batch, re.I | re.M)]
    try:
        tree = sqlglot.parse_one(batch, read="tsql")
        for t in tree.find_all(exp.Table):
            if t.catalog and t.catalog.lower() != "tempdb":
                kind = "four-part name" if len(t.parts) == 4 else "three-part name"
                hits.append(f"{kind}: {'.'.join(p.name for p in t.parts)}")
    except Exception as e:
        hits.append(f"(parser: {type(e).__name__})")

Output, sqlglot 30.17.0 on Python 3.12.8, unedited:

'CREATE ASSEMBLY RegexUtil FROM 'C:\clr\RegexUtil.dll' WITH PERMISSION_SET = SAFE' contains unsupported syntax. Falling back to parsing as a 'Command'.
batch 1: CREATE TABLE dbo.Orders ( OrderID int IDENTITY PRI
batch 2: CREATE TABLE dbo.Invoices ( InvoiceID uniqueidenti
    BLOCKER  FILESTREAM column
    BLOCKER  (parser: ParseError)
batch 3: CREATE VIEW dbo.vOrdersWithRegion AS SELECT o.Orde
    BLOCKER  three-part name: Reference.dbo.Regions
batch 4: CREATE PROCEDURE dbo.usp_NightlyRollup AS BEGIN IN
    BLOCKER  msdb (Agent / Database Mail)
    BLOCKER  three-part name: msdb.dbo.sp_send_dbmail
    BLOCKER  three-part name: Warehouse.dbo.DailySales
    BLOCKER  four-part name: LEGACYBOX.Archive.dbo.Orders2015
batch 5: CREATE PROCEDURE dbo.usp_FxRates AS SELECT * FROM 
    BLOCKER  OPENQUERY / OPENDATASOURCE
batch 6: CREATE ASSEMBLY RegexUtil FROM 'C:\clr\RegexUtil.d
    BLOCKER  CLR assembly

Every planted blocker was found. Two things worth knowing from the run. sqlglot could not parse the FILESTREAM column at all (ParseError), so the regex caught it, not the parser; a scanner built on parsing alone would have reported that table as clean. And the EXEC msdb.dbo.sp_send_dbmail call came back as a three-part name, which is right for the purpose: anything pointing into msdb is Agent or Database Mail and has no home in Azure SQL Database. This is a first pass on a script, not a replacement for the catalog queries above or for Microsoft's own assessment in SQL Server enabled by Azure Arc or Azure Migrate.

SQL Server to Azure SQL Database migration: which route

Route Use it when Downtime Watch out for
BACPAC with SqlPackage One database, small enough, a maintenance window exists Yes, from export start to import end Export is consistent only with no writes, or from a copy
Azure Database Migration Service You want Azure to copy tables, optionally the schema too Yes, starts when the migration starts No online mode for this target
Transactional replication The database cannot go offline Only the connection-string switch Push subscription only, every table needs a primary key

Sources for each row: the migration overview compares the methods; BACPAC needs downtime because data is exported at the source and imported at the destination; DMS says plainly that "online migrations for Azure SQL Database targets aren't available" and that offline downtime "starts when the migration starts" (DMS tutorial); replication needs a primary key on every replicated table and only push subscriptions (Replication to Azure SQL Database).

Step by step: the BACPAC route

  1. Create the target logical server and allow your client through its firewall. From behind a corporate firewall, port 1433 must be open outbound (import guide).
  2. Stop writes, or export from a copy. SqlPackage says an export is transactionally consistent only if "no write activity is occurring during the export" (SqlPackage Export).
  3. Export:
SqlPackage /Action:Export /TargetFile:"Sales.bacpac" \
    /SourceConnectionString:"Server=onprem-sql;Initial Catalog=Sales;Integrated Security=True;TrustServerCertificate=True"

Export runs schema validation rules on the package by default (VerifyExtraction=True). Leave it on: a problem reported here costs less than one found after the import.

  1. Import into Azure:
SqlPackage /Action:Import /SourceFile:"Sales.bacpac" \
    /TargetConnectionString:"Server=tcp:<server>.database.windows.net,1433;Initial Catalog=Sales;User ID=<user>;Password=<password>;Encrypt=True"

Use SqlPackage rather than the portal past 150 GB: Microsoft documents a known File contains corrupted data failure for larger files imported through the portal or PowerShell (import guide). SqlPackage export itself "performs best for databases under 200GB" (SqlPackage Export). The portal also only imports from Blob storage, and storage behind a firewall is not supported.

  1. Update statistics with full scan and run your validation queries on both sides (migration guide). The data validation guide has the queries I use.

I did not run SqlPackage for this guide; with no SQL Server container there was nothing to export from. The commands follow the syntax on the SqlPackage reference pages.

The DMS and replication routes

Azure Database Migration Service in the Azure portal needs a self-hosted integration runtime on a machine that can reach the source, a target database that already exists, and db_datareader plus VIEW ANY DEFINITION on the source (DMS tutorial). It can create missing schema objects first. Its listed limits matter for legacy databases: computed columns are not migrated, tables with large blob columns can time out, and columns with defaults arrive with the default instead of NULL. Empty tables are skipped. For many databases, the same service runs from PowerShell and the Azure CLI (migration overview).

Transactional replication is the near-zero-downtime path: Azure SQL Database becomes a push subscriber, changes keep flowing, you repoint the application, then remove the publication (migration guide). The publisher must be SQL Server 2016 or later, and setup happens on SQL Server, not in the Azure portal (Replication to Azure SQL Database). Snapshot creation locks the tables while it runs, so schedule it.

Agent jobs move separately whatever route you pick: rebuild them as elastic jobs, which run T-SQL on a schedule against one or many Azure SQL databases.

When Managed Instance is the better answer

Microsoft's own guide ends on this: Azure SQL Managed Instance takes an existing SQL Server instance "with minimal to no compatibility issues" (migration guide). The feature comparison shows Managed Instance has SQL Server Agent, CLR, Service Broker, Database Mail and cross-database queries within the instance; FILESTREAM is missing from both. If the scan above lights up on Agent, CLR and three-part names across five databases, rewriting all of that for Azure SQL Database is a project of its own. The comparison is in Azure SQL Database vs Managed Instance vs SQL Server on a VM, and the instance route in SQL Server to Azure SQL Managed Instance. For the DMS modes, see Azure DMS: Online vs Offline Migration.

Sources