Unanswered: Merge Replication - Phantom Conflicts
I will try & keep this short as I'm not very hopefull of a quick answer.
Effectively we are getting intermittent/sporadic/batches of update,update replication conflicts, mainly from HeadOffice updates when they are applied to the Central Publisher.
Central Publisher & Distribuor
Not subject to Inserts/Updates/Deletions from any GUI or process except replication.
400 Store Subscribers (Publication_A)
Merge Filtered by StoreNum (20 minute latency)
1 Head Office Subscriber (Publication_B)
Not Filtered (2 minute latency)
Each of the two Publications are identical except Pub_B unfiltered)
Each Publication effectively consisting of three Articles with DRI throught subscribers (Not For Rep configured)
1] Item (PK_ItemReference,..StoreNum..ItemNum..)
2] Item Transaction (PK_ItemReference,PK_TransactionReference,....)
3] Transaction (PK_TransactionReference,......StoreNum..)
Interestingly this exists on one of the articles
ADD CONSTRAINT [FK_TransactionItem_Transaction] FOREIGN KEY([TransactionReference])
REFERENCES [dbo].[Transaction] ([TransactionReference])
ON UPDATE CASCADE
ON DELETE CASCADE
NOT FOR REPLICATION
StoreNums can change when the item is transfered to HO from Store, HO change the StoreNum and it is often that the conflict loser is the storenum change from the HO Subscriber, Conflict winner is the Central Publisher with seemingly old data.
I've been digging into the replication system tables:-
My head hurts & I'm not getting anywhere with the investigation so I am focusing on one of the conflict tables putting a trigger on the MSMerge_Conflict_<Pub_B_Item> table which populates LOG tables (filtering by rowguid from INSERT/UPDATE conflict table), these are mirror images of system tables called
In the hope that I can trap two (almost instantaneously) generated different Gen_Num updates from the HO Subscriber
The Question is:-
Can anyone see why I may be getting these Phantom Conflicts OR Can anyone recommend a different approach to the investigation.
Further Info on request
All help greatly appreciated as I'm pulling my hair out with this
Note: I am investigating on a Live Production System so am subject to formal release management etc when applying schema changes etc.
"Everything should be made as simple as possible, but not simpler." - Albert Einstein
"Everything should be made as complex as possible, so I look Cleverer." - Application Developer