0

I have a REST Service that needs to update data into Azure SQL Server database and also into On Premise SQL Server database. Both databases should have same data in certain database tables.

Scheduling Service that runs in Azure and process data transfer into Azure Database and then into On Premise database e.g. after every 5 minutes is okay. Also other techniques too e.g. REST updating both Azure database and then On Premise database.

I'm quite new using these Azure techniques using both configurations and programming with C#.

I have read about Function Apps, Azure Data Factory, Azure Logic Apps and Synchronizations but don't know what technique is ok to transfer data from Azure to Azure database and then On Premise database.

How have you solved this kind of issue?

Best regards,

Michael

Mickex Land
  • 1

1 Answers1

0

Whatever service you use, will need to be able to access both SQL servers. Accessing Azure SQL is easy, but I imagine your on-premises SQL server is not exposed to the internet, so you need a way to connect to that.

Functions are a nice simple tool for running any sort of code without having to worry about servers etc, however, it does not have a way to reach into your on-prem environment. You could do something with having messages written to service bus and then run another application on-premises and have that collect the message and write it to the on-premises DB. All a bit painful though.

Both Data Factory and Logic Apps have a slightly better solution because the both have the premise of a "Data Gateway" an app you run on-premise which allows the Azure service to talk to on-premise resources without directly exposing them to the internet.

If you want to provide a REST endpoint and write to both databases at the same time then logic apps are probably the way to do it. It can present a REST endpoint to call and you can then build a workflow with this data to write to both places, using the data gateway for on-premises. If however, you are happy to write to the Azure SQL DB and then have a process sync that data to on premises, then Data Factory can do that for you.

Some useful links:

Logic Apps Data Gateway

Data Factory Integration Run Time

Copy Data To/From SQL Using Data Factory

Sam Cogan
  • 38,736
  • 6
  • 78
  • 114