Unanswered: Unexpected Merge Replication with filters Behaviour
I have a publication that uses a dynamic filter to allow different subscribers access different sets of customer records. The idea is to use one table to filter records in another.....
Customer_Subscriber_Mapping table contains a Subscriber field and a CustomerID field. Adding and deleting records from this table determine what customer records are accessed by a particular subscriber. For example.
* note that more than one subscriber can access the same customer records!
The Customer_Subscriber_Mapping is filtered by comparing the Subscriber field to HOST_NAME().
Then a join is made from the Customer_Subscriber_Mapping table to the Customers table on the customerID field.
This works fine for the very first set of records but once new records are added to the Customer_Subscriber_Mapping table something unexpected happens.......
The replication works fine but each time a subscriber merges it get the ALL new customer records downloaded no matter if they have changed or not.
To illustrate, when SUB1 does an initial merge it get 2 local changes. Each time after that if there are no changes to those customer records 10 and 13 at the publisher a merges cause 0 local changes. This is expected behaviour.
However, once a new record is added to the Customer_Subscriber_Mapping table ALL subsequent merges cause the new customer record to be downloaded - 1 local change.
So even when no changes occur in any of the data forever the merge process download a bunch of records that are already at the subscriber and have remained unchanged!
Can anybody please shed some light on this for me.
By the way, both the Customer_Subscriber_Mapping and the Customer tables are included as articles in the Publication. I'm using SQLServer2000.