Realtime replication of data from Azure SQL database to Synapse

Question

I have a few Azure SQL databases in production and would like to replicate the data in near real time to a Synapse SQL pool. The reason is that I have to offload some of the analytical work from the production databases and reduce the overall load on the production databases.

I currently have a pipeline in ADF with delta tables and a tumbling window that moves the data. This works fine, but I need to get this closer to real time.

Do any of you have any experience with moving data in near realtime from Azure SQL Servert to Synapse SQL pool?

Is there something similar to Cosmos HTAP?

Answer 1

You can implement this feature with custom code but Azure Data Factory is not optimal when (near) realtimeness is required. Microsoft has announced plans for Azure Synapse Link for Azure SQL Database and it will help you getting (near) real-time replication from DB to Synapse. This would be perfect fit for your needs. The announcement was stated in the ”Shape Your Future with Azure Data and Analytics” event where Purview and Synapse new features GA was published on 3rd Dec ’20.

If waiting for a while is an option, I would recommend waiting for Microsoft updates on this. The availability for public preview is not yet announced. Stay tuned!

Answer 2

Actually, there isn't a realtime Data Sync tool or service from Azure SQL database to Synapse SQL pool.

Since you currently have a pipeline in ADF with delta tables and a tumbling window that moves the data. You have two ways which are most close to real time:

1. Create a Schedule trigger(every 5 min) to run the pipeline to replicate the data in near real time to a Synapse SQL pool.

2. Create a Logic app trigger: When an item is modified to listen the Azure SQL database table, when the table data modified, add an action "Get a pipeline run" to call your Data Factory pipeline to copy the data to Synapse SQL pool.

HTH.