Sql-server – Possible to use a Trigger as failsafe for insert in two databases

error handlinglinked-serversql serversql-server-2008-r2

(This is for a project on MSSQL 2008R2 Server)

I have been tasked with splitting a table into an internal (same db as before) and an external one (different server).
Currently the idea is to read only from the external table and to insert into the external table IF possible. If the external is unreachable the inserts are to be redirected into the internal table until the external is back online. The records within the internal table will be moved into the external table manually afterwards.

At the moment we have created a view that points to the external table and a instead of insert Trigger that does the following:

ALTER TRIGGER dbo.Log_IoI ON dbo.Log
INSTEAD OF INSERT
AS
BEGIN
    BEGIN TRY       
        INSERT INTO LINKEDSERVER.dbo.ExternalLog (...)
        FROM INSERTED
    END TRY
    BEGIN CATCH
        INSERT INTO dbo.InternalLog (...)
        FROM INSERTED
    END CATCH   
END

(Let me state at this point, that we are using a view to replace the table because there are a ton of triggers and stored procedures targeting this logtable and we can't change them without an incredible amount of extra work.)

I thought this was a good idea at first but two problems reared their ugly head.

After a lot of try and error I can't seem to be able to contain critical error in the try. For example if the insert into the external db fails because the external db went offline I get the following Message:

OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVER" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 65535, Level 16, State 1, Line 0
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

After some research I have found some discussions over at SQL-Central that it is not possible to do this kind of error-handling within a trigger.

This has me stumped and brings me to my question:

IF this is true, is their any other way to build a failsafe "fork in the road"-Trigger?

I have tried to use
SAVE Transaction
before the Try and then to Rollback at the beginning of the catch. It had no effect.

I am open to a new direction to solve this problem as I am completly stumped and I might be kicking a dead horse by trying to make this solution work.

PS: Second problem is performance. If the trigger would work in case of a failure of the external db the trigger would wait for a timeout on evrey insert until the insert would be redirected to the internal database. Really bad for performance seeing as it is a logging table.

Best Answer

After a lot of try and error I can't seem to be able to contain critical error in the try.

The big problem here is that performing the remote insert inside the trigger involves a distributed transaction. Triggers execute inside the transaction of the calling statement; in this case, the transaction is the auto-commit transaction for the original INSERT statement. This local transaction is promoted to a distributed transaction when the operation on the linked server is attempted. Crucially, errors raised by DTC cannot be caught by TRY...CATCH.

An alternative approach is to write the view trigger so it only writes to the local table. A separate process (e.g. a SQL Agent job) then attempts to perform the remote inserts, removing successfully-processed entries from the local table.

To make this work, you will need to process the remote inserts one row at a time, via a stored procedure on the remote server that contains a TRY...CATCH block to catch any errors locally. This is restricted to a row at a time because the insert data must be passed via parameters to the stored procedure.

The job process needs to use a transaction to ensure local deletes and remote inserts are atomic, which you'd think would raise the same sorts of issues as with the trigger, but no - this does work in all scenarios I have tested so far.

You could also use Service Broker for this, which is a bit more work initially, but would avoid the row-at-a-time restriction, and is probably neater all round.

Second problem is performance.

You can reduce the impact of this by reducing the connection timeout value using sys.sp_serveroption, for example:

EXECUTE sys.sp_serveroption 
    @server = N'LINKEDSRV',
    @optname = 'connect timeout',
    @optvalue = N'1'; -- seconds

This is a moot point though if you decide to implement using Service Broker.

Related Question