Sync SQL Server onPrem Availability Group to Azure SQL Database

0
votes

We have an ERP solution running on an on-premise SQL Server Availability Group. The new data warehouse has been set up on Azure based on Azure SQL database. We now need a way to update the DWH based on ERP deltas into the cloud.

The challenge:

  • we don't want another VM with SQL Server in the cloud for replication
  • we have to have delta's because transaction tables and backups are too big to ship to azure daily
  • AG primary might failover to another secondary which then would need to know about the last sync

Any ideas on how to approach that and possible architectures/scenarios/tools?

1
sql-serversynchronizationazure-sql-database
Hi, If my answer is helpful for you, can you please accept(mark) 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

1 Answers

0
votes

If your Hub database is Azure SQL database, you can use Azure SQL Data Sync.

Azure SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

You can sync the data from on-premise SQL Server to Azure SQL database with Data Sync.

But Data Sync doesn't support Azure SQL Data Warehouse.

Another way maybe you can think about Azure Data Factory.

Using Data factory to sync the data from on-premise SQL server to Azure Data Warehouse. You can trigger and Schedule the pipeline execution daily or weekly, or other time.

You can reference these tutorials:

  1. Copy data to and from SQL Server by using Azure Data Factory
  2. Copy and transform data in Azure SQL Data Warehouse by using Azure Data Factory
  3. Syncing on-premise databases to Microsoft Azure Cloud using Azure Data Factory

Hope this helps.