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

Improve this question

3 Answers 3

Reset to default
2

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

Improve this answer
5
  • Its just i want to replicate a production db to sand box db
    – Nathan
    Jul 10, 2019 at 15:22
  • 1
    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, 2019 at 16:41
  • yes its just for testing purposes only
    – Nathan
    Jul 11, 2019 at 7:36
  • Interesting! Regarding Read Scale-Out, do you have a source for "Synchronous commit is used to prevent data loss"? I've been looking for this info and would like to make sure.
    – Timo
    Mar 22, 2020 at 12:23
  • What about zone-redundant configuration on the premium/buscrit tiers? Do they synchronously replicate to their same-datacenter secondary and asynchronously to the secondaries further away?
    – Timo
    Mar 22, 2020 at 12:24
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

Improve this answer
2
  • Its just i want to replicate a production db to sand box db
    – Nathan
    Jul 10, 2019 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, 2019 at 10:08
2

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.

Improve this answer
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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