Data Synchronization - Syncing Two Databases in SQL Server

Question

I'm needing to take one database and have it on two separate sql servers (in two different physical locations) and have it stay in sync. Is there a way that I can do this?

I've looked into SQL Server Replication

ie: https://learn.microsoft.com/en-us/sql/relational-databases/replication/tutorial-preparing-the-server-for-replication?view=sql-server-ver17

&

https://learn.microsoft.com/en-us/sql/relational-databases/replication/tutorial-replicating-data-between-continuously-connected-servers?view=sql-server-ver17

but this seems to just be updating the second server (subscriber) from changes made on the first server (distributor). I need it to work that way but also have changes made on the second server (subscriber) sync to the first server (distributor).

Any help is GREATLY appreciated.

Thanks!

Answer 1

Hi Brad,

In SQL Server, achieving bi-directional synchronization requires more than standard transactional replication, as it only supports one-way data flow. For updates to be synchronized between two writable nodes, you would need to use either Bi-Directional Transactional Replication or Merge Replication.

These options allow changes at both locations to be synchronized, but they are complex and require careful planning for conflict detection and resolution to prevent data inconsistencies. Generally, unless both sites need to be writable, it is more stable and less risky to use a single write source with read-only replicas.
Also thank you @Erland Sommarskog for providing the documentations.

Answer 2

Yes, this can be done. There are at least two ways to go within the realm of Replication:

• Bidirectional transactional replication.
• Merge replication.

I have not worked with any, so I cannot answer any specific questions. But it goes without saying that it is a complex task. What if the same row is updated in both databases, how to deal with that?

Answer 3

SQLServer does not support a distributed or cluster architecture. The best you can do is a read/write primary database with replicated readers.