How do I resolve data replication issues in AWS DMS tasks between the source and target databases?

Short description

To resolve your data replication issues, first identify the error. Then, increase replication resources, turn on data validation, and resolve the duplicate entries.

Duplicate primary key errors might occur for the following reasons:

• Primary keys on the tables don't allow duplicates
• Unique data field constraints or data type quotas have been reached
• Corrupted indexes
• Coding issues

Resolution

Identify and analyze your errors

To identify your error, complete the following steps:

1. To find specific error messages, run the following query from the Apply Exceptions table:

   select * from awsdms_apply_exceptions;

2. In the output, check if you have a duplicate primary key error or constraint violations. If you see these errors, then your migration configuration has data conflicts in the target database.

3. Set your log task settings for LOGGER_SEVERITY_DETAILED_DEBUG to True to activate detailed logging.

4. Turn on Amazon CloudWatch logs to monitor error messages or warnings.

5. Note the TABLE_NAME and ERROR values and timestamps when errors occur and compare them to your database activity and records in the awsdms_apply_exceptions table.

For more information, see How do I troubleshoot and resolve table errors in AWS DMS?

Create multiple tasks

To prevent resource constraints, create multiple tasks based on your schema names and table sizes.

To increase your data migration efficiency, use parallel load for selected relational tables, views, and collections.

Turn on data validation

To check data accuracy during replication, complete the following steps:

1. Open the AWS DMS console.
2. In the navigation pane, under Migrate or replicate, choose Tasks.
3. Select your task.
4. Choose Modify, and then choose Task settings.
5. Turn on Enable validation.
6. Configure the validation settings based on your requirements.

Resolve duplicate entries

To resolve duplicate primary key errors, set the TargetTablePrepMode parameter to TRUNCATE_BEFORE_LOAD. Then, reload the tables. After the tables reload, confirm that the data is correct.

Resolve LOB replication issues

If your task uses full large binary object (LOB) mode with multiple tables, then the task uses more memory and might lead to performance issues. If the largest LOB in your database is a few megabytes or less, then use limited LOB mode to reduce memory consumption.

First, identify the size of the largest LOB in your database to determine if limited LOB mode is appropriate for your tables. Then, set the maximum LOB size to the maximum LOB column length that your source table supports.

To change to limited LOB mode, complete the following steps:

1. Open the AWS DMS console.
2. In the navigation pane, under Migrate or replicate, choose Tasks.
3. Stop your AWS DMS task.
4. Choose Actions, and then choose Modify.
5. Choose Task settings under LOB column settings.
6. Choose Limited LOB mode.
7. Set the Maximum LOB size to the max LOB column length that you source table supports.
   Note: Set the maximum LOB size higher than the largest LOB present in your source database. The quota is 100 MB.
8. Choose Save, and then restart the task.

Optimize your resource allocation

To optimize your resource allocation, use CloudWatch metrics to check the memory pressure on the replication instance. Make sure that your task has sufficient CPU, memory, network, and input/output operations per second (IOPS) resources for your instance class. If you're experiencing resource constraints, then upgrade to a larger instance.

Run a premigration assessment

To prevent replication issues, run a premigration assessment to identify potential issues. Then, review your assessment results and fix identified issues.

Related information

Best practices for AWS DMS

Specifying task settings for AWS DMS tasks

AWS DMS data validation

Troubleshooting migration tasks in AWS DMS