Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007

    Unanswered: Replication without deletion

    Hello there,

    We are currently setting up out production server to the following requirements:

    1. Every month, delete records that haven't been changed in the last 90 days.
    2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse.

    The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too.

    Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem.

    Thanks for your time,

    Ulrik Rasmussen

  2. #2
    Join Date
    Jan 2004
    In a large office with bad lighting
    When you look under the covers, you will find that microsoft creates stored procedures in the publication (and secondary if merge) database to handle insert, update, and delete operations on the secondary database.

    You say that you set up replication between two databases, but you do not state whether it was snapshot, transactional, or merge. You cannot modify a snapshot replication model, but you can for transaction and merge.

    if you look in the publication database after you have created it, you will find stored procs whose names begin sp_MSdel (delete), sp_MSins (insert), and sp_MSupd (update). There will be one for each article in your publication. Modify each and every sp_MSdel to RETURN after the "as" phrase, and comment out the remainder of the proc so that you can easily re-activate the code if the need arises. If you have merge replication running, you will have to do this in both databases.

    Also, be aware that after each and every patch, you have to check all these procs again in case they have been changed as part of the upgrade.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Mar 2007
    I have tried all three replication modes, but forgot to mention that. Sorry .

    Editing the actual replication scripts sounds like a good option, as long as we document it properly . I will look into that, thanks for the quick answer!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts