When performing CDC on a large amount of data with Amazon RDS for SQL Server source, I get an "LSN '~' could not be found" error.

[iti-attunity-sup]

Hello

A customer was running a CDC test in an Amazon RDS for SQL Server source environment with a large amount of data flowing, and was informed that CDC failed with the following error about 5 minutes after the start of CDC.

In response, we checked the @pollingInterval setting and transaction log size for sufficiency and found no problems.
We then checked with AWS, and received the following response that the specifications are as follows

--------------------------------------------------
In Amazon RDS for SQL Server, transaction log backups are performed every 5 minutes by design.
During a transaction log backup, SQL Server truncates the inactive portion of the transaction log.
Unfortunately, it is not possible to change the behavior of RDS for SQL Server itself to archive and truncate the inactive transaction log every 5 minutes.
--------------------------------------------------

In other words, if Qlik takes more than 5 minutes to capture a source due to a large amount of data, this event would occur.

Please let me know how to avoid this.

The following is an excerpt from the source capture log of this event occurrence

=====
00004736: 2024-07-24T13:23:47 [SOURCE_CAPTURE ]I: Throughput monitor: Last DB time scanned: 2024-07-24T13:23:03.460. Last LSN scanned: 00000400:003ED5E0:0003. #scanned events: 281242. (sqlserver_log_utils.c:5000)
…
00004736: 2024-07-24T13:25:55 [SOURCE_CAPTURE ]I: Throughput monitor: Last DB time scanned: 2024-07-24T13:23:27.357. Last LSN scanned: 00000400:005A563B:0006. #scanned events: 613718. (sqlserver_log_utils.c:5000)
...
00004736: 2024-07-24T13:28:06 [SOURCE_CAPTURE ]I: Throughput monitor: Last DB time scanned: 2024-07-24T13:23:44.790. Last LSN scanned: 00000400:007668B5:0024. #scanned events: 729336. (sqlserver_log_utils.c:5000)
...
00004736: 2024-07-24T13:29:48 [SOURCE_CAPTURE ]E: LSN '00000400:008D1611:0002' could not be found, neither in the transaction log nor in the backup log [1020102] (sqlserver_endpoint_capture.c:814)　★
=====

Best Regards,
Hironori Fujihira

[john_wang]

Hello @iti-attunity-sup ,

Thanks for reaching out to Qlik Community!

Would you please share which endpoint you are running now? If you are running Amazon RDS for SQL server as a source endpoint, then you may hit the limitation.

In this scenario, you may use Microsoft SQL Server (MS-CDC) as a source endpoint.

Please take note some limitations exist in MS-CDC but not in SQL Server CDC for example DDL support etc.

Hope this helps.

John.

[iti-attunity-sup]

Hello John.

The source endpoint is Amazon RDS for SQL Server.

It did not fall under the limitations you described.

This event seems to be due to inactive transaction logs being backed up after 5 minutes, as described in the AWS response, rather than transaction backups due to storage being full as described in the limitation.

For AWSDMS, the following guide is available, but is there a similar feature or other workaround for Qlik?

　-https://repost.aws/knowledge-center/dms-tlog-full-sql-server-source

Best Regards,
Hironori Fujihira

[SushilKumar · Accepted solution]

Hello @iti-attunity-sup 

As mentioned by the Expert on the question that Amazon RDS for SQL Server moves the online logs to the backup location when the maximum allocated storage size is reached. This may sometimes occur before Replicate has read the logs, resulting in missing changes on the target.

Ans this Backup location is not reachable by the Qlik replicate.

As a normal Workaround for ON Prem Environment. either you provide access to backup transaction logs or configure online logs o that they can hold transaction logs longer to be read by Qlik  replicate task further. 

Both options are not possible as Availability is managed by Amazon itself. 

So over come this issue We suggest in this scenario, you may use Microsoft SQL Server (MS-CDC) as a source endpoint.

Please take note some limitations exist in MS-CDC but not in SQL Server CDC for example DDL support etc.

Regards,

Sushil Kumar