I have two sql servers, in the first I want to keep the data from the last three months and in the second I want to keep all the data with the purpose of getting historical reports. I know how to do a transactional replica but I don't know how to replicate only the inserts and updates, I don't want to replicate the deletions.

Is it possible to do this with replication between servers or do you have to program it yourself through triggers?

  • Maybe a scheduled job fits better – McNets Oct 14 '20 at 17:23
  • I just want to know if the replication between servers can only of updates and inserts, without taking into account the deletions. – felix Oct 14 '20 at 19:38

In the Article Properties, you can set the publication to not replicate deletes.

On the Article Properties page, just scroll down to Delete delivery format and change it to "Do not replicate..."

Note that if you're not replicating deletes, you risk a scenario where a row or update errors and causes issues. This will only happen if a deleted row has a value that is reused on the primary key or other unique constraint. If you're using an IDENTITY column for PK (and no other unique indexes on the replicated data), this would only happen only if the ID is reseeded or someone uses IDENTITY_INSERT. It's a more common problem to encounter when using a natural key. If you anticipate this being a problem, you can put instead of triggers on the replicated copy to convert inserts to upserts

Article Properties

  • Thank you very much I'm not sure if sql server reuses the guid type keys when removing the old ones. – felix Oct 16 '20 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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