Have Active-Active Azure Sql Server Database replication

Question

We can have passive read-only asynchronous real-time sync-up for Azure SQL database, for disaster recovery.

But our requirement is to have real-time sync-up between both active read-write databases to provide low latency to customers in different locations of the world.

for example:

I'm providing e-commerce website, I will update data in one of the database server and other connected databases in sync with this database should get updates.

Users from different servers of the world will get connected to their nearest data center for low latency. If someone buys something or puts some review, it should get updated in all other databases. In this way we need active-active database sync.

We explored multiple items on this, but did not find anything relative.

Can anyone please guide me on how to achieve this.

Answer 1

SQL Server has Peer-to-Peer Transactional Replication, but you need to ensure in the application that conflicting changes are not introduced on multiple nodes.

SQL Server also has Merge Replication, which allows updates at any subscriber, and supports custom conflict resolution.

These are both available on SQL Server VMs. Limited replication options are available on Azure SQL Database Managed Instance. Azure SQL Database also has Data Sync.

Azure Cosmos DB also supports Multi-Master.

In either case multi-master introduces significant cost/complexity. Often it's better to just have a single writable master with regional readable replicas. In that configuration the application needs to connect to the global master for writing, but can read from a local replica. For this pattern you can simply use Failover Groups.