Why This Matters

AWS Database Migration Service (DMS) is the backbone of cloud migration strategies across enterprises. Whether you are migrating a 10 TB Oracle data warehouse to Aurora PostgreSQL or replicating production MySQL to Redshift for analytics, DMS provides the reliability and CDC capabilities required for zero-downtime migrations. Understanding DMS internals, capacity planning, and failure recovery is essential for any data engineer working on modernization projects.

Key Insight: DMS does not convert schemas - it moves data. For heterogeneous migrations, AWS SCT (Schema Conversion Tool) must be used alongside DMS to transform the schema structure. This distinction is critical in interview settings.

AWS DMS Architecture

AWS DMS Migration Architecture

How DMS Works: The Migration Pipeline

AWS DMS operates on a task-based model with three migration types. Understanding when to use each is critical for designing reliable migration strategies.

Migration Types

DMS Task Lifecycle

1. Source endpoint connects to the database using native drivers
2. Full load reads tables in parallel, batches rows to replication instance
3. CDC start reads transaction logs (Oracle redo, MySQL binlog, PostgreSQL WAL)
4. Replication applies changes with conflict detection and retry logic
5. Target endpoint writes to destination using bulk insert or upsert patterns
6. Validation compares source and target row counts and checksums (optional)

Real-World Project Structure

dms-migration-project/
├── infrastructure/
│   ├── cloudformation/
│   │   ├── dms-replication-instance.yaml
│   │   ├── dms-endpoints.yaml
│   │   └── dms-tasks.yaml
│   └── terraform/
│       ├── main.tf
│       ├── endpoints.tf
│       └── variables.tf
├── scripts/
│   ├── create-replication-instance.py
│   ├── create-endpoints.py
│   ├── create-task.py
│   ├── monitor-task.py
│   └── validate-migration.py
├── configs/
│   ├── table-mappings/
│   │   ├── full-load.json
│   │   ├── cdc-only.json
│   │   └── full-load-cdc.json
│   └── task-settings/
│       ├── oracle-source.json
│       ├── mysql-source.json
│       └── postgres-target.json
├── monitoring/
│   ├── cloudwatch-dashboard.json
│   └── alarms.yaml
├── validation/
│   ├── row-count-check.py
│   ├── checksum-validation.py
│   └── schema-comparison.sql
└── docs/
    ├── migration-plan.md
    ├── rollback-procedures.md
    └── runbook.md

Production Code: Creating DMS Resources

Create Replication Instance with Boto3

import boto3
import json
import logging
import sys
from botocore.exceptions import ClientError

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)


def create_replication_instance(
    instance_id: str,
    instance_class: str = "dms.r5.2xlarge",
    allocated_storage: int = 500,
    vpc_security_group_ids: list = None,
    multi_az: bool = True
) -> dict:
    """
    Create a DMS replication instance with production-grade settings.

    Args:
        instance_id: Unique identifier for the replication instance
        instance_class: Instance type (dms.t3.medium to dms.r5.4xlarge)
        allocated_storage: Storage in GB (5 to 6144)
        vpc_security_group_ids: List of VPC security group IDs
        multi_az: Enable Multi-AZ for high availability

    Returns:
        dict with replication instance details
    """
    dms_client = boto3.client('dms')

    try:
        response = dms_client.create_replication_instance(
            ReplicationInstanceIdentifier=instance_id,
            ReplicationInstanceClass=instance_class,
            AllocatedStorage=allocated_storage,
            MultiAZ=multi_az,
            EngineVersion='3.5.2',
            AutoMinorVersionUpgrade=True,
            PubliclyAccessible=False,
            VpcSecurityGroupIds=vpc_security_group_ids or [],
            Tags=[
                {'Key': 'Environment', 'Value': 'production'},
                {'Key': 'ManagedBy', 'Value': 'boto3'}
            ]
        )

        instance_arn = response['ReplicationInstance']['ReplicationInstanceArn']
        logger.info(f"Created replication instance: {instance_arn}")

        waiter = dms_client.get_waiter('replication_instance_available')
        logger.info("Waiting for instance to become available...")
        waiter.wait(
            Filters=[{'Name': 'replication-instance-id', 'Values': [instance_id]}]
        )
        logger.info("Replication instance is available")

        return response['ReplicationInstance']

    except ClientError as e:
        error_code = e.response['Error']['Code']
        if error_code == 'ResourceAlreadyExistsFault':
            logger.warning(f"Instance {instance_id} already exists, fetching existing")
            return describe_replication_instance(instance_id)
        raise


def describe_replication_instance(instance_id: str) -> dict:
    """Retrieve details of an existing replication instance."""
    dms_client = boto3.client('dms')
    response = dms_client.describe_replication_instances(
        Filters=[{'Name': 'replication-instance-id', 'Values': [instance_id]}]
    )
    if response['ReplicationInstances']:
        return response['ReplicationInstances'][0]
    raise ValueError(f"Replication instance {instance_id} not found")


def create_source_endpoint(
    endpoint_id: str,
    engine_name: str,
    server_name: str,
    port: int,
    database_name: str,
    username: str,
    password: str,
    instance_arn: str,
    ssl_mode: str = "require"
) -> dict:
    """Create a DMS source database endpoint with SSL support."""
    dms_client = boto3.client('dms')

    endpoint_config = {
        'EndpointIdentifier': endpoint_id,
        'EndpointType': 'source',
        'EngineName': engine_name,
        'ServerName': server_name,
        'Port': port,
        'DatabaseName': database_name,
        'Username': username,
        'Password': password,
        'SslMode': ssl_mode,
        'ExtraConnectionAttributes': '',
        'Tags': [
            {'Key': 'Environment', 'Value': 'production'},
            {'Key': 'Role', 'Value': 'source'}
        ]
    }

    if engine_name == 'oracle':
        endpoint_config['ExtraConnectionAttributes'] = (
            'addSupplementalLogging=Y;'
            'failTransactionsOnLobTruncation=Y;'
            'charSet=CLEAR'
        )
    elif engine_name == 'mysql':
        endpoint_config['ExtraConnectionAttributes'] = (
            'initstmt=SET FOREIGN_KEY_CHECKS=0;'
            'afterConnectScript='
            'SET SESSION wait_timeout=28800'
        )

    try:
        response = dms_client.create_endpoint(**endpoint_config)
        endpoint_arn = response['Endpoint']['EndpointArn']
        logger.info(f"Created source endpoint: {endpoint_arn}")

        test_connection(dms_client, endpoint_arn, instance_arn)
        return response['Endpoint']

    except ClientError as e:
        logger.error(f"Failed to create endpoint: {e.response['Error']['Message']}")
        raise


def test_connection(dms_client, endpoint_arn: str, instance_arn: str) -> bool:
    """Test connectivity between replication instance and endpoint."""
    try:
        response = dms_client.test_connection(
            EndpointArn=endpoint_arn,
            ReplicationInstanceArn=instance_arn
        )
        status = response['Connection']['Status']
        logger.info(f"Connection test status: {status}")
        return status == 'successful'
    except ClientError as e:
        logger.error(f"Connection test failed: {e.response['Error']['Message']}")
        return False


if __name__ == "__main__":
    ri = create_replication_instance(
        instance_id="prod-dms-replication-01",
        instance_class="dms.r5.2xlarge",
        allocated_storage=500,
        multi_az=True
    )
    print(json.dumps(ri, indent=2, default=str))

Table Mapping: Full Load + CDC

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "include-all-tables",
      "object-locator": {
        "schema-name": "production",
        "table-name": "%"
      },
      "rule-action": "include"
    },
    {
      "rule-type": "selection",
      "rule-id": "2",
      "rule-name": "cdc-primary-key",
      "object-locator": {
        "schema-name": "production",
        "table-name": "orders"
      },
      "rule-action": "include",
      "filters": [
        {
          "filter-name": "active-only",
          "filter-type": "numeric",
          "filter-operator": "gte",
          "filter-value": "1"
        }
      ]
    },
    {
      "rule-type": "transformation",
      "rule-id": "3",
      "rule-name": "rename-schema",
      "rule-action": "rename",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "production"
      },
      "value": "analytics"
    },
    {
      "rule-type": "transformation",
      "rule-id": "4",
      "rule-name": "convert-timestamp",
      "rule-action": "convert-lowercase",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "production",
        "table-name": "orders",
        "column-name": "created_at"
      }
    }
  ]
}

Mathematical Formulas

DMS Capacity Planning

Required Replication Instances = ceil(Source_IOPS / Instance_Max_IOPS)

Throughput Formula:
  Replication_Lag (seconds) = CDC_Change_Rate (MB/s) / Instance_Throughput (MB/s)

Storage Required:
  Storage (GB) = (Full_Load_Size * 1.2) + (Daily_CDC_Change_GB * Retention_Days * 1.3)

DMS Cost Estimation

Monthly Cost = (Instance_Hours * Price_Per_Hour) + (Data_Transferred * Price_Per_GB)
Instance_Hours = 24 * Days_In_Month

Performance Considerations

Security Considerations

Interview Questions & Answers

Q1: What is the difference between Full Load and CDC in AWS DMS?

Answer: Full Load is a one-time bulk copy of the entire source table to the target. It reads the source data in batches and writes it to the target without capturing ongoing changes. CDC (Change Data Capture) captures ongoing insert, update, and delete operations from the source database transaction logs. Full Load + CDC first performs a bulk copy, then switches to continuous replication. Full Load alone is suitable for static datasets or initial migration. CDC is essential when you need to keep source and target synchronized during and after migration. The key trade-off is that Full Load requires a maintenance window while CDC enables near-zero downtime.

Q2: How does AWS DMS handle LOB (Large Object) data during migration?

Answer: DMS handles LOBs through three modes:

1. Limited LOB mode - Truncates LOBs larger than a specified max size (default 32KB). Fastest option when you know LOB sizes are bounded.
2. Full LOB mode - Handles LOBs of any size but is significantly slower because DMS must query the source for each LOB length. Can cause replication lag.
3. Inline LOB mode - Optimized for LOBs up to 16KB by embedding them in the row data. Best balance for mixed LOB workloads.

For Oracle, DMS can use Oracle LOB APIs to read LOBs efficiently. The recommendation is to use Limited LOB mode when possible and set max LOB size to cover 99% of your data.

Q3: What is the role of AWS SCT in the migration process?

Answer: AWS Schema Conversion Tool (SCT) analyzes source database schemas and converts them to be compatible with the target database engine. It is required for heterogeneous migrations where source and target use different engines (e.g., Oracle to PostgreSQL). SCT produces assessment reports showing conversion complexity, estimated effort, and manual intervention requirements. It handles:

• Data type conversions (VARCHAR2 to VARCHAR, NUMBER to NUMERIC)
• Stored procedure and function conversions
• Table and index DDL generation
• Data validation rules

SCT works alongside DMS - SCT handles schema conversion while DMS handles data movement. After SCT converts the schema, DMS migrates the data using the converted schema.

Q4: How do you minimize replication lag in a CDC migration?

Answer: Strategies to minimize CDC replication lag:

1. Increase replication instance size - Larger instances provide more CPU and memory for change processing
2. Reduce LOB overhead - Use Limited LOB mode instead of Full LOB mode
3. Optimize source logging - Ensure transaction logs have sufficient retention and minimal archival lag
4. Batch apply changes - Enable parallel apply for supported engines (PostgreSQL, Oracle)
5. Minimize network latency - Deploy replication instance in the same VPC as source/target
6. Reduce transaction size - Large bulk operations in source can cause lag spikes
7. Monitor CloudWatch metrics - CDCLatencyTarget tracks lag in real-time
8. Scale target IOPS - Ensure target database has sufficient write capacity

Q5: When would you use DMS Serverless vs. provisioned replication instances?

Answer: DMS Serverless is ideal for variable or unpredictable migration workloads where you want to avoid capacity planning. It automatically provisions and scales compute based on workload requirements. Use Serverless for:

• Ad-hoc migrations with unknown data volumes
• CDC workloads with variable change rates
• Development and testing environments
• Cost-sensitive projects where you want to pay only for compute used

Use provisioned instances for:

• Predictable, steady-state replication workloads
• Long-running CDC migrations with known throughput requirements
• Production environments requiring specific instance types
• When you need Multi-AZ for high availability

Serverless pricing is based on DMS capacity units (DCUs) consumed, while provisioned pricing is based on instance hours.

Q6: How do you validate a DMS migration was successful?

Answer: A comprehensive validation strategy includes:

1. Row count comparison - Compare source and target row counts for each table
2. Checksum validation - Use DMS table statistics or custom SQL to compare data checksums
3. Schema validation - Compare column names, types, constraints between source and target
4. Data type mapping - Verify that source types mapped correctly to target types
5. Null handling - Check that NULL values migrated correctly
6. Date/time precision - Validate timestamp precision and timezone handling
7. LOB completeness - Verify large objects were not truncated
8. Constraint validation - Check primary keys, foreign keys, and indexes exist on target

Use DMS validation settings with ValidationMode = ROW_LEVEL for comprehensive checking. Run validation queries in parallel for speed.

Q7: What are common DMS failure scenarios and how do you recover from them?

Answer: Common failure scenarios:

Recovery steps:

1. Check CloudWatch logs for specific error messages
2. Verify network connectivity between replication instance and endpoints
3. Check source database log settings and permissions
4. Restart the task (DMS resumes from the last checkpoint for CDC)
5. For Full Load failures, recreate the task and truncate the target table

Q8: How do you handle schema changes during an ongoing CDC migration?

Answer: Schema changes during CDC migration require careful handling:

1. Stop the DMS task before making schema changes to the source
2. Apply schema change to the source database (ADD COLUMN, ALTER COLUMN)
3. Apply equivalent change to the target database
4. Update DMS task settings if column mapping changed
5. Restart the DMS task - it will resume from the last committed transaction

DMS does not automatically replicate DDL changes. For databases that support it (MySQL, PostgreSQL), you can enable DDL logging via task settings. For Oracle, use the supplemental logging to capture DDL operations.

Best practice: Test schema changes in a staging environment first, and use a blue-green deployment pattern for complex migrations.

Common Pitfalls

Why This Matters for Your Career

DMS expertise is among the most requested skills in data engineering job postings. Organizations migrating to AWS need engineers who can design reliable migration strategies, handle CDC complexities, and validate data integrity. Mastering DMS concepts, capacity planning, and failure recovery patterns will significantly enhance your candidacy for cloud migration roles.

Key Takeaways

• DMS supports Full Load, CDC, and combined migration modes for different scenarios
• AWS SCT is required for heterogeneous migrations to convert schemas between engines
• LOB handling mode significantly impacts replication performance
• VPC placement and instance class are critical for throughput and security
• Comprehensive validation ensures data integrity throughout the migration process

See Also

• AWS Glue ETL
• AWS Redshift
• AWS Kinesis Streaming