Plan and implement a migration strategy
Unlock the complete study guide + 25 practice exams totaling 1,500 questions.
Bundled into the existing Administering Microsoft Azure SQL Solutions premium course — no separate purchase.
14-day refund guarantee on one-time purchases and your first subscription charge.
Included in this chapter:
- Start with an assessment, not a copy
- Size the target from representative history
- Move the instance state the databases depend on
- Offline versus online: where the write freeze falls
- Which migration methods each target accepts
- Azure Database Migration Service
- Log Replay Service: modes and commands
- Preparing backups and the source for LRS
- LRS cutover on the Business Critical tier
- Managed Instance link migration
- Update policy sets which way a database can travel
- Native restore from a URL
- Restoring a TDE-protected backup
- Exporting a BACPAC
- Importing a BACPAC
- Choosing the BACPAC import client
- Moving selected rows with bcp
- What bcp skips unless you ask
- Transactional replication as a migration path
- Replication topology and network paths
- Copying or relocating an Azure SQL Database
- Managed Instance copy and move
- Managed Instance copy and move prerequisites
- Diagnosing LRS storage access failures
- Tracking asynchronous operations before retrying
- Finding the constrained stage of a slow migration
- Replication stalls and the emergency reset
- Recognizing migration questions
Main methods for migrating SQL Server databases into Azure SQL
| Method property | DMS | LRS | Managed Instance link | Native restore | BACPAC (SqlPackage) | Transactional replication |
|---|---|---|---|---|---|---|
| Targets | SQL Database, Managed Instance, SQL Server on Azure VMs | Managed Instance | Managed Instance | Managed Instance (not SQL Database) | SQL Database; Managed Instance via SqlPackage or SSMS | SQL Database (push subscriber only), Managed Instance |
| Offline or online | Online and offline to Managed Instance and VMs; offline only to SQL Database | Online in continuous mode, which keeps restoring new backups; autocomplete restores a finished chain | Online | Offline | Offline | Online |
| What carries the data | Backup files for Managed Instance; schema and table data for SQL Database | Full, differential, and log backups in Blob Storage | Distributed availability group replication | A .bak file in Blob Storage | A .bacpac file of schema and table data | Published table changes through the distribution database |
| Target readable before cutover | Managed Instance: no (same restore technology as LRS) | No (restoring state) | Yes, read-only | Not until the restore finishes | Not ready for application use until import completes | Yes, but treat subscribers as read-only |
| Cutover action | Managed Instance online: final log backup shows Restored, then Complete cutover | Continuous: complete with the last backup name; autocomplete: automatic | Stop the workload, validate, fail over the link | Restore finishes and the database comes online | Import finishes on a new or empty database | Stop source writes, drain changes, redirect, remove replication |
Decision tree
Cheat sheet
Unlock with Premium — includes all practice exams and the complete study guide.
References
- https://learn.microsoft.com/en-us/azure/dms/dms-overview
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/azure/migrate/tutorial-assess-sql
- https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/migration-assessment
- https://learn.microsoft.com/en-us/previous-versions/sql/dma/dma-overview
- https://learn.microsoft.com/en-us/sql/tools/whats-happening-azure-data-studio
- https://learn.microsoft.com/en-us/answers/questions/1190831/retirement-notice-database-migration-service-class
- https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/managed-instance/sql-server-to-managed-instance-guide
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/log-replay-service-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-feature-overview
- https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server
- https://learn.microsoft.com/en-us/azure/dms/resource-scenario-status
- https://learn.microsoft.com/en-us/azure/dms/tutorial-sql-server-azure-sql-database-offline
- https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import
- https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import
- https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication
- https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/log-replay-service-migrate
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-database-to-sql-server
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-sample-database-quickstart
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/tde-certificate-migrate
- https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/connect-application-instance
- https://learn.microsoft.com/en-us/sql/tools/bcp/bcp-utility
- https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database
- https://learn.microsoft.com/en-us/sql/relational-databases/replication/snapshot-replication
- https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/filter-published-data
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-transactional-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy
- https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-geo-replication-link-status-azure-sql-database
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/database-copy-move-how-to
- https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/database/sql-server-to-sql-database-guide