Database Migration with AWS DMS

Moving databases to the cloud — or between AWS services — is a common task for organisations modernising their infrastructure. The AWS Database Migration Service (DMS) makes it possible to migrate databases with minimal downtime by continuously replicating data from the source to the target while your source database remains fully operational.

---

What Is AWS DMS?

AWS DMS is a managed service that migrates data between databases. It supports:

• Homogeneous migrations — same engine on both sides (e.g., Oracle to Oracle, MySQL to MySQL)
• Heterogeneous migrations — different engines (e.g., Oracle to Aurora PostgreSQL, SQL Server to MySQL)
• Continuous replication — ongoing change data capture (CDC) after the initial full load

Key Components

Component	Role
Source endpoint	Connection to the source database (on-premises, EC2, or another cloud)
Target endpoint	Connection to the target database (RDS, Aurora, DynamoDB, S3, Redshift, etc.)
Replication instance	An EC2 instance managed by DMS that runs the migration tasks
Migration task	Configuration that defines what to migrate, how, and when

---

Supported Sources and Targets

Sources

Source	Versions	Notes
Oracle	10g, 11g, 12c, 18c, 19c, 21c	Requires supplemental logging for CDC
SQL Server	2008 R2 – 2022	Requires MS-CDC or MS-Replication
MySQL	5.5 – 8.0	Binary logging must be enabled
PostgreSQL	9.4 – 16	Logical replication or pglogical plugin
MariaDB	10.0 – 10.11	Binary logging
MongoDB	3.x, 4.x, 5.x, 6.x	Document mode or table mode
Amazon S3	–	CSV, Parquet, or JSON files
Amazon Aurora	MySQL / PostgreSQL	Same as MySQL / PostgreSQL

Targets

Target	Notes
Amazon RDS (all engines)	Most common target for relational migrations
Amazon Aurora	Recommended for MySQL/PostgreSQL migrations
Amazon DynamoDB	Use for relational-to-NoSQL modernisation
Amazon S3	Archive, data lake, or staging area
Amazon Redshift	Data warehouse ingestion
Amazon OpenSearch	Full-text search and analytics
Amazon Neptune	Graph database
Amazon Kinesis Data Streams	Real-time streaming target

---

Migration Types

Type	Behaviour	Use Case
Full load	Migrates all existing data in one pass	One-time migration, small databases
Full load + CDC	Migrates existing data, then captures and applies ongoing changes	Most common; supports cutover with minimal downtime
CDC only	Captures and replicates only changes (no initial data load)	When you've already loaded the data by another method

---

Homogeneous vs Heterogeneous Migrations

Homogeneous (Same Engine)

Oracle → Oracle on RDS
MySQL → Aurora MySQL
PostgreSQL → Aurora PostgreSQL

These are straightforward: DMS reads from the source and writes to the target. Schema structures, data types, and SQL syntax are compatible.

Heterogeneous (Different Engines)

Oracle → Aurora PostgreSQL
SQL Server → MySQL
Oracle → DynamoDB

Heterogeneous migrations require an additional step: schema conversion. You use the AWS Schema Conversion Tool (SCT) to convert:

• Table definitions, views, stored procedures, and functions
• Data types (e.g., Oracle NUMBER → PostgreSQL NUMERIC)
• SQL syntax differences (e.g., Oracle's DECODE → PostgreSQL's CASE)

SCT generates a report highlighting:

Category	Meaning
🟢 Green	Automatically converted — no action needed
🟡 Yellow	Converted with minor manual adjustments
🔴 Red	Cannot be automatically converted — requires manual rewriting

---

Step-by-Step Migration Process

1. Assess

• Run SCT (for heterogeneous) to identify conversion issues
• Estimate the data volume and network bandwidth requirements
• Decide on the replication instance size

2. Set Up Source and Target

Source: On-premises Oracle 19c
  - Enable supplemental logging
  - Create a DMS user with SELECT and CDC privileges

Target: Aurora PostgreSQL 15
  - Create the database and schema (using SCT output)
  - Configure security group to allow traffic from the DMS replication instance

3. Create a Replication Instance