Hey All-
So I've been studying up on the System.Transactions namespace to prepare an architecture for our application to upgrade its databases. There are at least 2 databases used, possibly more, and they have to be updated atomically when the schema changes. The worse case scenario will be running on Sql2k5, but we do support Sql2k.
I think I already know the answer to this, but just want to make sure before I start. Even when both databases are running on the same Sql2k5 server instance, when the 2nd connection opens, the transaction gets promoted to MSDTC. From what I can tell, this is the expected behaviour simply because of more than 1 connection, even when using Sql2k5. Is this correct This thread seems to indicate there may be a bug in the framework and that in Sql2k5 the transaction should stay lightweight.
One other question...in regard to MSDTC; is it required on the client and server machines, just the server machines, or just the client Is it installed by Sql server Is it absolutely required in this scenario with 2 databases
Thanks in advance,
John
TransactionScope
Buddy132
My application is deployed to about 200 businesses across the United States. Each business runs a small LAn from 1-15 systems.
My users are not computer savvy. How do I enable MSDTC and network access to MSDTC programmatically so that when users install my application they can run it without getting MSDTC errors everywhere
amselem
Yes, System.Transactions will require MSDTC when using more than one database (multiple resources), or if you open more than one connection to a single database (even SS2K5); inside transactions only.
MSDTC must run on the SQL Server and on all machines running code that uses System.Transactions (or COM+ or EnterpriseServices). You can check if DTC is running by right-clicking the computer node in the treeview in "Component Services". The different DTCs must be able to communicate, which will require you to allow network access for DTC on the clients.
I think MSDTC is an optional component during SQL Server installation. It can be added afterwards when needed.