Database Migration Strategies

The three approaches

Replication and promote is the right default for most production databases where the engine is not changing. It gives a cutover measured in seconds without the machinery of change data capture.

Dump and restore

1. Estimate the restore time on a copy first. It is usually several times longer than the dump.
2. Stop writes, or accept losing anything written during the window.
3. Dump with the engine’s own tool, with compression.
4. Transfer, and verify a checksum of the file.
5. Restore into the new database.
6. Verify row counts, run a few known queries, check sequences and auto-increment values.
7. Point the application at the new database.

The step people skip is sequences and auto-increment counters. A restore that does not carry them forward produces duplicate key errors on the first insert.

Replication then promote

1. Set up the new database as a replica of the old one.
2. Let it catch up fully, and monitor replication lag until it is consistently near zero.
3. Verify the replica: row counts, spot checks, and application read traffic if you can direct some there.
4. At cutover, stop writes to the primary.
5. Wait for lag to reach zero.
6. Promote the replica to primary.
7. Point the application at the new database.
8. Verify writes are working before reopening traffic.

Downtime is the write pause plus promotion — typically under a minute. Provider migration services automate most of this within their own platforms.

Change data capture

CDC tools read the database’s change log and stream changes to the target continuously, including between different engines. It supports very large databases and heterogeneous migrations with near-zero downtime.

It also introduces real complexity: schema mapping, type conversion differences, handling of large objects, and validating that the two sides genuinely agree. Reach for it when the simpler options do not fit, not by default.

Verify properly

1. Row counts on every table, not only the large ones.
2. Checksums or aggregate values on important columns.
3. Foreign key integrity, which some restore methods can leave broken.
4. Indexes present and valid — a missing index is not a data error and will destroy performance.
5. Sequences, auto-increments and identity columns positioned correctly.
6. Stored procedures, triggers, views and permissions, which dumps do not always include by default.
7. A sample of real application queries returning the same results as on the old database.

Permissions and grants are frequently missed, and the failure appears as an application error long after the migration looked successful.

Rollback

Once writes go to the new database, rolling back means losing those writes unless you planned for it.

• Keep the old database running and untouched, so it remains a valid point-in-time copy.
• Set up reverse replication where the engine allows it, so the old database stays current.
• Define a rollback deadline — after which you go forward and fix rather than back.
• Take a final backup of the old database at the moment of cutover, and keep it.
• Decide in advance who makes the rollback call.

Common questions

Where to go next

See backup strategy and planning a cloud migration.