Change Data Capture preview feature not working

Anmol Ganju 176

I am trying to perform some poc by using change data capture feature which is in preview, but I am not able to see table results. I create a connection with Azure SQL Database and get a succesful connection, but when I try to fetch tables every time its giving me not able to fetch result error. I tried in different data centers East Asia, East US 2 but seems I am not able to fetch the results. Is this feature enabled for specific datacenters? Or do I need to enable it from Microsoft in order to test it out?

1 answer

PRADEEPCHEEKATLA-MSFT 89,471 Reputation points Microsoft Employee

2024-07-22T10:48:48.5966667+00:00
@Anmol Ganju - Thanks for the question and using MS Q&A platform.

You need to enable change data capture on Azure SQL DB before using this option in ADF.

Enable native change data capture(Preview): Use this option to tell ADF to only process delta data captured by SQL change data capture technology since the last time that the pipeline executed. With this option, the delta data including row insert, update and deletion will be loaded automatically without any incremental column required. You need to enable change data capture on Azure SQL DB before using this option in ADF. For more information about this option in ADF, see native change data capture.

Note: Before you can create a capture instance for individual tables, you must enable change data capture for the database.

To enable change data capture, run the stored procedure sys.sp_cdc_enable_db (Transact-SQL) in the database context. To determine if a database already has CDC enabled, query the is_cdc_enabled column in the sys.databases catalog view.

-- ==== -- Enable Database for CDC -- ==== USE MyDB GO EXEC sys.sp_cdc_enable_db GO

For more details, refer to Copy and transform data in Azure SQL Database by using Azure Data Factory or Azure Synapse Analytics.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
Anmol Ganju 176 Reputation points

2024-07-22T13:10:54.3333333+00:00

@PRADEEPCHEEKATLA-MSFT

I have tried this only after enabling CDC on Azure SQL Database and also changed its tier to S3, let me re-try the same method that you have suggested will let you know if I was able to connect.

PRADEEPCHEEKATLA-MSFT 89,471 Reputation points Microsoft Employee

2024-07-23T05:09:38.4433333+00:00

@Anmol Ganju - Please do try and do let us know if you are able connect.

Anmol Ganju 176 Reputation points

2024-07-23T06:36:24.9566667+00:00

@PRADEEPCHEEKATLA-MSFT that did work but I am unable to see other tables that do not have been set for CDC, but they do have an incremental ID column, can I not setup those tables for incremental load using CDC Preview feature?

PRADEEPCHEEKATLA-MSFT 89,471 Reputation points Microsoft Employee

2024-07-25T04:09:57.4966667+00:00

@Anmol Ganju - No, you cannot use the Change Data Capture (CDC) preview feature to load data from tables that do not have CDC enabled. CDC is a feature that captures changes made to a table and stores them in a separate table. This allows you to track changes made to the table and load only the changed data into your target system.

If a table does not have CDC enabled, you can still load data from it using other methods such as full load or incremental load based on an incremental ID column. For example, you can use the Lookup activity in Azure Data Factory to read data from the source table and compare it with the data in the target table based on the incremental ID column. You can then load only the changed data into the target table.

However, if you want to use CDC to load data from a table, you need to enable CDC for that table. Once CDC is enabled, you can use the CDC source component in Azure Data Factory to read data from the CDC table and load it into your target system.

Anmol Ganju 176 Reputation points

2024-07-25T04:46:44.4466667+00:00

@PRADEEPCHEEKATLA-MSFT on the below comment
If a table does not have CDC enabled, you can still load data from it using other methods such as full load or incremental load based on an incremental ID column. For example, you can use the Lookup activity in Azure Data Factory to read data from the source table and compare it with the data in the target table based on the incremental ID column. You can then load only the changed data into the target table.

I have a table that has an ID column but its not enabled for CDC so for this table the CDC preview feature should show that table to be set as incremental load on the basis of ID column that I have right? but in my case that table is not shown to be set as incremental load in my case.

PRADEEPCHEEKATLA-MSFT 89,471 Reputation points Microsoft Employee

2024-07-25T05:07:14.7866667+00:00

@Anmol Ganju - You are correct that if a table has an incremental ID column but is not enabled for CDC, you should be able to use that column to perform incremental loads in Azure Data Factory.

To use the incremental ID column for incremental loads, you can use the Lookup activity in Azure Data Factory to read data from the source table and compare it with the data in the target table based on the incremental ID column. You can then load only the changed data into the target table.

However, if the table is not showing up in the CDC preview feature, it is possible that there is an issue with the feature or the configuration. In this case, I would recommend checking the following:

Ensure that the table is present in the source database and you have the required permissions to access it.

Check if the incremental ID column is present in the table and has a unique index or constraint.

Verify that the CDC feature is enabled for the database and the table.

If the issue persists, you can open a support ticket to get further assistance.

I hope this helps! Let me know if you have any other questions.

Anmol Ganju 176 Reputation points

2024-07-25T05:24:41.8933333+00:00

@PRADEEPCHEEKATLA-MSFT I can see the table access it since I only created that table and I am the sys admin for the database, have added primary key constraint on it as well, CDC feature is enabled on the database but since I want to use it as incremental load do I really want to enable CDC feature on it? does it not show for incremental load if I don't enable the table for CDC?

PRADEEPCHEEKATLA-MSFT 89,471 Reputation points Microsoft Employee

2024-07-29T06:49:15.02+00:00

@Anmol Ganju - I agree that this issue looks strange and I wasn't able to reproduce this issue. If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us?

PRADEEPCHEEKATLA-MSFT 89,471 Reputation points Microsoft Employee

2024-08-03T03:56:41.6033333+00:00

@Anmol Ganju - Did you get a chance to open a support ticket?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Change Data Capture preview feature not working

1 answer

Your answer