ADF Change Data Capture

Question

ADF Change Data Capture

Ashwani SRIVASTAVA 60

Hi,

I have setup ADF CDC (Change Data Capture) which is currently in Preview to sync data from SQL Server Managed Instance to another SQL Server Managed Instance however it is not replicating any data. Source table and target table are identical in terms of columns and data types. I have also used below statements on my source database to enable CDC at database level for the table.
Anyone got any suggestions what the issue might be?

Note: The CDC pipeline is started.

EXEC sys.sp_cdc_enable_db

EXEC sys.sp_cdc_enable_table  
	@source_schema = N'dbo'  
	, @source_name = N'MytableName'  
	, @role_name = N'cdc_admin'  
	, @supports_net_changes = 1
        , @captured_column_list = NULL;

User's image

Answer accepted by question author

1 additional answer

Your answer

Answer 1

ShaikMaheer-MSFT 38,631 Microsoft Employee Moderator

Hi Ashwani SRIVASTAVA,

Thank you for posting query in Microsoft Q&A Platform.

Kindly delete existing CDC resource and recreate new one and make sure below points. It will work.

Before creating CDC resource make sure CDC enabled on DB and on source table.
During creation of CDC resource don't have any changes on source table data.
In the CDC set the Key column correctly.
set latency of CDC to real time.
once above all done, then publish the changes and then start CDC.

Please note, during entire CDC creation process make sure no changes on happening on source table for better experience.

Hope this helps. Please let me know if any further queries.

Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.

Ashwani SRIVASTAVA 60 Reputation points

2023-11-24T00:35:41.9166667+00:00

Thanks Shaik for your input.
I got it working but as you mentioned above are some really good points. However, I would like to add another point here based on my setup is that the source linked service and target linked service name should be different. For example, I want to replicate one table to another table in the same SQL MI database. For this, when I used same linked service name for data source and target with different table names, it did not work. So I used different names for linked service in this scenario and it started worked for data sync between two tables within same database.

Answer 2

Amira Bedhiafi 41,121 Volunteer Moderator

If the change rate on your source table is very low or if there haven't been changes since you enabled CDC, there might not be any data to replicate. Insert or update some rows in your source table to test.

Ashwani SRIVASTAVA 60 Reputation points

2023-11-18T11:14:56.1266667+00:00

Hi - yes I tried to insert and update records but no luck. Is there a to troubleshoot ?
Amira Bedhiafi 41,121 Reputation points Volunteer Moderator

2023-11-18T11:22:46.34+00:00

Since ADF CDC is in preview, there might be known issues or limitations. Try to open a ticket.

https://learn.microsoft.com/en-us/azure/azure-portal/supportability/how-to-create-azure-support-request
Ashwani SRIVASTAVA 60 Reputation points

2023-11-18T23:15:44.6133333+00:00

hi Amira - I did another test by creating two cdc jobs as follows and it works. So looks like selecting SQL MI at both source and target does not work. I will give SQL MI to SQL MI another try and see if it works.

1 - Sync data from source SQL Managed Instance to blob storage

2 - Sync data from blob storage to SQL Managed Instance
Amira Bedhiafi 41,121 Reputation points Volunteer Moderator

2023-11-19T12:40:14.7666667+00:00

In this case, you may need to open a ticket.

Share via

ADF Change Data Capture

1 additional answer

Your answer