AWS DMS Task getting failed after completion with error as AlwaysOn BACKUP-ed data is not available

Question

I have created DMS task for migrating data from one RDS MS SQL Server instance to another RDS MS SQL Server instance with "Full load + ongoing replication", and data copied to target instance but status showing as failed with below error -

Last Error AlwaysOn BACKUP-ed data is not available Task error notification received from subtask 0, thread 0 [reptask/replicationtask.c:2822] [1020465] Error executing source loop; Stream component failed at subtask 0, component st_0_NJZW5VSVPASASA4E4N2SJGTVEZ3UIDLJHX3NDY; Stream component 'st_0_NJZW5VSVASASAZ4E4N2SJGTVEZ3UIDLJHX3NDY' terminated [reptask/replicationtask.c:2829] [1020465] Stop Reason FATAL_ERROR Error Level FATAL

Cloud watch logs:

2021-01-04T15:24:02 [SOURCE_CAPTURE ]E: Failed to access LSN '0000009a:00000087:0010' in the backup log sets since BACKUP/LOG-s are not available. [1020465] (sqlserver_endpoint_capture.c:717) 2021-01-04T15:24:02 [TASK_MANAGER ]I: Task - PHTLF2WXLFVWESX4GLZ2CKAW3MY76HLRXHIYYYY is in ERROR state, updating starting status to AR_NOT_APPLICABLE (repository.c:5103) 2021-01-04T15:24:02 [SOURCE_CAPTURE ]E: Error executing source loop [1020465] (streamcomponent.c:1867) 2021-01-04T15:24:02 [TASK_MANAGER ]E: Stream component failed at subtask 0, component st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY [1020465] (subtask.c:1409) 2021-01-04T15:24:02 [SOURCE_CAPTURE ]E: Stream component 'st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY' terminated [1020465] (subtask.c:1578) 2021-01-04T15:24:02 [TASK_MANAGER ]E: Task error notification received from subtask 0, thread 0 [1020465] (replicationtask.c:2822) 2021-01-04T15:24:02 [TASK_MANAGER ]E: Error executing source loop; Stream component failed at subtask 0, component st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY; Stream component 'st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY' terminated [1020465] (replicationtask.c:2829) 2021-01-04T15:24:02 [TASK_MANAGER ]E: Task 'PHTLF2WXLFVWESX4GLZ2CKAW3MY76HLRXHIYYYY' encountered a fatal error (repository.c:5196)

What is the issue? any suggestions!

Answer 1

I don't know if you're still working through this, but I wanted to post the problem & solution for anyone else.

There is a bug in the AWS DMS replication engine version 3.4.3 when using RDS SQL Server as a source endpoint, where the 5 minute log backup used for point-in-time recovery with RDS disrupts the CDC process. The solution/workaround is to use a different replication engine version set on the replication instance, such as v3.4.2.

From AWS support:

Root Cause:

Researching internally, I observed this error to be a know issue with DMS replication instance running the version 3.4.3, where RDS SQL Server has truncated active transaction logs (TLOG) or there are no activities in the database. This is a known situation when using RDS SQL Server as a source.

Why task failed:

DMS 3.4.3 tasks are failing due to missing LSNs in source RDS SQL Sever databases, when source SQL Server MS-CDC enabled tables remains idle for a long period of time, the DMS task still fails even when you increase the MS-CDC polling interval on source database.

This is because MS-CDC polling interval prevents TLOGs truncation only when there are changes on source tables, if source tables remains idle, the LSNs does not increment, then the RDS t-log backup job removes the LSNs from the online t-log that are required by DMS, causing this issue.

Additionally, DMS task is failing with misleading errors such as:

00016458: 2020-12-28T09:30:08 [SOURCE CAPTURE ]E: Failed to access LSN '00001002:0001c389:0003' in the backup log sets since BACKUP/LOG-s are not available. [1020465] (sqlserverendpoint_capture.c:717

This error can still occur even when MS-CDC Polling interval is set to sufficient value, it happens when the RDS t-log backup job removes an LSN from the online t-log. This issue is especially seen in databases with less traffic since the LSNs are not incrementing and DMS is looking at the last committed LSN.

Workaround:

I would recommend trying to migrate the same tables by creating a similar task using DMS RI version 3.4.2 or 3.3.4

Answer 2

I had a similar error to this using Aurora PostgreSQL v14.5 and AWS DMS. I was using a DMS Full load + CDC job (using pglogical behind the scenes) to migrate from one table to another (on the same system).

Issue was resolved by rolling back my PostgreSQL version from 14.5 to 13.7.

Answer 3

DMS works with log backups or ldf file.

E: Failed to access LSN '0000009a:00000087:0010' in the backup log sets since BACKUP/LOG-s are not available.

According to the log said, your log backup might have been deleted or moved from the location which is taken. DMS did not found LSN in your backup, so it does not go on because log backups have a chain.

If you have, you can increase the retention period of your log backups.

SELECT 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 

You can check your log backups with a script which is above and check out your log backup on a physical device wheater or not is there.