Merge replication is most complicated among all replication , I have heard this since I am trying to use replication and this time I got a requirement where I have to use this without any questions.
Scenario is Client wants real time data on Secondary server for reporting purpose with min latency and maximum availability of database. We know we have few high availability option
- Logshipping in Standby Mode: But this need killing connection every time t-log will be restored and atleast I have to give 10mins latency.
- Mirroring: Except creating snapshot on mirror I can’t do anything and problem is latency of data. i.e. no real time data.
- Replication: This should be 1st option to use transaction replication but my all tables doesn’t have primary key and it’s not feasible to add PK in all tables of very old application.
- Merge Replication: So now you will question its Bidirectional. No , not always. We can also set one sided merge replication i.e. from subscriber no one can update data. Also called as Merge replication with download only.
Merger Replication with Download only: This feature was introduced in SQL 2005. The idea behind this feature is to make sure static tables can only be modified at publisher and for all subscribers it should be Read-only. You can set an article , group of articles or all articles property “synchronization direction” to “download only to subscriber, prohibits subscriber changes”.
If you specify that an article is download-only after subscriptions have been initialized, all client subscriptions that received the article must be reinitialized. Server subscriptions do not have to be reinitialized.
Below Here is Demo for Setting Merge Replication with Download only, I tried on My Local Machine.
Here I have 2 SQL 2014 servers
Server1 instance for publisher: SQL2014
Server2 instance for subscriber: ADIL2014
On Publisher instance SQL2014 I have run below query to create environment for replication
USE [master]
GO
/****************Creating Database*********************/
Create database Replication_Test
GO
ALTER DATABASE [Replication_Test] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [Replication_Test]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO
/****************Creating 2 Tables*********************/
CREATE TABLE [dbo].[EMP]( [id] [int] NOT NULL, [name] [nvarchar](50) NULL, [CITY] [nvarchar](50) NULL)
GO
CREATE TABLE [dbo].[EMP1]( [id] [int] NOT NULL, [name] [nvarchar](50) NULL, [CITY] [nvarchar](50) NULL)
GO
/****************Insert 4 rows in both table*********************/
INSERT INTO EMP (ID,Name,CITY) values (1, 'Saurabh', 'Bangalore')
INSERT INTO EMP (ID,Name,CITY) values (2, 'Sumit', 'Delhi')
INSERT INTO EMP (ID,Name,CITY) values (3, 'Amit', 'Chennai')
INSERT INTO EMP (ID,Name,CITY) values (4, 'Adil', 'Hyderabad')
GO
INSERT INTO EMP1 (ID,Name,CITY) values (1, 'Saurabh', 'Bangalore')
INSERT INTO EMP1 (ID,Name,CITY) values (2, 'Sumit', 'Delhi')
INSERT INTO EMP1 (ID,Name,CITY) values (3, 'Amit', 'Chennai')
INSERT INTO EMP1 (ID,Name,CITY) values (4, 'Adil', 'Hyderabad')
/**************************************************/
Now we will create publisher, In my environment publisher is already selected for distributor as well, If you want separate server for distributor you can use.
Click on New Publication
 |
| My local server |
Select Database “Replication_Test”
Select Merge Publication
Click next
Select object and their columns which you want to replicate. I have selected EMP and Emp2.
Click on Article properties at right hand side and select “Set Properties of all Table Articles”
As we are going with no update from subscriber (Uni directional) , In properties in synchronization direction select “Download only to subscriber, prohibits subscriber changes”
Now By clicking next it will automatically add unique identifier to all tables to use concept of primary key.
Click next, and let snapshot generate immediately.
Go To Security Settings
In Process account I am using my account having sysadmin and for Connect to publisher I need SQL Server Authentication login so I am using sa
 |
| My Local Machine |
Give unique name for publisher, I am using “Replication_Testing_PUB”
Now My publisher is created and in mins my snapshot will be generated. If your db size is huge it might take some time.
So now connect to sql server (refresh and you can see publisher created)
Now we will create Subscriber, so right click on publisher and select new subscription.
A wizard will be open and we have to re-select\ confirm our publisher
Next , I am using Push subscription here
Next , I have to select subscriber server , connect , and select database for subscription. If you want you can create new database as well. For me database is “Replication_Test_Downloadonly”
Next , I will get option to give security for publisher and subscriber so be careful while picking login for authentication.
Now Here you can see our publisher and subscriber is configured
--At Publisher Server\ SQL2014
Use [Replication_Test]
GO
Select count(*) from [Replication_Test].dbo.emp
-- 4 Rows
/************************************/
-- At Subscriber Subscriber\ADIL2014
Use [Replication_Test_Downloadonly]
GO
Select count(*) from [Replication_Test_Downloadonly].dbo.emp
GO
-- 4 Rows
/************************************/
--At Publisher Server\ SQL2014
Use [Replication_Test]
GO
INSERT INTO EMP (ID,Name,CITY) values (1, 'Saurabh', 'Bangalore')
GO
/************************************/
Select count(*) from [Replication_Test].dbo.emp
-- 5 Rows
-- At Subscriber Subscriber\ADIL2014 after few Seconds
/************************************/
Use [Replication_Test_Downloadonly]
GO
Select count(*) from [Replication_Test_Downloadonly].dbo.emp
GO
-- 5 Rows
/************************************/
No comments:
Post a Comment