SQL Replication - restricting deletes
-
I have been asked to set up replication (either merge or transactional) between two SQL 2000 boxes. The issue is that they want to restrict delete operations so that if there are deletions on the publisher that the subscriber does not get the deletes replicated. I know in SQL 2005 there is a method to do that but is there anything similar in 2000?
Thanks
-
If you navigate to Publication Properties>Article defaults. Choose tabel articles and select the 'commands' tab you will see that there are options as to what happens when an INSERT, UPDATE, or DELETE occurs on the publisher. In your case I believe that you can alter the behavior for a DELETE such that 'nothing' happens on the subscriber.
For more information on this subject I highly recommend Hillary Cotter's book on SQL Server 2000 Replication.
-
Hi, we have a similar setup here. I scripted our replication setup and set @del_cmd = N'NONE'. Similar if you enter NONE in the commands Tab for deletes it should work. I have the Hillary's book as recommended by Stephen and it is excellent. In the past we generated a snapshot which created the schema etc and we forgot to omit deletes. So before the users got on the system I went to the subscriber and opened the sp_MS_Del_tablename proc and commented out the delete logic. Not neat but it worked for us that time. Hope this helps, Derek
-
Thanks guys! That really helps a lot. I appreciate BOTH your responses. I will definitely get that book.
Greg
-
These are my thoughts on this ....... Let me know if I'm wrong:
Disabling DELETEs may give you unexpected results. For instance if the primary key is UPDATED at the publisher, SQL really does a DELETE and INSERT to accomplish that, so at the subscriber, you'll have the old record and the new record. Also, if you delete a record at the publisher, but not at the subscriber, then at a later date it gets re-inserted at the publisher, you may encounter a situation where you're trying to insert a duplicate key at the subscriber.
Another "gotcha" could be IF you reinitialize the subscription for some reason. Then when the data from the publisher is re-snapshotted and sent to the subscriber, it will wipe out the current subscriber data, and then replace it with the data WITHOUT the deleted records. Depending on their importance, it might be a good idea to archive the deleted records somewhere just in case.
-
Hi Addict, indeed everything you say is correct. However in our case we run a service ( not SQL Server replication ) which selects from a production database and inserts into an archive database and then deletes from the production database. Both of these databases reside on the same server. Then - using transactional replication we replicate inserts and updates from the archive databases to an archive database on a different server. Now - this is where the deletes come in. We run a SQL Server Agent job in the backgorund back that deletes records from the archive database on the production box - leaving just 30 days of data on this database. We don't replicate the deletes as we have to keep all the archive records on the the remote box indefinitely. Now we have had issues in the past where we had to reinitialize a subscriber and forced to generate a snapshot. In this situation we renamed the archive database on the remote server and created a new archive database. We have views which incorporate which databases to look at when users query the archive data on the report server. I hope I haven't confused ye totally - Rgds Derek.
-
Couple of thoughts...
1. Homebrew01's name is not addict
Merely that he/she has posted 436 times giving the "rank" of addict.2. Real thoughts... It might well be easier to roll your own transfer of records rather than rely on SQL replication - whilst SQL replication will get the data out there for you, it has its drawbacks such as replicating deletes, etc. How much data are you storing - sounds like a lot?
-
Hi Ian, First of sincere all apologies in relation to Homebrew01. When I am busy I reply to this newsgroup sometimes in a hurry !! Apologies once again.
Our current archive database is 35gb. We have just implemented a process to archive the archive. If you follow me. The reason we are using Replication as opposed to a homegrown service of some sort is we are really directed by our Corporate IT heads who are the actual developers and all code must be developed by them. I do take your point and Replication is simply the copying of data from A to B. ( Prior to a recent server migration our archive database was over 120gb ). Personally I like to keep my hands on replication so as to keep my technical skills up etc. Rgds Derej
-
Hey no apologies needed - common mistake that everyone makes. Fortunately no one has called me Mr or Mrs 500 yet
That's a big archive - I can see why you'd want to archive the archive. Hmm - well then in that case, given corporate politics, etc, you are probably on a reasonable track. I wish you luck. If you do come across any better solutions or have some tricky details that you discover, please post them so we can all benefit.
Cheers
-
Hello again
, as posted in the past I bought SQL Server 2000 Transactional and Snapshot Replication by Hilary Cotter ISBN 0-9749736-0-2. I found it a brilliant book. Lifesaver. Every DBA invloved in replication should have it. A few months ago I changed our replication architecture to use a central distributor and Hilary's book got me out of big trouble at 3:30 am. There I was all alone in the building, clock ticking, production starting at 7:30 am and lo and behold that book gave me the answer to a problem I was completely stumped with. Not labouring the point but I'd advise anyone that doesn't have it - get a copy !! Rgds Derek
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply