Search code examples
sql-serverreplication

Is there a way to force replication that replicate only inserted and updated data?

I want to have a version of my db that contains all of data from first to now. If I use sql server replication, can I force that to ignore delete operation for replication?

Solution

  • I am not aware of such feature.

    And I would be surprised if one exists out of the box. What if instead of UPDATE one does DELETE and INSERT? Your target database will not be able to handle such change because it will not delete a row with some unique identifier (column value) XXX, and then will try to insert a new one with this identifier, resulting in a unique key violation.

    EDIT-1: If what you want is the only-recent database in production and the whole database for audit, then maybe you should try different solution:

    1. Logical Delete and a View: you change your DELETE logic (either by changing the DELETE command, or by using INSERT OF trigger) to only mark rows as deleted. Then you create a view that basically wraps your table but filters only those that are not deleted. Run all your SELECT/INSERT/UPDATE queries against this view instead. At the end also in your current solution you do mark "deleted" records as deleted somehow, right?
    2. Audit tables: keep your history also in your main database, but in AUDIT (shadow) tables. Again you can do this with AFTER triggers almost non-intrusively.