SQL Server Performance Forum – Threads Archive

Replication corrupts the database beyond return?

Hi We have an app with some 100’s tables. Merge replication, anonymus. 2 publication, one for data and one for documents. Large Sales Force Automation program. We have gone throug the process of trimming the database structure etc and it really has improved the performance. To be able to have advanced filtering we use sync tables containing the ID for every customer that every salesrep shoud have in their laptops. We fill the sync tables by an SP and a lot of rules. ItÂ´s a lot of customers, contacts, finance data, discount rows so the contents and tombestone are filled quick. Retention period is 30-60 days depending on the customers need. Clients vary from 5-250. After a while, the replication stops with errors, sometime, by shortening the retention period it will jumpstart back again, until now. We scripted and dropped replication. Disable replication, enable replication, creating snapshots etc. Now, when we started again, it looks ok, but the following happened. 1. duplicate keys, several reps has the same indentity range
2. When we create the initial snapshot, the .trg files was create but EMPTY (we did not know this at this thime) Whatever we do, we cant get the replication to works ok, later on, we scripted the database structure and data into a new database and are just trying to see if this helps, at least the .trg files is created as it should. Sometimes it feels that my old MS Access homemade replication was more stable… Questions
————- So, what is your experience that the replication sometimes will completely ruin the database and what are your solution to deal with this. How to go back to a clean database? Are others using sync tables to be able to filter the right data to every user, + and minus about that (except the extra overhead in terms of tables and volumes) Anybody else "feeling" that the more time you removes and add the replication on a database, the more rubbish is filled up in your database and finally it’s dead. The longest time we had replication up an running without manual add ons is six month for one company, with 35 merge clients. Whats your experience? Is replication suitable for databases from 50mb to 1,5 gb, 250 users and every user having their own needs of what data shoud be on the laptop? Thanks a lot in advance!
Peter

We have had couple of issues on the replication but with close monitoring the activity resolved the issues and I hope your SQL installations are on latest service packs just in case to fix few bugs with latest service fixes. May be PROFILER will help to monitor the issues when this issue arises again. hTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Gosh, written the answer, forget to log in, all gone… Will enter it again. Tanks for the reply. Yes profiles solved some of the problems, speeded it up and so on, but now, efter a lot of disabling and enabling of the replication it now has ended up into a disaster, giving the same range to different users, creating trigger files that are empty and so on. Is there any method/tool available that cleans everything left behind the replication is dropped? We are quite sure it is garbage in some system tables, because the databas works ok for non replication use. Are there others having the same experience ? Thanks a lot in advance!
Peter

Confirm the setting for the following items:
– Identity ranges.
– Primary keys.
– Synchronizing with alternate synchronization partners.
– Conflict detection/resolution on Row-level tracking and column-level tracking.
– Triggers and business rules.
– text and image data types in merge replication. What is the level of service pack on SQL SErver & Windows? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

SQL 2000 + Service pack 3a + the latest security update, that also includes some stuff to apply when you got a – (dash) in sp names. We have checked all above, today, efter moving all data into a new DB, it works again. Whats scares me if this happens again….
Thanks a lot in advance!
Peter

]]>