SQL Server – How Merge Replication Works

merge-replicationreplicationsql serversql-server-2008-r2

I'm not DBA at all but I need to do something to synchronize four SQL server databases. I just discover there is a Merge Replication system in SQL Server 2008. At this moment I'm reading the specification and the documentation but it's not clear if this tool is able to do real synchronization or is just merging data from my master database to my slave database. By real synchronization I mean the data is synchronized from my master to my slaves but also from my slaves to my master.

https://technet.microsoft.com/en-us/library/ms151329(v=sql.105).aspx

Best Answer

Merge replication supports bidirectional subscriptions, with changes propagated from the subscribers to the publisher.

Other types of replication support this scenario: Transational Replication with updateable subscriptions (deprecated) and Peer-To-Peer Transactional Replication (more complicated to maintain, requires Enterprise Edition).

Keep in mind that Merge Replication keeps track of changes using triggers on each published table (article), which can determine substantial overhead to all write operations against those tables. Merge Replication also requires a unique uniqueidentifier column on each article: if you don't have one, it wil add it for you (more space needes, heavy fragmentation after adding the column).

Make sure you test performance before going down this road.