0

We have a custom application that uses a database trigger that updates a separate "Audit" database to track any changes in data. I'd like to come up with a way to keep another database on separate server (Archive server) that would keep older archive data. Current thought is Prod Server keeps an archive database with changes made that day/week. Archive database pushes any changes older than a day/week to the historical archive server, then truncates all the data in the archive database (on production server) to keep inserts fast and limits the amount of data. I'd keep the data on the archive server for a set period. Is it possible to setup replication to keep inserting on the archive server, but not delete the data when I truncate the tables on the source database?

1 Answer 1

1

If you're planning on using Transactional or Merge Replication, then you won't be allowed to run a TRUNCATE command on any of your published tables in the Publisher Database. Please see the Microsoft BOL Frequently Asked Questions for Replication Administrators - Database Maintenance, specifically the first section called Why can't I run TRUNCATE TABLE on a published table?:

TRUNCATE TABLE is a DDL statement that does not log individual row deletions and does not fire DML triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through DML triggers on published tables.

That being said, you can modify the properties of your published articles such that DELETE statements don't get replicated to the Subscriber Database, as detailed in this article Options to not replicate SQL Server DELETE commands. Or you can follow the second option of that article to modify the underlying stored procedure that would execute the DELETE statement, though I find the first solution cleaner.

Either of these options should help you achieve your goal.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.