Data Lost-Merge Replication

We are using merge replication over internet to repliacte our data b/w 2 servers located at 2 different branch offices. But the strange thing is we are losing data i.e. data is being automatically deleted during replication.

any idea/help for fixing it would be hihgly appreciated

thank in advanceProbably failed insert at subscriber poss due to Referential Integrity Failure.

Because SQL Must make publisher & Subscriber with same data it will come back & delete publisher row

Keep an eye on the conflicts

data lost

Hi,

I have encountered problem in the merge replication with conflict caused by foreign key constraint. As I did some reseach that it can be avoided by increase the upload and download batch generations.

My question is how can I recover the data which has been lost?

Thanks

You can try the mscontents and the conflict tables to say if you can recover the data there if these table has not been cleaned up yet.

I am more interested in how the data was lost. is there any nonconvergence between publisher and subscriber data ?

are you using Sql2000 or SQL2005 ?

thanks

Yunwen

|||When you get a conflict, the info is stored in conflict tables, which you can see in the Replication Conflict Viewer. Conflicts are kept here until you pick which one wins.|||

Thank you for your response.

The conflict message says:"INSERT statement conflicted with COLUMN FOREIGN KEY constraint" so the record can not be inserted in the child table on the subscriber site.

Then somehow the record is also deleted in the child table on the publisher site.

I may be able to get the rowguid from the msmerge_delete_conflicts table, but no more the detail data like stored in the [conflict_replication name_table name]

It seems the data get lost permanently

|||I am using SQL2000|||

If you are using SQL Server 2000 SP4, then please look at the option of 'compensate_for_errors'. This is a paramtere to sp_addmergearticle/sp_changemergearticle.

More info in this article: http://support.microsoft.com/kb/828637

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.

data loss when reinitializing subscription

we had setup merge replication on 2 db servers. For some reason, the subscription started failing a month back with the error " invalid object sysmergexxxx on the subscriber. I did a reinitialize and now all the changes on the subscriber which werent synced got deleted. I have tried all 3 log recovery tools with no luck. Is there any hope of recovering data. the last backup on the subscriber was a month ago.

I believe when you reinitialized the subscriber, you did not chose to upload the unsent changes from subscriber and hence you lost your subscriber data. Sorry to say, but there is no way to recover this data now and is overwritten by the publisher's data.

You should try to either upload the changes next time this occurs or save a backup copy of the subscriber database, then reinitialize (without upload changes) and then diff between the current subscriber and the backup and redo the DML that are different.

Hope that helps next time.

I am also moving this thread to the replication forums.

Data loss in replication

hi,

Im using merge replication.I have created one publisher with dynamic filtering and i have creted two subscribers from it(pull subscription).It was working fine.but after sysnchronizing 2-3 times data from some tables went missing

can any one help me....

thanks and regards

Dhanya

Can you provide more detail? If you don't mind, can you post your table schema and filter information or your replication setup script and indicate what was missing? Data from the publisher table or from the subscriber table?

Regards,

Gary

|||

It may be because your DML caused data to go out of partition from the subscribers.

Is this SQL 2000? Is the setting compensate_for_errors=true for the article you are experiencing data loss?

And by data loss, do you mean an insert done at publisher after synchronizing, does not exist anymore at the publisher and subscriber?

Data loss in replication

hi,

Im using merge replication.I have created one publisher with dynamic filtering and i have creted two subscribers from it(pull subscription).It was working fine.but after sysnchronizing 2-3 times data from some tables went missing

can any one help me....

thanks and regards

Dhanya

Can you provide more detail? If you don't mind, can you post your table schema and filter information or your replication setup script and indicate what was missing? Data from the publisher table or from the subscriber table?

Regards,

Gary

|||

It may be because your DML caused data to go out of partition from the subscribers.

Is this SQL 2000? Is the setting compensate_for_errors=true for the article you are experiencing data loss?

And by data loss, do you mean an insert done at publisher after synchronizing, does not exist anymore at the publisher and subscriber?