Mirroring and triggers

I am currently testing mirroring on a database which uses many triggers which
update data in a seperate database. Those triggers DO NOT use "not for
replication" option. The mirroring seems to work fine, but it seems that
when data is changed on the mirrored database in a table which has these
triggers that they aren't triggered. Is there a way to get triggers still to
trigger when using mirroring? If not is this possible with log shipping?
Cheers!
TonyIn the mirrored database the triggers shoud be applied because of the content
of the transaction log sended by the principal DB. If you cannot see the
modifications due to trigger actions, verify the principal trigger
definitions and verify also that on principal the triggers arer enabled ans
successfully operating.
Gilberto Zampatti
"Cayman Visitor" wrote:
> I am currently testing mirroring on a database which uses many triggers which
> update data in a seperate database. Those triggers DO NOT use "not for
> replication" option. The mirroring seems to work fine, but it seems that
> when data is changed on the mirrored database in a table which has these
> triggers that they aren't triggered. Is there a way to get triggers still to
> trigger when using mirroring? If not is this possible with log shipping?
> Cheers!
> Tony|||They are working properly on the primary server, but not on the mirrored
server. Not sure what to even try.
"Gilberto Zampatti" wrote:
> In the mirrored database the triggers shoud be applied because of the content
> of the transaction log sended by the principal DB. If you cannot see the
> modifications due to trigger actions, verify the principal trigger
> definitions and verify also that on principal the triggers arer enabled ans
> successfully operating.
> Gilberto Zampatti
> "Cayman Visitor" wrote:
> > I am currently testing mirroring on a database which uses many triggers which
> > update data in a seperate database. Those triggers DO NOT use "not for
> > replication" option. The mirroring seems to work fine, but it seems that
> > when data is changed on the mirrored database in a table which has these
> > triggers that they aren't triggered. Is there a way to get triggers still to
> > trigger when using mirroring? If not is this possible with log shipping?
> >
> > Cheers!
> > Tony|||Beg pardon, i did'nt read the word "separate database in the first message.
Well, if the trigger in the principal DB is well functioning, why should ir
work also on the mirrored DB? it would operate two times in this case. The
mirrored DB is a stand-by "NORECOVERY" DB, updated via transaction log by the
principal, and consistent with it. The separated DB shoul be update by the
triggers, but just once.
Gilberto Zampatti
"Cayman Visitor" wrote:
> They are working properly on the primary server, but not on the mirrored
> server. Not sure what to even try.
> "Gilberto Zampatti" wrote:
> > In the mirrored database the triggers shoud be applied because of the content
> > of the transaction log sended by the principal DB. If you cannot see the
> > modifications due to trigger actions, verify the principal trigger
> > definitions and verify also that on principal the triggers arer enabled ans
> > successfully operating.
> > Gilberto Zampatti
> >
> > "Cayman Visitor" wrote:
> >
> > > I am currently testing mirroring on a database which uses many triggers which
> > > update data in a seperate database. Those triggers DO NOT use "not for
> > > replication" option. The mirroring seems to work fine, but it seems that
> > > when data is changed on the mirrored database in a table which has these
> > > triggers that they aren't triggered. Is there a way to get triggers still to
> > > trigger when using mirroring? If not is this possible with log shipping?
> > >
> > > Cheers!
> > > Tony|||The issue is these triggers update a seperate database which is NOT in
"NORECOVERY" state. Here is my scenerio in a bit more detail.
I have two databases, Data_DB and Audit_DB. Data_DB is the DB our
applications work against. Audit_DB contains the audit trail history from
Data_DB. Audit_DB is ONLY ever changed via triggers which run in Data_DB.
Data_DB is currently about 220MB while Audit_DB is currently around 4.5GB.
So audit history has a LOT more data changes. We want to mirror this to a
remote location for DR and and our pipeline between our primary site and our
DR site has TONS of data going over it so I'd like to add as little data
moving across this wire as possible.
So my hope was I could just mirror the Data_DB. Then on the mirror side, as
data changes our audit triggers on the mirror DB will automatically make the
proper entries on the Audit_DB at the remote site instead of having to also
mirror the Audit_DB (thus significantly increasing the data moving between
the two sites).
It seems because the mirrored DB is in the NORECOVERY state this isn't
possible? If that is the case do you happen to know if such a scenerio could
work with log shipping?
Thx so much!
Tony
"Gilberto Zampatti" wrote:
> Beg pardon, i did'nt read the word "separate database in the first message.
> Well, if the trigger in the principal DB is well functioning, why should ir
> work also on the mirrored DB? it would operate two times in this case. The
> mirrored DB is a stand-by "NORECOVERY" DB, updated via transaction log by the
> principal, and consistent with it. The separated DB shoul be update by the
> triggers, but just once.
> Gilberto Zampatti
> "Cayman Visitor" wrote:
> > They are working properly on the primary server, but not on the mirrored
> > server. Not sure what to even try.
> >
> > "Gilberto Zampatti" wrote:
> >
> > > In the mirrored database the triggers shoud be applied because of the content
> > > of the transaction log sended by the principal DB. If you cannot see the
> > > modifications due to trigger actions, verify the principal trigger
> > > definitions and verify also that on principal the triggers arer enabled ans
> > > successfully operating.
> > > Gilberto Zampatti
> > >
> > > "Cayman Visitor" wrote:
> > >
> > > > I am currently testing mirroring on a database which uses many triggers which
> > > > update data in a seperate database. Those triggers DO NOT use "not for
> > > > replication" option. The mirroring seems to work fine, but it seems that
> > > > when data is changed on the mirrored database in a table which has these
> > > > triggers that they aren't triggered. Is there a way to get triggers still to
> > > > trigger when using mirroring? If not is this possible with log shipping?
> > > >
> > > > Cheers!
> > > > Tony|||You'll think i'm stupid: but you wrote: "...Audit_DB is ONLY ever changed via
triggers which run in Data_DB..."; The only way to recover Audit_DB is mirror
it or using log shipping on it. If you apply log shipping on Data_DB i think
the result will be the same you have with irroring, just less efficient and
secure. I don't know an alternative way, sorry
Gilberto Zampatti
"Cayman Visitor" wrote:
> The issue is these triggers update a seperate database which is NOT in
> "NORECOVERY" state. Here is my scenerio in a bit more detail.
> I have two databases, Data_DB and Audit_DB. Data_DB is the DB our
> applications work against. Audit_DB contains the audit trail history from
> Data_DB. Audit_DB is ONLY ever changed via triggers which run in Data_DB.
> Data_DB is currently about 220MB while Audit_DB is currently around 4.5GB.
> So audit history has a LOT more data changes. We want to mirror this to a
> remote location for DR and and our pipeline between our primary site and our
> DR site has TONS of data going over it so I'd like to add as little data
> moving across this wire as possible.
> So my hope was I could just mirror the Data_DB. Then on the mirror side, as
> data changes our audit triggers on the mirror DB will automatically make the
> proper entries on the Audit_DB at the remote site instead of having to also
> mirror the Audit_DB (thus significantly increasing the data moving between
> the two sites).
> It seems because the mirrored DB is in the NORECOVERY state this isn't
> possible? If that is the case do you happen to know if such a scenerio could
> work with log shipping?
> Thx so much!
> Tony
> "Gilberto Zampatti" wrote:
> > Beg pardon, i did'nt read the word "separate database in the first message.
> > Well, if the trigger in the principal DB is well functioning, why should ir
> > work also on the mirrored DB? it would operate two times in this case. The
> > mirrored DB is a stand-by "NORECOVERY" DB, updated via transaction log by the
> > principal, and consistent with it. The separated DB shoul be update by the
> > triggers, but just once.
> > Gilberto Zampatti
> >
> > "Cayman Visitor" wrote:
> >
> > > They are working properly on the primary server, but not on the mirrored
> > > server. Not sure what to even try.
> > >
> > > "Gilberto Zampatti" wrote:
> > >
> > > > In the mirrored database the triggers shoud be applied because of the content
> > > > of the transaction log sended by the principal DB. If you cannot see the
> > > > modifications due to trigger actions, verify the principal trigger
> > > > definitions and verify also that on principal the triggers arer enabled ans
> > > > successfully operating.
> > > > Gilberto Zampatti
> > > >
> > > > "Cayman Visitor" wrote:
> > > >
> > > > > I am currently testing mirroring on a database which uses many triggers which
> > > > > update data in a seperate database. Those triggers DO NOT use "not for
> > > > > replication" option. The mirroring seems to work fine, but it seems that
> > > > > when data is changed on the mirrored database in a table which has these
> > > > > triggers that they aren't triggered. Is there a way to get triggers still to
> > > > > trigger when using mirroring? If not is this possible with log shipping?
> > > > >
> > > > > Cheers!
> > > > > Tony