THE Microsoft Distributed Transaction Coordinator (MSDTC) is a pretty nifty tool in every Windows system. What this tool allows is that it enables applications to make use of Transactions (and all its glory) when communicating across distributed systems. MSDTC provides the same atomic properties of single a Transaction – but the overhead of maintaining Transactional atomicity across these distributed system is managed internally by the MSDTC.

But…There is always a ‘but’…

What if you are building a system that operates on one Server? Alot of small to medium sized applications would fall into this category. As an example: Consider one transactional system that spans across many Databases that lie on the same Database Server. Would you still need, or perhaps want, the MSDTC to handle your transactions?

In this article I will explain how certain situations may misuse MSDTC escalations, and how it is possible for you to avoid it…

Discussion

Don’t get me wrong, MSDTC is an awesome feature. It’s very simple to use, as a matter of fact once it is enabled on a Server any Transaction in your application code will automatically be escalated to the MSDTC for management. You don’t need to trouble yourself with anything else.

It all started when I was developing an application on a new SOE machine that had MSDTC disabled by default in the OS. I built the database and started coding my mid-tier code. To give you an idea of the architecture used for the mid-tier code, I had ‘manager’ classes that communicate with the ‘data repositories’ which in turn handles all queries to the SQL Server instance. These ‘manager’ classes is where the Transaction beings and ends – wrapping all calls to the ‘data repositories’ within a Transaction Scope.

Simplistic data retrievals that consumed one call to a repository proved easy enough – no issues were raised. But when the complex Business Entities required calls to multiple repositories that’s when the following message popped up:

MSDTC on server ‘servername’ is unavailable

This was how I found out MSDTC was disabled on my local development machine. And so I began questioning MSDTC and whether or not I actually needed it…

Brain storming

So the situation was like this:  There is one Database Server, with quite possibly multiple database, and an application that solely uses this single Database Server.  Do you allow MSDTC to manage your transactions ??

My answer is – You can, but there isn’t the need to. Using MSDTC in a non-distributed environment adds an overhead (as minute as it may be); and if you are picky with split second delays you would kind of agree with me here (hopefully…)

If you are in the same situation, this is one way how you can avoid unnecessary MSDTC escalations.

Implementing Database Connection Scopes

MSDTC is avoidable if you use the same connection for multiple database call. Period. This is the fundamental key to prevent queries from being escalated when MSDTC is enabled.

In the article How to Implement a SQL Connection Scope: I explain in detail how you can achieve this.

I hope this article given you more insight into MSDTC! 🙂