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 here, because the Aurora cluster bills by the hour whether or not a task is running. In Sydney, where deploy.sh defaults, Aurora MySQL Serverless v2 on Aurora Standard lists at USD$0.20 per ACU-hour, so the 0.5-ACU floor is USD$0.10 an hour, and the dms.t3.micro adds USD$0.028. Call it USD$0.13 an hour sitting idle, rising towards USD$0.43 if the cluster climbs to its 2-ACU ceiling.

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, tail the source’s binary log and apply every new change to the target. The source stays live throughout. When the CDC lag is near zero, cutover takes minutes, and that is the difference between a migration the business signs off and one it keeps deferring.

The source is Aurora MySQL on Serverless v2, configured for CDC. The cluster parameter group sets binlog_format=ROW, which is what DMS requires to read the binary log; binlog_row_image has to be FULL, and already is by default. Serverless v1, which reached end of life on 31 March 2025, could not do this at all, because its parameter groups would not let you turn binary logging on, so it was a full-load-only DMS source. Serverless v2 supports CDC. The cluster also has the RDS Data API enabled, so the lab runs its SQL through the AWS CLI with no client, no driver, and no public endpoint. That needs Aurora MySQL 3.07 or later, and a Region where the Data API is offered: Sydney is, Melbourne is not, so leave the default Region alone unless you check. The target is an S3 bucket. The same endpoint type lands a full load for a data lake, stages CDC for downstream replay, or keeps an audit trail.

What you’re given

CloudFormation builds the world around the migration: a VPC with two private subnets and no internet, the Aurora cluster and its secret, the target bucket, the role DMS assumes to write into it, and the replication subnet group. DMS reaches Aurora inside the VPC and reaches S3 through a gateway endpoint, which carries no hourly charge. deploy.sh also creates the account’s one-time dms-vpc-role if it is missing. DMS looks that role up by its exact name, and will not place a replication instance in your VPC without it.

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 the migration type carries the design decision and the table-mappings JSON 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. A production migration sizes it by table count and row width, and goes Multi-AZ so the task survives an AZ failure rather than waiting on a replacement. A source endpoint is how the worker logs in: engine aurora, which is the engine name DMS uses for Aurora MySQL, the cluster’s writer address, port 3306, and the password resolved from the same secret the cluster uses. Point it at the writer, not a reader. DMS supports an Aurora MySQL replica as a source for full load only.

A target endpoint is where copies land: engine s3, the provided role and bucket, and AddColumnName: true in S3Settings so the CSVs carry a header row. And the replication task ties them together. full-load copies a moment in time and stops. cdc streams changes only, for when the bulk copy happened some other way. full-load-and-cdc does both, and 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, raises binlog retention to 24 hours, then finds and starts your task. It waits for the full load to land those three 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 417238, 417239) ...
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.

The two ids are derived from the clock, so they differ on every run.

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. Task logging to CloudWatch needs a second account-wide role named dms-cloudwatch-logs-role, which this lab does not create.
• Full load lands, CDC never arrives. Either the migration type is full-load, which stops after the bulk copy, 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 a 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 resumes instead of starting over.
• A migration is four nouns: a worker, two endpoints, and a task. The task holds the shape (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.
• DMS Serverless is the other way to run this. You create a replication configuration rather than an instance, and DMS works out the capacity from the source metadata. A lab pins an instance class so the size is known.

Next

This is lab six of six, the last of the SA Pro builds. All six are listed with their domains and scaffolding levels in the track’s README.