How can I use transactions that span procedures chained across multiple servers?

Question

I'm trying to test a proposition that one of our vendors presented to us for accessing their product database and it regards to queries and transactions that span multiple servers. I've never done this directly on the database before and to be frank, I'm clueless, so I'm trying to mock up a proof that this works at least conceptually.

I've got two SQL Server 2005 servers. Let's for argument's sake call them Server1 and Server2 [hold your applause] each containing a dummy database. The dummy database on Server1 is called Source and that on Server2 is called Destination, just to keep things simple. The databases each hold a single table called Input and Output respectively, so the structure is quasi explained like so:

• Server1.Source.dbo.Input
• Server2.Destination.dbo.Output

I have a stored procedure on Server2 called WriteDataToOutput that receives a single Varchar argument and writes it's content to the output table.

Now the trickiness starts:

1. I want to create a stored procedure on Server1.Source that calls the WriteDataToOutput stored procedure defined on Server2, which seems like the simple step.
2. I want this call to be part of a transaction so that if the procedure that invokes it fails, the entire transaction is is rolled back.

And here endeth my knowledge of what to do. Can anyone point me in the right direction? I tried this on two different databases on the same server, and it worked just fine, leading me to assume that it will work on different servers, the question is, how do I go about doing such a thing? Where do I start?

Answer 1

You will want to link the servers:

http://msdn.microsoft.com/en-us/library/aa213778.aspx

Answer 2

You need to set up a link between one server and another.

Answer 3

for step 2 you need to have Distributed Transaction Coordinator running, you also need to use SET XACT_ABORT ON to make sure it will all rollback you also need to enable RPC which is turned off by default in 2005 and up

There is a whole bunch of stuff that can bite you in the neck

Answer 4

Using linked servers, you can run stored procedures on either server within a single transaction using DTC (Distributed Transactino Coordinator). You will definitely want to do some performance analysis. I have found some SPs using links can drastically slow down down database performance, especially if you try to join result sets from each of the two servers.

Answer 5

Set up a linked server, then you should be able to execute selects/inserts/updates across the servers. Something like:

INSERT INTO Server2.Destination.dbo.Output
SELECT * FROM Input  
WHERE <Criteria>

This assumes you are running the query from Server1.Source, so you wouldn't need to fully qualify.

Answer 6

As others have noted, I agree that a linked server is the best way to go.

Here are a couple of pointers that snagged me the first time I dealt with linked servers:

• If the linked server is an instance, make sure you bracket the name. For example [SERVERNAME\INSTANCENAME].

• Use an alias for the table or view from the linked server or you will get a "multi-part identifier cannot be bound" error. There is a limit of a 4 part naming convention. For example SERVER.DATABASE.dbo.TABLE.FIELD has five parts and will give an error. However, SELECT linked.FieldName FROM SERVER.DATABASE.dbo.TABLE AS linked will work fine

Answer 7

MSDN says you can have transactions across linked servers if you use the command BEGIN DISTRIBUTED TRANSACTION.

I remember though that I had problems called a stored procedure on a linked server, but I worked around it, rather than solving it.