5.1.3.2. Design for Database Migration Services
5.1.3.2. Design for Database Migration Services
💡 First Principle: A purpose-built database migration service is essential for facilitating the secure, efficient, and often near-zero downtime transfer of databases to the cloud, addressing the unique challenges of data consistency, schema conversion, and continuous replication.
Scenario: You are tasked with migrating a large on-premises PostgreSQL database to Azure Database for PostgreSQL with minimal disruption to a 24/7 production application. You need a service that can keep the data synchronized during the migration and allow for a quick cutover.
Database migration services are Azure services designed to help you move your databases to Azure.
Key Design Considerations:
- Azure Database Migration Service (DMS):
- Purpose: A fully managed service designed to enable seamless migrations from various database sources to Azure data platforms with minimal downtime.
- Supported Sources: SQL Server, MySQL, PostgreSQL, Oracle, MongoDB.
- Supported Targets: Azure SQL Database, Azure Database for MySQL/PostgreSQL/MariaDB, Azure Cosmos DB.
- Migration Types: Supports offline (downtime required) and online (minimal downtime via continuous data synchronization) migrations.
- Migration Strategy:
- Offline Migration: Suitable for applications that can tolerate downtime. Simpler and lower cost.
- Online Migration: Ideal for mission-critical applications. DMS continuously synchronizes data, allowing for a seamless cutover.
- Assessment and Planning: Before migration, use tools like Data Migration Assistant (DMA) to assess database compatibility and identify potential migration blockers.
- Data Consistency and Integrity: Ensure data integrity throughout the migration process. DMS handles data synchronization and validation.
Design decisions in practice:
| Decision | Choose | When |
|---|---|---|
| Migration mode | Offline | A maintenance window exists that comfortably exceeds the copy time |
| Online | The database is 24/7 and downtime must be limited to the cutover itself | |
| Target | Azure SQL Database | A single database with no instance-level dependencies |
| Azure SQL Managed Instance | SQL Agent jobs, cross-database queries, CLR, Service Broker, linked servers | |
| SQL Server on a VM | A specific SQL Server build, OS control, or vendor support constraints |
Online migration is not free. It performs the initial load while the source stays in service and then keeps the target synchronized from the source's transaction log, so the source must be in a recovery model that retains that log, and the migration service needs private network connectivity to reach the source. Downtime shrinks to the cutover, but the migration runs longer and costs more to operate.
Assess before choosing a target. The Data Migration Assistant reports blocking compatibility issues and unsupported features, and it is what turns "we will move it to Azure SQL Database" into a decision rather than an assumption — a single unsupported dependency such as a linked server pushes the target to Managed Instance.
⚠️ Exam Trap: backup-and-restore is an offline migration however it is described. Every transaction committed after the backup is taken is lost unless the source is stopped first, so it cannot satisfy a near-zero data-loss requirement.
⚠️ Common Pitfall: Attempting a heterogeneous migration (e.g., Oracle to PostgreSQL) without using the associated Schema Conversion Tool. DMS migrates the data, but you often need a separate tool to convert the schema, stored procedures, and application code.
Key Trade-Offs:
- Online vs. Offline Migration: Online migration provides minimal downtime but is more complex and can be more costly due to the need for continuous replication infrastructure. Offline migration is simpler but requires a significant downtime window.
Reflection Question: How do database migration services (specifically Azure Database Migration Service (DMS) with its online migration capabilities) address the unique challenges of migrating databases (e.g., data consistency, downtime minimization) to ensure a secure and efficient transfer to Azure, particularly for mission-critical workloads?
