I need to perform two syncing of data from on-premise to Azure SQL database and vice versa.
I have a database on premises which is used by many applications or systems. I've moved the database to Azure SQL by using transaction replication but I can't make all the systems to use Azure SQL database.
Now, few systems would be consuming the on premise SQL Server database and performing CRUD operations on top of it and few systems would be using Azure SQL DB and performing CRUD operation on top of it.
I need both the databases in sync.
What is the best way to achieve this?
To do a true two-way sync between Azure SQL Database and an on-premises database, you need to set up merge replication. That is not supported in Azure SQL Database though.
Your next alternative is to build your own process to handle this. This Microsoft post might help some.
After that, you're looking at purchasing a third party tool or service. There is Zumero, which has a service on Azure. There may be others, but I can't find them.
Frankly, I wouldn't do it. I'd spend the necessary time and effort to get everyone connected to the Azure SQL Database if that's the preferred location for data management. Any other approach is likely to be costly and error prone.
