1

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?

2

yes, that is possible - you create 3 different publications - and in each publication you add only the respective tables you want for each server.

for example - only 2 tables for server1 enter image description here

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:

Merge SQL Server replication parameterized row filter issues

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()

enter image description here

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

enter image description here

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.

enter image description here

Now click on generate the selected snapshot now and click OK.

enter image description here

Right click on publication in merge replication, launch SQL Server replication monitor and make sure the partitioned snapshot is also completed.

enter image description here

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.

enter image description here

  • Thanks for the answer. I think I did not explained my self enough. The three servers have a common set of tables that they need to replicate between all of servers. Beside these tables, each of the two subscribers need to synchronize a unique set of tables with the publisher. Do I have to create 2 different publications (which means to public the common table set twice), or is there a way to specify the tables set for each subscribers? – Eyal Hasson Sep 3 at 6:12
  • I have updated the answer to reflect this. you might be able to do it with one publication - check if you can figure out how to manage the filters. I used to work for a political party in the UK - we had merge replication with over 250 subscribers - each will see only their share in the main database. – Marcello Miorelli Sep 5 at 10:47
  • 1
    At first look, I thought that filtering will not do the job, since I need to filter which tables gets synchonized, not rows, But I think this can be used also in my case, if I make the row filterring return all rows for one subscriber, and no rows for the other one. Thanks! – Eyal Hasson Sep 5 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.