Realtime replication of data from Azure SQL database to Synapse

2
votes

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?

2
azureazure-sql-databaseanalyticsazure-synapse
do you think about Data sync？ – Leon Yue
Do you mean AWS Data sync? I'm currently locked to Azure, so AWS products will not be a solution. – AlbianAndy
Hi @AlbianAndy, If my answer is helpful for you, hope you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Leon Yue

2 Answers

1
votes

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.

enter image description here

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

enter image description here

HTH.

0
votes

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!