Domain 1 of 5 · Chapter 3 of 3

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 propertyDMSLRSManaged Instance linkNative restoreBACPAC (SqlPackage)Transactional replication
TargetsSQL Database, Managed Instance, SQL Server on Azure VMsManaged InstanceManaged InstanceManaged Instance (not SQL Database)SQL Database; Managed Instance via SqlPackage or SSMSSQL Database (push subscriber only), Managed Instance
Offline or onlineOnline and offline to Managed Instance and VMs; offline only to SQL DatabaseOnline in continuous mode, which keeps restoring new backups; autocomplete restores a finished chainOnlineOfflineOfflineOnline
What carries the dataBackup files for Managed Instance; schema and table data for SQL DatabaseFull, differential, and log backups in Blob StorageDistributed availability group replicationA .bak file in Blob StorageA .bacpac file of schema and table dataPublished table changes through the distribution database
Target readable before cutoverManaged Instance: no (same restore technology as LRS)No (restoring state)Yes, read-onlyNot until the restore finishesNot ready for application use until import completesYes, but treat subscribers as read-only
Cutover actionManaged Instance online: final log backup shows Restored, then Complete cutoverContinuous: complete with the last backup name; autocomplete: automaticStop the workload, validate, fail over the linkRestore finishes and the database comes onlineImport finishes on a new or empty databaseStop source writes, drain changes, redirect, remove replication

Decision tree

Into Azure SQL Database or Managed InstanceSource must staywritable until cutover?NoYesTarget isManaged Instance?YesNativerestoreNoBACPAC orDMS offlineTarget isManaged Instance?NoTransactionalreplicationYesTarget readablebefore cutover, orBusiness Critical?YesNoManagedInstancelinkLRS orDMS online

Cheat sheet

  • Migration readiness assessment identifies blockers before data movement starts
  • Performance-based migration sizing needs representative workload history
  • Moving user databases does not move the instance state they depend on
  • Online migration reduces the final write-freeze window rather than removing cutover
  • DMS online migration support depends on the selected Azure SQL target
  • A DMS backup migration needs access to the backup location and every final log
  • LRS continuous mode accepts new backups while autocomplete consumes a fixed chain
  • LRS needs a valid backup chain organized separately for each database
  • LRS migration to Business Critical includes secondary seeding in the final outage
  • Managed Instance link supports online migration with a readable Azure secondary
  • A link migration's return path depends on SQL Server version and Managed Instance update policy
  • Native Managed Instance migration restores SQL Server backups from Blob Storage URLs
  • Native migration of a TDE-protected database requires its source encryption certificate
  • A BACPAC export needs a write-free source or a transactionally consistent copy
  • BACPAC import creates a new database or loads an empty one rather than merging schema
  • An offline return from Managed Instance to SQL Server requires a format-aligned update policy
  • bcp transfers selected row data into prepared tables rather than packaging a database
  • bcp import options control value preservation and relational integrity
  • SQL Database copy creates an independent transactionally consistent database
  • Transactional replication can move selected tables while the source remains active
  • Managed Instance copy preserves the source while move transfers ownership of the workload
  • Managed Instance relocation requires replication connectivity as well as control-plane permission
  • A replication migration needs a supported distributor and separate snapshot-share connectivity
  • Managed Instance copy and move require compatible update policies and eligible replication state
  • LRS storage-access failures require validation of the exact token and database path
  • Asynchronous migration progress requires the status interface for the selected operation
  • Migration throughput tuning must target the constrained stage of the transfer
  • BACPAC import client selection must satisfy authentication and network requirements
  • An emergency replication reset discards pending delivery and requires reinitialization

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. https://learn.microsoft.com/en-us/azure/dms/dms-overview
  2. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  3. https://learn.microsoft.com/en-us/azure/migrate/tutorial-assess-sql
  4. https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/migration-assessment
  5. https://learn.microsoft.com/en-us/previous-versions/sql/dma/dma-overview
  6. https://learn.microsoft.com/en-us/sql/tools/whats-happening-azure-data-studio
  7. https://learn.microsoft.com/en-us/answers/questions/1190831/retirement-notice-database-migration-service-class
  8. https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/managed-instance/sql-server-to-managed-instance-guide
  9. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
  10. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/log-replay-service-overview
  11. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-feature-overview
  12. https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server
  13. https://learn.microsoft.com/en-us/azure/dms/resource-scenario-status
  14. https://learn.microsoft.com/en-us/azure/dms/tutorial-sql-server-azure-sql-database-offline
  15. https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import
  16. https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import
  17. https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication
  18. https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison
  19. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/log-replay-service-migrate
  20. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups
  21. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-database-to-sql-server
  22. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-sample-database-quickstart
  23. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url
  24. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption
  25. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/tde-certificate-migrate
  26. https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export
  27. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/connect-application-instance
  28. https://learn.microsoft.com/en-us/sql/tools/bcp/bcp-utility
  29. https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql
  30. https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database
  31. https://learn.microsoft.com/en-us/sql/relational-databases/replication/snapshot-replication
  32. https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/filter-published-data
  33. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-transactional-overview
  34. https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy
  35. https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview
  36. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-geo-replication-link-status-azure-sql-database
  37. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/database-copy-move-how-to
  38. https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/database/sql-server-to-sql-database-guide