Migrating Databases Across AWS Accounts with AWS DMS: Lessons Learned

Migrating databases between AWS accounts is a critical yet intricate task that many organizations encounter during mergers, acquisitions, account restructuring, or optimizing cloud resources. AWS Database Migration Service (DMS) is a powerful tool to achieve seamless database migrations, but it comes with its own set of challenges and nuances. In this article, we will share insights and lessons learned from using AWS DMS to migrate databases across AWS accounts.

1. Understand the Basics of AWS DMS

AWS DMS simplifies the process of migrating databases to and from AWS. It supports homogeneous migrations (e.g., Oracle to Oracle) as well as heterogeneous migrations (e.g., Oracle to MySQL). The service ensures minimal downtime by continuously replicating changes from the source to the target database.

However, while DMS is straightforward in theory, planning and executing a cross-account migration requires detailed preparation.

2. Prepare Both AWS Accounts

To facilitate a smooth migration, both the source and target AWS accounts need to be properly configured:

• Networking: Establish connectivity between the two accounts using VPC peering, AWS PrivateLink, or a VPN connection. Ensure that the necessary ports for database communication are open.
• IAM Permissions: Grant sufficient permissions to the DMS service and the roles/users managing the migration. Use IAM roles for cross-account access to ensure security and traceability.
• Database Accessibility: Verify that the source database is accessible to the replication instance and that the target database is ready to accept incoming data.

3. Choose the Right Replication Instance

The DMS replication instance is the backbone of the migration process. Selecting the appropriate instance size and class ensures that the migration process is efficient. Consider the following:

• Workload: Assess the size and complexity of your database to choose an instance with adequate compute and memory.
• Region Placement: Place the replication instance in a region that minimizes latency between the source and target databases.

4. Enable Change Data Capture (CDC)

Change Data Capture (CDC) is crucial for continuous replication during migrations. It ensures that changes made to the source database are replicated to the target database in near real-time. Enabling CDC requires different steps depending on whether you are working with SQL or NoSQL databases:

• For DocumentDB (NoSQL):

To enable CDC in DocumentDB, use the following command:

db.adminCommand({
  modifyChangeStreams: 1,
  database: "DB_NAME",
  collection: "",
  enable: true
});
        

This command modifies the change streams setting for the specified database and enables CDC for incremental data replication.

• For SQL Databases:

In SQL databases, CDC can be enabled by updating the parameter group settings. For example:

1. Update the database parameter group to include the rds.logical_replication parameter and set it to 1.
2. Restart the database instance to apply the changes.

Here’s an example command to check the CDC status in a SQL database:

EXEC sys.sp_cdc_enable_db;        

This ensures the database is prepared for capturing changes. Once enabled, DMS can use these change logs to replicate data incrementally.

5. Schema Conversion and Data Transformation

For heterogeneous migrations, schema conversion can be a significant hurdle. AWS Schema Conversion Tool (SCT) can help automate this process, but manual intervention may still be required for:

• Complex data types and stored procedures
• Indexes, triggers, and custom scripts
• Performance optimization post-migration

6. Monitoring and Troubleshooting

Migration projects rarely go off without a hitch, and real-time monitoring is crucial:

• Use CloudWatch metrics to monitor replication instance performance.
• Enable DMS task logs for detailed troubleshooting in case of failures.
• Watch for common issues such as latency spikes, task failures, or schema mismatches.

7. Test Extensively Before Cutover

Testing is non-negotiable in database migrations. Before performing the final cutover:

• Run multiple test migrations to identify and resolve issues.
• Verify data integrity using row counts, checksums, or automated scripts.
• Test application connectivity and performance with the target database.

8. Lessons Learned

Through our experience, we identified several key takeaways:

• Plan for Downtime: While DMS supports near-zero downtime migrations, some downtime is inevitable during the final cutover. Communicate this clearly to stakeholders.
• Anticipate Data Anomalies: Be prepared to handle unexpected data discrepancies or application behavior post-migration.
• Schema and Data Migration Tasks: Below is an example of how to define a JSON task for migrating only specific tables:

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "include-table",
      "object-locator": {
        "schema-name": "source_schema",
        "table-name": "table_name"
      },
      "rule-action": "include"
    }
  ]
}        

Upload this task file to filter specific tables during migration.

• Query Monitoring: Use queries to verify migration progress and troubleshoot issues. For example, to check apply exceptions:

SELECT * FROM awsdms_apply_exceptions;        

This query helps identify rows that failed during replication.

• Document Everything: Maintain comprehensive documentation of configurations, tasks, and troubleshooting steps for future reference.
• Iterate and Optimize: Use each migration as a learning opportunity to improve processes and minimize risks in subsequent projects.

9. Conclusion

Migrating databases across AWS accounts using DMS is a powerful yet complex undertaking. By following best practices, preparing diligently, and learning from challenges, organizations can achieve successful migrations with minimal disruption.

Whether you’re planning your first migration or seeking to refine your process, AWS DMS offers robust capabilities to help you meet your goals. Have questions or want to share your experiences? Let’s connect and discuss!