How to Properly Trigger an Insert to a Linked SQL Server

How to properly trigger an insert to a linked sql server?

Perform inserts from a trigger into a table on linked server - bad decision.
This will great affects on insert performance in the source table ([dbo].[Table_1])
and also there is a distributed transaction, and configuring servers
for support distributed transactions - nightmare.

One possible solution is:

1. On the source server you can create a synchronization queue table. For example:

   CREATE TABLE dbo.SyncQueue
   (
       QueueId INT IDENTITY(1,1),        
       KeyForSync INT, -- Primary key value of record in dbo.SourceTable
       SyncStatus INT  -- statuses can be: 0 - New, 1 - Synchronized, 2 - Error
   )
   
   suppose you source table is
   
   CREATE TABLE dbo.SourceTable
   (
           Key INT, -- primary key of the table
           Data varchar(xxx)
   )
   

2. Triger on dbo.SourceTable can quickly insert into dbo.SyncQueue record Key that you need synchronize

3. Some periodically performed stored procedure can then insert records from the queue in
   table on the linked server.

INSERT trigger from another table from a linked Server is slow

Triggers should always be written to be as fast as possible to avoid exactly this problem. Intensive operations of any kind should ideally not be carried out within a trigger. That applies doubly when the operation involves contacting other servers as that can easily end up taking real time.

Instead queue the action and process your queue outside the trigger using a service or agent.

A queue might looks like a record in a table which gets flagged once processed. The record needs to contain enough information for the service to carry out the related actions, which could be contained within a Stored Procedure.

Related Topics