Possible data loss?

• CeliaL

  Newbie

  Points: 9

  More actions

  June 27, 2006 at 4:10 am

  #173287

  We had merge replication setup between 2 tables, Table A and Table B using SQL 2000. This was working 100%.  The users asked to disable updated/deletes to both these tables if data existed on 2 other tables. Table AA and Table BB. We implemented it as follows:

   

  1) Created Insert/Update/Delete triggers for Table A & B. It basically check for Table A is there a record in Table AA, if it exists, raise an error and don’t commit.

   

  2) Removed all foreign constraints from Table AA and BB

   

  3) Added Table AA and BB to the current replication.

   

  Then all hell broke loose, we got conflicts all other the place saying that Table AA cannot be updated because records does not exist in Table X. To our surprise we found triggers generated by Erwin in 1998 – that check for “foreign contsraints” and removed them immediately.

   

  We continued to get conflicts but could see from the error messages it was generated by the triggers in point 1. We added the NOT FOR REPLICATION clause and everything has been running smoothly or so we thought…..

   

  After 2 months we got a call that data is missing from Tables AA and BB. It’s random data and the only explanation I have is that replication caused that. My biggest reason for saying this is tracking the application audit trail I’ve found that all the data missing was added during the period we had all the conflicts.

   

  I need a solid explanation for this and can anyone confirm that this is possible?

   

• Mark Lyell

  Valued Member

  Points: 52

  More actions

  June 28, 2006 at 5:52 pm

  #646518

  Check MS Article ID 828637

  "SYMPTOMS

  <script type=text/javascript>loadTOCNode(1, 'symptoms');</script>

  When you use merge replication, the Merge Agent may experience synchronization errors if an insert change, an update change, or a delete change cannot be applied at a destination replica for various reasons, including a primary key violation at the publisher or at the subscriber. The Merge Agent always sends a compensating change to the source replica to undo the failed change. The Merge Agent executes the compensating change process to make sure that data is maintained in a consistent and convergent state at all replicas. Without the compensating changes, data may vary between replicas if an error such as a primary key violation occurs. If the data varies across replicas, non-convergence of data occurs.

  Before this hotfix, there was no way to permit users to control the compensating change process. The compensating change process was handled internally by the Merge Agent. Because this process was handled internally by the Merge Agent, it was difficult to troubleshoot why certain conflicts occurred. Also, because the conflicting row is deleted from or undone in all the replicas, the row data is removed from the whole topology. To help troubleshoot these issues, Microsoft has added a new article-level property to merge replication that permits users to control the compensating change process."