Migrating databases with AWS DMS operational best practices

Introduction

This article is part of a set of AWS Countdown Premium readiness articles.

Solution overview

Use the best practices in this article to avoid the following issues that AWS Support engineers identified during AWS Countdown Premium engagements that supported AWS DMS migrations across Oracle and PostgreSQL workloads:

• Batch apply changes that exceed your quotas

• Character set misconfigurations

• Primary key management issues

• Errors that cause change data capture (CDC) latency

• Incorrect PostgreSQL upgrade choices

• Incorrect post-migration tuning

These issues often occur in the CDC phase when the task must keep the target database up to date with a live source that it tracks.

Solution implementation

Prerequisites

The best practices in this article require the following prerequisites:

• A source and target database with a planned AWS DMS migration task

• Access to the AWS DMS task configuration and source and target database metrics

• A defined cutover window and rollback plan

Configure batch apply for your data

By default, AWS DMS uses transactional apply during the CDC phase. However, the batch apply mode sometimes improves throughput when a task captures a high volume of transactions and target latency grows.

When you use batch apply, adhere to the following best practices:

• Use tables that have a primary key or unique index. Batch apply works only on tables that have a primary key or unique index. For tables without primary keys or unique indexes, AWS DMS takes more time because it applies inserts in bulk and then applies updates and deletions one by one.

• Move tables with large object (LOB) columns, such as Oracle LONG RAW, to their own tasks and use transactional apply on them. Batch apply supports only limited LOB mode for tables that include LOB columns. For more information, see Target metadata task settings.

Match the target character set

Confirm that your source database character set and ID match the target character set. Character set mismatches sometimes cause “ORA-00932” errors in the CDC phase because AWS DMS defaults to the AL32UTF8 character set for Oracle targets.

For example, if the source uses the UTF8 character set, which corresponds to nlsCharsetId 871, then set the extra connection attribute nlsCharsetId=871 on the target.

Keep primary keys on the target

Don’t deactivate primary keys on the target during the CDC phase. Deactivated primary keys cause high CDC latency, batch apply failures, and data validation failures.

Keep primary keys in place and separately manage other constraints. AWS DMS data validation requires primary keys to compare rows between the source and target.

Resolve issues that cause CDC latency

If you experience CDC latency, then check for the following issues:

• If your materialized views refresh on the target, then suspend them during the CDC phase.

• Deactivate foreign key constraints during the reverse CDC task. Foreign key constraints block reverse replication tasks.

• Exclude large delete operations on tables without primary keys from your task and move the data with export and import.

Choose the correct PostgreSQL upgrade path

When you upgrade a PostgreSQL database that is the source or target of an active AWS DMS migration task, the upgrade method determines whether replication continues.

Choose one of the following upgrade methods:

• Choose in-place upgrades for replications that must continue through the upgrade. In-place upgrades preserve AWS DMS replication slots.

• Choose blue/green deployments when you don’t have an active AWS DMS dependency. For blue/green deployments, you must stop the AWS DMS task to delete the existing replication slots.

Tune performance after migration

After cutover, take the following actions to tune the target:

• If your migration target is an Oracle database that runs on Amazon Relational Database Service (Amazon RDS), then tune the target’s per-session memory. Increase the value of the pga_aggregate_target setting from the default to the value that your workload needs based on your demand.

• To confirm the performance of the target database, compare commit latency benchmarks before and after migration.

• Use Amazon CloudWatch Database Insights on Amazon RDS to monitor DB load for the first 48 hours after cutover.

• Before you decommission the source database, keep it in read-only mode for a 7–14 day rollback window.

Conclusion

To complete a stable AWS DMS migration, you must match your task settings to your data. Be sure to run a test migration to measure CDC latency before cutover. For more information about how Support engineers empower database migrations, see AWS Countdown Premium.