SQL Server 2008 – Best Practices for Recovering CRUD Statements to Linked Server

linked-serverservice-brokersql-server-2008sql-server-2008-r2transactional-replication

I am looking for the best practice for the following scenario.

We have a CRM in our company. When an employee updates the record of a company, there is trigger that fires a stored procedure which has a CRUD statement to the linked server hosting the SQL DB of our website.

Question:

What happens when the connection is lost in the middle of the CRUD and the SQL DB of the website did not get updated?
What would be the best way to have the SQL statement processed again when the connection is back?

I read about Service Broker or Transactional Replication. Is one of these more appropriate for that situation?

The configuration:

Local: SQL server 2008 R2

Website: SQL server 2008

Best Answer

Instead of having the trigger rely on the linked server being available, use a queue table. Have a background job that can use sp_testlinkedserver and process all of the items in the queue only when the server is available. It can use TRY/CATCH as well, and only mark those as processed when they are successfully updated, otherwise it will continue trying them every time the background job runs.

This allows you to continue updating the local table and not worry about the connection to the other server (and not making your local transactions wait on updating a remote server).

Related Question