Task is running slowly need to identify the cause of the table ID

[desmondchew]

We have an existing task that replicates from SQL Server 2019 to Snowflake AWS. It has been running fine with CDC. But lately the CDC latency has been increasing and we are seeing error below. I believe some changes we made at the source.

How can we find out more information based on the error below?

It looks like the table doesn't has PK that's explain the slow CDC.

mssql_approve_table_full_logging_setup(...) failed in checking PK presence for table identified by ID '114515887'

Also further checking, showed that the following errors in the logs for several tables.

MS-REPLICATION is not enabled for table 'dbo.TABLE'. 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.

I went to each of the table and notice there is already PK defined over them. How can we resolve this please?

[SushilKumar]

Hello @desmondchew 

Thanks for reaching out to Qlik via Qlik Community Support page.

mssql_approve_table_full_logging_setup(...) failed in checking PK presence for table identified by ID '114515887'

Also further checking, showed that the following errors in the logs for several tables.

MS-REPLICATION is not enabled for table 'dbo.TABLE'. 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.

Its always recommend defining pf for the table that are going to participate in any replication. 

MS-REPLICATION is not enabled for table 'dbo.TABLE'.  that's indicate pre -requiste are not mat for table as per Qlik help Doc

Request you to follow below link for more info.

https://help.qlik.com/en-US/replicate/May2023/Content/Replicate/Main/SQL%20Server/Replicate_Tables_t...

Regards,
Sushil kumar

[Yadunandan]

Hello @desmondchew 

It is a documented behavior, 

"If your database is not set up for MS-REPLICATION or MS-CDC, you can still capture tables that do not have a Primary Key, but bear in mind that in such a setup only INSERT/DELETE DML events will be captured. UPDATE events will be ignored."

You must enable MS-Replication to capture UPDATEs,
https://help.qlik.com/en-US/replicate/May2023/Content/Global_Common/Content/SharedReplicateHDD/SQLSe...

Regards,
Yadunandan

[desmondchew]

I woud like to clarify further checking on the tables I could see PK are defined on them. So how can we resolve it?

[SachinB]

Hello @desmondchew ,

Thanks for reaching out to the Qlik community!

The error message "mssql_approve_table_full_logging_setup(...) failed in checking PK presence for table identified by ID" means that the SQL Server function mssql_approve_table_full_logging_setup() failed to find a primary key for the table identified. Kindly check PKs are defined for this table or not.

Regarding the second issue "MS-REPLICATION is not enabled for table 'dbo.TABLE'. 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."

You can refer the below community link for the same.

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Task-logs-a-warning-W-MS-REPL...

Regards,

Sachin B

[DesmondWOO]

Hi @desmondchew ,

Regarding "mssql_approve_table_full_logging_setup(...) failed in checking PK presence for table identified by ID '114515887', it only means that Replicate cannot capture UPDATE DML but it does not mean the performance is impacted.

Regarding task running slowly, please identify latency issue occurs at the source side or target side. Check your task log if there is a warning or error messages that may cause the latency issue.

Regards,
Desmond

[SwathiPulagam]

Hi @desmondchew ,

You mentioned that some changes occurred at the source. Could you please check if any tables were removed from the publication?

Are you using MS-Replication or MS-CDC? If it is MS-Replication, please verify that all your tables still exist in the publication.

Thanks,
Swathi

[john_wang]

Hello @desmondchew ,

mssql_approve_table_full_logging_setup(...) failed in checking PK presence for table identified by ID '114515887'

This ID probably is the object ID in SQL Server. You may check by below queries:

select id,name from sysobjects where id = 114515887;
select id,name from sysobjects where name = '<tableName>';

Hope this helps.

John.

[desmondchew]

EXEC sp_helppublication; EXEC sp_helpsubscription;

I could see name=AR_PUBLICATION_00005

We are on MS-Replication.It appears some tables are not in the publication. How can we identify the list of missing tables and how to add it back?

[desmondchew]

Thanks John. I will keep this SQL handy. We do not need it at this moment.