confusion about transactions and msdtc

Question

I have some basic confusion about how transactions and msdtc work together.

I have a basic server/client winforms app. The app uses transactionscope to encapsulate several sql commands that are executed on the sql server.

The app seemed to work fine when I enabled msdtc network access on the server only. Then one day it stopped working saying network access was not enabled.

Now it seems that I have to enable msdtc network access on both the client computer and server for transactionscope to work.

Does the client or server msdtc service do the transaction work? Or maybe its both?

Does anyone have guidance on whether msdtc network access is needed on both client and server or just server?

Answer 1

If you are using MSDTC, then you will need the client (your application) and the server (database) to both run MSDTC and also to be configured properly.

This can be source of pain especially when dealing with firewalls. If you are having trouble see Troubleshooting Problems with MSDTC. It talks about BizTalk but it applies to MSDTC in general. DTCPING is also your friend.

Now if you are using SQL Server 2005 and higher, are accessing only one database, are using one database connection and are not passing Transactions between app domains then you should not require the use of MSDTC. Under those circumstances the System.Transactions transaction manager will manage your transactions for you. If any of the previous situations occurs then the transaction will be promoted to a distributed transaction (and the transaction manager will be MSDTC). See Transaction Management Escalation for more information.

In general it is best to avoid the use of MSDTC if you do not need it. i.e. if you are only dealing with a single SQL Server 2005+ database then try to design your code to not use MSDTC. Besides the configuration hassle, DTC imposes a performance penalty because all calls to MSDTC are out of process combined with the overhead of the two phase commit protocol (which MSDTC uses).

In terms of what is happening in your specific situation it is hard to say. If your code hasn't changed, then perhaps the firewall rules have changed? I have also seen Windows Updates change DTC configuration (for security) which caused a problem.

Update Based on Comment:

For monitoring transaction promotion or escalation, if you are not using any distributed transactions I think you could use some of the Distributed Transaction Coordinator performance counters to track committed transactions. If testing you could disable MSDTC and see if your code fails. Another way would be to monitor transactions in SQL Server. From a coding perspective you could try to handle the DistributedTransactionStarted event and do some logging (but remove that code before going to production).

For a code example using a single connection go to the TransactionScope page at MSDN. Basically, create a TransactionScope, create a SqlConnection, do some work with the SqlConnection, close the connection, call scope.Complete().

Note that if you are using Data Adapter methods, they automatically manage your connection so the connection is closed or returned to the connection pool. Either way, if another operation is called then the transaction will be promoted to a DTC transaction. See System.Transactions and connection pooling for more details.