0

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.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Saurabh
  • 241
  • 2
  • 12
  • Please share the filter definition on the merge article. – Greg Nov 06 '15 at 11:11
  • This is the condition "SETTINGS.ID < 10" – Saurabh Nov 06 '15 at 11:34
  • is an identity column? what value was inserted on the subscriber? if you have identity ranges set, you will need to filter differently. – Greg Nov 06 '15 at 11:42
  • Yes it is a identity column (PK) but its not auto incremented. I insert it manually. So i insert a row at 11th position. So the client has a row at 11th position with a rowguid of its own. But as i have added a filter till only the 10th record this should not come in picture when sync goes on. Correct? – Saurabh Nov 06 '15 at 11:46
  • ok, see my answer below. – Greg Nov 06 '15 at 12:53

1 Answers1

1

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.

Greg
  • 3,861
  • 3
  • 23
  • 58
  • So what do you suggest in my scenario ? Can i achieve this by using dynamic filters? If yes how ? – Saurabh Nov 06 '15 at 13:21
  • Is there a way to prevent sending of certain rows from subscriber to publisher ? What i understand from your reply is, any change that is done at the subscriber will always get propagated to the publisher even if it is outside the filter criteria. – Saurabh Nov 06 '15 at 13:38
  • You can prevent all changes made the subscriber from being replicated back, but not selectively. Sounds like you need a design review, which can't be done over the forums, best to consult an expert locally. – Greg Nov 06 '15 at 13:49
  • Thanks for the help . :-) – Saurabh Nov 09 '15 at 05:07
  • Can you please have a look at the below issue and give your suggestions. http://stackoverflow.com/questions/33605719/sql-merge-replication-how-to-tell-what-has-changed – Saurabh Nov 10 '15 at 11:57
  • Thanks for the help. One more related query. What happens on the usage of 'article.RemoveReplicatedColumns' ? Does the changes made at the subscriber end get propagated to the publisher for a column removed from replication ? Sorry for the constant inflow of queries but the documentation doesn't clearly state the behavior. – Saurabh Nov 16 '15 at 12:29