How to achieve the transaction replications setup from many on prem sql in azure.server

Question

Currently we are using transactional replication to bring real time data from on prem sql source servers to azure db destination. I need to migrate this setup in azure with real time availability of data. I used the cdc ad event triggered methods but client won't accept it. Is there any other way to achieve real time data syn from on prem sql servers 2019 version to dwh in azure or fabric. Also synapse link method won't work for 2019 version.

Answer 1

@Jothi Lakshmi P M - Thanks for the question and using MS Q&A paltform.

To achieve real-time data synchronization from on-premises SQL Server 2019 to Azure Data Warehouse or Azure Synapse Analytics.

One option is to use Azure Data Factory with the "Copy Data" activity and the "Change Data Capture" (CDC) feature. CDC captures changes to the source database and stores them in a separate table, which can then be used by Data Factory to incrementally load data into the destination.

"Copy Data" activity and the "Change Data Capture" (CDC) feature to achieve real-time data synchronization from on-premises SQL Server 2019 to Azure Data Warehouse or Azure Synapse Analytics.

To use CDC with Azure Data Factory, you need to enable CDC on the source database and create a CDC control table to track changes. You can then use Data Factory to read the changes from the control table and incrementally load them into the destination. The "Copy Data" activity in Data Factory supports CDC natively, so you can configure it to read the changes from the control table and apply them to the destination.

For more information on using CDC with Azure Data Factory, you can refer to the official documentation. Hope this helps. Do let us know if you have any further queries.