My team has a pretty dedicated Azure SQL for the use of pretty much everything.
For our web application, however, we use Azure Postgres (it's a Django app running on Linux App Service).
The data engineers usually only work on the Azure SQL DB. So what's the best way to have real-time movement of data from Postgres to Azure SQL database?
So far, the only option I have in mind is to have a Copy Activity in Azure Data Factory to do the copy job. But apparently it's not real time.
Many thanks!
As I searched and per my experience, there isn't a way to sync the data between Azure Postgres and Azure SQL database. Even the Azure SQL database Data Sync also have the 5min delay.
As you said, we could create a Copy Activity in Azure Data Factory to do the copy job. Using upsert opreation in the Azure SQL sink settings.
Run the pipeline first to make data same in Azure Postgres and Azure SQL database. Then create a schedule trigger(ervey 5 or 10 min) to execute the pipeline. The may achieve the data sync.
upsert operation? Why don't we delete the data as a pre-copy script and then start the copy data activity?
upsert means update and insert. If the data changed in Source, then update it in Sink. If there is new data, then insert the new data. We don't need to delete all the data.upsert will have a better performance than delete then insert?