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.
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.
Select the DB1 database.
Add table and finish the wizard.
We need to add the subscriber to this publication, so right click on the publication and select New Subscription.
Select the publication.
Specify where the Merge Agent will run.
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.
Go to the All Subscriptions tab and select View Details.
Go to the Merge Agent Job Properties.
In the job you need to add -ExchangeType 2 to the command as shown below.
Restart the Merge SQL Server Agent Job and one way Merge replication should now be configured.
Next Steps
- Please test in a QA environment first.
- Check out these other SQL Server Replication Tips.
Ranga Babu is a SQL Server DBA with experience in handling large Development, QA and Production database environments. I have worked on SQL Server high availability solutions like log shipping, replication, mirroring and clustering. I always like to work on SQL Server performance tuning and automation projects.
First of all, thank you very much for preparing such a beautiful portal and for your labour.
-DB1 is Publication, DB3 is Sunscribers. I want to use Data Diode and use unidirectional structure. Will UDP support when I want to use physical isolation of data and UDP in unidirectional transmission?
-How should I create a static route to show SQL DB3 in different vLAN and network?
Or do you have a suggestion?
hello, how can we configure the merge replication to listener in “always on”
Hi is it possible with this configuration to make changes at the subscriber database doing updates and on next replication job execution just to add the new records from publication to subscriber need to find replication solution that allows the subscriber to do its own data changes without going back to the publisher.