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
NOT FOR REPLICATIONdeactivates trigger for changes from replication.