Can I migrate an on premise SQL database to Azure SQL Database?
Solved Email & Outlook
AJ
Andrew Jackson
October 27, 2020
2 replies
9,180 views
Reviewed by moderators

Management wants our main application database out of the server room and into Azure. It is a single 80GB database on SQL Server 2014.

Is this a lift and shift or are there landmines, and what is the actual migration procedure?

Accepted Answer
Verified by Edwin J. Hoffer, Database Specialist ยท Reviewed October 2020

It migrates, but Azure SQL Database is not a hosted copy of your server, it is a single database service missing instance level machinery. The landmines live exactly there. Assessment first, then the move.

The assessment tool is free: Data Migration Assistant from Microsoft. Point it at the 2014 database in an assessment project targeting Azure SQL Database and it lists every compatibility problem by severity, cross database queries, SQL Agent jobs, linked servers, CLR, unsupported features your application may lean on without anyone remembering. Run this before promising management any date, because the findings decide the target: a clean report means Azure SQL Database proper. A report full of instance level dependencies means Azure SQL Managed Instance instead, which keeps Agent and cross database queries at a higher price. The third outcome is a fix list to work through first.

The migration itself at 80GB with a clean report: DMA performs the schema plus data migration directly, or a BACPAC export through SSMS, Tasks, Export Data-tier Application uploaded to Azure, both fine with a maintenance window since the database should be quiet during either. For minimal downtime the Azure Database Migration Service runs continuous sync until you cut over, worth it if the application cannot sleep for the hours an 80GB copy takes.

After landing: pick the service tier by measuring, start a tier up from the DTU or vCore calculator's suggestion and tune down after a real week, verify the application's connection strings handle transient retry since cloud databases fail over briefly by design, and rebuild whatever Agent jobs the assessment flagged as elastic jobs or Azure Automation. The database moves in a day, the honest work lives in the assessment findings.

DMA found two cross database queries and four Agent jobs, so the honest work assessment was exact. Fixing the queries, jobs become Azure Automation, then the BACPAC route in next month's window. Management has a truthful date at last.