AWS Database Migration Service (DMS) helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. The AWS Database Migration Service can migrate your data to and from most widely used commercial and open-source databases.
The Database Migration Service is a data mover. It creates only the structures required to migrate your data, (this is for performance reasons mainly.) Additionally, it doesn’t migrate secondary indexes, default values, procedures, triggers, auto increment columns etc. These objects/modifications need to be made after migrating the data, (and typically prior to switching the app.)
But it can be fixed by importing schema manually.
Problem
missing foreign keys and/or indexes
Solution
To fix foreign keys & indexes missing issue, follow this
- Import Database schema manually to RDS.
- Set “`Target table preparation mode“` to “`Truncate“`
Using JSON:
Using DMS GUI:
Now run the task.
You will see all foreign keys and indexes in target (RDS).
Hi,
Your post describes basic solution to fix the problem of indices while replicating and it will work well on small databases. I have learned by experience that for large datasets recommended approach is to run DMS task with option “Drop Tables on target” wait until initial load have completed and then deploy indices. Having indices on the target before running full load will dramatically increase load time.
Regards,
Darek
So if we “Drop Tables on target”…
Doesn’t that destroy the schema which we applied before starting DMS task ?
That’s the reason I don’t recommend “dropping”, I prefer truncate which will not override schema on target database.
But you can prefer to drop if you don’t get any issues with schema, indexes and foreign keys.