0

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

1

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).

  • Thanks Aaron. In that queue table, what is the best way to store things? Can it store query as plain text and run them or should it store as column1 field to update, column2 new value etc...? – Greg Mar 25 '15 at 14:55
  • @Greg I don't know what is best for you, sorry. Whatever is easiest to store and then later execute. That might be a query or it might just be parameter values. I've used both approaches. – Aaron Bertrand Mar 25 '15 at 15:12
  • Would it be possible to mix both queue table and update in trigger? if update in trigger did not work than insert it in the queue? – Greg Mar 25 '15 at 15:25
  • @Greg Why would you want to do that? If the remote table can't be reached it could time out after 30 seconds. Do you really want the initial transaction to wait for a remote server to time out? Your goal should be to minimize the amount of work your trigger has to do, not maximize it. Just let the background job update the other table, if you're going to set that up anyway... – Aaron Bertrand Mar 25 '15 at 15:28
  • Yes what you say makes sense. Did not think of it. I will read more about a queue table system. – Greg Mar 25 '15 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.