Cross-database transactions from one SP

Question

I need to update multiple databases with a few simple SQL statement. The databases are configurared in SQL using 'Linked Servers', and the SQL versions are mixed (SQL 2008, SQL 2005, and SQL 2000). I intend to write a stored procedure in one of the databases, but I would like to do so using a transaction to make sure that each database gets updated consistently.

Which of the following is the most accurate:

1. Will a single BEGIN/COMMIT TRANSACTION work to guarantee that all statements across all databases are successful?
2. Will I need multiple BEGIN TRANSACTIONS for each individual set of commands on a database?
3. Are transactions even supported when updating remote databases? I would need to execute a remote SP with embedded transaction support.

Note that I don't care about any kind of cross-database referential integrity; I'm just trying to update multiple databases at the same time from a single stored procedure if possible.

Any other suggestions are welcome as well. Thanks!

Answer 1

You should be able to accomplish #1 using a distributed transaction. You will need DTC active and you'll need to use BEGIN DISTRIBUTED TRANSACTION along with ROLLBACK TRANSACTION and COMMIT TRANSACTION within your stored procedure.

Dealing with the DTC can being up a lot of gotchas, so good luck :)

Answer 2

It is possible. You can use explicit BEGIN DISTRIBUTED TRANSACTION in your controlling procedure, or simply start a normal transaction and rely on DTC to elevate your transaction to a distributed one the moment you go across a linked server, this happens automatically. See Transact-SQL Distributed Transactions in MSDN.

However I must warn you that it's a slippery slope. The number of failures and the downtime dramatically increase as soon as you bring DQ (distributed queries) into picture. If you have 99.5% up time servers (ie. 43 hours of downtime a year) and your query touches 5 servers your availability becomes 97.5% (216 hours of downtime a year). With 10 servers it becomes 95% up time (428 hours of down time year). Things like managing an OS patch deployment, a engine SP upgrade or application maintenance (think index rebuilds and such) become a nightmare to orchestrate and coordinate.

The way to go is to decouple the servers, use something like Service Broker instead of DQ.