Issue

A capture job does not see an update transaction with a Sql*Server source.

Description

When capturing from SQL Server database if CDC is disabled on the database, capture job will not see the update made on this table

Below is a demonstration on this-

Pre-requisites

• Added table test3 to the channel cdcdemo.
• Initialized this table with all the options so that CDC is enabled for this table

You can check if CDC is enabled by running the below statement against the database in which table test3 exists. In this case, this table resides in the database 'bidb'.

sys.sp_cdc_help_change_data_capture

Another way of checking this is by checking the System Tables of this database

The table hvr_2087014516_CT is the CDC table for table test3. Local Data Processing created the table with name hvr_<source_object_ID>_CT.

When the change table is present for test3 table inserts, updates and deletes are captured

When CDC is disabled for this database you will see that insert and deletes are captured but update is not.

You can check if CDC is disabled by running below command on SQL Server source against database bidb.

sys.sp_cdc_help_change_data_capture

You will get an error message like the one below:

Msg 22901, Level 16, State 1, Procedure sp_cdc_help_change_data_capture, Line 19

The database 'bidb' is not enabled for Change Data Capture. Ensure that the correct database context is set and retry the operation. To report on the databases enabled for Change Data Capture, query the is_cdc_enabled column in the sys.databases catalog view.

I executed the insert and update statement on the SQL server

But only insert was captured

Workaround

To resolve this issue make sure CDC is enabled on the table. This can be done by running Local Data Processing initialize with supplemental logging and Scripts and Jobs option.

Also since CDC was not enabled it is required that a full refresh is done for the table that missed updates.

Please note Capture rewind does not work in this case because the CDC tables were not present and this change has not been logged in the change tables.