Applies toSQL Server 2008+ to Azure SQL Database (General Purpose, Business Critical, Hyperscale, serverless)
1. Fix blockers first
Azure SQL Database is one database, not an instance:
| Feature on-premises | Azure SQL Database alternative |
|---|---|
Cross-database queries (otherdb.dbo.table) | Merge databases, elastic query, or app changes |
| SQL Agent jobs | Elastic jobs, Azure Automation, Logic Apps, ADF |
| Linked servers | Elastic query, or move logic to ADF/app |
| CLR, FILESTREAM, Service Broker, DB Mail | Not supported — redesign, or pick Azure SQL MI |
| Windows logins | Microsoft Entra ID users/groups or contained SQL users |
USE otherdb in scripts | Connect to each database directly |
If many blockers remain, choose Managed Instance instead.
2. Pick the method
| Method | Downtime | Best for |
|---|---|---|
| Azure Database Migration Service (offline) | Copy time | Most databases up to a few hundred GB |
BACPAC export/import (SqlPackage) | Export + import time | Small databases, dev/test |
| Transactional replication (SQL Server publisher → Azure SQL DB subscriber) | Minutes | Large or critical databases needing minimal downtime |
3. DMS offline migration
- Create the target database (right tier) and deploy the schema first (SqlPackage / DACPAC, or DMS schema migration).
- Create a Database Migration Service instance; register a self-hosted integration runtime on a server that can reach SQL Server.
- Create a migration (source → Azure SQL Database), select tables, start.
- Monitor until complete; validate row counts.
4. Transactional replication (minimal downtime)
-- on the on-premises publisher (distributor configured)
EXEC sp_addpublication @publication = N'pub_sales', @status = N'active', @allow_push = N'true';
EXEC sp_addarticle @publication = N'pub_sales', @article = N'Orders', @source_object = N'Orders';
EXEC sp_addsubscription @publication = N'pub_sales', @subscriber = N'sqlsrv-prod.database.windows.net',
@destination_db = N'Sales', @subscription_type = N'Push';
EXEC sp_addpushsubscription_agent @publication = N'pub_sales',
@subscriber = N'sqlsrv-prod.database.windows.net', @subscriber_db = N'Sales',
@subscriber_security_mode = 0, @subscriber_login = N'repl_user', @subscriber_password = N'...';
- Every replicated table needs a primary key.
- At cutover: stop the app, wait for replication to catch up, stop the distribution agent, switch the app.
5. After the move
- Set the right service tier; consider serverless for spiky dev/test or low-use apps.
- Configure Entra ID authentication and turn off SQL auth where possible.
- Backups: PITR retention (7–35 days) and long-term retention if needed.
- HA/DR: zone redundancy and a failover group (see the Azure SQL HA/DR posts).
Tip
Merge databases that always query each other into one database with separate schemas. It removes the biggest blocker for Azure SQL Database and simplifies the app.