I am using merge replication with SQL Server 2017.
I have one publisher, and two subscribers (all in all 3 database servers).
I need to replicate different tables to each subscriber.
Is this possible?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
yes, that is possible - you create 3 different publications - and in each publication you add only the respective tables you want for each server.
The other tables will be in other publications, not this one.
Alternatively, within the merge replication you can set up filters and each subscriber would see only a specific partition as you define it.
This example below comes from this article:
add tables to merge replication with parametrized row filters.
Follow the steps in the referenced article to configure merge publication and adding tables to the publication
At Add filter step add below filter.
SELECT <published_columns> FROM [dbo].[UserDetails] WHERE [UserOrg] = HOST_NAME()
Once the publication setup is completed, generate the snapshot for merge replication. After generating the snapshot navigate to publication, right click and click on Properties -> Data Partitions
Click on Add and enter the partition value i.e. ORG1 and click ok. As of now I am adding only one partition ORG1. We can add more based on subscribers. For example, if I have a subscriber with partition ORG2 then we can add partition for ORG2 here and generate a partitioned snapshot which will be applied on specific subscriber.
Now click on generate the selected snapshot now and click OK.
Right click on publication in merge replication, launch SQL Server replication monitor and make sure the partitioned snapshot is also completed.
Now add subscription. Please follow steps in the following article to add subscriber: Merge Replication – What gets replicated and what doesn’t
While adding the subscriber specify the HOST_NAME() value of the subscriber, so that the rows with UserOrg value ORG1 only will be replicated to the subscriber.