How to use Dataflows/CDC feature in data factory with Self hosted Integration Runtime

Question

Hello team,

I wanted to use CDC feature of Azure Data factory. We have a on-premise SQL server we are connecting to through Self hosted integration runtime(SHIR).

Is it possible to use CDC or Data flows with SHIR. If not, is there a workaround for it.

Or can we connect to on-premise SQL through Azure Integration runtime? Also, I am not sure if it is correct to use Azure Integration runtime to connect to on-prem SQL server or not

Thankyou

Answer 1

Hi @Shweta Chavan ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

If you would like to copy and transform On Prem SQL server data incrementally (CDC or delta data), you can follow below two methods as per your convenience.

Note: Data flow doesn't support SHIR, hence Azure IR is only supported in mapping data flows.

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 need.

Here is a video demonstration of this method by ADF product team - How to transform data from SQL Server on-prem using ADF with Mapping Data Flows.

Method 2: The other option is to access on-premises SQL Server from Data Factory Managed VNet using Private Endpoint. In this process you can avoid installing SHIR and you can rely on Azure IR with vNet.

Below is high level flow which explains how the network connecton is established between ADF managed vNet using Private endpoint and On Prem SQL Corporate Network.

Here is a tutorial which provides steps for using the Azure portal to setup Private Link Service and access on-premises SQL Server from Managed VNet using Private Endpoint. - How to access on-premises SQL Server from Data Factory Managed VNet using Private Endpoint

Once you have established the connection from ADF Managed vNet to your OnPrem SQL server CorpNet as mentioned above, you can utliize Azure IR with vNet to connect to your On Prem SQL and utilize the CDC feature in Maping dataflow.

Hope this info helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.