By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

How to resolve DMS failure to access LSN issue?

0

I am trying to use DMS to capture change logs from the SQL server and write them to S3. I have set up a long polling period of 6 hours on MSSQL CDC Capture job. (AWS recommends > 1 hour). DMS fails with the below error when the database is idle for a few hours during the night.

DMS Error: Last Error AlwaysOn BACKUP-ed data is not available Task error notification received from subtask 0, thread 0

Error from cloud watch - Failed to access LSN '000033fc:00005314:01e6' in the backup log sets since BACKUP/LOG-s are not available

I am currently using DMS version 3.4.6 with multi-az.

I always thought the DMS reads the change data immediately after updating the T log with the DML changes. Why do we see this error even with a long polling period? Can someone explain why this issue is caused? how we can handle this ?

  • cedhood
    2 years ago

    Hello and thanks for your question. Sorry that you are encountering this issue. The description indicates 2 scenarios, 1] that the backup t-logs have been moved off of the server prematurely or that the database has failed over (possibly due to maintenance, patching) to a server in the Always on Availability group however, the fail-over server does not have the backup files stored on it as did the primary. You may need to have a shared folder for both servers or increase the retention period of the back up files. The following limitations apply when accessing the backup transaction logs at file level: The backup transaction logs must reside in a shared folder with the appropriate permissions and access rights. Active transaction logs are accessed through the Microsoft SQL Server API (and not at file-level). UNIX platforms aren't supported. Reading the backup logs from multiple stripes isn't supported. Microsoft SQL Server backup to multiple disks (for example, MIRROR TO DISK) isn't supported.

  • rePost-User-4533217
    2 years ago

    Does increasing the retention period help in this case? We have the current backup period set as 3 days. Do we need to further increase? Also, we are not using Multi-AZ for the above. Does having multi-AZ solve this issue?

  • Zack
    2 years ago

    We are having the same issue and I have the same question as "rePost-User-4533217". Our retention period is 7 days. When "cedhood" says " the backup t-logs have been moved off of the server prematurely" - what defines "prematurely"? How long do we have to keep backups? Surely not forever. That would be cost prohibitive.

Topics
Migration & ModernizationDatabase
Tags
AWS Database Migration ServiceMicrosoft SQL ServerDatabase
Language
English
rePost-User-4533217
asked 2 years ago2.3K views
2 Answers
0

I am having a similar problem. The Replication instance that I am using is 3.4.7. Can someone help to resolve the issue?

rePost-User-5335600
answered 2 years ago
0

This seems to be an issue with DMS, from my experience, we are seeing this when maintenance is running from DMS but it does not occur every time maintenance is running, the workaround for this:

Check when the next DMS maintenance will be executed Stop DMS Task Associated with the DMS Instance Check when the DMS instance is up and running (normally takes 30min) Resume the DMS Task Associated with the DMS Instance Also, follow these AWS guidelines for a proper configuration with SQL Server as a source for DMS https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html

We implemented the Multi-AZ for DMS and for now, it is working with no issues at all, but it doubles our billing having another machine to cover a possible issue with DMS.

jthalliens
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content