How to implement CDC from on-premises sources to Azure synapse?

Question

I need to implement CDC on on-premises data sources such as sql server/ excel / oracle/ api's to azure synapse
Could anyone please explain steps/demo how to implement solution.

Answer 1

Hi @Neeraj Sharma ,

Welcome to Microsoft Q&A Platform. Thank you for posting your query here.

You can leverage Synapse Pipelines or Azure data factory pipelines to implement CDC.

There are may tutorials or articles documented to explain how to perform Incremental copy from different types of source data such as structured data or semi-structured/unstructured data.

Below are few documentation links for same. Kindly check them for better understanding.

• Incrementally load data from Azure SQL Managed Instance to Azure Storage using change data capture (CDC)
• Using Change Data Capture and Azure Data Factory to Incrementally Load Change Data
• Incrementally load data from Azure SQL Database to Azure Blob Storage using change tracking information using the Azure portal
• Incrementally load data from a source data store to a destination data store
• Incrementally copy new files by LastModifiedDate with Azure Data Factory
• Incrementally load data from multiple tables in SQL Server to a database in Azure SQL Database using the Azure portal
• Incrementally copy new and changed files based on LastModifiedDate by using the Copy Data tool

Please Note, Logic explained above can also be applied on on-prem SQL as well. We should consider installing Self-hosted IR to access our on-prem data.

Hope this will help. Please let us know if any further queries.

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

• 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

Answer 2

If source is on premise SQL Server then

Export data using BCP utility into files. Use Last modified column to read the only updated data
Upload these files to Azure storage using AzCopy and then use ADF to read and process it further
You can also maintain a audit of it by logging the details into a table like

Batch ID | SrcTableName | StartDate | EndDate | Status

So next day when you read previous day's EndDate is your start date (> Greater than)

Answer 3

Just checking in to see if there are any tools we an use to implement CDC from on-premise Oracle database to Azure Synapse