Clustered DTC versus Local DTC

In the last 6 months I have faced two cases of linked servers between a SQL Server 2008 R2 Instance running on Windows Server 2008 R2 and a SQL Server 2000 instance running on Windows 2003 were distributed transactions were not working correctly.

The root cause of the problem is still under investigation, however, trying to understand problem I verified that everything is correctly configured and did some tests.

I review the following points:

-          Name resolution was working correctly

-          Security setting of the DTC correctly configured

-          The linked server without distributed transaction working correctly

-          DTCPing worked

-          As far as I remember the DTCTester also worked.

After all this points the distributed transactions were still failing. In my tests, I tried creating a Clustered DTC in the SQL Server Group and the distributed transactions continue failing.

Later, without much hope, I tried deleting all the clustered DTC’s and configuring the local DTC, to my surprise, it worked.

At this point the question changed to What disadvantages do I have if I use the local DTC instead of a clustered DTC?, so I had to research and test this point.

The first point is, What is the reason to use a clustered DTC?, before Windows 2008, the reason was that it was the only way we could use the DTC on clustered environment, however, in Windows 2008 the number of options increased and now we can create more than one clustered DTC and also use the local DTC on each node. What are the advantages of a clustered DTC and what are the limitations of using the local?

The short answer (based in my knowledge and tests) is that in some scenarios it is better to use the clustered DTC but in most cases the difference is so small that using the local DTC instead of the clustered DTC doesn’t add a significant risk.

When is preferable to use the clustered DTC instead of the local DTC? , One scenario is when you have multiple instances of SQL Server in each node. In this case, multiple instances of SQL Server will use a single DTC which, in extreme cases, could cause bottleneck, if you want more scalability then you need multiple clustered DTC’s.

Another scenario is when the local DTC fails, in that case, the SQL instances won’t failover automatically. If you need more high availability for the distributed transactions it is better to use the clustered DTC.

In summary, it is recommended to use clustered DTC’s, however , if you are having problems with the clustered DTC, you can try with the local DTC.

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”