What's the best way to Sync on premise SQL Server database to Azure Windows Server SQL Server

0
votes

What's the best way to sync an on-premise SQL Server database to Azure Windows Server SQL Server?

The offline server we have Windows Server 2016, SQL Server and Dynamics Nav.

We created an Azure VM and installed Windows Server 2016, SQL Server and restored the database. We also installed Nav Dynamics and sync the VM SQL Server. Everything is integrated on the VM.

We want to automate data and sync the on-premise SQL Server with the online SQL Server once every hour.

What's the best, efficient way to achieve that. We tried to use Azure SQL Server but there is limitation for the data sync group where it can only sync database with less 500 tables in it. We have more than 2500+ tables on our database.

1
sql-serverazureazure-sql-databasewindows-server-2016dynamics-nav
hi, for azure sync it looks like you have to create multiple sync groups e.g. azure.microsoft.com/en-us/blog/… Couple of other ways possibly is to use Azure Data Factory to schedule runs which check for updated data since last run and then insert into corresponding tables or use Azure Functions/Queues to mimic the same thing, schedule a trigger to select latest rows, send to queue, listener to a queue then applies update on azure sql correspondingly. That is probably the cheapest way as well possibly – Mark West

1 Answers

1
votes

I know from your previous question that Azure SQL Data Sync is not for you, but SQL Data Sync is based on Microsoft Sync Framework, and I believe you can build your own solution using Microsoft Sync Framework as shown here.

A commercial alternative you have is SymmetricDS.

Another option, that you may like more is SQL Server Log Shipping but using Azure Blob Storage account as pipeline, with the benefit that you will be sending your backups to Azure Storage account. You would need to do your own backups to Azure Blob Storage, and then have the secondary server(s) restore them from Azure Blob Storage, because Log Shipping wizard on SSMS does not support Azure Blob Storage (to my knowledge).