DMS, SCT and Database Migrations

By the end of this lesson you will be able to

• Explain what AWS Database Migration Service (DMS) does: a replication instance that reads from a source endpoint and writes to a target endpoint while the source stays in use, with minimal (not zero) downtime at cutover.
• Name the main DMS sources and targets, and apply the rule that at least one endpoint must be in AWS.
• Tell a homogeneous migration from a heterogeneous one, and say what AWS SCT (or DMS Schema Conversion) converts, what DMS creates by itself, and what neither does for you.
• Choose between the three task types (full load, full load and CDC, CDC only), and list what CDC needs from the source’s logs.
• Make the migration itself highly available with a Multi-AZ replication instance, and know DMS Serverless, data validation and LOB modes.
• Pick the native path for RDS and external MySQL or PostgreSQL into Aurora: Aurora read replica and promote, snapshot restore, Percona XtraBackup from S3, mysqldump, pg_dump, or DMS.

#Why moving a database is its own problem

Lessons 23.1 and 23.2 were about surviving a disaster. This lesson and the next two are about moving into AWS. Servers can be copied block by block (lesson 23.4) and files shipped on devices (lesson 23.5), but a live database is harder: it changes every second, other systems depend on its exact behaviour, and its schema and stored code are written in one engine’s dialect.

Two Bangladeshi examples run through this lesson:

• A Dhaka telco runs prepaid billing on an on-premises Oracle database of 3 TB. It wants Aurora PostgreSQL to stop paying Oracle licences. Customers recharge day and night, so the database can be down for minutes at most.
• A Dhaka e-commerce shop runs RDS for MySQL and wants Aurora MySQL for faster failover and storage that grows by itself (lesson 7.3).

The first needs schema conversion and continuous replication. The second needs neither, and the cleverest answer uses no migration service at all. Telling those situations apart is what the exam tests.

#AWS DMS: copy a database while it keeps running

AWS Database Migration Service (DMS) moves data from one database or data store to another, quickly and securely, while the source database remains fully operational. The application keeps reading and writing the old database during the whole copy; you only stop it for a short cutover at the end.

#The pieces

The slides say “you must create an EC2 instance to perform the replication tasks”. More precisely, you create a replication instance, which DMS builds on EC2 and manages; you never log in to it. With DMS Serverless (below) you create no instance at all.

DMS is resilient and self-healing: a task that hits a temporary network error retries and resumes from its last position, and the replication instance can be made Multi-AZ.

#Sources and targets

DMS speaks many engines. The lists below are from the current DMS documentation; the exam asks mostly about the relational ones.

#The one rule: at least one endpoint in AWS

The DMS frequently asked questions page states it plainly: the source or the target (or both) must be in AWS (on RDS, Aurora, EC2, or an AWS data store such as S3 or DynamoDB), and on-premises to on-premises replication is not supported. So DMS cannot copy your Dhaka data center to your Chattogram data center, and it cannot copy Azure to Google Cloud. It can copy into AWS, out of AWS (EC2 or RDS to on-premises, for example to leave AWS or to keep a fallback), and within AWS (RDS to Redshift, Aurora to S3, one Region to another).

The replication instance needs a network path to both endpoints. From a data center that means a Site-to-Site VPN or Direct Connect (lessons 22.4 and 22.5), or the public internet with SSL. A replication instance can be private (no public IP) when a private path exists.

#Same engine or different: where SCT comes in

#Homogeneous and heterogeneous

• Homogeneous: the engine stays the same. Oracle to RDS for Oracle, MySQL on EC2 to Aurora MySQL, on-premises PostgreSQL to RDS for PostgreSQL. RDS is only the platform; the engine and its dialect are unchanged, so nothing needs translating.
• Heterogeneous: the engine changes. Oracle to Aurora PostgreSQL, SQL Server to Aurora MySQL, Teradata to Redshift. Data types, SQL syntax and procedure languages differ, so the schema and code must be rewritten before data can land.

#What DMS creates by itself (and what it does not)

The notes say DMS moves only data, not the schema. That is slightly too strong. When the target tables do not exist, DMS creates basic tables and their primary keys (and sometimes unique indexes), enough to load data efficiently. It does not create secondary indexes, foreign keys, defaults, views, sequences, stored procedures, functions or triggers. So even for a homogeneous move, you usually create the full schema first with the engine’s own tools (for example a schema-only pg_dump), and let DMS load the data into it.

#AWS Schema Conversion Tool (SCT)

AWS SCT is a free desktop application (Windows and Linux) that converts a database schema from one engine to another:

• It reads the source tables, data types, indexes, constraints, views, sequences, stored procedures, functions and triggers, and rewrites them for the target, for example Oracle’s procedure code into PostgreSQL’s PL/pgSQL or SQL Server’s T-SQL into MySQL.
• Its database migration assessment report says how much was converted automatically and lists action items: objects it could not convert, with an estimate of the manual effort. This report is how teams size a migration before committing.
• It applies the converted schema to the target, and then DMS moves the data into it.
• OLTP examples: Oracle or SQL Server to MySQL, PostgreSQL or Aurora. Data warehouse examples: Teradata, Oracle, Netezza, Greenplum, Vertica or other warehouses to Amazon Redshift, where SCT also picks distribution and sort keys and its data extraction agents move the bulk data through S3 (lesson 17.2).
• Conversion is CPU-heavy, so run SCT (and its agents) on a compute-optimized machine.

If the engine stays the same, you do not need SCT: on-premises PostgreSQL to RDS for PostgreSQL is still PostgreSQL.

#Full load, CDC and the cutover

#The three migration types

CDC (Change Data Capture) reads the source database’s transaction log, the record of every insert, update and delete (DML) the engine writes for its own recovery, and applies those changes to the target in commit order. A migration of a few terabytes can take days; CDC means the target catches up and then stays in step for as long as you need, so the final cutover loses nothing.

#What CDC needs from the source

CDC only works if the source keeps the right logs, in the right format, long enough. This is the most common reason a DMS task fails, and the notes do not mention it.

If the logs DMS still needs are deleted (the binlog retention ran out while the task was stopped), the task fails and the affected tables must be reloaded. On PostgreSQL the opposite happens: a stopped task’s slot holds every WAL file, and the source disk can fill up.

#The cutover

1. Let CDC catch up

   Watch the task’s CDC latency (source and target) in CloudWatch until it stays near zero. Run data validation so DMS compares rows on both sides.

2. Stop writes on the source

   At a quiet hour (3 a.m. for the telco), stop the application or put it in read-only mode. From now on nothing new enters the source.

3. Drain and check

   Wait until the last changes are applied (latency zero, no pending changes) and validation reports no mismatches. Create anything DMS did not migrate yet: secondary indexes, foreign keys, triggers, sequences set past the last value.

4. Switch and keep a way back

   Point the application at the target (a changed connection string, or a Route 53 record you own). Optionally start a reverse CDC task from the new database to the old one, so you can fall back if something goes wrong in the first days.

#Keeping the migration itself available

#Multi-AZ replication instance

A migration that runs CDC for weeks depends on one server. If the replication instance fails, the task stops; if it stays down long enough for the source to purge its logs, CDC cannot resume.

With Multi-AZ turned on, DMS provisions and keeps a standby replication instance in another Availability Zone, with its storage synchronously replicated from the primary. If the primary or its AZ fails, DMS fails over to the standby automatically and the task resumes from its last checkpoint. The standby serves nothing while waiting; it costs about the same as a second instance. The slides add the benefits AWS lists: data redundancy, no I/O freezes during backups, and fewer latency spikes.

#DMS Serverless

DMS Serverless removes the replication instance from your hands. You create a replication configuration (endpoints, migration type, table mappings, settings) and give a minimum and maximum capacity in DMS capacity units (DCUs); one DCU is 2 GB of memory. DMS checks the source, calculates and provisions capacity, scales it up and down while the replication runs (not down during a full load), and bills only the DCUs in use. It supports Multi-AZ too, and most common engines (Oracle, SQL Server, MySQL, MariaDB, PostgreSQL, MongoDB, DocumentDB, Db2, S3 as sources; relational engines, Redshift, S3, DynamoDB, Kinesis, MSK, OpenSearch, DocumentDB and Neptune as targets). Choose it when you cannot predict the load or do not want to size instances; choose a provisioned instance when you need an engine Serverless does not support or want fixed, predictable cost.

#Other task features worth knowing

• Premigration assessment: checks before the task starts (unsupported data types, missing primary keys, wrong log settings, LOB columns) and writes a report to S3.
• Data validation: compares source and target rows during and after the load and reports mismatches per table.
• Table mappings: selection rules (which schemas and tables) and transformation rules (rename a schema, add a column, change case).
• LOB modes: large object columns (big text, JSON, images) slow DMS down. Limited LOB mode (the default) is fast but truncates anything above the maximum size you set; full LOB mode copies everything, piece by piece, slowly; inline LOB mode mixes the two. Know your largest LOB before choosing.
• Pricing: you pay for the replication instance (per hour, more for Multi-AZ) or the Serverless DCUs, plus extra storage; data transfer into DMS is free, and DMS Schema Conversion is free.

#Native paths: RDS and Aurora, MySQL and PostgreSQL

For the same engine family inside AWS, DMS is often not the best tool. AWS’s own replication and snapshot features are simpler, cheaper and faster. The rule from the notes: within AWS and the same engine, prefer snapshots or read replicas; use DMS for on-premises sources, for engine changes, or when both databases must keep running side by side.

#RDS for MySQL to Aurora MySQL

• Option 1: snapshot restore. Take a DB snapshot of the RDS instance and restore (migrate) it as an Aurora MySQL cluster. Simple and cheap, but writes must stop from the snapshot until the restore ends, which takes hours for a large database.
• Option 2: Aurora read replica, then promote (the exam answer). Create an Aurora read replica of the RDS instance: RDS takes a private snapshot, builds an Aurora cluster from it, and then replicates every change through the binlog. When replica lag reaches 0, stop writes, promote the replica to a standalone cluster, and point the app at it. Downtime is minutes. It takes time (AWS says roughly several hours per TiB for the first copy) and you pay for both databases meanwhile. Only one Aurora read replica per RDS instance, and do not write to the replica before promotion, or replication breaks.

#External MySQL to Aurora MySQL

• Percona XtraBackup to S3: take a physical backup of the on-premises MySQL with Percona XtraBackup, upload the files to an S3 bucket, and create a new Aurora MySQL cluster from S3 (restore-db-cluster-from-s3). The fastest way to load a large database, already met in lesson 7.3.
• mysqldump: create an Aurora MySQL cluster and load a logical dump into it. Simple, but slower than the S3 method; fine for small databases.
• DMS, when both databases are up and running and the source must keep working: full load and CDC, or the native backup for the bulk plus a CDC-only task (or MySQL binlog replication into Aurora) for the changes.

#RDS for PostgreSQL to Aurora PostgreSQL

The same two options: restore a DB snapshot of RDS for PostgreSQL as an Aurora PostgreSQL cluster (downtime while it runs), or create an Aurora read replica of the RDS for PostgreSQL instance and promote it when lag is 0 (minutes of downtime).

#External PostgreSQL to Aurora PostgreSQL

• pg_dump and pg_restore (or a schema-only dump plus DMS for the data).
• The slides say: put a backup in S3 and import it with the aws_s3 extension. Precisely, aws_s3.table_import_from_s3 imports data files (CSV, text or binary COPY format) from S3 into existing tables; it does not restore a pg_dump backup. So you create the schema first, export each table to files, upload them, and import.
• Native logical replication (publication and subscription) or DMS with CDC, when both databases are running and downtime must be short.

#Choosing the path

#What goes wrong

• Test connection fails: no network path from the replication instance (missing VPN route, firewall or security group rule, private instance with no path), or a wrong user, password or SSL setting.
• CDC fails to start or stops: binary logging off, binlog_format not ROW, RDS automated backups off, Oracle supplemental logging missing, PostgreSQL logical replication off.
• Task fails after a pause: the source purged logs DMS still needed. Keep logs 24 hours or more, alarm on CDC latency, use Multi-AZ.
• Source disk fills up (PostgreSQL): a stopped or deleted task left its replication slot holding WAL. Resume the task or drop the slot.
• Target is slow and latency grows: no primary keys or indexes on the target for updates and deletes, an undersized replication instance, or LOB columns in full LOB mode.
• Truncated text or images on the target: limited LOB mode with a maximum size smaller than the real LOBs.
• Application errors after cutover: secondary indexes, foreign keys, triggers or sequences were never created on the target, or stored code was not converted (SCT action items ignored).
• Missing rows: a full-load-only task while writes continued, or cascaded deletes in MySQL that are not in the binlog.
• Aurora read replica broke: someone wrote to it before promotion. Recreate it.
• Wrong tool chosen: DMS for RDS MySQL to Aurora MySQL (works, but slower and costlier than a read replica), or DMS planned between two on-premises sites (not supported).

#DMS in system design

• Migration with a fallback: DMS full load and CDC into the new database, cutover, then a reverse CDC task back to the old database for a week, so a bad surprise means a switch back, not a restore.
• Engine modernisation: SCT plus DMS from Oracle or SQL Server to Aurora PostgreSQL to cut licence costs; Babelfish when the SQL Server app cannot be rewritten (lessons 16.1 and 16.2).
• Feeding analytics: ongoing CDC from an operational database into S3 (Parquet), Kinesis Data Streams or MSK, so a data lake or a Redshift warehouse gets changes within seconds without querying production (lesson 17.8). Zero-ETL integrations from Aurora and RDS to Redshift now cover part of this.
• Large moves: ship the bulk on Snowball or as a native backup, then run a CDC-only task from the recorded log position (lesson 23.5).
• Consolidation and splitting: table mappings let one task copy a few schemas out of a big database, or many small databases into one.

#Try it yourself

These commands use the AWS CLI v2 with made-up names, IDs and the documentation address range. A replication instance costs money every hour, so delete everything at the end.

# 1. A Multi-AZ replication instance in private subnets (the subnet group already exists)
aws dms create-replication-instance \
  --replication-instance-identifier telco-mig-ri \
  --replication-instance-class dms.r5.xlarge \
  --allocated-storage 200 --multi-az --no-publicly-accessible \
  --replication-subnet-group-identifier dms-private-subnets
# "ReplicationInstanceStatus": "creating", "MultiAZ": true

# 2. Source (on-premises Oracle over Direct Connect) and target (Aurora PostgreSQL) endpoints
aws dms create-endpoint --endpoint-identifier oracle-src --endpoint-type source \
  --engine-name oracle --server-name 10.20.1.15 --port 1521 \
  --database-name BILLING --username dms_user --password 'use-a-secret-instead'
aws dms create-endpoint --endpoint-identifier aurora-tgt --endpoint-type target \
  --engine-name aurora-postgresql \
  --server-name billing.cluster-abcdefexample.ap-south-1.rds.amazonaws.com \
  --port 5432 --database-name billing --username dms_user --password 'use-a-secret-instead'

# 3. Test both connections from the replication instance
aws dms test-connection \
  --replication-instance-arn arn:aws:dms:ap-south-1:111122223333:rep:EXAMPLERI \
  --endpoint-arn arn:aws:dms:ap-south-1:111122223333:endpoint:EXAMPLESRC
aws dms describe-connections --query 'Connections[].[EndpointIdentifier,Status]'
# [ [ "oracle-src", "successful" ], [ "aurora-tgt", "successful" ] ]

# 4. A full load and CDC task for the BILLING schema, with validation on
cat > mappings.json <<'EOF'
{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "billing",
  "object-locator": { "schema-name": "BILLING", "table-name": "%" }, "rule-action": "include" } ] }
EOF
aws dms create-replication-task --replication-task-identifier billing-full-cdc \
  --migration-type full-load-and-cdc \
  --source-endpoint-arn arn:aws:dms:ap-south-1:111122223333:endpoint:EXAMPLESRC \
  --target-endpoint-arn arn:aws:dms:ap-south-1:111122223333:endpoint:EXAMPLETGT \
  --replication-instance-arn arn:aws:dms:ap-south-1:111122223333:rep:EXAMPLERI \
  --table-mappings file://mappings.json \
  --replication-task-settings '{"ValidationSettings":{"EnableValidation":true}}'
aws dms start-replication-task --start-replication-task-type start-replication \
  --replication-task-arn arn:aws:dms:ap-south-1:111122223333:task:EXAMPLETASK

# 5. Progress per table
aws dms describe-table-statistics \
  --replication-task-arn arn:aws:dms:ap-south-1:111122223333:task:EXAMPLETASK \
  --query 'TableStatistics[].[TableName,FullLoadRows,Inserts,Updates,TableState,ValidationState]'
# [ [ "RECHARGES", 81200400, 52311, 0, "Table completed", "Validated" ],
#   [ "SUBSCRIBERS", 9400212, 1180, 40221, "Table completed", "Validated" ] ]

# 6. The same migration as DMS Serverless: a replication config with 2 to 32 DCUs
aws dms create-replication-config --replication-config-identifier billing-serverless \
  --replication-type full-load-and-cdc \
  --source-endpoint-arn arn:aws:dms:ap-south-1:111122223333:endpoint:EXAMPLESRC \
  --target-endpoint-arn arn:aws:dms:ap-south-1:111122223333:endpoint:EXAMPLETGT \
  --table-mappings file://mappings.json \
  --compute-config 'MinCapacityUnits=2,MaxCapacityUnits=32,MultiAZ=true,ReplicationSubnetGroupId=dms-private-subnets'

-- 7. On an RDS for MySQL source, keep binlogs 24 hours so CDC survives a pause
call mysql.rds_set_configuration('binlog retention hours', 24);
call mysql.rds_show_configuration;
-- binlog retention hours | 24

# 8. RDS MySQL to Aurora MySQL the native way: an Aurora read replica cluster, then promote
aws rds create-db-cluster --db-cluster-identifier shop-aurora --engine aurora-mysql \
  --replication-source-identifier arn:aws:rds:ap-southeast-1:111122223333:db:shop-mysql \
  --db-subnet-group-name shop-db-subnets --vpc-security-group-ids sg-0abcd1234example
aws rds create-db-instance --db-cluster-identifier shop-aurora \
  --db-instance-identifier shop-aurora-1 --db-instance-class db.r6g.large --engine aurora-mysql
# ...when replica lag is 0 and writes to RDS are stopped:
aws rds promote-read-replica-db-cluster --db-cluster-identifier shop-aurora

# 9. External MySQL to Aurora from Percona XtraBackup files in S3
aws rds restore-db-cluster-from-s3 --db-cluster-identifier erp-aurora \
  --engine aurora-mysql --master-username admin --manage-master-user-password \
  --source-engine mysql --source-engine-version 8.0.36 \
  --s3-bucket-name erp-backup-example --s3-prefix xtrabackup/2026-11-01 \
  --s3-ingestion-role-arn arn:aws:iam::111122223333:role/aurora-s3-import

# 10. Clean up
aws dms delete-replication-task --replication-task-arn arn:aws:dms:ap-south-1:111122223333:task:EXAMPLETASK
aws dms delete-replication-instance --replication-instance-arn arn:aws:dms:ap-south-1:111122223333:rep:EXAMPLERI

Also try in the console: open a migration project in DMS Schema Conversion, run the assessment on a sample schema, and read which objects it marks as needing manual work.

#Summary

Key takeaways

• DMS moves data between databases while the source stays operational: a replication instance (or DMS Serverless, sized in DCUs) reads a source endpoint and writes a target endpoint; downtime at cutover is minimal, not zero.
• At least one endpoint must be in AWS; on-premises to on-premises, or one cloud to another, is not supported.
• Sources include Oracle, SQL Server, MySQL, MariaDB, PostgreSQL, MongoDB, SAP ASE, Db2, Azure SQL, RDS, Aurora, S3, DocumentDB; targets add Redshift, DynamoDB, S3, OpenSearch Service (not “Elasticsearch”), Kinesis, Kafka/MSK, DocumentDB, Neptune, Redis, Babelfish.
• Homogeneous (same engine): no SCT. Heterogeneous (Oracle → PostgreSQL, SQL Server → MySQL): SCT (or DMS Schema Conversion) converts schema and code, its report lists action items, then DMS moves the data. DMS itself creates only tables and primary keys. SCT also converts warehouses to Redshift.
• Task types: full load, full load and CDC (the usual one), CDC only. CDC reads the transaction log and needs it kept: binlog in ROW format with retention, Oracle supplemental logging, PostgreSQL logical replication (watch the slot).
• Multi-AZ replication instance = synchronous standby in another AZ with automatic failover: the answer for a fault-tolerant migration.
• Inside AWS, same engine: RDS MySQL/PostgreSQL → Aurora by Aurora read replica + promote (minutes) or snapshot restore (hours). External MySQL → Percona XtraBackup in S3 or mysqldump; external PostgreSQL → pg_dump, aws_s3 file import, or DMS when both must keep running.

Self-check: DMS, SCT and database migrations30 questions