How do I troubleshoot an AWS DMS task that isn't migrating objects?

Short description

If your AWS DMS task doesn't migrate an object, then the AWS DMS task might not migrate that type of object. For example, AWS DMS doesn't create secondary indexes, data defaults, and non-primary key constraints.

To migrate the remaining objects that aren't migrated by AWS DMS, use either homogeneous or heterogeneous migration.

If you're migrating between two databases of the same engine type, then use homogeneous migration. Use your engine's native tools to export and import the schema without any data.

If you're migrating between two databases that use different engine types, then use heterogeneous migration. Use the AWS Schema Conversion Tool (AWS SCT) or AWS DMS Schema Conversion to generate a complete target schema.

Resolution

Check whether an AWS DMS task migrates an object

When an AWS DMS task creates the table on the target, it also migrates the primary key constraint and index. If no primary key exists, then the task creates the unique key if it exists in the source table.

If NOT NULL constraints exist on the source table, then the AWS DMS task creates the NOT NULL constraints on the target. This doesn't include large binary object (LOB) columns. If AWS DMS creates the target tables, then it sets LOB columns to nullable, by default. For more information, see Migrating LOBs.

If the source table has an auto-increment column, then the task doesn't migrate the auto-increment property. AWS DMS tasks don't replicate other table specific properties such as foreign key constraints, checks, data defaults, and comments.

AWS DMS tasks don't migrate database objects such as secondary indexes, views, procedures, functions, packages, and triggers. Migrate these objects separately.

Migrate objects with your engine's native tools (homogenous migration)

For homogenous database migration, use your native tools to take an export of the schema metadata without the data. AWS DMS imports this metadata only dump into the target database to create the database objects, without any data. Then, run the AWS DMS task with the TargetTablePrepMode task setting set to TRUNCATE_BEFORE_LOAD. This means that the task doesn't drop and re-create the tables on the target.

Oracle

To migrate objects with Oracle databases, use the export data pump utility (expdp) to take a metadata only dump. When you use the CONTENT=METADATA_ONLY parameter, the data pump utility unloads only database object definitions. The utility doesn't unload any table row data.

Complete the following steps:

1. To take a metadata only dump, create a parameter file similar to the following example to export the schema:

   cat export_sample_user.par userid=DMS_USER/password@SOURCE_DB
   directory=DATA_PUMP_DIR
   logfile=export_dms_sample_user_metadata.log
   dumpfile=export_dms_sample_user_metadata_%U.dmp
   schemas=DMS_SAMPLE
   CONTENT=METADATA_ONLY

2. Run the export with the expdp utility:

   expdp parfile=export_sample_user.par

3. After the utility creates the dump files in the source database's DATA_PUMP_DIR, copy the files over to the DATA_PUMP_DIR of the target Oracle database.

4. To create the objects on the target Oracle database, run the import data pump utility (impdp) to import the metadata:

   impdp admin@TARGET_DB directory=DATA_PUMP_DIR logfile=import.log dumpfile=export_dms_sample_user_metadata_%U.dmp

   Note: If you're working with directories and files with an Amazon Relational Database Service (Amazon RDS) for Oracle instance, see Performing miscellaneous tasks for Oracle DB instances.

SQL Server

To migrate objects that use SQL Server, complete the following steps:

1. Use SQL Server Management Studio (SSMS) to log in to the source database.
2. Right click the database that you want to generate scripts for, choose Tasks, and then choose Generate Scripts.
3. Under Select specific database objects, select the tables, views, and any other objects that you want to use, and then choose Next.
4. Choose the Advanced button, and then select the types of data that you want to script.
5. If you want to get the SQL for secondary indexes, set Script indexes to true.
6. Choose how you want to save the scripts. For example, create a SQL file on disk, copy the SQL to a new query window, or copy the scripts to the clipboard.
7. Choose Next.
8. Choose Next again, and then choose Finish.
9. To create objects on the target database, run the SQL script that SSMS generates.

MySQL

To migrate objects that use MySQL, use the mysqldump utility to generate a dump file that contains only the schema metadata. The --no-data option tells mysqldump not to dump table data, so the results in the dump file contain only statements to create the tables. For a definition-only dump, add the --routines and --events options to also include stored routine and event definitions.

Example:

mysqldump --no-data --routines --events -h SOURCE_DB_SERVER_NAME -u DMS_USER -p SOURCE_DB > path_to_dump_file.sql

To create the objects on the target database, run the SQL script that MySQL generates after you run the preceding command.

PostgreSQL

To migrate objects with PostgreSQL databases, use the pg_dump utility to generate a dump file that contains only the schema metadata. The -s or --schema-only option dumps the object definitions (schema) and not the data.

Example:

pg_dump --host SOURCE_DB_SERVER_NAME --port 5432 --username DMS_USER --schema-only --verbose SOURCE_DB > path_to_dump_file.sql

To create the objects on the target database, run the SQL script that pg_dump generates after you run the preceding command.

Migrate objects with the AWS SCT or DMS Schema Conversion (heterogenous migration)

AWS SCT

To convert and migrate your existing database schemas from one database engine to another, use the AWS SCT. Or, use the AWS SCT to copy an existing on-premises database schema to an Amazon RDS DB instance that's running the same engine.

To convert your database schema, complete the following steps:

1. Create migration rules in AWS SCT.
2. Convert your schema.
3. Save and apply your converted schema.

DMS Schema Conversion

To migrate objects with DMS Schema Conversion, see Using DMS Schema Conversion.

Related information

High-level view of AWS DMS