Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2006-12-12 : 11:59:36
|
| Guys,We have a replication set up with a single publisher and a single subscriber.Recently, replication failed due to the following reason:Data, erroneously, was deleted from subscriber. Then, to make the publisher and subscriber synch, the same data was deleted from publisher.At that point, the publisher, as it should, attempted to delete the data in the subscriber. But since the data is not there, the attempt failed, and replication failed.I tried to restart the agent, but replication kept failing. Since nothing worked, I just deleted the subscription and recreated it. The replication then started, but since it had to go through all the steps again (i.e. Snapshot ...), it took us 2 hours to get the data back in synch.Luckily this happened in the testing environment. If it would be production, we would be in trouble, as availability is our #1 concern.Would anyone know of any other (i.e. more elegant) way I could've solved this problem?The only other idea I had was the following: It is possible that the data which the publisher plans to delete is temporarily stored in some file and if that file is located and deleted, and then the agent is restarted, it could possibly work - but, I could not locate any such files - and this is a completely hypothetical idea.Any suggestions would be appreciated.Thanks |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2006-12-13 : 11:17:06
|
| Guys,To solve the problem stated above, I am currently considering to implement the following replication model:No user, other than the replication user, will be allowed to perform any insert/delete/update to the tables in the subscriber.That way, we can avoid the problem that happened this time, which resulted in a replication failure.In order to implement this, I am assuming that I should create 'replication' user and then give execution priviliges to this user for all the replication stored procedures.The issues I need help with are the following:1. There are too many stored procedures related to replication (and I don't know if I can find all of them)2. I am not sure whether it would be sufficient just to do that (i.e. is there anything more related with replication in addition to the sps, that I should be concerned about?)Can anyone suggest a more elegant solution for implementing this model or any other suggestions?Thank you very much |
 |
|
|
|
|
|
|
|
Is there another way out?