When can I add secondary objects to a target database during AWS DMS migration?

Short description

When AWS DMS creates tables on the target database, AWS DMS migrates only the objects that it needs to migrate data to the target. For more information, see Foreign keys and secondary indexes are missing.

If you manually create tables on the target, then it's a best practice to drop secondary objects, such as secondary indexes, before migration starts.

Note: You don't need to drop secondary objects for only a change data capture (CDC) task.

When you add secondary objects to your target database depends on the migration method that the task uses.

Resolution

Only full-load tasks

For only full-load tasks, drop primary keys and all secondary objects before migration starts. Create the objects after the full load completes. If you have secondary objects on the target database during the full load, then you might experience additional maintenance.

If you have foreign keys on the target, then the foreign keys might cause the task to fail. This failure occurs because the task loads groups of tables together in no specific order. For more information, see How can I troubleshoot an AWS DMS task that failed with a foreign key constraint violation error?

To avoid this issue, manually specify the load-order in table mappings.

Only CDC tasks

For CDC only tasks, create the secondary indexes and the foreign keys on the target database before the migration. Then, create triggers on the target after the migration completes, but before the application cutover.

Full-load and CDC tasks

For full-load and CDC tasks, drop all secondary objects before migration starts. When the full load is in progress, the task caches changes to the tables that are loading. After the full load for the table completes, the task applies cached changes and the target tables are transactionally consistent. Then, AWS DMS begins the ongoing replication stage.

Apply secondary objects on the target database during the following stages of migration:

• The full load of existing data: Add secondary indexes after the task completes a full load but before the task applies cached changes.
• The application of cached changes: Add foreign keys, such as referential integrity constraints, after the task applies cached changes.
• Ongoing replication: Create triggers after migration completes but before the application cutover.

To stop the task during migration, use task settings. To stop the task before it applies cached changes, use StopTaskCachedChangesNotApplied. To stop the task after it applies cached changes, use StopTaskCachedChangesApplied.

To turn on task settings, you can use the AWS Command Line Interface (AWS CLI).

Note: If you receive errors when you run AWS CLI commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Related information

Creating a task

Full-load task settings

High-level view of AWS DMS

Target table preparation mode