SA Pro Lab 06 — Replicate a database with DMS and change data capture

Scaffold: 1/5. The world around the migration is built: the source database, the target bucket, the network, the roles. The migration itself — all four DMS resources — is yours to design and build from a requirement.

The scenario

A production database has to move, and the business will not take an outage window big enough to dump-and-restore it. The migration shape that solves this is full load plus change data capture: bulk-copy the rows that exist, and while that copy runs (and after it finishes), tail the source’s binary log and apply every new change to the target. When the CDC lag is near zero, the cutover is minutes, not hours.

The source here is Aurora MySQL, configured the way CDC demands (binlog_format=ROW) and with the Data API enabled so the lab can run SQL through the AWS CLI — no client, no driver, no public endpoint. The target is S3, DMS’s utility-knife target: the same pattern lands a full load for a data lake, stages CDC for downstream replay, or feeds an audit trail. Swapping the target endpoint for another database engine is the same architecture.

The requirement

Three rows exist in labdb.orders before the migration starts. After your DMS resources deploy and the task runs: the three rows land in S3 as a bulk-copy file, and two rows inserted after the full load arrive in S3 as CDC changes, with no second bulk copy.

What’s provided

• src/template.yaml — the VPC (private subnets, S3 gateway endpoint, no internet), the Aurora cluster with binlog and Data API on, the target bucket, the S3 access role for DMS, and the replication subnet group. The TODO block specifies the four resources you build.
• solution/template.yaml — the reference answer.
• scripts/ — deploy, test, and teardown. Deploy also creates the account’s one-time dms-vpc-role prerequisite if it is missing.

Your task

Build the migration in src/template.yaml:

1. A replication instance — the worker (dms.t3.micro, private, identifier sa-pro-lab-06 so the test can find it).
2. A source endpoint — engine aurora, pointing at the cluster’s writer endpoint, password resolved from the same secret the cluster uses.
3. A target endpoint — engine s3, using the provided role and bucket, with addColumnName=true so the CSVs carry headers.
4. A replication task — full-load-and-cdc, identifier sa-pro-lab-06-task, with a table mapping that includes schema labdb, table %.

Run it

# Defaults: stack sa-pro-lab-06, region ap-southeast-2.
./scripts/deploy.sh          # 15-20 min first time: Aurora + DMS in parallel
./scripts/test.sh            # seeds, starts the task, proves full load then CDC
./scripts/teardown.sh        # deletes everything (several minutes)

This is the track’s most expensive lab while running — roughly US$0.15–0.30 an hour across Aurora Serverless v2 and the DMS instance. Tear it down when you’re done; the deploy tags the stack for the lab reaper as a backstop.

What success looks like

./scripts/test.sh prints the arc of the migration:

  full load complete: 3 rows copied.

What landed in S3 (the LOAD file is the bulk copy):
  ... LOAD00000001.csv

Inserting two NEW rows into the source (ids 1042, 1043) ...
Waiting for CDC to carry them across ...
  CDC delivered: 2 change rows applied since the full load.

PASS: the full load copied the existing rows, and rows written afterwards
flowed across via CDC with no second bulk copy.

With the shipped template the test stops early: no task exists, and it tells you what to build.

If it fails

• The task never leaves starting, or fails immediately. An endpoint cannot connect. Check the source endpoint’s server name is the cluster writer endpoint, the port is 3306, and the DMS security group is the one the database’s security group admits. The DMS console’s connection test pinpoints which endpoint is unhappy.
• Full load works, CDC never arrives. Either the task’s MigrationType is full-load (which stops after the bulk copy — the exam loves this distinction) or the source isn’t producing a row-format binary log (the cluster parameter group must set binlog_format=ROW).
• ReplicationSubnetGroup fails to create. The account is missing dms-vpc-role; run ./scripts/deploy.sh (it creates the role) rather than deploying the template by hand.

Reveal the solution

SRC=solution ./scripts/deploy.sh && ./scripts/test.sh

What you just learned

• full-load and full-load-and-cdc are different promises. The first copies a moment in time; the second keeps the target current while the source stays live, which is what makes near-zero-downtime cutover possible.
• CDC is a consumer of the source’s binary log, so the source must be configured for it (binlog_format=ROW, and retention long enough that a paused task can catch up rather than restart).
• A DMS migration is four nouns: a worker, two endpoints, and a task. The task is where the shape lives — migration type, table mappings, transformations — and the same worker can run many tasks.
• The replication instance sits in your VPC and needs a real network path to both ends: security groups to the source, a gateway endpoint (or NAT) to S3. Most “DMS doesn’t work” is networking, not DMS.

Next

The rest of the SA Pro lab track is listed in labs/README-sa-pro.md.