Automatic Replication Trigger

HI All,
Using SQL Server 2000 SP3.
Replication is set up on a few databases, on each of the tables that are
replicated the following trigger appears:
create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
update as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [dbo].[Voucher]
set msrepl_tran_version = newid()
from [dbo].[Voucher], inserted
where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
This trigger is causing a significant slowdown when called and I would
like to remove it. However I do not want to break the replication.
I need to understand what has created this trigger and why. I belive
that this has been implemented as a workaround for some problem that was
encountered by the replication process but this is merely speculation on
my behalf.
Your help is very much appreciated.
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
It looks like you are using immediate or queued updating which puts these
triggers in place on the publisher. I can't comment on Microsoft's logic
behind this proc. Basically what it does is assigns a new value to the guid
column if the guid column it self is not modified/supplied.
I would look at whether you really need immediate updating or bi-directional
replication in general as bi-directional transactional replication offers
some of the functionality but will not cause the performance degradation.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:uU1y0aSSGHA.4956@.TK2MSFTNGP09.phx.gbl...
>
> HI All,
> Using SQL Server 2000 SP3.
> Replication is set up on a few databases, on each of the tables that are
> replicated the following trigger appears:
> create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
> update as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [dbo].[Voucher]
> set msrepl_tran_version = newid()
> from [dbo].[Voucher], inserted
> where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
>
> This trigger is causing a significant slowdown when called and I would
> like to remove it. However I do not want to break the replication.
> I need to understand what has created this trigger and why. I belive
> that this has been implemented as a workaround for some problem that was
> encountered by the replication process but this is merely speculation on
> my behalf.
> Your help is very much appreciated.
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks for your response here. What is required is one-way replication
as we have a primary server that needs to replicate information to a
backup server.
I presume that this is a different configuration from immediate/queued
updating. I thought that transactional replication is what was set up
but perhaps this is not the case?
Can the specific kind of replication be altered in situ or would it need
to be dropped and recreated?
Again many thanks for any help!
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
|||Use transactional replication if you have no requirement for
bi-directionality. to fix this drop the subscriber, and then disable the
triggers, then redeploy the subscriber as a plain vanilla subscriber.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:OarBgsZSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>
> Thanks for your response here. What is required is one-way replication
> as we have a primary server that needs to replicate information to a
> backup server.
> I presume that this is a different configuration from immediate/queued
> updating. I thought that transactional replication is what was set up
> but perhaps this is not the case?
> Can the specific kind of replication be altered in situ or would it need
> to be dropped and recreated?
> Again many thanks for any help!
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***