How to migrate from SQL Server 2016 to Azure SQL DB

Question

I plan to migrate 1.2 TB Database from SQL 2016 (on Proem) to Azure SQL DB. What is the best way to migrate from on-prem to Azure DB? Can I take a full backup and restore it on Azure SQL DB?

Answer 1

Azure SQL Database does not support direct restore of native SQL Server backups.

You can try the folowing

1. Azure Database Migration Service (DMS) – Recommended for minimal downtime
   • Supports online (continuous sync) or offline (one-time) migration.
   • Ensures data consistency and minimal impact on the source system.
   • Steps:
     1. Deploy Azure DMS from the Azure Portal.
     2. Select online or offline migration mode.
     3. Configure the source (on-prem SQL 2016) and destination (Azure SQL Database).
     4. Start the migration and monitor progress.
2. Data Migration Assistant (DMA) – Suitable for schema and data migration
   • Assess and migrate schema first, then migrate data.
   • Works well for smaller databases or when downtime is acceptable.
   • Steps:
     1. Install and run DMA to check for compatibility issues.
     2. Migrate the schema first.
     3. Use DMS or bulk data movement techniques to migrate data.
3. BACPAC File – Simple but slow for large databases
   • Exports the database into a .bacpac file, which can be imported into Azure SQL Database.
   • Not recommended for very large databases due to performance constraints.
   • Steps:
     1. Export .bacpac from SQL Server Management Studio (SSMS).
     2. Upload .bacpac to Azure Blob Storage.
     3. Import into Azure SQL Database.
4. Transactional Replication – Near-Zero Downtime Option
   • Keeps the Azure SQL Database in sync with the on-prem server until cutover.
   • Requires SQL Server Enterprise or Standard edition on-prem.
   • Steps:
     1. Configure transactional replication from SQL 2016 to Azure SQL Database.
     2. Sync data until ready to switch over.
     3. Redirect applications to Azure SQL Database.

For minimal downtime: Use DMS (Online Mode) or Transactional Replication.
For a one-time migration with some downtime: Use DMS (Offline Mode) + DMA.
For a simple but slower migration: Use BACPAC (although this is not particularly suitable for 1.2 TB).

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin