CODEWITHSUNDEEP
c#sql-serversql-server-2008replicationmerge-replication
Saurabh
Saurabh

Reputation: 261

SQL Merge Replication Issue

I have a issue regarding Merge Replication. I have a table SETTINGS where in i store the settings of my software. The schema of the table is ID ( PK) , Description , Value. Suppose i have 15 rows in this table on my server. Now i have applied filter on this table saying only the first 10 rows would replicate.

Now with this settings when i sync for the first time, i receive the 10 rows on my client (having subscription). Then i add the remaining 5 on my client. Now when i sync again it gives me a conflict saying that

A row insert at 'ClientServer.ClientDatabaseName' could not be propagated to 'MyServer.ServerDatabaseName'. This failure can be caused by a constraint violation. Violation of PRIMARY KEY constraint 'PK_SETTINGS'. Cannot insert duplicate key in object 'dbo.SETTINGS'. The duplicate key value is (11).

What i don't understand is why is it trying to replicate something (row) which is outside the subset filter applied on that table ?? Please help guys.

Is this scenario not possible with Merge replication ?

https://msdn.microsoft.com/en-us/library/ms151775.aspx the link suggests that this is possible. But confused.

Upvotes: 0

Views: 431

Answers (1)

Greg
Greg

Reputation: 4045

Filters created on for a merge article are evaluated only at the publisher. Changes made at the subscriber will always be propagated back to the subscriber, even if they are outside the filter criteria. However if the changes from the one subscriber do not meet the filtering criteria, then they will sit on the publisher, but not be replicated to all the other subscribers.

Is this a production scenario, or are you playing around with replication? If you do static filtering, which is what you have above, it is typically done on read-only type of tables. For example, a salesperson in the field may only need prices for products in their region. They are not expected to update this table. If you do dynamic filtering, for example, filtering based on HOSTNAME(), then you would only get data specific for that user. For example, a salesperson in the field would receive only their customer information. Thus, any updates to that information, unless it's shared across multiple salespersons, would propagate back up, and not flow to anyone else.

In your case, i would not recommend updating tables on the subscriber that have static filters, thus i suggest re-evaluating your filtering design to ensure you have the right filtering model for your scenario.

Upvotes: 1

Related Questions