DBA

How to Protect Transactional Replica Against Breaking Data Modifications?

In our scenario we have created transactional replica of our the production database to the staging machine where developers can prepare queries to be later executed in production. But in few days one of developers executed prepared DELETE statement directly against replica and the replication stopped to work with the error: The row was not found at the Subscriber when applying the replicated command. To prevent this situation we have decided to disable all DML operations against this replica for all accounts except of the system one used by replication agent. After searching for various solutions the easiest way (not saying that the best one) for us was to generate triggers where all transactions from other then replication accounts will be rolled back.

Below is simple script to generate required triggers for every table in database. You only need to set @ReplAccount variable to your replication system account. If you will later remove all triggers you can switch @DeleteOnly variable from 0 to 1.

If you will test this solution the try to update the data in any table and the following exception will be generated:

trigger_to_prevent_replica_to_be_updated_exception

Leave a Reply

Your email address will not be published. Required fields are marked *