Database Migration Service Expertise

AWS DMS Operational Review & Troubleshooting

Conduct comprehensive operational reviews, best practices validation, troubleshooting, and cost optimization assessments for AWS Database Migration Service (DMS) environments aligned with AWS Well-Architected principles.

When to Use

Activate this skill when the user asks to:

• Review, audit, or assess DMS configurations
• Check DMS best practices compliance
• Troubleshoot DMS task failures, latency, or connectivity issues
• Evaluate DMS cost optimization opportunities
• Plan or execute a database migration cutover
• Assess DMS version deprecations and upgrade paths
• Run a pre-migration assessment
• Evaluate DMS Serverless vs provisioned suitability
• Investigate data validation mismatches
• Review DMS homogeneous data migration options

Step 1: Identify Target Resources

Ask the user which DMS resources to review. Accept:

• Specific replication instance names and regions
• Specific replication task identifiers
• "all instances" in a specific region
• "all instances in all regions"

Query AWS APIs to discover DMS resources:

• List replication instances across the configured account regions
• List replication tasks and their states
• List endpoints and connectivity status

Step 2: Collect DMS Configuration

For EACH replication instance and its associated tasks, gather the following data using AWS APIs.

2.1 Replication Instance Config

Engine version, instance class, Multi-AZ, storage, public accessibility, KMS encryption, auto-upgrade, VPC/subnet configuration, pending maintenance actions.

bashCopy code
aws dms describe-replication-instances --profile <profile> --region <region>
aws dms describe-pending-maintenance-actions --profile <profile> --region <region>

2.2 Replication Tasks

Task identifiers, status, migration type, table mappings, task settings, CDC start positions, last failure messages, stop reasons, replication task statistics.

bashCopy code
aws dms describe-replication-tasks --profile <profile> --region <region>

2.3 Endpoints

Endpoint identifiers, types (source/target), engine names, SSL mode, connection status, Secrets Manager integration, security group configurations.

bashCopy code
aws dms describe-endpoints --profile <profile> --region <region>
aws dms describe-connections --filter Name=endpoint-arn,Values=<endpoint-arn> --profile <profile> --region <region>

2.4 Table Statistics

Per-table state, row counts, validation state, error rows, DDL counts.

bashCopy code
aws dms describe-table-statistics --replication-task-arn <task-arn> --profile <profile> --region <region>

2.5 DMS Serverless (if applicable)

Replication configs, serverless replications, DCU utilization.

bashCopy code
aws dms describe-replication-configs --profile <profile> --region <region>
aws dms describe-replications --profile <profile> --region <region>

2.6 Version and Deprecation Data

Available engine versions, pending maintenance actions, AWS Health events.

bashCopy code
aws dms describe-orderable-replication-instances --query "OrderableReplicationInstances[*].EngineVersion" --profile <profile> --region <region>
aws health describe-events --filter '{"services":["DMS"],"eventTypeCategories":["scheduledChange"]}' --profile <profile> --region us-east-1

Note: AWS Health API is a global service and must be called with --region us-east-1 regardless of where DMS resources are deployed.

Step 3: Collect Observability Data (7-Day Historical)

3.1 CloudWatch Metrics (7 days)

Replication Instance Metrics (namespace: AWS/DMS):

• CPUUtilization (Average, Maximum)
• FreeableMemory (Average, Minimum)
• SwapUsage (Average, Maximum)
• DiskQueueDepth (Average, Maximum)
• ReadIOPS / WriteIOPS (Average, Maximum)
• ReadLatency / WriteLatency (Average, Maximum)
• NetworkTransmitThroughput (Average)
• NetworkReceiveThroughput (Average)

Task Metrics (namespace: AWS/DMS):

• CDCLatencySource (Average, Maximum)
• CDCLatencyTarget (Average, Maximum)
• CDCIncomingChanges (Average, Maximum)
• CDCChangesMemorySource (Average, Maximum)
• CDCChangesDiskSource (Maximum)
• CDCChangesDiskTarget (Maximum)
• FullLoadThroughputBandwidthTarget (Average)
• FullLoadThroughputRowsTarget (Average)

DMS Serverless (namespace: AWS/DMS, if applicable):

• ServerlessTotalCapacityUnits (Average, Maximum)

3.2 CloudWatch Logs (7 days)

Query task logs for error patterns:

• ERROR — general errors (count)
• Network/connectivity errors (Network error, Connection lost, timeout)
• Memory/resource errors (out of memory, OOM, swap)
• CDC-specific errors (WAL, binlog, LogMiner patterns)
• Data/constraint errors (truncation, constraint, duplicate key)

3.3 AWS Health Events

Query DMS scheduled changes and deprecation notices:

• Upcoming engine version deprecations
• Pending mandatory maintenance actions
• Certificate expirations

Step 4: Analyze Against Best Practices

Evaluate ALL collected data against these categories. Assign severity to every finding: CRITICAL, HIGH, MEDIUM, LOW, or INFO.

Reference: references/dms-best-practices.md for detailed best practices. Reference: references/dms-validation-checklist.md for validation criteria and thresholds. Reference: references/dms-version-reference.md for version lifecycle and known issues.

4.1 Instance Health (25 points)

• Instance status (must be "available")
• Engine version currency (EOL → CRITICAL; not on latest or latest-1 active version → HIGH)
• Instance class appropriate (CPU < 80%, Memory > 20% free)
• Multi-AZ for production CDC tasks
• Storage utilization (< 80%)
• Not publicly accessible
• KMS encryption enabled
• Auto minor version upgrade enabled (non-prod)

4.2 Task Health (25 points)

• Task status (running/stopped, not failed/error)
• No tables in error/suspended state
• Logging enabled on all tasks
• CDC latency within thresholds (< 120s)
• Recovery checkpoint exists
• No full load error rows
• Task settings follow best practices (LOB mode, parallelism, batch apply)

4.3 Endpoint Security (20 points)

• SSL/TLS enabled on all endpoints (SslMode != "none")
• Secrets Manager integration for credentials
• Connection tests passing
• Security groups restrict access (no 0.0.0.0/0)
• VPC endpoints configured for AWS services
• Private subnet placement (not publicly accessible)

4.4 Performance (15 points)

• CPU utilization within thresholds (< 80% avg)
• Freeable memory adequate (> 2 GB minimum)
• No swap usage (SwapUsage = 0)
• Disk queue depth normal (< 10)
• No sustained disk-based CDC caching (CDCChangesDisk* = 0)
• CDC throughput adequate for workload

4.5 Cost Efficiency (15 points)

• No over-provisioned instances (CPU avg > 20% for 7+ days)
• No idle instances (no running tasks for 7+ days)
• Multi-AZ justified (production/critical only)
• Debug logging disabled in production
• Same-engine migrations using homogeneous/native tools where applicable
• DMS Serverless evaluated for variable workloads

Step 5: Troubleshooting (When Applicable)

When the user reports an active issue, follow this structured approach:

Phase 0: Version Check (Always First)

Check DMS engine version and cross-reference with references/dms-version-reference.md for known bugs.

Phase 1: Error Classification

Route by error category:

Phase 2: Targeted Diagnostics

Follow the appropriate diagnostic path using AWS CLI commands to gather evidence.

Phase 3: Log Analysis

Query CloudWatch Logs with engine-specific filter patterns (Oracle/PostgreSQL/MySQL/SQL Server).

Step 6: Migration Cutover Guidance (When Applicable)

When the user is planning or executing a cutover:

Pre-Cutover Checklist (T-7 to T-1 days)

• CDC latency at zero
• No tables in error
• Validation passing
• No pending maintenance
• Instance resources healthy
• Recovery checkpoint exists
• Target schema matches source
• Application connection strings prepared
• Rollback plan documented

Cutover Execution (T-0)

1. Final latency check
2. Stop application writes to source
3. Wait for CDC to drain (zero latency)
4. Run final validation
5. Stop DMS task
6. Switch application to target
7. Validate application
8. Monitor (T+0 to T+24h)

Rollback Procedure

Documented steps to revert to source if cutover fails.

Post-Cutover Cleanup (T+3 to T+7 days)

Resource deletion and documentation updates.

Step 7: Generate Report

Generate a separate shareable report artifact for EACH environment reviewed.

Artifact naming: dms-review-<instance-name>-<YYYY-MM-DD>.md Example: dms-review-prod-replication-2026-07-22.md

For each environment, create the artifact as a Markdown document with these sections:

Report Header

Copy code
# DMS Operational Review — <instance-name>
Account: <account-id> | Region: <region> | Date: <YYYY-MM-DD> | Engine Version: <version>

Executive Summary

• Environment health: ✅ HEALTHY / ⚠️ WARNINGS / ❌ CRITICAL
• Health Score: XX/100 [Rating]
• Finding counts by severity
• Top 3 critical/high items

Health Score Breakdown

Resource Inventory

Findings by Category

For each of the 5 categories above, present:

CloudWatch Metrics (7-Day)

CloudWatch Logs Analysis (7-Day)

Version & Deprecation Status

All instances with version, EOL status, upgrade recommendations.

Cost Optimization Opportunities

Priority Matrix

Next Steps

• Immediate (CRITICAL/HIGH — 7 days)
• Short-term (MEDIUM — 30 days)
• Long-term (LOW — 90 days)

Appendix

Refer to the AWS DMS documentation for detailed guidance on best practices, monitoring, troubleshooting, release notes, serverless, and data validation.

Severity Definitions

Health Score Formula

Scoring Categories (100 points total)

Score Interpretation