Monday, 17 August 2015

MSSQL - How to replicate to readonly database?

First, I have to apologize, title of article lies - it is unfortunately impossible to set up replication into the READ-ONLY database.
One of the reasons why we want to have the target database read-only, is the need to avoid direct changes of data in it.
For this purpose, we can use another method - Triggers, which prohibit any changes except changes from the replication.

There such trigger:
USE MySourceDB
GO
ALTER TRIGGER dbo.trig_mytable_CheckContext4Changes ON dbo.MyTable
    FOR INSERT, UPDATE, DELETE
    NOT FOR REPLICATION
AS
IF NOT (@@SERVERNAME = 'MySourceServer' AND DB_NAME = 'MySourceDB')
    ROLLBACK;

Very important is this expression: NOT FOR REPLICATION.
On the target database at the same time we have to
- prohibit all direct changes (INSERT/UPDATE/DELETE)
- but allow data changes which are comming from replication
Expression NOT FOR REPLICATION deactivates trigger for changes from replication.