Need to copy CDC changes from MULTIPLE Azure SQL DB source TABLES and SCHEMAS to an Azure Managed SQL Instance using ADF

Question

I'm relatively new to ADF and finding it difficult to configure CDC replication between multiple source Azure SQL Database tables to associated target Azure SQL Managed Instance db tables.
I have found numerous excellent blogs and demo's on SINGLE table/schema CDC configurations but there is very limited information available that makes any sense (at least to me!) on how to setup an E2E pipeline for multiple source CDC-enabled schemas/tables.

I came across this blog which is probably the closest match however it's missing heaps of details for each activity and I just don't get it! https://video2.skills-academy.com/en-us/answers/questions/542408/how-to-migrate-cdc-changes-of-multiple-tables-to-a.html

I understand there is a way to do this in ADF without having to create multiple Lookup/ForEach activities but I'm struggling to work out how this is done.
Is someone able to provide clear concise working instructions preferably with screenshots for this?

I have several hundred source tables all with CDC enabled and all are required to be replicated to a target Azure SQL Managed Instance via ADF (this is the only tooling I am able to use).
Thanks in advance
Ozi

Accepted Answer

Hello @Ozi ,
Thanks for the ask and using Microsoft Q&A platform .
As we understand the ask here is to copy the data for cdc enabled table from source to sink and also you want to this without foreach and lookup table . Please do let me know if that is not accurate,

Just to make sure you understand the reason as to why the we are using the lookup and foreach here .
Why we are using the Lookup ?

As you mentioned that you have 100 table which are cdc enabled and needs to be copied . The lookup here helps get all the details of the 100 tables .
By implmenting this , we are making the solution more generic . In future if you add enable cdc on 10 more table , this should take care of that .

Why are you using the foreach loop ?
We are looping through all the 100 table and copying them in parallel .

I understand that you are new to ADF , but then Lookup and foreach are very much used in the pipelines and i suggest that give it some time to learn .
https://video2.skills-academy.com/en-us/azure/data-factory/control-flow-lookup-activity
https://video2.skills-academy.com/en-us/azure/data-factory/control-flow-for-each-activity

Can you make the implement this without Lookup and FE loop ?
I think we can do that with a simple copy activity , but then the solution will not be generic . In the source you will have to specify the table name
select * from cdc.HR_Employee_ct
In other words what this means is that you will have to create one copy actvity for each table .

Updated :3/4/2022
I am adding the below animation of my implemenatation . Please do click on the image of the ADF and the snaimation should work .
https://imgur.com/a/9ERFtqI
Please dont forgot to define temparray .

https://imgur.com/a/9ERFtqI

Please do let me if you have any queries .
Thanks
Himanshu

-------------------------------------------------------------------------------------------------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Share via

Need to copy CDC changes from MULTIPLE Azure SQL DB source TABLES and SCHEMAS to an Azure Managed SQL Instance using ADF

0 additional answers