Search code examples
amazon-s3amazon-rdsdata-migrationaws-dmsdms

AWS DMS S3 Target Validation Stuck in "Records Pending"

I'm using AWS Database Migration Service (DMS) to migrate data from a source database (SQL Server RDS) to an S3 bucket. The DMS and the RDS are deployed in the same account but in different regions, and the S3 bucket is in a different account.

When running my replication task (Full load), I can see that the migration works great and all the tables get migrated to the S3 bucket (this indicates that I don't have any issues with access permissions). However, I'm encountering an issue where the validation process for the S3 target is stuck in the "Records pending" state. enter image description here Here are some details about my setup :

Task settings JSON

{
"Logging": {
    "EnableLogging": true,
    "EnableLogContext": false,
    "LogComponents": [
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "TRANSFORMATION"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "SOURCE_UNLOAD"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "IO"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "TARGET_LOAD"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "PERFORMANCE"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "SOURCE_CAPTURE"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "SORTER"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "REST_SERVER"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "VALIDATOR_EXT"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "TARGET_APPLY"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "TASK_MANAGER"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "TABLES_MANAGER"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "METADATA_MANAGER"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "FILE_FACTORY"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "COMMON"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "ADDONS"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "DATA_STRUCTURE"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "COMMUNICATION"
        },
        {
            "Severity": "LOGGER_SEVERITY_DEFAULT",
            "Id": "FILE_TRANSFER"
        }
    ],
    "LogConfiguration": {
        "TraceOnErrorMb": 10,
        "EnableTraceOnError": false
    },
    "CloudWatchLogGroup": "dms-tasks-replication-instance",
    "CloudWatchLogStream": "dms-task-ID"
},
"StreamBufferSettings": {
    "StreamBufferCount": 3,
    "CtrlStreamBufferSizeInMB": 5,
    "StreamBufferSizeInMB": 8
},
"ErrorBehavior": {
    "FailOnNoTablesCaptured": false,
    "ApplyErrorUpdatePolicy": "LOG_ERROR",
    "FailOnTransactionConsistencyBreached": false,
    "RecoverableErrorThrottlingMax": 1800,
    "DataErrorEscalationPolicy": "STOP_TASK",
    "ApplyErrorEscalationCount": 0,
    "RecoverableErrorStopRetryAfterThrottlingMax": true,
    "RecoverableErrorThrottling": true,
    "ApplyErrorFailOnTruncationDdl": false,
    "DataTruncationErrorPolicy": "LOG_ERROR",
    "ApplyErrorInsertPolicy": "LOG_ERROR",
    "EventErrorPolicy": "IGNORE",
    "ApplyErrorEscalationPolicy": "LOG_ERROR",
    "RecoverableErrorCount": -1,
    "DataErrorEscalationCount": 0,
    "TableErrorEscalationPolicy": "STOP_TASK",
    "RecoverableErrorInterval": 5,
    "ApplyErrorDeletePolicy": "IGNORE_RECORD",
    "TableErrorEscalationCount": 0,
    "FullLoadIgnoreConflicts": true,
    "DataErrorPolicy": "LOG_ERROR",
    "TableErrorPolicy": "STOP_TASK"
},
"ValidationSettings": {
    "ValidationPartialLobSize": 0,
    "PartitionSize": 10000,
    "RecordFailureDelayLimitInMinutes": 30,
    "SkipLobColumns": false,
    "FailureMaxCount": 10000,
    "HandleCollationDiff": true,
    "ValidationQueryCdcDelaySeconds": 0,
    "ValidationMode": "ROW_LEVEL",
    "TableFailureMaxCount": 1000,
    "RecordFailureDelayInMinutes": 5,
    "MaxKeyColumnSize": 8096,
    "EnableValidation": true,
    "ThreadCount": 10,
    "RecordSuspendDelayInMinutes": 30,
    "ValidationOnly": false
},
"TTSettings": null,
"FullLoadSettings": {
    "CommitRate": 10000,
    "StopTaskCachedChangesApplied": false,
    "StopTaskCachedChangesNotApplied": false,
    "MaxFullLoadSubTasks": 8,
    "TransactionConsistencyTimeout": 600,
    "CreatePkAfterFullLoad": false,
    "TargetTablePrepMode": "DROP_AND_CREATE"
},
"TargetMetadata": {
    "ParallelApplyBufferSize": 0,
    "ParallelApplyQueuesPerThread": 0,
    "ParallelApplyThreads": 0,
    "TargetSchema": "",
    "InlineLobMaxSize": 0,
    "ParallelLoadQueuesPerThread": 0,
    "SupportLobs": true,
    "LobChunkSize": 64,
    "TaskRecoveryTableEnabled": false,
    "ParallelLoadThreads": 0,
    "LobMaxSize": 32,
    "BatchApplyEnabled": false,
    "FullLobMode": false,
    "LimitedSizeLobMode": true,
    "LoadMaxFileSize": 0,
    "ParallelLoadBufferSize": 0
},
"BeforeImageSettings": null,
"ControlTablesSettings": {
    "historyTimeslotInMinutes": 5,
    "HistoryTimeslotInMinutes": 5,
    "StatusTableEnabled": false,
    "SuspendedTablesTableEnabled": false,
    "HistoryTableEnabled": false,
    "ControlSchema": "",
    "FullLoadExceptionTableEnabled": false
},
"LoopbackPreventionSettings": null,
"CharacterSetSettings": null,
"FailTaskWhenCleanTaskResourceFailed": false,
"ChangeProcessingTuning": {
    "StatementCacheSize": 50,
    "CommitTimeout": 1,
    "BatchApplyPreserveTransaction": true,
    "BatchApplyTimeoutMin": 1,
    "BatchSplitSize": 0,
    "BatchApplyTimeoutMax": 30,
    "MinTransactionSize": 1000,
    "MemoryKeepTime": 60,
    "BatchApplyMemoryLimit": 500,
    "MemoryLimitTotal": 1024
},
"ChangeProcessingDdlHandlingPolicy": {
    "HandleSourceTableDropped": true,
    "HandleSourceTableTruncated": true,
    "HandleSourceTableAltered": true
},
"PostProcessingRules": null

}

Issue:

The DMS task starts and appears to be migrating data correctly.
When I check the validation status, it shows "Records pending" and doesn't progress beyond this state.

Checked CloudWatch Logs and this is the only error I found:

2024-06-07T09:04:34 [TASK_MANAGER ]E: AccessDenied: Access Denied [1001705] (transfer_client.cpp:722) 2024-06-07T09:04:34 [TASK_MANAGER ]E: failed to download file <SIDP/aws_dms_validation_meta_data/aws_dms_validation_athena_results/b1281cda-8a8d-42d5-8f7b-73378520c09f.csv> from bucket <my_target_s3> as </rdsdbdata/data/tasks/HFS5QQYR45GS7AX3YBZDBBKC5Y/athena_results/b1281cda-8a8d-42d5-8f7b-73378520c09f.csv>, status = 4 (FAILED) [1001705] (transfer_client.cpp:725)

Still this AccessDenied error that I can't understand ! Has anyone encountered a similar issue or have any suggestions on how to resolve this?

Thank you in advance.

Solution

  • After reaching out to AWS support, I was able to identify the root cause of the problem. It turned out that DMS was attempting to access a different version of the object in S3, particularly because versioning was enabled on the target bucket. To resolve this issue, I needed to add "s3:GetObjectVersion" permissions to the IAM role used with DMS, which successfully resolved the problem.

    Personally, I found it surprising that such crucial information was not mentioned in the official AWS documentation. Therefore, I'm sharing this here in case it helps others facing similar issues :).

    [+] Prerequisites for using Amazon S3 as a target: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html#CHAP_Target.S3.Prerequisites