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

Short description

AWS DMS creates tables on the target database using the TargetTablePrepMode option. When AWS DMS creates target tables, it migrates only the objects that it needs to effectively migrate data to the target. For example, AWS DMS creates tables, primary keys, and in some cases, unique indexes. But, it doesn't create secondary indexes, non-primary key constraints, data defaults, or user accounts. For more information, see Foreign keys and secondary indexes are missing.

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

Note: This doesn't apply for a change data capture (CDC) only task.

So, to make sure that migration is successful and to improve task performance, it's important that you understand when to create secondary objects. The timing varies depending on the migration method that the task uses:

• Full load only (migrate existing data)
• Full load and CDC (migrate existing data and replicate ongoing changes)
• CDC (replicate data changes only)

Resolution

Full load only

For a full load only task, it's a best practice to drop primary keys and all secondary objects before the start of migration. Don't create these objects until after full load completes. If you have secondary objects on the target database during full load, then you might see additional maintenance overhead.

If you have foreign keys on the target, this can cause the task to fail. This happens because the task loads groups of tables together in no specific order, unless you have manually specified in table mappings.

Similarly, insert, update, or delete triggers might cause errors if they are present on the target database. For example, a row insert that's is triggered by an insert trigger on a previously loaded table might cause duplicate rows. Other types of triggers also affect performance because they cause added processing.

Full load and CDC

For full load and CDC tasks, it's a best practice to drop all secondary objects before migration starts. But, you must apply secondary objects on the target database at different phases of migration.

Review the stages of full load and CDC tasks migration, and at which stage to apply specific secondary objects:

• The full load of existing data - Add secondary indexes after the task has completed full load, but before it applies the captured cached changes.
• The application of cached changes - Add foreign keys (referential integrity constraints) after the task has applied cached changes.
• Ongoing replication - Create triggers after migration is complete and before the application cutover.

While full load is in progress, any changes that you make to the tables that are being loaded are then cached. These cached changes are applied when the full load for the table completes. After full load completes, and the cached changes are applied, then the target tables are transactionally consistent. AWS DMS then begins the ongoing replication phase. For more information on this, see High-level view of AWS DMS.

To stop the task during migration, use these task settings:

• Use StopTaskCachedChangesNotApplied to stop the task before applying cached changes.
• Use StopTaskCachedChangesApplied to stop the task after applying cached changes.

Note: You can turn on both StopTaskCachedChangesNotApplied and StopTaskCachedChangesApplied using the AWS Command Line Interface (AWS CLI). If you receive errors when running AWS CLI commands, make sure that you’re using the most recent version of the AWS CLI.

CDC only tasks

For CDC only tasks, you can create the secondary indexes and the foreign keys on the target database before the migration. Then, create the triggers on the target after the migration is complete, and before the application cutover.

---

Related information

Creating a task

Full-load task settings