triggers operating on synchronous replicated server
Posted on 2013-01-14
I am (unfortunately) using SQL-Server 2005.
I am working on an application which needs to track changes to a sql-server database. I have written triggers for the sql database that record the necessary details in new tables, which the application then reads, then modifies to indicate that they have 'synced'. (i.e.: There is a synced column which defaults to 0, then when the application has read it, the application sets the column to 1)
The application will read the data from a sql-server that is replicated (synchronously) from the main database. Like this:
Office sql-server -----> sql-server B <-------> Application
The only information that the application commits to the sql-server B is to change the synced columns from 0 to 1 when it has read them.
Is there a way to have the triggers fire on sql-serverB only, for changes made to tables on the office server?
I have 2 reasons for wanting this:
1) server load: Some of the triggers query complex views, so i'd rather that processing was done by sql-server B.
2) safety: We want to modify the office sql-server as little as possible. The office server is mission critical, sql-server B is not.