Possible to use a Trigger as failsafe for insert in two databases?
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.