Facing issue in Microsoft SQL server MS-CDC.

[Tamil__]

Source is SQL MS-CDC, we created full load + CDC task and perform DDL operation(add column) and Qlik replicate is captured that one.

Same operation we created when task in stopped state. Then we resumed the task,  then table got suspended. 

We tried to reload the table and task, while inserting a data in source and table got suspended. In that scenario, how should we run the task?

[SachinB]

Hello @Tamil__ ,

Thank you for reaching out to the Qlik community.

We could see that you are getting below warning message.
++++++++++++++++++++++++++++++++++++++
00018352: 2024-04-17T13:48:27 [SOURCE_CAPTURE ]W: The metadata for source table 'dbo.Users' is different than the corresponding MS-CDC Change Table. The table will be suspended.
++++++++++++++++++++++++++++++++++

This is because the base table and the CDC table on MS-CDC have change .

Please follow the below steps: 

Truncation
1. stop the task
2. disable cdc for table
example below :
exec sys.sp_cdc_disable_table
@source_schema = 'dbo_schema', @source_name = 'EMPLOYEE2_table', @capture_instance = 'all'
3. do truncation on source
4. do truncation on target (or do a reload of table after task resume)
5. resume task

DDL Change Handling
1. do the alter table
2. stop the task
3. disable ms-cdc for table
example below:
exec sys.sp_cdc_disable_table
@source_schema = 'dbo_schema', @source_name = 'EMPLOYEE2_table', @capture_instance = 'all'
4. resume task
5. unsuspend table or reload the table
- NOTE: Only the first DDL change will be picked up, will need to follow 'Truncation' steps if a DDL change is done while the table is suspended.

Alternate DDL Change Handling
1. stop task
2. disable ms-cdc for table
exec sys.sp_cdc_disable_table
@source_schema = 'dbo_schema', @source_name = 'EMPLOYEE2_table', @capture_instance = 'all'
3. modify source table
4. modify target table
5. start task with metadata only run (Create missing tables and then stop) – this will refresh the internal metadata without losing position
6. enable ms-cdc for table (if task is not set to do it automatically)
7. resume task
===
===
notes, if customer have multiple tables and want to disable all , is better to disable the cdc database.
example to disable:
exec demo_s_database.sys.sp_cdc_disable_db;

example to enable:
exec demo_s.dbo.sp_cdc_enable_db;

====
NOTES on older 2014 SQL server ,this issue could happen.
customer can do :
1. Upgrade you sql server to latest version

or

2. Add a new column to each cdc table as below

a. example: Alter table table_cdc
Add [__$command_id] int

b. unsuspend all the tables

c. Uncheck the capture ddl events in advance table of the source ms-cdc , and save.

d. reload the task

e. task picked up


Regards,

Sachin B

[DesmondWOO]

Hi @Tamil__ ,

I can't find any attachment in the post. If you performed DDL change (add column) and you got  following error message

"The metadata for source table 'dbo.Users' is different than the corresponding MS-CDC Change Table."

Most likely, the table structure of your source table dbo.Users does not align with SQL Server's Change Table (cdc.dbo.Users_CT), resulting in the suspension of your table.

As mentioned above, you need to run sys.sp_cdc_disable_table and sys.sp_cdc_enable_table to rebuild SQL Server's change table.

Please note that
1) Running sys.sp_cdc_disable_table will drop SQL Server's Change Table. Make sure all changes are consumed by Replicate before applying DDL change.
2) Once DDL change is applied, make sure no activity on this table before rebuilding the change table. Otherwise, you may need to reload this table.
3) Make sure table structures of your target table and Replicate's store changes table align with the new structure before resuming the task.

Regards,
Desmond