Syncing two Azure databases without using Azure Data Sync

Question

I have a huge (500+ table) Azure SQL database (SQL Server). I need to create a clone of this database on Azure, and sync the two databases once daily. The clone is for reporting purposes.

What is the best way to implement the sync, outside of Azure Data Sync? We've experimented with Azure Data Sync, and it's proven unreliable due to the large size of the database.

I've looked into transactional replication, but I cannot find any documentation that states that it is supported from an Azure database to another Azure database. Geo-replication may be another option, though I'm not sure it is a good fit for this use case.

I haven't, but the Cloud Data Integration component looks promising. Thanks — wooters
– wooters, Commented Sep 21, 2017 at 21:38
I think Georeplication is also a good choice and lag is very minimal — TheGameiswar
– TheGameiswar, Commented Sep 22, 2017 at 4:29
@TheGameiswar After some more research, I'm thinking that Georeplication is the way to go. Now I just have to figure out if SSRS can connect to the geo-replicated db and function with read-only access. — wooters
– wooters, Commented Sep 22, 2017 at 16:57

Alberto Morillo · Accepted Answer · 2017-09-22 20:11:16Z

1

To my knowledge, your best option is Azure Data Factory. It has a very easy to use Wizard as explained here. You can create yourself your copy activities as explained here and here.

You can schedule ADF execution as explained here too.

SQL Data Sync is in Preview and for that reason not recommended for Production environment.

Geo-Replication cannot scheduled for synchronization.

Another option is to use Cross-Database queries as mentioned here, and schedule execution of synchronization procedure created by yourself using elastic jobs or Azure Automation.

Hope this helps.

answered Sep 22, 2017 at 20:11

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wooters Over a year ago

Thanks, this looks like a powerful tool. Curious as to why geo-replication is not a good option because it cannot be scheduled? The RPO, or lag time, for geo-replication is quite low. learn.microsoft.com/en-us/azure/sql-database/…

Alberto Morillo Over a year ago

I did not recommended Geo-replication because synchronization cannot be scheduled and that is one of your requirements.

Collectives™ on Stack Overflow

Syncing two Azure databases without using Azure Data Sync

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related