Labels

How To Setup Merge Replication With Download Only / One Direction in SQL 2014 : Demo





                 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


  1. Logshipping in Standby Mode: But this need killing connection every time t-log will be restored and atleast I have to give 10mins latency.
  2. Mirroring: Except creating snapshot on mirror I can’t do anything and problem is latency of data. i.e. no real time data.
  3. 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.
  4. 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.








In connect to Subscriber option please put sql authentication login at subscriber server. 


 Select Agent schedule as "Run Continuosly"







Select Initialization "immediately" 









Select Subscription type as Client other wise this will start working as publisher and it will no more remain "Download only" for more understanding(Click here)


 Initialization is completed


Now Here you can see our publisher and subscriber is configured





 Now we will Verify if my replication is working fine?

/************************************/
--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
/************************************/






Now you cant test to insert some values to Subscriber and it will fail , Bcoz we st it as Undirectional. 




No comments:
Write comments

Please do not enter spam links

Services

More Services