SQL Server 2012: synchronize two servers with writes to both

Question

I have a SQL Server 2012 Enterprise setup issue and was so far unable to find a solution of my specific use case:

I have two SQL servers, one in the United States and one in Germany. Both are being used for reading and writing and the task is to make them synchronized. The good news is, that while reading happens a lot, writing only once every minute or so (to various tables however). Basically I am looking for a replication setup in which both servers are masters and can send changes to the other..

Is that possible?

Thanks, Christoph

Answer 1

Either Merge Replication, Bidirectional Transactional Replication, or Peer-to-Peer Replication is the best fit here.

Since writes can occur at both servers, you will need to consider what to do in the event of a conflict. A conflict will occur when the same row/column is changed on 2 different servers between a sync. If possible, it is best to avoid conflicts altogether by partitioning the write operations. One way this can be acheived is by adding a location-specific identifier column to the writable tables and ensure that write operations for a particular row are performed at only one location.

Merge Replication provides bidirectional synchronization and the ability to define static and parameterized row filters to provide a subset of data to be published to subscribers. Merge Replication also provides built-in conflict resolvers along with the ability to implement custom conflict resolvers.

Bidirectional Transactional Replication provides bidirectional synchronization but does not offer any type of conflict detection or resolution.

Peer-to-Peer Replication provides bidirectional synchronization, however, it requires all nodes to be Enterprise Edition and does not support row or column filtering. Peer-to-Peer Replication has built-in conflict detection but does not offer automatic conflict resolution.

I would recommend setting each one up in your test environment to see which replication type best fits your needs.