0

I would like to ask a question how to setup azure syncing of two database on azure

i need real time data replication to the other database

2

Depends on the Azure offering you want to use.

Azure SQL Database

You can get Geo-replication setup which is very straight forward and will give you a read-only secondary.

Azure Portal : Geo replication

Azure SQL Managed Instance : You can use regular transaction replication (under preview) or geo-replication.

Azure Managed Instance

  • Its just i want to replicate a production db to sand box db – Nathan Jul 10 at 15:21
  • A read-only replicated db from a Production will also fall into Production category. It is your way to offload some Production read work onto it. For a Sandbox, use a backup-restore to refresh your Sandbox. – Ramakant Dadhichi Jul 11 at 10:08
1

Note: Since you tagged your question with Azure SQL Database, I assume this is the PAAS solution you are using.

It depends on what you are trying to achieve. Do you want to read data from a readable secondary database or do you want a DR solution?

In Azure SQL Database there are 2 options to replicate your data: Read Scale-out and Active geo-Replication.

Read Scale-Out feature enables you to read data from read-only replica's instead of using the primary replica solely. This feature is enabled by default when you are using Premium, Business Critical or Hyperscale.
When running on Basic, Standard or General Purpose you cannot use this feature since it uses a different high availability technology than Premium. Whereas Premium uses a technology similar to AlwayOn availability groups, standard uses a separated compute and storage layers. More information about the underlying architecture can be found here.
Read Scale-out isn't a DR solution since all replicas are kept in the same DC. Synchronous commit is used to prevent data loss.
More info

Active geo-replication on the other hand allows you to create readable secondary databases in the same or different region(s). These databases must be of the same service tier, but apposed to Read scale-out, premium isn't required. You will only pay additional licensing cost for each secondary you add.
Asynchronous replication is used to prevent a performance hit on your primary database. Consequently, it's possible that there is some latency when reading from a replica, escpecially when this replica is hosted in another DC.
This can be used as a DR solution if you host your replica(s) in another DC.
More info

  • Its just i want to replicate a production db to sand box db – Nathan Jul 10 at 15:22
  • And what would be the purpose of this sandbox? If it's for testing purposes I wouldn't use any form of replication or HA and just restore a copy of the database on a regular basis – Thomas Costers Jul 10 at 16:41
  • yes its just for testing purposes only – Nathan Jul 11 at 7:36
0

If you would like to have two-way or one-way replication between two Azure SQL Databases then you can use Azure SQL Data Sync. It is free. You need consider one of the databases a hub and another database a member database. The member database can be another Azure SQL Database or a SQL Server instance (IaaS or on-premises). You can have many member databases.

The big disadvantage of this method is a 5 minute (or more) latency because the mimimum sync interval is 5 minutes.

If you want more information about SQL Data Sync visit this article and if you want to go with this option please start here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.