A specific type of replication that is powerful for distributed environments is merge replication. The power of merge replication is that it enables multiple subscribers to update back to the publisher database. This means that you can have multiple replicas of the same database (or parts of it using filters) at different dispersed locations. These databases can serve different sites in your distributed organization with minimum latency, while maintaining consistency and getting latest updates from the other databases.

 Note: There is another option to reach a similar result which is Transactional Replication with Updatable Subscriptions; however, it will be removed from the next feature of SQL Server so it’s better to avoid it. For more information on this replication type, please check http://msdn.microsoft.com/en-us/library/ms151718(v=SQL.105).aspx

 The main scenario for merge replication is a company that has many regional offices. Each of the offices needs to have a subset of the data pertaining to its work; moreover, there are master data coming from the central office that the different offices can read and utilize but not update. The central location will have the full copy of the data. A diagram of this scenario is as follows:

 

For an overview of merge replication, please check http://msdn.microsoft.com/en-us/library/ms152746.aspx . The scenario for merge replication is present at http://msdn.microsoft.com/en-us/library/ms151790(v=SQL.105).aspx

When using this type of replication for a solution, care needs to be taken with some of the options of the replication. Notably, the following:

1.       Merge Replication adds a uniqueidentifier column to each table. This can affect the application if the developer didn’t build it with the additional column initially created; moreover, it increases the size of the table.

2.       Set the direction of the articles properly. If an article is read-only at the subscriber, then set its properties to “Download Only to Subscriber”

3.       Set Identity Ranges for the different tables using the publisher range size, subscriber range size and range threshold. You can read more on identity columns in Merge replication at http://msdn.microsoft.com/en-us/library/ms152543(v=SQL.105).aspx

4.       Create Filters to filter data across the different locations.  This will decrease the size of the database at each replica as well as alleviate any security concerns of storing data of one location in all the other locations. Filter rules are explained at http://msdn.microsoft.com/en-us/library/ms152478.aspx and http://msdn.microsoft.com/en-us/library/ms152486.aspx

 

My next blog entry will include a sample merge replication creation step-by-step