AWS DMS Data Latency and Transaction Log Growth Issues in SQL Server to RDS MySQL Replication

Question

We are replicating specific tables from a SQL Server source to an RDS
MySQL destination using AWS DMS. The DMS task is configured with a 
sysadmin role for the DMS database user. It was later reported that the SQL 
Server transaction log had experienced significant growth. After root-cause 
analysis, we found that the SQL Server Log Reader Agent was not 
functioning properly. We were unable to resolve this issue.

To mitigate this, we added the ReadBackupOnly
parameter in the DMS task configuration. This parameter causes DMS to read 
changes from transaction log backups rather than the active transaction 
logs (TLOG). While this approach helped with the transaction log growth, it 
introduced latency and data inconsistencies, with some changes not being 
captured and data being delayed by up to 2 days.

What We’ve Tried So Far:

1. Exploring SafeguardPolicy: EXCLUSIVE_AUTOMATIC_TRUNCATION:
• In an attempt to avoid reliance on the SQL Server Log Reader Agent, we 
switched the SafeguardPolicy parameter to 
EXCLUSIVE_AUTOMATIC_TRUNCATION. This setting allows DMS to manage TLOG truncation directly using sp_repldone, bypassing the 
Log Reader Agent.

• However, this resulted in multiple task failures. The DMS task threw 
the following error:

Last Error: Task '****' was suspended due to 6 successive 
unexpected failures. Stop Reason: FATAL_ERROR, Error Level: FATAL.

• Further logs can be provided if needed.

2. Revisiting the ReadBackupOnly Approach:

• Another approach we are considering is to continue using the 
ReadBackupOnly parameter but to increase the frequency of 
transaction log backups. Currently, log backups are done daily, 
and increasing the frequency could mitigate data latency issues by 
ensuring DMS has more frequent access to transaction log changes.

Any insights are greatly appreciated

Subhash Raut · Answer

as mentioned by you 2 approaches
1. EXCLUSIVE_AUTOMATIC _TRUNCATION : I would suggest using the default mode RELY_ON_SQL_SERVER_REPLICATION_AGENT just to make sure we dont run into similar tlog  full issues you can setup a sql agent job running continuously but checking after specific interval (say 15 mins or 1 hour) if SQL Server Log Reader Agent is running if not start (and also you check failures if more than 10 instances you could send a email to have it checked) wherein the schedule of this job could be **SQL Server Agent starts**
2. using readbackuponly could be another way but instead of daily backups we need to reduce the frequency to say every hour or every 15 mins.

AWS DMS Data Latency and Transaction Log Growth Issues in SQL Server to RDS MySQL Replication

Add your answer

Relevant content