Near-zero-downtime database cutover with AWS DMS change data capture

Every database migration eventually arrives at the same question: how long can the application be down? For an internal reporting database the honest answer might be a weekend. For the system that takes orders, books freight, or processes payments, it is minutes, and the wrong minutes cost real money.

The old approach, take the database offline, copy it, point the application at the copy, only works when the copy is fast relative to your tolerance for downtime. Past a few hundred gigabytes, or with a busy write workload, it stops fitting inside any window you can negotiate. Change data capture (CDC) removes the constraint: you copy the data while the source is still live, then keep the target continuously up to date until the moment you switch. The outage shrinks from “as long as the copy takes” to “as long as it takes to repoint a connection string.”

On AWS, the tool is AWS Database Migration Service (DMS), and the migration type you want is full-load-and-cdc. The rest of this post is how that works in practice, and the parts that catch teams out.

What “near-zero downtime” actually means

It does not mean zero. It means the downtime is decoupled from the size of the database. A two-terabyte database and a twenty-gigabyte one can both cut over in the same handful of minutes, because at cutover you are not moving data, you are draining the last few seconds of change and flipping a switch. The work, full load plus weeks of replication if you want it, happens with the application fully online.

What you trade for that is complexity: a replication path that has to be set up, monitored, validated, and torn down cleanly. CDC is not harder conceptually, but it has more moving parts than a copy, and each one is a place to get it wrong.

How DMS CDC works

A DMS task in full-load-and-cdc mode runs in two phases against a replication instance that sits between source and target:

Full load seeds the target; CDC keeps it in lockstep with the live source until cutover.

First the full load bulk-copies the existing rows. Critically, DMS records the log position at which the full load started, so that the second phase, CDC, can replay every insert, update and delete that happened from that point forward by reading the source’s transaction log. The two phases overlap cleanly: changes that land during the full load are caught by CDC, so you never lose a write.

Because CDC reads the transaction log rather than querying tables, its load on the source is light, and it can keep a target current for as long as you need, days or weeks, while you rehearse the cutover.

Turn the source on first

CDC only works if the source is emitting a usable transaction log. This is the most common reason a migration stalls on day one, so confirm it before anything else:

• MySQL / MariaDB: binary logging in ROW format (binlog_format=ROW), with a retention long enough to cover a full-load-plus-catch-up window.
• PostgreSQL: logical replication (wal_level=logical; on RDS, rds.logical_replication=1) and enough replication slots.
• Oracle: ARCHIVELOG mode and supplemental logging (ADD SUPPLEMENTAL LOG DATA), read via LogMiner or DMS Binary Reader.
• SQL Server: full recovery model and transaction-log access (or MS-CDC), so DMS can read committed changes.

The task settings that earn their keep

Most of a DMS task’s behaviour is controlled by a JSON settings document. These are the fields that matter for a near-zero-downtime cutover:

// DMS task settings — full-load-and-cdc, validation on
{
  "TargetMetadata": {
    "LobMaxSize": 64,           // KB; rows with larger LOBs are truncated in Limited mode
    "FullLobMode": false,        // false = Limited LOB mode (fast); true = Full (slow, unlimited)
    "LimitedSizeLobMode": true
  },
  "FullLoadSettings": {
    "TargetTablePrepMode": "DROP_AND_CREATE",
    "MaxFullLoadSubTasks": 8,     // parallel table loads
    "CommitRate": 50000
  },
  "ValidationSettings": {
    "EnableValidation": true,    // DMS compares source vs target, row by row
    "ThreadCount": 5
  },
  "ChangeProcessingDdlHandlingPolicy": {
    "HandleSourceTableTruncated": true,
    "HandleSourceTableDropped": true
  },
  "Logging": { "EnableLogging": true }
}

LOB handling is the field people underestimate. Full LOB mode moves large objects of any size but is slow and serial; Limited LOB mode is fast but silently truncates anything over LobMaxSize. Inventory your largest BLOB/CLOB/TEXT columns and set the limit deliberately, or you will discover the truncation in production. ValidationSettings turns on DMS’s own row-by-row comparison, which you want running throughout, not just at the end.

Size the replication instance for the spike, not the average

The replication instance is a managed EC2 host, and an undersized one is the usual cause of CDC falling behind. Full load is CPU- and network-bound; CDC is memory-bound, because uncommitted transactions are held in memory until they commit. A long-running transaction on the source can balloon that. Start a class or two larger than you think you need, watch it under real load, and scale down later, never the reverse during a live migration.

The cutover: drain the latency to zero

This is the few minutes that the whole exercise exists to make small. Watch two CloudWatch metrics on the task: CDCLatencyTarget (how far behind the target is) and CDCIncomingChanges (the backlog). With the application still live, both sit at a small steady-state. The cutover is then a sequence, not an event:

1. Quiesce writes on the source, put the app into read-only or maintenance mode, or stop it. This is the only true downtime.
2. Let CDC drain. Wait for CDCLatencyTarget to fall to ~0 and CDCIncomingChanges to flush. The target is now byte-for-byte current.
3. Validate (next section), while writes are still frozen.
4. Repoint the application, change the connection string, or better, swap a DNS/endpoint alias so no redeploy is needed.
5. Resume writes against the target and confirm.

Done well, steps 1–5 are minutes. The database could be two terabytes; it makes no difference, because no data moves during the window.

Validate before you switch, not after

The fastest way to lose trust in a migration is to cut over and then find a row-count mismatch. Validate inside the frozen window, with two independent checks. DMS’s built-in validation (the EnableValidation flag above) compares source and target continuously and reports mismatches per table. Back it with your own reconciliation, a count and a cheap checksum per critical table:

-- independent reconciliation, run against source and target
SELECT
  COUNT(*)                              AS row_count,
  SUM(CRC32(CONCAT_WS('|', id, status, amount, updated_at))) AS checksum
FROM orders;

Equal counts and equal checksums on the tables that matter, plus a clean DMS validation report, is your go/no-go signal. Disagreement is a stop.

Where CDC bites: the blind spots

Beyond LOBs and sequences, keep these on the runbook:

• Schema changes during migration. DDL replication is partial. Freeze schema changes on the source for the duration; if one is unavoidable, plan it explicitly on both sides.
• Foreign keys and secondary indexes. Leaving them on during full load slows it and can cause ordering errors. The common pattern is to load with them disabled, then rebuild before CDC apply, faster load, clean constraints.
• Very large or long-running transactions on the source inflate replication-instance memory and CDC latency. Know your worst offenders before you start.
• Heterogeneous edge cases. When the engine also changes (say SQL Server to Aurora PostgreSQL), data-type and collation differences surface here, and belong in the schema-conversion workstream, not at cutover.

The fallback you hope never to use

A near-zero-downtime cutover is reversible right up to the moment you resume writes on the target, because the source is untouched and current. The discipline is to keep it that way: do not decommission the source on cutover day. If the worst happens after writes have resumed on the target, your clean rollback path is a reverse CDC task (target back to source) stood up in advance, so the old database can be made current again. Most migrations never trigger it. The ones that need it are very glad it exists.

The shape of a good cutover

Strip it back and a near-zero-downtime migration is four disciplines: get the source emitting a clean log, size the replication path for the spike, validate inside the frozen window with two independent checks, and keep a real way back. The data volume stops mattering, which is the whole point. The window is set by your process, not your database size.