How do I migrate tables without primary keys in AWS DMS tasks for CDC replication and validation?

Resolution

Migrate Oracle source tables

If you migrate an Oracle source table that doesn't have primary keys, then you must activate supplemental logging to capture column data for CDC operations.

Before you configure supplemental logging at the table level, run the following command to activate database-level supplemental logging on the Oracle source database:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Confirm that you have the ALTER permission for the tables that you're migrating.

To grant permission, run the following command:

GRANT ALTER ANY TABLE to dms_user

To add supplemental logging on a subset of columns in a table and include a unique index, run the following command:

ALTER TABLE table_name ADD SUPPLEMENTAL LOG GROUP example_log_group (UniqueIndexColumn1, UniqueIndexColumn2) ALWAYS;

Note: Replace table_name with your table name, example_log_group with your log group name, and UniqueIndexColumn1 and UniqueIndexColumn2 with your unique index column names.

To add supplemental logging for table columns, run the following command:

ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Note: If you activate (ALL) COLUMNS supplemental logging on a table, then you don't need to add supplemental logging for the table.

To have AWS DMS automatically manage supplemental logging, run the following command to add an extra connection attribute (ECA) to your Oracle source endpoint configuration:

addSupplementalLogging=true ;

Migrate SQL Server source tables

To capture changes for Microsoft SQL Server tables that don't have primary keys, you must activate MS-CDC at the database level and for each table. If you don't configure the database for MS-REPLICATION or MS-CDC, then AWS DMS captures only INSERT/DELETE DML events.

To find tables that don't have a primary or a unique key on your SQL Server database, run the following SQL query:

USE [DBname]
SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0
AND OBJECTPROPERTY(object_id, 'TableHasUniqueCnst') = 0
ORDER BY schema_name, table_name;

Note: Replace DBname with your database name, schema_id with your schema ID, and schema_name with your schema name. Also, replace table_name with your table name and object_id with your object ID.

If you aren't using a read-only replica, then AWS DMS version 3.4.7 and later automatically configure MS-CDC for your database and tables.

Migrate PostgreSQL source tables

For PostgreSQL source tables that don't have primary keys, set the REPLICA IDENTITY table-level parameter to FULL and then capture the necessary row information. Before you set REPLICA IDENTITY to FULL, it's a best practice to review your requirements because the parameter generates additional write-ahead logging (WAL) entries.

Or, you can run the following SQL query on the PostgreSQL database:

ALTER TABLE schema_name.table_name REPLICA IDENTITY FULL;

Note: Replace schema_name with your schema name and table_name with your table name.

AWS DMS supports REPLICA IDENTITY as FULL with logical decoding plugins but doesn't support the pglogical plugin. For more information about the parameter, see REPLICA IDENTITY on the PostgreSQL website.

Migrate MySQL source tables

MySQL source tables that don't have primary keys or unique indexes can cause slow CDC replication or CDC failure. To prevent these issues, add a primary key or unique index to your MySQL source tables.

To add a primary key, run the following command:

ALTER TABLE table_name ADD COLUMN id BIGINT AUTO_INCREMENT PRIMARY KEY;

Note: Replace table_name with your table name.

To add a unique index, run the following command:

CREATE UNIQUE INDEX idx_unique ON table_name (column1, column2);

Note: Replace idx_unique with your unique ID and table_name with your table name.

Troubleshoot validation errors

Valid primary key or unique index

AWS DMS task validation requires a valid primary key or unique index on both source and target tables. If AWS DMS can't identify a key, then validation fails and you get the following error message:

"No eligible primary/unique key found."

To resolve this issue, create a primary key or add a unique index constraint on your table.

To create a primary key, run the following command:

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

Note: Replace table_name with your table name and column_name with your column name.

To add a unique index constraint, run the following command:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

Note: Replace table_name with your table name and column_name with your column name.

If you can't add primary keys or unique indexes, then create a new validation-only task that includes only tables with primary keys. Use table mappings to explicitly select tables that have valid primary keys.

Example JSON:

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "select-tables-with-pk",
      "object-locator": {
        "schema-name": "your_schema",
        "table-name": "table_with_pk"
      },
      "rule-action": "include"
    }
  ]
}

Note: Replace your_schema with your schema name.

Or, you can manually perform validation for tables that don't have primary keys. To compare row counts or checksums between the source and target, use custom SQL queries.

Partitioned tables

If you migrate partitioned tables to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition, then make sure to include partition key columns in your unique indexes. PostgreSQL doesn't support global unique indexes. To enforce uniqueness across partitions, make sure that you set the partition key as part of the unique constraint. If the unique index doesn't include partitioning columns, then AWS DMS validation fails and you get the following error message:

"No eligible primary/unique key found"

To create a unique index with partitioned columns, run the following command:

CREATE UNIQUE INDEX index_name ON schema.table_name (unique_column1, unique_column2, partition_key_column);

Note: Replace index_name with your index name, schema with your schema name, table_name with your table name, and unique_column1, unique_column2, partition_key_column with your column names.