SQL server to snowflake replication issues

[sha1985]

We are replicating data from sql server to snowflake target. The task is running fine and record counts are matching, but some of the source columns data is being updated and those updates are not being reflected to snowflake during the cdc.

source : 

Id, status,created,updatedon 

100,1,05/25/24,05/25/24   --this record is being replicated

but the same record gets updated to status 3 

100,3,05/25/24,05/30/24 -- status and updatedon columns are not being  updated during the cdc process.

Any specific settings need to updated or modified in order to capture the updates, any thoughts????

Thanks in advance!!!

[john_wang]

Hello @sha1985 ,

Welcome to Qlik Community forum and thanks for reaching out here!

We need additional information to understand the scenario:

1. If the UPDATE was performed during the Full Load stage ? (The UPDATE was initiated after Full Load startup but before the table's Full Load completion)

2. Would you please share what's the UPDATE SQL and table creation DDL in source DB?

3. We may run function fn_dblog() in SSMS manually to confirm what's the change events were recorded in TLOG, just for comparisons to Qlik Replicate result however it's some complex

4. I'd like to suggest you opening a support ticket with SOURCE_CAPTURE/TARGET_APPLY to Verbose, and Decrypt the task log file, attach the Diag Packages, Decrypted Task log files, and source table creation DDL to support case. Our support team will be more than happy to assist you.

5. If you can provide reproduction steps then it's much easier for us, I'd like to try to repeat it in my labs to understand the behavior.

Regards,

John.

[sha1985]

hello John, please see my notes below

1. If the UPDATE was performed during the Full Load stage ? (The UPDATE was initiated after Full Load startup but before the table's Full Load completion)

: Update was initiated after the full load replication 

2. Would you please share what's the UPDATE SQL and table creation DDL in source DB?

 : I cannot get the exact update sql as the internal MS SQL server is being replicated by external vendor over the vpn tunnel 
Data flow:   external vendor--->internal source sql server--->snowflake target

3. We may run function fn_dblog() in SSMS manually to confirm what's the change events were recorded in TLOG, just for comparisons to Qlik Replicate result however it's some complex.

: At this moment I don't have access to run that command.

4. I'd like to suggest you opening a support ticket with SOURCE_CAPTURE/TARGET_APPLY to Verbose, and Decrypt the task log file, attach the Diag Packages, Decrypted Task log files, and source table creation DDL to support case. Our support team will be more than happy to assist you.

: Already opened a case and provided the requested details.

5. If you can provide reproduction steps then it's much easier for us, I'd like to try to repeat it in my labs to understand the behavior.

Source: MS sql server 2019

Target end point: snowflake

ADDITIONAL NOTES: While I was testing observed below warning messages in the log. 

Replicate encountered third-party backup files during preliminary processing. As the 'Use third-party backup device' option is currently disabled, these files will not be processed for changes.

MS-REPLICATION is not enabled for table 'dbo.***tablename****'. Therefore, UPDATE changes to it will not be captured. If you want UPDATE changes to be captured, either define a Primary Key for the table (if missing) or enable Microsoft CDC instead.

[DesmondWOO]

Hi @sha1985 ,

About following warning message, 

"Replicate encountered third-party backup files during preliminary processing. As the 'Use third-party backup device' option is currently disabled, these files will not be processed for changes."

It seems that you are using a third party tool to back up the transaction log file. These backup files must be exported to the specified location in standard Microsoft SQL Server format. You can define the specified location in the "Alternate backup folder" within your endpoint.

Regards,
Desmond 

[john_wang]

Hello @sha1985 ,

Besides @DesmondWOO comments, the warning messages described the root case of the missing UPDATE events:

---

MS-REPLICATION is not enabled for table 'dbo.***tablename****'. Therefore, UPDATE changes to it will not be captured. If you want UPDATE changes to be captured, either define a Primary Key for the table (if missing) or enable Microsoft CDC instead.

---

The solution is:

1- if the table has PK then enable MS-REPLICATION

2- If the table has NO PK, then enable MS-CDC

Hope this helps.

John.

[DesmondWOO]

Hi @sha1985 ,

About following message,

"MS-REPLICATION is not enabled for table 'dbo.***tablename****'. Therefore, UPDATE changes to it will not be captured. If you want UPDATE changes to be captured, either define a Primary Key for the table (if missing) or enable Microsoft CDC instead."

To obtain detailed information, such as the before image, from the TLOG, enable MS-REPLICATION   is necessary. If your database is not set up for MS-REPLICATION or MS-CDC, only INSERT and DELETE events will be captured.

To enable MS-REPLICATION or Microsoft CDC, please check following links for details.

Setting up Microsoft SQL Server for replication

Use MS-CDC

Regards,
Desmond