Big-Bang, Trickle, or CDC: Choosing Your Migration Strategy

Strip away the vendor language and every database migration is one of three shapes. Picking the wrong one doesn't fail loudly — it shows up as a maintenance window that overruns, or a target that's quietly three days stale. Two numbers decide it: your downtime budget, and your source's write rate.

1. Big-bang: stop, copy, switch

Freeze writes, migrate everything, validate, point the application at the target. Simplest to reason about, easiest to validate — source isn't moving, so counts and checksums are exact.

• Right when: downtime window fits the copy time — internal tools, staging, nightly-quiet systems, smaller databases.
• Wrong when: the copy takes 6 hours and you have 30 minutes. No partial credit — overruns mean rollback or extended outage.
• Make it safer: dry-run first to learn the real duration; parallel transfer and deferred indexes to shrink it.

2. Trickle: bulk copy + incremental top-ups

Bulk-copy while the source stays live, then repeatedly transfer only what changed (UPSERT on a sync key — an updated-at column or monotonic ID). Each pass is smaller; the final pass during a short freeze is minutes, not hours.

• Right when: you can tolerate minutes of downtime and your tables have a usable sync key.
• Watch for: hard deletes (an UPSERT sync can't see them — soft-delete or reconcile separately) and rows that mutate without touching the sync key.

3. CDC: stream every change, cut over when ready

Mark the source's replication position, bulk-copy, then replay every insert/update/delete from the log until the target trails by seconds. Cutover is a connection-string change at a moment you choose.

• Right when: downtime budget is ~zero, write rate is high, or you want a long parallel-running period to build confidence.
• Cost: the most moving parts — replication log access on the source, idempotent apply, lag monitoring. Worth it exactly when the first two options aren't.

The decision in one table

• Downtime budget ≥ copy time → big-bang.
• Minutes of downtime OK + sync key exists → trickle.
• Near-zero downtime, or no usable sync key but log access → CDC.

Whichever shape you pick: rehearse on a copy first, validate with more than row counts, and have a written rollback plan before cutover, not during it.