Migrating SQL Server to Azure SQL
The exam rarely asks how migration works internally. It asks which method fits the target and the downtime requirement.
Step 1: Assess
Before moving, assess the source. The Azure SQL migration extension in Azure Data Studio (the successor of Data Migration Assistant) checks compatibility, flags blocking features and recommends an Azure SKU. Azure Migrate can also collect performance data from many servers.
Step 2: Pick the method
| Target | Offline | Online (minimal downtime) |
|---|---|---|
| Azure SQL Database | BACPAC, bcp, Azure Database Migration Service | Transactional replication |
| Azure SQL Managed Instance | Native RESTORE FROM URL | Log Replay Service, Managed Instance link, Azure Database Migration Service |
| SQL Server on Azure VMs | Backup and restore, detach and attach | Log shipping, availability group, Azure Migrate |
Key points:
- Log Replay Service works with SQL Server 2008 and later. You upload full, differential and log backups to Azure Blob Storage and LRS restores them continuously. You then cut over.
- The Managed Instance link uses distributed availability groups for near real-time replication from SQL Server 2016 and later.
- A BACPAC contains schema and data but no transaction logs, so changes made after export are lost. Treat it as offline.
Native restore on Managed Instance
Backups must sit in Azure Blob Storage:
RESTORE DATABASE Sales
FROM URL = 'https://acct.blob.core.windows.net/backups/Sales.bak';You cannot restore to Managed Instance from a local path, and file layout is handled automatically.
Network and security
Managed Instance is deployed in a dedicated subnet. For migration from on-premises, use VPN or ExpressRoute. For Azure SQL Database, use a private endpoint if the connection must not use a public endpoint.
After the move
Change the database compatibility level only after testing. Use Query Store to compare plans before and after, and check that logins, jobs and linked servers were recreated, because they live outside the database.
Exam traps
- Log shipping and availability groups are not valid online methods to Azure SQL Database.
- BACPAC is not minimal downtime.
- Managed Instance cannot restore from local disk, only from URL.
- Logins, SQL Agent jobs and linked servers are not inside the user database, so they must be migrated separately.
Practice questions
Choose an answer, then review the explanation to check your understanding.
1. You must move a SQL Server 2019 database to Azure SQL Managed Instance with minimal downtime and minimal administrative effort. What do you use?
Show answer
Answer: B.Log Replay Service (LRS), or Azure Database Migration Service which uses it, restores backups continuously from Azure Blob Storage until you cut over.
2. A small database can be offline during the move to Azure SQL Database. What is the simplest method?
Show answer
Answer: A.A BACPAC export and import is an offline method. It fits small databases where downtime is acceptable.
3. Which tool checks an on-premises server for compatibility problems and recommends a target SKU before migration?
Show answer
Answer: B.The Azure SQL migration extension for Azure Data Studio assesses readiness, recommends a target size and starts the migration.
4. You migrate to Azure SQL Managed Instance and the source server cannot see Azure over the internet. How should the connection be secured?
Show answer
Answer: B.Managed Instance lives in a virtual network. VPN or ExpressRoute gives a private path for the migration traffic.