<

SQL Server Replication - Merge Replication type step by step

Published on
10,941 Points
7,641 Views
3 Endorsements
Last Modified:
In my previous article, I talked about transactional replication and presented an example of how to configure and use it. Today, I'm going to investigate the second type of replication called Merge Replication.
 

Definition and Mechanism

Merge replication allows two or more databases to be kept in sync. Whenever changes happen on one database, they are automatically applied to the other databases. If the changes happen on the Publisher, they are applied to the Subcriber and vice versa.

Merge Replication uses the Merge Agent to control data. It is responsible for synchronizing the changes between the Publisher and its Subcriber. Because data can be changed on both the Publisher and the Subcriber, it will handle conflicts if they happen.

Merge replication allows the Subcriber to disconnect to the Publisher and they will be re-synchronized after re-connecting.

MergeReplication1.gif(Source: http://msdn.microsoft.com/en-us/library/ms152746(v=sql.110).aspx)

Merge Replication uses the Snapshot agent and Merge Agent instead of Log Reader Agent or Snapshot Agent and Distribution Agent, which were used by Transactional Replication.
 

Scenario

My client has a website called Retails Management that serves online transactions for goods sold. They have another system to manage inventory that is called Stock Management. Whenever a customer buys an item, the system needs to update the quantity of the item on both Retail Management and Stock Management. When an item is imported into Stock Management it is also updated on Retail Management to display that an item is available or not. Because the client needs the data on both systems, we chose the MERGE replication type. 

Overview architecture

MergeReplication2.pngFrom the above image, the Merge Agent is responsible for synchronizing changed data between Publisher and Subscriber.
 

Merge Replication Configuration

Setting up database on SQLServer2012 and SQLServer2008R2

CREATE DATABASE Sales_Retail
ON PRIMARY
(
NAME=Sales_Retail,
FILENAME='D:\DataSQL2012\Sales_Retail.mdf',
SIZE=5MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10MB
)
LOG ON
(
NAME = Sales_Retail_log,
FILENAME='D:\DataSQL2012\Sales_Retail_log.mdf',
SIZE=10MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10MB
);
GO
USE Sales_Retail
GO
CREATE TABLE dbo.Product
(
ProductID INT PRIMARY KEY,
ProductNumber nvarchar(50),
ProductNname nvarchar(100),
Quantity INT,
ModifiedDate DATE
)
GO
INSERT INTO dbo.Product
VALUES(1,'AR-5381','Adjustable Race',2,GETDATE()),
(2,'BA-8327','Bearing Ball',3,GETDATE()),
(3,'BE-2349','BB Ball Bearing',4,GETDATE())

Open in new window


Setting up Publication

1- Right-click on Replication folder -> Choose Publisher properties -> Choose Merge type for Sales_Retail
MergeReplication3.png2- Expand Replication folder -> New publication -> Next -> Choose Sales_Retail -> Choose Merge publication
MergeReplication4.PNG3- Choose SQL Server 2008 or later
4- Choose Product table ->Set Properties of Highlighted Table Article. This option allows us to configure properties for our articles 
MergeReplication6.PNGMergeReplication5.PNG5- Snapshot Agent congfiguration 
MergeReplication7.PNG6- Security configuration-> SQL Server Service agent account
MergeReplication8.PNG7- Click Next -> Enter publication name Sales_Retail_Merge
MergeReplication9.PNG8- Finish
MergeReplication10.PNG

Setting up Subscriber

1- Connect to SQLServer2008 -> expand Replicaion folder -> New subscription -> Connect to Publisher on SQLServer2012 -> Choose Sales_Retail_Merge
MergeReplication11.PNG
2- Choose Run all agents at the Distributor
MergeReplication12.PNG
3- Merge Agent Security -> SQL Server service agent account
MergeReplication13.PNG4- Choose Agent Schedule Run continuously and run immediately
MergeReplication14.PNG
5- Subcription Type-> Choose Server type
MergeReplication15.PNG
7- Finish
 

Verify Merge Replication mechanism

I have completed the configuration of Merge Repliction. Now, you can view its synchronization progress via the Replication Monitor. Right click on Sales_Retail_Merge -> Launch Replication Monitor
MergeReplication16.png
We will update data on SQLServer2012 to see the progress of synchronization. 
UPDATE dbo.Product
SET Quantity = 5
WHERE ProductID = 1

Open in new window

MergeReplication17.PNG
The number of inserted records is 4 and updated record is 1 because we updated ProductID = 1.

Continue to update data on SQLServer2008 to observe how Merge Replication works to re-synchronize data from the Subcriber to its Publisher. Update the quantity of ProductID 2 to 6:
UPDATE dbo.Product
SET Quantity = 6
WHERE ProductID = 2

Open in new window

Obivously, an Upload Changes to Pulisher happened after we updated data in Subscriber.
MergeReplication18.pngIf you query data from SQLServer2012, you can see that Quantity of ProductID 2 was updated to six.
 

Conclusion

Merge Replication is one of replication types that allows us sychronize data among two or many database servers. Depending on your requirements, you can apply this type appropriately. One of the most important aspects of Merge Replication is how it deals with conflicts resolving while synchronizing data, I will talk about that in my next article.
3
Comment
Author:Dung Dinh
3 Comments
 
LVL 8

Expert Comment

by:Yashwant Vishwakarma
Hi Dung Dinh :)
You wrote very nice article on Merge replication and transaction replication.
Could you please write articles for snapshot and peer to peer replication.
It would be a great help from you.
Voted Yes for your nice article. :)
0
 
LVL 9

Author Comment

by:Dung Dinh
@Yashwant Vishwakarma:  I have just written a new article for Snapshot. You can read it from https://www.experts-exchange.com/articles/28397/SQL-Server-Replication-Snapshot-step-by-step.html

Thanks for your votes,
0
 

Expert Comment

by:Omran Abdurrahman
very well done . Thank you for sharing !
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month