SQL Server 2012: synchronize two servers with writes to both

sql sql-server-2012 replication mirroring
ChristophMa picture ChristophMa · Nov 12, 2013 · Viewed 11.2k times · Source

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

Brandon Williams picture Brandon Williams · Nov 12, 2013

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.

Related questions

Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More
Recover unsaved SQL query scripts

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Read More
SQL Server IF EXISTS THEN 1 ELSE 2

Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code: IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName =…

Read More