Issue with Azure SQL Database Table Detection in CDC

Question

I have a table in azure sql database with schema,

TABLE NAME: EMPLOYEE   
COLUMNS:   PersonID int NOT NULL,

    Name varchar(255),

    Age int

But this table is not detected in the pane list of source table. As per MS doc, The pane lists only tables that have supported incremental column data types. But this table has Person Id of int data type which is incremental data only. What are all the supported data types ?

Answer 1

Hi @Aswin

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

Regarding the detection of the EMPLOYEE table in Azure SQL Database for Change Data Capture (CDC). Based on the information you provided, it seems that the PersonID column, which is of type int, should indeed be a valid incremental column type for CDC. The int data type for PersonID is indeed supported for CDC. In fact, the schema you have provided (int, varchar(255), int) is compatible with CDC, as all these data types are supported.

However, there are a few key factors to consider that might explain why the table is not appearing in the source table list:

Primary Key Requirement - CDC requires that the table has a primary key defined. Please ensure that the EMPLOYEE table has a primary key constraint on the PersonID column.

CDC Enablement - Make sure that CDC is enabled for both the database and the specific table.

Permissions - Verify that your user account has the necessary permissions to access CDC features.

Database Compatibility Level - Check the compatibility level of your Azure SQL Database. Some features may not be available in lower compatibility levels.

Schema Changes - If there have been recent changes to the schema of the table, it may take some time for those changes to be reflected in the CDC configuration.

For additional information, please refer: Change data capture (CDC) with Azure SQL Database

What are all the supported data types ?

https://docs.informatica.com/data-integration/powerexchange-for-cdc-and-mainframe/10-5-1/cdc-guide-for-linux--unix--and-windows/part-3--powerexchange-cdc-data-sources/microsoft-sql-server-cdc/planning-for-sql-server-cdc/sql-server-datatypes-supported-for-cdc.html

Disclaimer: This response contains a reference to a third-party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

I hope this information helps. Please do let us know if you have 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.

Answer 2

Hi Aswin S ,

As mentioned in the video: CDC (change data capture) Resource in Azure Data Factory
CDC gets enables on a table , not on any column . Below is the SQL script to enable the CDC.

Once the CDC is enabled in SQL, you can disable Automapping and perform mapping manually and select the relevant column as 'Key column' . Checkout the images below:

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou