Lab: Migrate a Database With DMS Change Data Capture

This is the sixth lab in the SA Pro hands-on track, and the one with the least scaffolding: a requirement, a skeleton, and most of the build left to you. The reference posts argue the decisions; this stands one up. It pairs with the DMS and SCT post, which chooses between full load and CDC by the downtime the business will tolerate. Here you build both and watch the difference. The full lab is in lab-sa-pro-06-dms-cdc.zip.

Before your first lab, do the one-time, once-per-account setup: run the zip’s preflight.sh to confirm your account is ready, then deploy the lab reaper, a standing backstop that auto-deletes any lab you forget to tear down after 24 hours. Every SA Pro lab tags its stack for the reaper on deploy, and it matters most here: this is the track’s most expensive lab while running, roughly US$0.15 to US$0.30 an hour across Aurora and DMS.

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 shape that solves this is full load plus change data capture: bulk-copy the rows that exist, and while the copy runs (and after it finishes), tail the source’s binary log and apply every new change to the target. The source stays live the whole time. When the CDC lag is near zero, cutover is minutes, and that is the difference between a migration the business signs off and one it keeps deferring.

The source is Aurora MySQL, configured for CDC: the cluster parameter group sets binlog_format=ROW, because CDC is a consumer of the binary log and row format is what DMS requires. The cluster also has the Data API enabled, so the lab runs its SQL through the AWS CLI with no client, no driver, and 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. Swap the target endpoint for another database engine and the architecture is unchanged.

What you’re given

CloudFormation builds the world around the migration: a VPC with two private subnets and no internet (DMS reaches Aurora inside the VPC and reaches S3 through a free gateway endpoint), the Aurora cluster and its secret, the target bucket, the role DMS assumes to write into it, and the replication subnet group. deploy.sh also creates the account’s one-time dms-vpc-role prerequisite if it is missing, a service role DMS requires before it will place a replication instance in your VPC.

What is missing is the migration itself. The TODO block in src/template.yaml specifies four resources and leaves the building to you. The heart of it is the replication task, where one property carries the whole design decision: the migration type, and the table-mappings JSON that scopes which schema and tables move.

Your task

A DMS migration is four nouns, and you build all four. A replication instance is the worker: dms.t3.micro, private, single-AZ here, though a production migration sizes it by table count and row width and goes Multi-AZ when the CDC stream cannot afford a gap. A source endpoint is how the worker logs in: engine aurora (the engine name for Aurora MySQL), the cluster’s writer address, and the password resolved from the same secret the cluster uses, so it exists in exactly one place. A target endpoint is where copies land: engine s3, the provided role and bucket, and addColumnName=true so the CSVs carry a header row. And the replication task ties them together, with the migration type carrying the whole design decision: full-load copies a moment in time and stops; full-load-and-cdc keeps the target current while the source stays live.

Run it

./scripts/deploy.sh          # 15-20 min first time: Aurora + DMS create in parallel
./scripts/test.sh            # seeds, starts the task, proves full load then CDC
./scripts/teardown.sh        # deletes everything (several minutes)

test.sh tells the migration’s story in order. It seeds labdb.orders with three rows through the Data API, finds and starts your task, and waits for the full load to land those rows in S3. Then the half that matters: it inserts two new rows into the live source and waits for CDC to carry them across, with no second bulk copy. With the unedited template it stops early, reports that no task exists, and points at the TODO. Complete, it prints:

  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 (DMS batches changes; allow a few minutes) ...
  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.

If it fails

• The task never leaves starting, or fails immediately. An endpoint cannot connect, and most “DMS doesn’t work” is networking. Check the source endpoint points at the cluster writer address on 3306, and that the replication instance carries the DMS security group the database’s security group admits. The DMS console’s connection test pinpoints which end is failing.
• Full load lands, CDC never arrives. Either the migration type is full-load, which stops after the bulk copy and is a distinction the Professional paper leans on, or the source has no row-format binary log to read (the cluster parameter group must set binlog_format=ROW).
• The replication subnet group fails to create. The account is missing dms-vpc-role; run ./scripts/deploy.sh rather than deploying the template by hand, and it creates the role first.

Reveal the solution

This is the least-scaffolded lab in the track on purpose: designing the four resources from the requirement is the exercise. When you want the reference answer, deploy it without editing anything:

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

Or unfold the heart of it here:

ReplicationTask:
  Type: AWS::DMS::ReplicationTask
  Properties:
    ReplicationTaskIdentifier: sa-pro-lab-06-task
    MigrationType: full-load-and-cdc
    ReplicationInstanceArn: !Ref ReplicationInstance
    SourceEndpointArn: !Ref SourceEndpoint
    TargetEndpointArn: !Ref TargetEndpoint
    TableMappings: '{"rules":[{"rule-type":"selection","rule-id":"1",
      "rule-name":"include-labdb","object-locator":{"schema-name":"labdb",
      "table-name":"%"},"rule-action":"include"}]}'

What you just learned

• full-load and full-load-and-cdc are different promises. One copies the past; the other keeps the target current while the source stays live, which is what makes near-zero-downtime cutover possible.
• CDC consumes the source’s binary log, so the source must be configured for it: row format, and retention long enough that a paused task catches up instead of starting over.
• A migration is four nouns: a worker, two endpoints, and a task. The task is where the shape lives (migration type, table mappings, transformations), and one worker can run many tasks.
• The replication instance lives in your VPC and needs a real network path to both ends: security groups to the source, a gateway endpoint or NAT to S3. Diagnose connectivity before you suspect DMS.

Next

That closes the SA Pro lab arc: six builds across the four domains, listed with their scaffolding levels in the track’s README.