How do I use premigration assessments to prevent errors and compatibility issues when I migrate databases with AWS DMS?

Resolution

Run a premigration assessment

Note: It's a best practice to use premigration assessments to identify potential migration errors and incompatibilities before you run a database migration with AWS DMS.

Before you run a premigration assessment, create the prerequisites. Then, test the connectivity between your endpoints and the replication instance.

To test the connectivity, complete the following steps:

1. Open the AWS DMS console.
2. In the navigation pane, under Migrate or replicate, choose Endpoints.
3. Select your source endpoint.
4. Choose Actions, and then choose Test connection.
5. Select your replication instance.
6. Choose Run.
7. Repeat steps 3-6 for your target endpoint.

If the tests fail, then check that your network configuration has the required security rules.

To run a premigration assessment, specify your individual assessments for your source and target database engines. Then, start and view your premigration assessment runs.

If the assessment identified issues, then resolve the issues before your migration.

Resolve issues that the premigration assessment identified

Tables without primary keys

If the assessment identifies source tables without a primary key or unique constraint, then decreased performance can occur during the change data capture (CDC) operations and full large binary object (LOB) migrations.

To resolve this issue, take one or more of the following actions:

• Add a primary key or unique constraint to the source tables before migration.
• Use full load only to migrate the source tables.
• For tables that must use CDC without primary keys, use the CDCLatencySource and CDCLatencyTarget Amazon CloudWatch metrics to monitor replication latency. If needed, split large tables into separate tasks.
• For Oracle source tables, you can turn on supplemental logging to capture column data necessary for CDC operations.
• For SQL Server source tables, you can activate Microsoft CDC (MS-CDC) at both the database and table levels.
• For PostgreSQL source tables, set the REPLICA IDENTITY parameter to FULL for tables without primary keys.

Foreign key constraints

Foreign key constraints can cause migration task failures when AWS DMS loads data in parallel.

To prevent this issue, you can deactivate or drop the foreign keys in the target database before the migration. Run the following command:

ALTER TABLE table_name DISABLE CONSTRAINT foreign_key_name

Note: Replace table_name with your table name.

You can also configure the target table preparation mode to the Do nothing mode to avoid target constraints. Or, set it to the Drop tables on target mode to create only objects that are necessary for a full load to succeed.

For migrations that use PostgreSQL as a target, to skip foreign keys and user triggers, set the extra connection attribute (ECA) session_replication_role to replica. For more information, see the Use the AWS DMS console to add endpoint settings (or ECAs) to an existing endpoint section of How can I add or modify endpoint settings for AWS DMS endpoints?

After the full load completes successfully, recreate or activate the foreign key constraints on the target database. For CDC migrations or before the CDC phase of a full load and CDC task, activate foreign keys to maintain data consistency during ongoing replication.

For more information, see How can I troubleshoot an AWS DMS task that failed with a foreign key constraint violation error?

Data type incompatibilities

If you encounter issues with data type conversions, then split the problematic tables into separate migration tasks.

To resolve, if you don't require the data in the target or you can manually migrate it, then modify table mapping to exclude problematic tables. Or, if you do require the data in the target, then convert the data types in the source database before migration.

Schema migration

If you migrate tables from different schemas to a new schema in the target, then use transformation rules in your AWS DMS task to rename the target schema. Then, set the target table preparation mode to TRUNCATE_BEFORE_LOAD.

Target LOB columns are set to NOT NULL

When AWS DMS migrates LOBs, AWS DMS inserts a NULL value in the LOB column. Then, AWS DMS updates the row with the actual LOB data. If the target LOB column has a NOT NULL constraint, then the migration task fails because AWS DMS can't insert the initial NULL value.

To resolve this issue, see How can I troubleshoot "ERROR: null value in column violates not-null constraint" for my AWS DMS full load and CDC task?

Related information

Creating source and target endpoints

Selection rules and actions

Assessments for all endpoint types