Data Loss with Merge Replication?

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. 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?

Yes it's possible for merge replication to lose changes, but these types of scenarios are rare, and even if this was the case it most likely cannot be investigated two months after the fact, especially if metadata has been cleaned up. However simply blaming replication is not fair, you really need to investigate what changes were made, what conflicts were occuring, what conflicts were and weren't resolved.

You should also make it a point to run validation every so often to catch nonconvergence issues earlier, this way when you find you're missing data, it can be investigated with all the necessary information available.

|||Thanks for your quick response Greg. I was unfortunatly not involved when the conflicts were resolved and have no idea how it was done. My knowledge of replication is also verly limited. Does SQL keep a history of replication and can you give me some guidelines to resolve this issue. The client is very unhappy as you can imagine and I need to give an explanation. We have already put measures in place to do validation but it's a case of a little to late.......|||In these kinds of scenarios where you experience data loss and believe it's replication, you need to call CSS (Microsoft customer support) and open a case with them, they have engineers who are experts in investigating problems like this.