Homogeneous and heterogeneous database migration paths
Choose homogeneous native or managed data movement versus heterogeneous schema conversion by engine compatibility, ongoing writes, downtime, conversion scope, validation, cutover, and rollback requirements.
LESSON GOALS
By the end, you can
- Distinguish homogeneous from heterogeneous database migration and separate schema conversion from data movement.
- Plan full load, ongoing CDC, validation, cutover, rollback, and compatibility work.
01
Inventory compatibility and downtime
Record source and target engine, versions, size, change rate, data types, large objects, stored procedures, functions, triggers, extensions, character sets, application SQL, network path, allowed downtime, and rollback window. A homogeneous migration retains the same or a compatible engine; schema conversion may be unnecessary, and native backup, restore, or replication can be simplest. AWS DMS remains useful when managed full load and ongoing replication satisfy low-downtime needs.
A heterogeneous migration changes engines. Data movement alone cannot translate incompatible schemas, procedural code, or application SQL. Assess conversion, automatically convert supported objects, manually remediate unsupported objects, and test the target before copying production data. Use AWS DMS Schema Conversion or AWS SCT where their current support fits, and verify exact compatibility rather than assuming a service name covers every engine pair.
02
Separate full load, CDC, and schema conversion
DMS tasks can perform full load, change data capture, or full load plus CDC. Full load copies existing data. CDC replicates ongoing source changes. For low downtime while writes continue, full load plus CDC is usually the shape: establish target schema, load baseline, apply changes, and monitor source and target latency until caught up. A fast full load does not prove CDC can keep pace with production change rate.
Size the migration path from source read headroom, network throughput, replication compute, task parallelism, target write capacity, and validation load. Secure endpoints and credentials, encrypt traffic, and avoid exhausting the source. Heterogeneous conversion must happen before application cutover; DMS moving rows does not make stored procedures or SQL syntax compatible. Homogeneous native tools can win when they preserve engine-specific features and meet downtime with less machinery.
03
Validate, cut over, and preserve rollback
Rehearse with production-like data. Validate counts, checksums where appropriate, constraints, query results, permissions, and application behavior. Monitor CDC lag, then quiesce writes for the final bounded window, apply remaining changes, validate again, redirect clients, and observe errors and performance. Do not allow both databases to accept uncontrolled writes unless bidirectional conflict behavior is designed.
Rollback must specify how traffic and post-cutover writes return to the source; a vague snapshot is not enough. Retain backups and migration evidence according to policy. Compare cost across migration compute, dual-running databases, conversion labor, licenses, and outage risk. Treat supported engine versions, conversion rules, CDC limitations, and DMS modes as volatile. The durable decision rule is compatibility plus downtime: native compatible path when it meets objectives, conversion plus data movement when engines differ.
Task mode and schema conversion must remain separate at every gate. AWS DMS documents full-load, CDC-only, and full-load-plus-CDC task shapes; choosing one states whether existing rows, ongoing changes, or both move. DMS Schema Conversion assesses and converts supported source objects for a heterogeneous target, while unsupported procedural code or application SQL still needs remediation and tests. Observe the distinction by recording converted-object findings before the task starts, table-load status during baseline copy, and CDC source/target latency while writes continue. A task that reports full load complete can still be unready because converted functions fail or CDC falls behind. Limit endpoint credentials to required schemas, encrypt the network path, and prevent migration logs from exposing row data. Dual-running source, target, replication capacity, conversion labor, and rollback retention form the migration cost; a longer low-downtime run can cost more than a short outage that the business is willing to accept.
DECISION TABLE
Compare the architecture constraints
| requirement | choose | because | reject |
|---|---|---|---|
| Same engine; downtime permits restore | Native backup/restore | Compatibility and simple cutover fit | Schema conversion and CDC add unnecessary machinery |
| Same/compatible engine; writes continue and downtime is short | DMS full load plus CDC or native replication | Ongoing changes must catch up | Full load alone misses later writes |
| Different engines | Schema conversion and remediation plus DMS | Schema/code compatibility and data movement are separate | DMS-only row movement leaves incompatible objects |
| CDC lag cannot catch up | Increase path/target capacity or reduce change rate before cutover | Cutover requires bounded lag | Proceeding loses writes or extends outage |
GUIDED WORKED SCENARIO
Reason from the stated constraints
This is a guided example, not a question to memorise. The knowledge check uses new situations to test the same reasoning.
Move a 6 TB commercial relational database to PostgreSQL. Writes continue at 20 MB/s and cutover may pause them for only 15 minutes. Stored procedures and proprietary types exist.
Requirements
- Change engines
- Keep cutover within 15 minutes
- Validate converted schema and data
- Retain a tested rollback path
Reasoning path
- Classify as heterogeneous.
- Assess and convert schema/code, then remediate unsupported objects.
- Run DMS full load plus CDC and prove CDC catches up.
- Quiesce writes, validate, switch, observe, and execute rollback criteria if needed.
Why it wins
- It handles both incompatible schema/code and ongoing data changes.
- Lag and application validation are measured before traffic moves.
Why the alternatives lose
Writes after the load begins are absent and schema objects remain incompatible.
Becomes correct when: The source is offline for the full migration and target schema is already compatible.The source is a different commercial engine with incompatible objects.
Becomes correct when: Migration is homogeneous PostgreSQL-to-PostgreSQL.It violates the 15-minute cutover requirement.
Becomes correct when: Business accepts an outage covering the complete copy and validation.ARCHITECTURE VISUAL · TEXT MODE
Inspect the model
A migration runbook table separates conversion findings, full-load status, CDC lag, validation evidence, cutover clock, and rollback ownership.
KNOWLEDGE CHECK
Answer one question at a time
Pass every question to complete this lesson. Each answer includes reasoning before you continue.
What distinguishes homogeneous from heterogeneous migration?
Reveal reasoning
Whether source and target engines and schemas are compatible, not data size.
What metric must reach an acceptable bound before CDC cutover?
Reveal reasoning
Source and target CDC lag, plus completed validation.
Change one decisive constraint: The source remains offline for the entire copy and validation window, and the target schema is already compatible. Which architecture now wins, and what observable result proves the reversal?
Reveal reasoning
The migration winner reverses to a DMS full-load-only task. The proof is: Validation reconciles source and target counts with zero post-copy writes and no unresolved schema objects.
PRIMARY SOURCES