AWS DMS data validation

Oracle

PostgreSQL

MySQL

MariaDB

Microsoft SQL Server

Amazon Aurora (MySQL)

Amazon Aurora (PostgreSQL)

IBM Db2 LUW

EnableValidation – Enables or disables data validation.

FailureMaxCount – Specifies the maximum number of records that can fail validation before validation is suspended for the task.

HandleCollationDiff – Accounts for column collation differences in PostgreSQL endpoints when identifying source and target records to compare.

RecordFailureDelayLimitInMinutes – Specifies the delay before reporting any validation failure details.

TableFailureMaxCount – Specifies the maximum number of tables that can fail validation before validation is suspended for the task.

ThreadCount – Adjusts the number of execution threads that AWS DMS uses during validation.

ValidationOnly – Previews the validation for the task without performing any migration or replication of data. To use this option, set the task migration type to Replicate data changes only in the AWS DMS console, or set the migration type to cdc in the AWS DMS API. In addition, set the target table task setting, TargetTablePrepMode, to DO_NOTHING.

ValidationState—The validation state of the table. The parameter can have the following values:

ValidationPending—The number of records that have been migrated to the target, but that haven't yet been validated.

ValidationSuspended—The number of records that AWS DMS can't compare. For example, if a record at the source is constantly being updated, AWS DMS can't compare the source and the target. For more information, see 错误处理任务设置

ValidationFailed—The number of records that didn't pass the data validation phase. For more information, see 错误处理任务设置.

On the console, you can choose to validate a task when you create or modify the task. To view the data validation report using the console, choose the task on the Tasks page and choose the Table statistics tab in the details section.

Using the CLI, set the EnableValidation parameter to true when creating or modifying a task to begin data validation. The following example creates a task and enables data validation.

create-replication-task  
  --replication-task-settings '{"ValidationSettings":{"EnableValidation":true}}' 
  --replication-instance-arn arn:aws:dms:us-east-1:5731014:
     rep:36KWVMB7Q  
  --source-endpoint-arn arn:aws:dms:us-east-1:5731014:
     endpoint:CSZAEFQURFYMM  
  --target-endpoint-arn arn:aws:dms:us-east-1:5731014:
     endpoint:CGPP7MF6WT4JQ 
  --migration-type full-load-and-cdc 
  --table-mappings '{"rules": [{"rule-type": "selection", "rule-id": "1", 
     "rule-name": "1", "object-locator": {"schema-name": "data_types", "table-name": "%"}, 
     "rule-action": "include"}]}'

Use the describe-table-statistics command to receive the data validation report in JSON format. The following command shows the data validation report.

aws dms  describe-table-statistics --replication-task-arn arn:aws:dms:us-east-1:5731014:
rep:36KWVMB7Q

The report would be similar to the following.

{
    "ReplicationTaskArn": "arn:aws:dms:us-west-2:5731014:task:VFPFTYKK2RYSI", 
    "TableStatistics": [
        {
            "ValidationPendingRecords": 2, 
            "Inserts": 25, 
            "ValidationState": "Pending records", 
            "ValidationSuspendedRecords": 0, 
            "LastUpdateTime": 1510181065.349, 
            "FullLoadErrorRows": 0, 
            "FullLoadCondtnlChkFailedRows": 0, 
            "Ddls": 0, 
            "TableName": "t_binary", 
            "ValidationFailedRecords": 0, 
            "Updates": 0, 
            "FullLoadRows": 10, 
            "TableState": "Table completed", 
            "SchemaName": "d_types_s_sqlserver", 
            "Deletes": 0
        }
}

Using the AWS DMS API, create a task using the CreateReplicationTask action and set the EnableValidation parameter to true to validate the data migrated by the task. Use the DescribeTableStatistics action to receive the data validation report in JSON format.

ValidationSucceededRecordCount— Number of rows that AWS DMS validated, per minute.

ValidationAttemptedRecordCount— Number of rows that validation was attempted, per minute.

ValidationFailedOverallCount— Number of rows where validation failed.

ValidationSuspendedOverallCount— Number of rows where validation was suspended.

ValidationPendingOverallCount— Number of rows where the validation is still pending.

ValidationBulkQuerySourceLatency— AWS DMS can do data validation in bulk, especially in certain scenarios during a full-load or on-going replication when there are many changes. This metric indicates the latency required to read a bulk set of data from the source endpoint.

ValidationBulkQueryTargetLatency— AWS DMS can do data validation in bulk, especially in certain scenarios during a full-load or on-going replication when there are many changes. This metric indicates the latency required to read a bulk set of data on the target endpoint.

ValidationItemQuerySourceLatency— During on-going replication, data validation can identify on-going changes and validate those changes. This metric indicates the latency in reading those changes from the source. Validation can run more queries than required, based on number of changes, if there are errors during validation.

ValidationItemQueryTargetLatency— During on-going replication, data validation can identify on-going changes and validate the changes row by row. This metric gives us the latency in reading those changes from the target. Validation may run more queries than required, based on number of changes, if there are errors during validation.

Data validation requires that the table has a primary key or unique index.

If the collation of the primary key column in the target PostgreSQL instance isn't set to "C", the sort order of the primary key is different compared to the sort order in Oracle. If the sort order is different between PostgreSQL and Oracle, data validation fails to validate the records.

Data validation generates additional queries against the source and target databases. You must ensure that both databases have enough resources to handle this additional load.

Data validation isn't supported if a migration uses customized filtering or when consolidating several databases into one.

For a source or target Oracle endpoint, AWS DMS uses DBMS_CRYPTO to validate LOBs. If your Oracle endpoint uses LOBs, then you must grant the execute permission on dbms_crypto to the user account used to access the Oracle endpoint. You can do this by running the following statement:

grant execute on sys.dbms_crypto to dms_endpoint_user;

If the target database is modified outside of AWS DMS during validation, then discrepancies might not be reported accurately. This result can occur if one of your applications writes data to the target table, while AWS DMS is performing validation on that same table.

If one or more rows are being continuously modified during the validation, then AWS DMS can't validate those rows. However, you can validate those rows manually, after the task completes.

If AWS DMS detects more than 10,000 failed or suspended records, it stops the validation. Before you proceed further, resolve any underlying problems with the data.