2

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.

Nirman
  • 6,715
  • 19
  • 72
  • 139

1 Answers1

0

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Yes, I found Azure Data Sync is more suitable for Azure SQL and not for SQL Server instance installed on Azure VM. But the solution you have mentioned will sync the database on a periodic basis and will require manual intervention. What I am looking for is the approach using which change implemented in primary instance (which is on-prem SQL) will be applied immediately to other instance with minimum or no efforts. – Nirman May 07 '19 at 04:56
  • You could use Transnational Replication. But if you want to be able to apply schema and data changes at either dev instance, that won't work. – David Browne - Microsoft May 07 '19 at 11:57