What objects can I migrate using an AWS DMS task?

Last updated: 2022-07-25

I have an AWS Database Migration Service (AWS DMS) task that isn't migrating all the database schema objects. What objects does an AWS DMS task migrate, and how do I migrate the remaining objects?

Short description

If your AWS DMS task isn't migrating an object, it might be because AWS DMS tasks don't migrate this type of object. AWS DMS creates a number of objects such as tables and primary keys, but it doesn't create objects that aren't needed to migrate data from the source. Some examples of objects that AWS DMS doesn't create include secondary indexes, data defaults, and non-primary key constraints

For more information on what objects AWS DMS creates, see Troubleshooting migration tasks in AWS Database Migration Service.

To migrate the remaining objects that aren't migrated by AWS DMS, use one of these methods:

  • For homogeneous migration (between two databases of the same engine type), migrate the schema by using your engine's native tools to export and import the schema itself, without any data.
  • For heterogeneous migration (between two databases that use different engine types), use the AWS Schema Conversion Tool (AWS SCT) to generate a complete target schema.

Resolution

Check if an object is migrated by an AWS DMS task

First, use these guidelines to check if an object is migrated by an AWS DMS task:

  • An AWS DMS task creates the table on the target, along with 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 object (LOB) columns. If AWS DMS creates the target tables, then it sets LOB columns to nullable, by default. For more information, see Migrating large binary objects (LOBs).
  • If the source table has an auto-increment column, then the auto-increment property isn't migrated.
  • Other table specific properties like foreign key constraints, checks, data defaults, and comments aren't replicated by the AWS DMS task.
  • Database objects like secondary indexes, views, procedures, functions, packages, and triggers aren't migrated by the AWS DMS task. Migrate these objects separately.

Migrate objects using your engine’s native tools (homogeneous migration)

For homogenous database migration, use your native tools to take an export of the schema metadata, without the data. This metadata only dump is then imported 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. Using the CONTENT=METADATA_ONLY parameter, the data pump utility unloads only database object definitions. The utility doesn't unload any table row data.

1.    To take a metadata only dump, create a parameter file 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 using the expdp utility:

expdp parfile=export_sample_user.par

3.    After the dump files are created in the source database’s DATA_PUMP_DIR, copy them over to the DATA_PUMP_DIR of the target Oracle database. 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

SQL Server

To migrate objects using SQL Server, generate a SQL script for the database schema:

  1. Log in to the source database using SQL Server Management Studio (SSMS).
  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. Select 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. Script indexes is set to false, by default.
  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.

To create objects on the target database, run the SQL script that is generated after you complete these steps.

MySQL

To migrate objects with MySQL, use the mysqldump utility to generate a dump file containing 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 is generated after you run the previous command.

PostgreSQL

To migrate objects with PostgreSQL databases, use the pg_dump utility to generate a dump file containing 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 is generated after you run the previous command.

Migrate objects using the AWS Schema Conversion Tool (AWS SCT)

Use the AWS SCT to convert and migrate your existing database schemas from one database engine to another. Or, use the AWS SCT to copy an existing on-premises database schema to an Amazon Relational Database Service (Amazon RDS) DB instance running the same engine.

To convert your database schema, follow these steps:

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

For more information, see Converting database schemas using AWS SCT.


Did this article help?


Do you need billing or technical support?