Configure One Way Merge Replication for SQL Server


By:   |   Updated: 2016-12-01   |   Comments (2)   |   Related: More > Replication

Problem

We are using SQL Server Merge replication and we have a need to setup a one way publication. We needed Data Manipulation Language (DML) changes on the publisher to get replicated to one of the subscribers, but not from that subscriber to the publisher.  In this tip we look at how this can be done.

Solution

Here is what the environment looks like.  DB1 is the publisher database and DB2 and DB3 are subscriber databases. We have Merge replication already configured on DB1 and DB2 and the data needs to go both ways.  Now we need replicate data from DB1 to DB3 and not from DB3 to DB1.

Scenario

Steps to Configure one way Merge SQL Server Replication

Let's create a new SQL Server Merge publication called OneWay. Right click on Local Publications and select New Publication.

create a new SQL Server Merge publication called OneWay

Select the DB1 database.

Select the SQL Server Publisher Database

Add table and finish the wizard.

Add the tables to the Article in SQL Server Merge Replication

We need to add the subscriber to this publication, so right click on the publication and select New Subscription.

New SQL Server Subscription

Select the publication.

Select the Publication as Subscriber1

Specify where the Merge Agent will run.

Specifiy where the Merge Agent will run

Select the subscriber and subscription database and finish the wizard.

Select the subscriber and subscription database and finish the wizard

Now Launch Replication Monitor, by right clicking on Local Publications and selecting Launch Replication Monitor.

Launch Replication Monitor

Go to the All Subscriptions tab and select View Details.

All Subscriptions tab and select View Details

Go to the Merge Agent Job Properties.

Merge Agent Job Properties

In the job you need to add -ExchangeType 2 to the command as shown below.

add -Exchange Type 2 to the command

Restart the Merge SQL Server Agent Job and one way Merge replication should now be configured.

Next Steps



Last Updated: 2016-12-01


get scripts

next tip button



About the author
MSSQLTips author Ranga Babu Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

View all my tips
Related Resources





Comments For This Article




Friday, September 27, 2019 - 7:07:29 AM - Ranga Back To Top

Hi Bob,

you can replicate across instances.

if you need one way replication go for transactional.


Thursday, September 26, 2019 - 2:05:54 PM - bob Back To Top

Hi - does this one-way DB table replication rely on the  2 DBs being located in the same instance or can i replicate across instances

Thanks in advance

Bob



download


Recommended Reading

Troubleshooting transactional replication latency issues in SQL Server

Add new article to existing publication for SQL Server Transactional Replication

Change Not For Replication Value for SQL Server Identity Columns

Steps to clean up orphaned replication settings in SQL Server

SQL Server Replication Scripts to get Replication Configuration Information





get free sql tips
agree to terms


Learn more about SQL Server tools