CDC with DMS Deep Dive

data aws cdc replication claude-curated

Builds on AWS DMS. Where that note covers DMS broadly, this one focuses on the CDC half — what Change Data Capture actually does, how DMS reads each engine’s transaction log, and the failure modes you only hit in production.

What CDC is

Change Data Capture replicates ongoing changes from a source database by reading its transaction log — the same log the engine uses for crash recovery and replication. Every committed INSERT/UPDATE/DELETE produces a log record; CDC tooling tails the log and applies (or stages) those changes against a target.

CDC is fundamentally different from query-based extraction (SELECT * WHERE updated_at > ?):

Captures deletes (a query can’t see a row that no longer exists).
No reliance on application-maintained timestamp columns.
Near-real-time — log events stream as they’re written.
Lower load on source than periodic polling of large tables.

The cost is engine-specific log configuration and the operational surface that comes with it.

How DMS reads each engine’s log

PostgreSQL — logical replication slots

DMS connects as a logical replication client and creates a replication slot. The slot is a server-side cursor: PostgreSQL retains WAL segments until the slot consumes past them.

Required server settings:

wal_level = logical
max_replication_slots = <at least 1 per DMS task>
max_wal_senders   = <at least 1 per DMS task>

Per-database/role grants: REPLICATION privilege, SELECT on replicated tables, USAGE on the schema.

DMS uses the pglogical or test_decoding plugin (defaults vary by DMS version; recent versions support pgoutput natively). For RDS PostgreSQL, set rds.logical_replication = 1.

Slot bloat is the main operational risk. If DMS stops consuming (task failed, paused, network blip) the slot pins WAL retention. The disk fills, and the source crashes. Always alarm on pg_replication_slots.confirmed_flush_lsn lagging, and on free disk on the source.

MySQL — binary logs

DMS reads the binlog as a replica. Required settings:

binlog_format = ROW
binlog_row_image = FULL
binlog_row_metadata = FULL    -- needed for column metadata in newer DMS versions
log_bin = ON
expire_logs_seconds (or binlog_expire_logs_seconds) >= long enough that DMS can catch up

ROW format (not STATEMENT or MIXED) is mandatory — DMS replays row images, not SQL. FULL row image gives DMS the before+after for every column, required for reliable updates and deletes.

Binlog retention is the silent killer: if MySQL rotates a binlog file before DMS reads it, the task fails with “binary log not found” and CDC has to be reseeded. On RDS MySQL, set call mysql.rds_set_configuration('binlog retention hours', 168); or similar.

Oracle — LogMiner or Binary Reader

Two modes:

LogMiner — Oracle’s built-in API. Easier to set up; slower at high write rates.
Binary Reader — DMS reads redo logs directly. Faster but more setup, more privileges, and requires the redo log files to be accessible (NFS or DMS-side download).

Both require ARCHIVELOG mode and supplemental logging at the database (ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;) and table level (ALTER TABLE ... ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;). Without supplemental logging, updates and deletes don’t have enough information to replay.

DMS-required Oracle privileges are extensive — see the AWS DMS Oracle source documentation. Skipping any of them produces opaque errors at task start.

SQL Server — MS-CDC or transaction log

DMS supports two SQL Server modes:

MS-CDC — SQL Server’s built-in CDC feature; DMS reads the change tables. Requires EXEC sys.sp_cdc_enable_db; EXEC sys.sp_cdc_enable_table; per table.
Transaction log — DMS reads the log directly via fn_dblog/fn_dump_dblog. No per-table setup but requires sysadmin-level privileges and full recovery model.

SIMPLE recovery model truncates the log on checkpoint and is incompatible with DMS CDC. Switch to FULL recovery and take regular log backups before enabling CDC.

DMS task settings that matter for CDC

Setting	Effect
`TargetTablePrepMode`	`DO_NOTHING` / `TRUNCATE_BEFORE_LOAD` / `DROP_AND_CREATE`. CDC-only tasks usually want `DO_NOTHING`.
`BatchApplyEnabled`	Apply changes in batches to the target. Higher throughput, but error rows are harder to isolate.
`BatchApplyPreserveTransaction`	Keep source transaction boundaries when batching. Off means changes from different txns can mix.
`ParallelApplyThreads`	Number of parallel threads applying to target (Kinesis/Kafka/some DBs). Critical for throughput.
`cdcStartTime`	Start CDC from a wall-clock time. DMS scans logs to find the matching position.
`cdcStartPosition`	Start from an exact engine-specific position (LSN, SCN, binlog file+position). More precise.
`FailOnNoTablesCaptured`	Helpful safety net — task fails fast if filters match nothing.

Transformation rules (rename schemas/tables/columns, add metadata columns) are JSON expressions in the task. Common addition: a transformation that injects AR_H_OPERATION, AR_H_TIMESTAMP, and AR_H_COMMIT_TIMESTAMP headers as columns on the target — invaluable for debugging.

Source endpoint extra connection attributes

Engine-specific knobs live in the endpoint’s “extra connection attributes” string. Examples:

PostgreSQL — heartbeatEnable=Y;heartbeatFrequency=5;heartbeatSchema=public makes DMS write a heartbeat row, which advances the replication slot’s confirmed LSN even when no real changes are happening. Without this, low-traffic sources accumulate WAL because the slot never advances.
MySQL — eventsPollInterval=5 controls how often DMS polls the binlog.
Oracle — useLogminerReader=N;useBfile=Y switches to Binary Reader mode.
SQL Server — safeguardPolicy=RELY_ON_SQL_SERVER_REPLICATION_AGENT controls log truncation behaviour.

Read the engine-specific page in the DMS user guide before going to production — these flags are where most subtle CDC issues hide.

Replication instance sizing for CDC

Sizing is driven by peak source write rate, not average. A 100 GB database with steady writes might be fine on a small instance; the same database with a nightly bulk job that pushes 50 GB of changes in 30 minutes needs enough memory and network throughput to keep up during that burst.

Memory matters more than CPU for CDC — DMS buffers in-flight changes in memory. Spilling to disk (the swap file) tanks throughput. Watch FreeableMemory and SwapUsage in CloudWatch; if swap goes non-zero, scale up.

Monitoring CDC lag

Two CloudWatch metrics drive every CDC alarm you’ll write:

CDCLatencySource — gap between the latest source commit and the latest event DMS has read from the log. Driven by source-side bottlenecks (slow log reads, network, source under-provisioned).
CDCLatencyTarget — gap between the latest event DMS has read and the latest event applied to the target. Driven by target-side bottlenecks (target slow, batch settings wrong, indexes on target slowing inserts).

Splitting them tells you which side to fix. Both should be alarmed independently with realistic thresholds (e.g. > 5 minutes sustained).

Other useful metrics: CDCChangesMemorySource/Target, CDCChangesDiskSource/Target (non-zero disk = memory-bound), NetworkReceiveThroughput.

Common CDC failures

Log rotated before read — source retention shorter than DMS’s processing time. Symptoms: “binlog not found”, “WAL segment removed”, “redo log missing”. Fix: increase retention, or accept reseed and resync from a snapshot.
Replication slot bloat (PostgreSQL) — DMS task stopped, slot pins WAL, source disk fills. Fix: drop unused slots; alarm on slot age.
Schema changes mid-stream — ALTER TABLE ADD COLUMN on the source while CDC is running. DMS may or may not pick it up depending on engine and settings. Best practice: pause CDC, apply DDL on both sides, restart from current position. For MySQL set binlog_row_metadata = FULL; PostgreSQL with pgoutput handles many additive changes natively.
Long-running source transactions — a hours-long transaction on the source pins log retention until it commits. Both PostgreSQL and Oracle exhibit this. Hunt these in the source before enabling CDC.
Target throttling — target is the bottleneck (small RDS instance, undersized Kinesis stream, locked tables on target). Lag grows monotonically. Fix is target-side, not DMS-side.
Identity / sequence drift after cutover — covered in AWS DMS. Always advance target sequences past the highest migrated value.

DMS Serverless

DMS Serverless removes replication-instance provisioning. AWS sizes capacity automatically based on observed load (in DCUs — DMS Capacity Units), with min/max bounds you set. For variable or unpredictable workloads it’s much simpler than picking and resizing instances; for steady high-throughput CDC, provisioned can still be cheaper. Feature parity with provisioned DMS is close but not 100% — check the limitations page before committing.

mgarden

Explorer