I have a basic invoice -> invoiceitem relationship in my merge replication setup on SQL2K8 R2.
[Invoice] article is replicated with overlapping partition options and filter
WHERE [Invoice].[SubscriptionHostName] = HOST_NAME() AND [Invoice].[Replicate] = 'True'
[InvoiceItem] article is replicated with overlapping partition options and join condition
INNER JOIN [Invoice] ON [InvoiceItem].[InvoiceId] = [Invoice].[InvoiceId] AND [InvoiceItem].[Replicate] = 'True'
The [Replicate] columns are flags that are always true right now, but will be used in the future to reduce the subset of records included in replication. The have a default constraint of 'True'.
Our merge replication system only has new [Invoice] and new [InvoiceItem] records being created at the subscriber under normal conditions, we insert at the publisher extremely rarely although it does happen. There are approximately 2000 new [Invoice] records per day, and about 10,000 joined [InvoiceItem] records per day across 131 subscriptions. I have no triggers in the entire system other than the merge system triggers.
The following scenario happens to about 2 out of the 10,000 [InvoiceItem] records per day. The [InvoiceItem] gets inserted, the insert statement contains the proper [InvoiceId] and defaults [Replicate] to true. This [InvoiceItem] record belongs at the subscriber, yet the next sync causes the record to be deleted. The delete is only temporary, when the subscriber synchronizes next, 10 minutes later, that same record that was just deleted is inserted again because it belongs there! The example that I investigated today, the [InvoiceItem] record was inserted at 11:02AM. at 11:04AM that record was removed from the subscriber. At 11:14AM that record was inserted back into the subscriber. It means that between 11:04AM and 11:14AM that record didn't exist, and it caused an error in the software at 11:05AM that was running on the subscriber, the record was inserted again, and at 11:14AM the subscriber ended up with two [InvoiceItem] records.
The [InvoiceItem] record was not modified in any way at the publisher, it was only inserted at the subscriber.
Can anyone tell me in what scenario that a record would be out of partition one sync, then back in for the next sync even though it wasn't modified?
If it was out of partition for one sync, how could it then be added back in without being updated in any way?
The articles only contain row-level tracking, but is there any way through the merge system tables that I can investigate why this record would have been removed from the subscriber in the first place?