rdun25
asked on
Triggers on replicated SQL-server database
I am using sql-server 2005. My setup is like this:
Server A ----> Server B.
Server A is replicated to server B synchronously. (No data needs to come back from server B) I am using the built in transcription replication.
I have written triggers that need to fire when data is modified on Server A. I only need to read the data generated by these triggers on server B.
The triggers involve some complex views and will be quite taxing on server A. Server A is mission critical, server B is not. I want there to be minimal impact on Server A.
Is it possible to have triggers fire on Server B when the transactions from Server A change things, as opposed to have them fire on Server A when the tables are first modified?
To illustrate:
SCENARIO ONE:
a. User modifies table x on Server A.
b. an update to table x causes a trigger to fire which records data in table y.
c. Server A replicates the data in table x and table y to server B.
SCENARIO TWO:
a. User modifies tabel x on Server A.
b. Server A replicates the data in table x to server B.
c. An update trigger on Server B fires when it detects that the replication transaction has changed the data in table x.
d Server B records data in table y.
I want Scenario Two, can it be done?
Server A ----> Server B.
Server A is replicated to server B synchronously. (No data needs to come back from server B) I am using the built in transcription replication.
I have written triggers that need to fire when data is modified on Server A. I only need to read the data generated by these triggers on server B.
The triggers involve some complex views and will be quite taxing on server A. Server A is mission critical, server B is not. I want there to be minimal impact on Server A.
Is it possible to have triggers fire on Server B when the transactions from Server A change things, as opposed to have them fire on Server A when the tables are first modified?
To illustrate:
SCENARIO ONE:
a. User modifies table x on Server A.
b. an update to table x causes a trigger to fire which records data in table y.
c. Server A replicates the data in table x and table y to server B.
SCENARIO TWO:
a. User modifies tabel x on Server A.
b. Server A replicates the data in table x to server B.
c. An update trigger on Server B fires when it detects that the replication transaction has changed the data in table x.
d Server B records data in table y.
I want Scenario Two, can it be done?
ASKER
Thank you for your response. I am sorry, I may be confusing terms here (I've only had cursory experience with SQL).
The replication from Server A to Server B is a transactional replication. So any changes to Server A are replicated to Server B. I am using the built in Replication system.
Could you elaborate on how a scheduled job would work? I need to record if any changes have been made to several tables in the database. At the moment there are UPDATE, CREATE and DELETE triggers which record the Primary Key of the altered table and a timestamp.
The replication from Server A to Server B is a transactional replication. So any changes to Server A are replicated to Server B. I am using the built in Replication system.
Could you elaborate on how a scheduled job would work? I need to record if any changes have been made to several tables in the database. At the moment there are UPDATE, CREATE and DELETE triggers which record the Primary Key of the altered table and a timestamp.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, that's brilliant. Probably be better and less demanding than triggers.
The thing to be careful of is what is the replication synchronisation set to? Is it delete table and create a new one? (this will delete your triggers) or truncate and insert records?
I can't see why scenario two would work.
The catch for me is that you said the replication is synchronous - doesn't that mean that before ServerA commits a transaction, it sees that it committed in ServerB first? Therefore anything you do by way of a trigger on either server will affect performance.
Do you need a trigger? Can it be done on ServerB with a regularly scheduled job?
Regards
David