0

I have Azure SQL Georeplication setup on a production database. This process has worked fine for some time. The georeplicated database is on another server in an Elastic Pool. I now want to keep an on-prem database in sync and am considering setting up Azure SQL Data Sync (ADS). My preference would be to have Azure Data Sync work between the georeplicated replica and the on-prem SQL. In that model, Azure Georeplication keeps working as is, and the data from the relica syncs to the on-prem server.

I have read all of the limitations of Azure Data Sync and it does not say that this model will not work. In my case, I have over 500 tables in the data model, only 25 or so that need to be synced. When I use the Azure Portal to setup these tables, it errors saying that ADS only supports 500 tables. The official workaround for this is to create a user that only has rights to the tables I want to replicate (the 25), and use this user to connect to the hub DB.

Because the replica is read only (since it is a mirrored reeplica), the CREATE USER statement fails, complaining the DB is read only.

My question: Is this a feasible scenario, to use a georeplicated database as a hub DB in Azure SQL Data Sync? I can create the user in the base DB which will replicate to the georeplicated DB, but it feels like plumbing, and worse, when I add my replicated tables (the 25) it will need to create triggers on those tables which will also likely fail because of the readonly attribute of the replicated DB.

Any insight is appreciated.

Thanks...

DanielG
  • 1,669
  • 1
  • 12
  • 26

1 Answers1

0

Azure SQL Data Sync won't work in this scenario because if a failover happens SQL Data Sync will fail.

If you think about it, Azure SQL Data Sync offers you already the benefit of Geo-replication. What I mean is that SQL Data Sync helps you too to geo-replicate your data across data centers and with on-premise also.

Based on the above considerations, i would remove the geo-replication and set 2 Azure SQL Databases and one on-premises SQL Server instance using SQL Data Sync. Your applications targeting Azure SQL can connect to either one of the Azure SQL Databases, if they cannot connect to one they can connect to the other. With SQL Data Sync you can let your users connect to the Azure SQL Database nearest to them.

Applications on premise if they cannot connect to Azure SQL, they can connect to the SQL on-premise.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30