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.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.