How to resume capturing changes after corrupted change tables due to failover

[MoeE]

Hi,

A client has presented the scenario where after their source MS-CDC SQL Server fails over to their secondary node, the change tables are corrupted. They asked if there is a way to resume capturing changes from the correct point in time without running a full load.

Is it perhaps by design that the secondary node does not contain change table data or it will not replicate the primary node change table data? Are the sql server cdc tables replicated to the secondary node?

Any thoughts on this/general things to look into? Would be appreciated. Thank you.

Kind regards,

Mohammed

[john_wang]

Hello Mohammed, @MoeE 

At the moment, it’s difficult to provide further analysis because there’s no detailed information about the reported change table corruption.

In a SQL Server high-availability environment, MS-CDC is supported during failover. However, the CDC capture and cleanup jobs may need to be re-created or re-enabled on the new primary replica after the failover.

Importantly, the LSN continues to increase after failover and is not reset. Therefore, the task can either be resumed or restarted from a specific LSN or timestamp.

Hope this helps.

John.

[DesmondWOO]

Hi @MoeE ,

You may try creating a new task using MS‑Replication, which allows Qlik Replicate to retrieve changes from the transaction log or backup files at an earlier timestamp. However, please test this approach first — for example, by creating a STORE CHANGES ONLY task to verify that the captured changes are correct. Additionally, be aware that duplicate records may occur when switching the task back to an MS‑CDC configuration

Regards,
Desmond