Replication of only updates and inserts

Question

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?

asked 2020-10-14 by felix

Answer

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