Post reply

Merge replication - how to debug

  • September 13, 2004 at 11:02 am

    #86444

    Hi.

    I have to debug a system and although I'm fairly 'ok' with SQL server generally, I am not sure where to begin with this one.  If anyone could tell me how I could go about diagnosing the problem I would be very grateful.

    I have Merge replication set-up on two machines. Both are SQL Server 2000 Enterprise.   Both are accessed by third party software packages to record realtime information. The systems are sometimes hit pretty hard, but never simultaniously. 

    now - it appears that every month, although several million rows of data are effectively replicated (from either direction) approximately 20-30 rows appear on one box, but not the other!  That's not a bad ratio of success, but obviously if it isn't 100% replication then it isn't really replication

    The tables on both systems use triggers to update an audit table.  The rows are missing on the audit table!  So I suspect something is amis with the triggers.

    So, my question is - is there something genreally iffy about merge replication I can check for, and considering the rarity of the error, would there be a sensible way to diagnose the problem?

    Much oblidged to anyone who reads/answers.

    Sy

  • September 14, 2004 at 5:41 am

    #522920

    I would advise you to run a validation to the scope of your database consistency problem.

    The I would also look at the conflict table(s) using the conflict viewer to see if this gives you any clues.

    In my experieince this missing data is normally caused by a non-logged operation (BCP or DTS) which will cause the merge replication triggers not to fire.

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

     

  • September 14, 2004 at 10:18 am

    #523003

    Yes, my first guess would be some BCP or DTS import that is non-logged, but my other guess is that maybe there are some replication conflicts going on that he doesn't know about. 

    Check this out, go into the database in question in enterprise manager, click on the publication subfolder, right click on your publication, then click "view conflicts".  You might have some in here.

    If you're using identity fields and you do have some conflicts with numbers from each server overlapping each other, try using identity ranges.

  • September 16, 2004 at 4:21 am

    #523285

    Thank you for the pointers.  As far as I am aware there aren't any non logged operations - however, you have reminded me that the original third party application sometimes used field names such as 'reason' which clashed with the resolver.  I had to recode their application to accomodate for it.  I wonder if perhaps something similar is happening now.

     

    I'll have a look from here on in. Thank you again.

  • September 23, 2004 at 3:06 pm

    #524262

    is there a pattern or some factor in common among the non-replicated rows?

  • September 24, 2004 at 3:49 am

    #524297

    No.  But - the rows are populated by a stored procedure. The stored procedure has been named sp_reconcile_job - and I 'think' it's the actual naming convention which could be causing the occasional problem.

     

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

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