Using Azure Data Sync when On-Prem machine hosting SQL Server may periodically lose internet connectivity

Question

I was looking to use Azure SQL Data Sync to replicate bi-directional changes between an On-Prem SQL Server DB and an Azure SQL DB. I have got it working successfully, however I do have a challenge when using it.

The challenge I face is that there will be times when the On-Prem machine will lose internet connectivity (it will be onboard a ship travelling all over the world).

When the data sync agent recognises the loss of connectivity to the sync then it immediately requests a new Agent key be entered in order to start working again, this is typically generated from within the Azure portal although I think it may be possible to generate the key via a PowerShell cmdlet. I need the sync-ing to start automatically once internet connectivity is re-established. It isn't essential that the two servers are constantly connected, but it is a requirement that the minimum amount of human intervention is required to get the connection working again.

I believe this feature in Azure SQL Data Sync means that this isn't the tool for this particular job.

Would you agree or is there a work around ? If there is no work around then can you recommend another tool in the Microsoft toolbox that would do the job ?

thanks

Answer 1

You can create your own sync application as explained here.

Have you tried using transactional replication? You can implement replication as explained here.

A non-Microsoft alternative to SQL Data Sync is SymmetricDS.