Merge Replication Deletes Records

Question

Merge Replication Deletes Records

Monte Pix-294031

Old Hand

Points: 371
More actions
October 9, 2007 at 6:12 am

#180067

I have a publisher, separate distributor, and several anonymous merge subscribers. For some reason, replication will delete some child records from the subscriber during the merge, complaining that there is no parent record (key violation). The parent record will be merged up to the publisher from the subscriber, but the child record ends up in the conflict resolver and remains there indefinitely. I seem to remember this being a problem prior to SP4 on SQL2000 - but "it's baaaack". Also seems to only occur on subscribers connecting by DSL. T1 subscribers do not have the problem. Any ideas?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 1

Hi,

I think this related to the fact that if the child records are replicated before the parent, you will get key violations. Increasing the batch size may help to capture all Parent and child records in one batch. The nickname for the articles will ensure the PK records are applied before the FK records.

Alternatively (and a bit of a cop out), have a working practice where pk records are entered and replicated before fk records.

Regards

Graeme

Monte Pix-294031 Old Hand Points: 371 More actions · Answer 2

You're right about the PK not being replicated before the child. What do you mean by "nickname" and where does one set it?

Also, how do you ensure parent tables get replicated before child tables?

Thanks

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 3

The nickname is a number that is given automatically by merge replication to the articles. The PK/Parent tables have a higher number (I think that's the right way round) than all the FK/Child tables. So when a batch of records are replicated, they are applied in order of nickname so that the PK records always get applied first.

I'm not sure there is any real way of making sure the parent records get applied first other than increasing the size of your batch of records so that all records relating to a particular parent/child set are captured and applied in the correct order.

Another option is working practice. Getting those that enter the data to enter the primary data first...although I think 9 times out of 10 that is not practical 🙁

Regards

Graeme

Monte Pix-294031 Old Hand Points: 371 More actions · Answer 4

Thanks Graeme.

I was afraid of that (nicknames).

I was under the impression that the order of replication was determined by the order in which the objects are added to the publication, but with a very complex database with multiple layers of dependencies, it is hard to ensure that you add them progressively in the right order.

I will try increasing the batch size.

regards

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 5

Hey..

After a chat with my esteemed colleague I was reminded....

If you make all your FKs NOT FOR REPLICATION, then when the replication applies the child record, even if the parent record is not there yet, it will still apply because the FK constraint will ignore it.

Hope this helps

Thanks

Graeme

Animal Magic SSChampion Points: 13964 More actions · Answer 6

Graeme100 (10/17/2007)
Hey..
After a chat with my esteemed colleague I was reminded....
If you make all your FKs NOT FOR REPLICATION, then when the replication applies the child record, even if the parent record is not there yet, it will still apply because the FK constraint will ignore it.
Hope this helps
Thanks
Graeme

I had a post on here a couple of months ago regarding exactly the same problem (deletes occuring when they shouldnt have been) and the above comment was the reason. As soon as that was resolved the conflicts causing the deletes stopped straight away.