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
GO
SET ANSI_NULLS ON
GO
ALTER procedure "sp_MSins_tablename" @c1 int,@c2 int … @c142 datetime
AS
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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
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.