Change Data Capture for On-premises SQL Server

Question

Hi,

I want to get on-premises SQL Server data into Microsoft Fabric by using change data capture feature in Azure Data Factory Dataflow, what are the steps i need to follow(I tried SHIR but data flow doesn't support ).I followed the below doc but unable do complete it.

https://learn.microsoft.com/en-us/azure/data-factory/tutorial-managed-virtual-network-on-premise-sql-server

Please help on this.

Regards

kiran

Answer 1

Hello Ramakiran Gadde,

Welcome to the Microsoft Q&A forum.

Are you using Data factory for Microsoft fabric here? If yes, Fabric pipeline currently does not offer support for on-premises data sources. However, you can implement a workaround by transferring the data to a cloud storage using a Dataflow Gen2

Please follow this below document:

https://learn.microsoft.com/en-us/fabric/data-factory/how-to-access-on-premises-data#connect-your-on-premises-data-source-to-a-dataflow-gen2-in-data-factory-for-microsoft-fabric

and please note we have a dedicated Fabric community forum that you can ask questions here:

https://community.fabric.microsoft.com/t5/Get-Help-with-Data-Factory/ct-p/df_gethelp

if you are using ADF, the below thread explains how to use Dataflows/CDC feature in data factory with Self hosted Integration Runtime

https://learn.microsoft.com/en-us/answers/questions/1193589/how-to-use-dataflows-cdc-feature-in-data-factory-w

Method 1: Using SHIR in Copy activity get the incremental/Changed data and stage it in Azure (Blob storage/ADLS Gen2) and then use ADF dataflow to access that data and transform as per the business need3.

Method 2: The other option is to access on-premises SQL Server from Data Factory Managed VNet using Private Endpoint

I hope this helps.