dms

How to fix missing foreign keys and/or indexes – AWS DMS

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

  1. Import Database schema manually to RDS.
  2. 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).

2 thoughts on “How to fix missing foreign keys and/or indexes – AWS DMS

  1. 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

    1. Akhil – India – DevOps Engineer with hands-on experience supporting, automating, and optimizing mission-critical deployments, leveraging configuration management, CI/CD, and DevOps processes.
      Akhil Jalagam says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *