SQL Server Performance Forum – Threads Archive

Is there another way out?

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

---

try with replication conflict solver —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

What is the volume of data in this case? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

By the way, I forgot to mention that the type of replication is transactional (don’t know if it matters). Data volume was 20 records with 10 being deleted (extremely small table). Let me look into replication conflict solver.
Thank you

---

Without snapshot you can handle this situation by add the -SkipErrors swith in your distribution job…and later you run the snapshot if neccessary… Use the "-SkipErrors" parameter in Distribution Agent cautiously http://support.microsoft.com/default.aspx/kb/327817 Mohammed U.

---

There is also a tablediff.exe that MS made available in 2005. It’s used to ‘diff’ replicated databases and will even generate the synch statements for you. I’ve had limited success with it, in regards to this type of data-difference problem. Look up tablediff in BOL.
Edit: It’s only available in 2005, but works on any 2000 or greater instance…

---

I don’t think tablediff works with sql 2000…Does it? Mohammed U.

---

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

---

Yes, it works with 2000. I’ve used it several times on a 2000 instance to resynch.

---

Hi. I’m not sure if this makes sense or if it helps any, but I’ll toss my two cents in anyway, in the hopes that it will help. I have a small site that uses transactional replication, and have had the problems with records going missing at the subscriber. What I did to recover from that, especially because the tables werern’t that big, was I exported the table in question to a new db. Backed up the new db and restored it on the subscriber. Then I just coppied the missing records from the new db to the subscriber db. After that, the replication kept on going. I tried using the skip errors clause, and found that it ended up skipping more than I expected, so I generally try to avoid using that clause. HTH
K.

---

"Data, erroneously, was deleted from subscriber. Then, to make the publisher and subscriber synch, the same data was deleted from publisher." I think the above caused some complications to the problem. You see, when you tried deleting the same data from Publisher it will try to replicate the same to your Subscriber. I’ve experienced this problem before and managed to resolve by doing the following steps: 1. Go to the subscriber, make sure the subscription in question is not synchronizing at the moment.
2. Right-click the subscription and choose "Reinitialize" from the popup window, the subscription will now be marked for reinitialization.
3. Run or execute the snapshot for the subscription in question.
4. The data are applied at the subscriber after the next time the distribution agent runs. Jon M

---

]]>