I am syncing up data between three server using MSDTC via SQL server 2000. My triggers are appending data from one linked server to another to another. What I have noticed is when an issue arises on one of the three servers, speed and performance is degraded drastically. Is there a piece of code that I can incorporate to do some error checking or see if the distributed transaction has failed? I thought that is what SET xact abort ON did but it just run so slow. Also, if the middle system goes down, I need the initial table tblTest to still be able to process inserts quickly and it just slows it down to much. Any ideas would be very helpful.
Trigger Example:
CREATE TRIGGER tr_Insert
ON tblTest
FOR INSERT
AS
SET xact_abort ON
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LinkedServer.Tables
(
ID
)
SELECT
i.ID
From inserted i
COMMIT TRAN
Trigger Example:
CREATE TRIGGER tr_Insert
ON tblTest
FOR INSERT
AS
SET xact_abort ON
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LinkedServer.Tables
(
ID
)
SELECT
i.ID
From inserted i
COMMIT TRAN