2.1 AWS DMS (Database Migration Service) & Change Data Capture (CDC)

2.1 AWS DMS (Database Migration Service) & Change Data Capture (CDC)

Quick Answer: AWS Database Migration Service (AWS DMS) migrates databases to AWS quickly and securely while keeping the source database operational during migration. For homogeneous migrations (e.g., PostgreSQL to RDS PostgreSQL), DMS handles both schema and data replication. For heterogeneous migrations (for example, Oracle to PostgreSQL), assess and convert schema definitions with DMS Schema Conversion before DMS moves table data. The legacy desktop AWS SCT can still appear in older material, but current AWS guidance recommends the managed DMS capability.

---

AWS DMS Core Architecture

AWS DMS operates using three fundamental components: Replication Instances, Endpoints, and Replication Tasks.

+-----------------------------------------------------------------------------------+
|                                AWS DMS Architecture                               |
|                                                                                   |
|  +-------------------+       +-----------------------+       +-----------------+  |
|  |  Source Database  | ----> | Replication Instance  | ----> | Target Service  |  |
|  | (Oracle/Postgres) |       | (Source/Target Endpts)|       | (Redshift/S3)   |  |
|  +-------------------+       |  - Full Load Task     |       +-----------------+  |
|                              |  - CDC Engine Task    |                            |
|                              +-----------------------+                            |
+-----------------------------------------------------------------------------------+

1. Replication Instance

A managed EC2 instance running within an AWS VPC that hosts one or more replication tasks. DMS offers compute-optimized (c5), memory-optimized (r5), and general-purpose (dms.t3) instances.

• Multi-AZ Deployment: Highly recommended for ongoing CDC tasks to ensure high availability and automatic failover across Availability Zones.
• Storage Allocation: Local EBS storage holds task logs and cached change data during change processing.

2. Endpoints

Endpoints define connection details, authentication credentials, network settings, and engine configurations for source and target stores.

• Source Endpoints: Relational databases (RDS, EC2, on-premises Oracle, SQL Server, MySQL, PostgreSQL, DB2), S3, Azure SQL DB.
• Target Endpoints: Amazon RDS, Aurora, Amazon Redshift, Amazon S3, Amazon Kinesis Data Streams, Amazon MSK (Kafka), Amazon OpenSearch, DynamoDB.

3. Replication Tasks

Tasks define what data is migrated and how. AWS DMS supports three task types:

1. Full Load (Migrate existing data): Extracts all records from source tables and writes them to the target. Tables can be loaded in parallel.
2. Full Load + CDC (Migrate existing data and replicate ongoing changes): Captures changes while full load is executing, buffers them on the replication instance, and applies them after full load completes.
3. CDC Only (Replicate ongoing changes): Starts capturing data changes from a specified Log Sequence Number (LSN), System Change Number (SCN), or timestamp.

---

Change Data Capture (CDC) Mechanics

AWS DMS CDC does not poll tables using SELECT statements (which creates heavy source database overhead). Instead, it reads the engine-native transaction logs of the source database.

Transaction Log Retention Requirements

If DMS experiences network downtime or replication instance failover, it must resume reading from the exact LSN/SCN where it left off. If the source database purges transaction logs before DMS reads them, the CDC task fails with an unrecoverable log position error. You must adjust log retention policies:

• PostgreSQL: Configure wal_keep_size or max_slot_wal_keep_size.
• MySQL: Set binlog_expire_logs_seconds to at least 24–48 hours.
• Oracle: Retain archived redo logs on disk for 24+ hours.

---

DMS Schema Conversion Workflow

While DMS converts basic data types during full load, it does not convert complex database objects such as stored procedures, triggers, views, secondary indexes, or foreign keys in heterogeneous migrations.

Homogeneous Migration:   Source DB ------ (DMS Task Handles Schema & Data) ------> Target DB
Heterogeneous Migration: Source DB --> [DMS Schema Conversion] --> Target DB --> [DMS CDC Data Task]

Heterogeneous Migration Pipeline

1. Run DMS schema assessment report: Evaluates the source schema and generates an executive report listing items that can be automatically converted versus items requiring manual refactoring.
2. Convert schema with DMS Schema Conversion: Converts schema objects (DDL) into the target dialect (e.g., converting Oracle PL/SQL to PostgreSQL PL/pgSQL) and applies them to the target database.
3. Drop Foreign Keys & Secondary Indexes: Temporarily drop target constraints/indexes to maximize DMS full load write performance.
4. Run DMS Full Load + CDC: Execute data ingestion via DMS.
5. Re-apply Indexes & Constraints: Re-create foreign keys and indexes after Full Load completes before final cutover.

---

Large Object (LOB) Optimization Modes

LOB columns (e.g., CLOB, BLOB, VARCHAR(MAX), TEXT) present major migration bottlenecks. DMS handles LOBs using three distinct modes:

1. Limited LOB Mode (Fastest)

• Truncates LOB data at a specified MaxLOBSize (e.g., 32 KB).
• Performance: Extremely high. All data is fetched in a single query.
• Use Case: When LOB size is known and uniform across all tables.

2. Full LOB Mode (Slowest)

• Does not truncate LOB data regardless of size.
• Performance: Very slow. DMS extracts non-LOB columns first, then queries the source table row-by-row to fetch LOB data individually.
• Use Case: Required when LOBs exceed hundreds of megabytes and cannot be truncated.

3. Inline LOB Mode (Recommended Compromise)

• With Full LOB mode enabled, transfers small LOBs inline with tabular data up to InlineLobMaxSize (for example, 64 KB). Larger LOBs fall back to Full LOB mode instead of being truncated.
• Performance: Speeds up small LOBs while preserving large values, but the target endpoint must support both Full LOB mode and Inline LOB mode.

---

DMS Performance & Advanced Configuration Settings

To optimize throughput on large tables or high-volume CDC streams, data engineers configure extra connection attributes and task JSON settings:

{
  "TargetMetadata": {
    "SupportLobs": true,
    "FullLobMode": false,
    "LimitedSizeLobMode": true,
    "LobMaxSize": 64,
    "BatchApplyEnabled": true
  },
  "FullLoadSettings": {
    "TargetTablePrepMode": "DROP_AND_CREATE",
    "CreatePkAfterFullLoad": true,
    "MaxFullLoadSubTasks": 8,
    "CommitRate": 10000
  },
  "ValidationSettings": {
    "EnableValidation": true,
    "ValidationMode": "ROW_LEVEL"
  }
}

Key Parameters Defined:

• BatchApplyEnabled=true: Switches CDC from executing individual one-by-one SQL statements (INSERT, UPDATE, DELETE) to compiling changes into bulk staging tables and executing set-based bulk upserts. Drastically reduces target CPU and write I/O.
• MaxFullLoadSubTasks: Controls how many tables DMS can load in parallel during full load; table-level parallel-load settings are endpoint-specific and should be verified for the selected target.
• ValidationMode=ROW_LEVEL: Compares source and target records after Full Load and CDC to confirm data integrity, generating detailed CloudWatch validation failure metrics.