Approach for using Azure VM for SQL

Question

Following is the exact scenario in my application:

• SQL Server database is hosted on-premise locally in US office for development purpose.
• Developers are distributed in 3 different regions (US, India and Australia).
• Developers from India and Australia faces a lot of delay while trying to access the database from application.

In order to resolve the above issue, we identified a following approach -

• Create a VM and install SQL Server there.
• Restore database over there.
• US developers would continue using the database deployed in their environment.
• India and Australia developers would use the SQL database instance hosted in Azure VM.

In order to enable synchronization of data and schema between these two databases (Azure VM SQL, and On-prem SQL instance), we are planning to use Azure Data Sync.

I believe most of the things in above scenario are subject of research. But guidance of someone who has already worked on similar things would be very much helpful. Also, we are not using Azure SQL because that would require changes in database schema, as its very old and legacy database

Could you please suggest if the above approach is ideal or not? Note, this is only for the ease of development, and we are not moving our production database outside on-prem setup.

Answer 1

I would not attempt to use Azure Data Sync here, first because you are not otherwise using Azure SQL Database, and second because it's not intended to sync schema changes like this.

Instead pick a primary replica for data changes, and periodically ship and restore backups to refresh the secondary instance. For schema changes, use SQL Server Data Tools and your Source Code Repository (Azure DevOps) to manage the changes.