SQL 2008 Express (R2): Creating an UPDATE trigger to update a table on another server?

Question

Hi there,

I am just trying to think through a process I need where I have:

1. One SQL 2008 express database on a webserver - takes orders into an orders table
2. Another SQL 2008 express database on another webserver acting as a backend system to process the orders etc

The idea being that the webserver database can be left to do what it does, and only when a new order record is inserted (actually updated.. another part of the story) I wanted that row to be inserted to an orders table on another server...

The two servers do have local IP addresses, and are essentially on the same subnet - i.e. can communicate easily enough...

Just trying to work out if a SQL Trigger is the way to go, or do I have perhaps a scheduled job on the second server which queries the first server say every 5 mins (plenty, not too many orders) and inserts any new orders into it's own database.. the idea is the table in the source/first server has a bit field to indicate if that row has been seen/read by server two...

Any ideas? unfortunately it's all SQL 2008 R2 express at this stage...

Answer 1

You don't want a trigger accessing another server as part of a transaction. One solution, is to use Service Broker; have the trigger insert a message into the the local queue, and given the other server access to this queue.

SQL Server Service Broker provides the SQL Server Database Engine native support for messaging and queuing applications. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.

Answer 2

You need to read up on linking servers first. This ought to be a great article for you to start with. Better yet, the scenario it describes is actually fairly similar to yours. Also, replication between the two servers may be a better choice than triggers. Here's a link for an article doing just that.