Multi-Database Transactions

[David Wimbush]

I've been looking into adding transaction capabilities into my object
model framework. I always understood that, with SQL Server, a
transaction could only cover one database, regardless of whether the
transaction was done in a stored procedure or ADO ('classic ADO' or
ADO.Net). But when I started reading in detail it got vague. For
example, people were talking about data _sources_ instead of databases.
So I did some testing and I can do a transaction in either SQL or ADO
that successfully commits or rolls back updates to two databases. I'm
using a single SQL Server 2000 Standard Edition with multiple databases
on it and .Net 1.1.

Before I get carried away, can anybody tell me the facts or point me to
some definitive information about this, please? Thanks.

 

[William \(Bill\) Vaughn]

SQL Server has supported the concept of cross-database (or even cross
server) transactions for ages. It implements this behind the scenes by using
"2-phase commit". Generally, when I want to do this, I do so in a Stored
Procedure that links to the other server (data source) and database. I would
see books online for information on linked servers and cross-server
transactions.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sahil Malik]

David,

Distributed transactions are always a second choice when single database
transactions - just won't do it.

In order of preference, I'd implement it using one of the following
solutions -

a) Linked database/stored proc.
b) Begin Distributed Transaction
c) System.Transactions in .NET 2.0, using Sql 2k5
d) System.Transactions in .NET 2.0, using Sql 2k
e) Enterprise Services based solution
f) MTS based solution in C++
g) MTS based solution in VB6

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/