Partager via

SQL Merge Replication

  • Article

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 https://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 https://msdn.microsoft.com/en-us/library/ms152746.aspx
. The scenario for merge replication is present at https://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 https://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 https://msdn.microsoft.com/en-us/library/ms152478.aspx
and https://msdn.microsoft.com/en-us/library/ms152486.aspx

 

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

Comments

  • Anonymous
    December 28, 2010
    Thanks! Looking forward to that follow-up article showing a step-by-step merge replication setup, especially if it shows using SQL 2008 R2.

  • Anonymous
    January 06, 2011
    Added