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.

DECLARE @Stmt NVARCHAR(MAX)
DECLARE @DeleteOnly BIT
DECLARE @ReplAccount NVARCHAR(128)

SET @ReplAccount = 'domain\account'
SET @DeleteOnly = 0

SET @Stmt = ''

SELECT 
	@Stmt = @Stmt + 
	CASE @DeleteOnly 
		WHEN 0 THEN 
			N'EXECUTE(''CREATE TRIGGER ['+ [sch].[name] + '].[trg_prevent_modify_' + [tab].[name] + '] 
							ON [' + [sch].[name] + '].[' + [tab].[name] + ']
			FOR INSERT, UPDATE, DELETE
			AS
			BEGIN
				IF SUSER_SNAME() <> ''''' + @ReplAccount + '''''
				BEGIN
					RAISERROR(''''Database is replicated and data cannot be modified!'''',16,1);
					ROLLBACK;
				END
			END'');

			'
		ELSE 'DROP TRIGGER ['+[sch].[name]+'].[trg_prevent_modify_'+[tab].[name]+']' + CHAR(13)
	END
FROM sys.tables AS tab
	JOIN sys.schemas AS sch ON tab.[schema_id] = sch.[schema_id]
WHERE tab.[type] = 'U'

PRINT @Stmt
EXECUTE(@Stmt)
GO

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.