Replication “Alternative Trigger action”

2006-11-21T09:48:00Z

One of the recent technical challenges that I was required to resolve was an issue with several Publication’s (Transactional) failing shortly after new triggers where added on the subscriber tables, the problem was not occurring on other identical publication’s which do not have the triggers applied. (Note: the triggers where added to perform distributed transactions - Inserts / Updates /Deletes).

Several new snapshots and profiler trace sessions later it was evident that the problem was not a one-off and was re-producible {some progress then – but issue was still unresolved}.

So my next step was to come up with a alternative solution that would resolve the issue quickly (within 4 hours!), the solution was one I had thought previously might be a better solution than the original triggers, so time to test out my theory.

I try will explain the solution clearly and the benefit’s (IMHO), but first I had also identified the following additional problem during profiler sessions

An update on the publisher database where created as replication command “Pairs”, a Delete then Insert command, this of course has performance implications creating fragmentation and blocking actions, higher database loads @ the subscriber’s etc..

Due to the large number of updates (> 10,000 / Per Hour) it was critical that the solution would be efficient as possibly (of course)

Firstly I addressed the command pair issue, by implementing the trace flag -8207 run the following @ the publisher DBCC TRACEFLAG(8207, -1) & –T8207 on the database startup properties

See the following link for additional details on this trace flag http://support.microsoft.com/kb/302341

Now the updates @ the Publisher are ‘singleton’ updates I can implement my proposed solution, The revised trigger solution is basically quite simple (best solutions are usually the simplest).

I took the code from within the 3 different triggers and modified these in the 3 replication stored procedures sp_MSins_xxx, sp_MSupd_xxx & sp_MSdel_xxx @ the subscriber

I have posted some sample code below to illustrate the replacement trigger action

Note: Modified from original and missing most of the parameters for readability

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

ALTER procedure "sp_MSins_tablename" @c1 int,@c2 int … @c142 datetime

BEGIN

insert into "dbo"."tablename"( "lID", "lTitleID"…"dtUpdated" )

values ( @c1, @c2, …@c142 )

/* Replace Trigger Operation */

SET XACT_ABORT ON

DECLARE @ID INT

SELECT @ID = @c1 -- ID Value -- Note this should be = @pkc1 for the Inserts / Deletes versions

IF @ID IS NOT NULL

BEGIN

IF NOT EXISTS (SELECT ID FROM DB1.dbo.tx_Hold WHERE DBKEY1 = @ID AND STATUS = 1)

BEGIN DISTRIBUTED TRANSACTION

INSERT INTO DB1.dbo.tx_Hold

(DBKEY1, DEALTYPEID, PARTIALU, TX, TXTIMESTAMP, STATUS, RESULT1)

VALUES

(@ID, 1, 2, 'I', GETDATE(),1,'')

IF @@rowcount = 0

BEGIN

INSERT INTO DB1.dbo.tx_Error

(DBKEY1, DEALTYPEID, PARTIALU, TX, TXTIMESTAMP, STATUS, RESULT1)

VALUES

(@ID, 1, 2, 'I', GETDATE(),1,'')

END

COMMIT TRAN

END

SET XACT_ABORT OFF

END

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

Now I have a revised ‘Working’ solution, I needed to determine it’s pro’s & con’s

Pro’s

Replication now works without the previous failures (Goal # 1 achieved) and replication stored performance is not adversely affected with the added code (Goal #2), Mgmt & Client now happy (Bonus !)

Con’s

No longer able to run commands directly on table to perform the previous the trigger actions, this is being handled by running separate statements to perform the required action’s & modifying other stored procedures to include the necessary code where required.

Summary

Using tools like profiler and your knowledge of SQL Server workings is key to understanding the problem and devising alternative solutions, reviewing BOL and other sources (forum’s, colleagues, searches) until you understand the problem

Then ‘Write’ it down and if needed brainstorm until some idea is formulated.

Finally identify the likeliest solution and perform your TESTING until you are happy you have a potential workable solution.. that will resolve your identified problem's

Implement your solution.. RE-TEST and confirm you have resolved the problem and other's are in agreement. then you carry on attacking your other priorities and get home to down a few beers and smile smugly.

Lastly don't forget to document your success or failures (even these are valuable in finding the solution) and keep this in a searchable folder as you never know when you or another colleague will need the info in the future.

NeilHambly

Replication “Alternative Trigger action”