Administration

DTC – Why Doesn’t it work?

This is the story of my saga to get DTC working in a clustered environment.  It’s a bit different from other postings that I read and tried so I thought it was worth posting…

We have a “test” SQL Server instance and it was noted that DTC (Distributed Transaction Coordinator) was throwing errors.  The users get an error

MSDTC on server ‘myservername’ is unavailable“

each time they try to perform a distributed transaction.  Our environment is a 2-node cluster (Windows 2012), SQL Server 2014.  This cluster was setup by someone else, and DTC has apparently never worked.  So I began to dig.  First I determined that DTC was installed but not completely configured.  So I followed the instructions here to configure it:

https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/15/msdtc-recommendations-on-sql-failover-cluster/

I added the MSDTC resource to the cluster.  I used PowerShell commands.

Then I configured DTC using suggestions from the website above and also by examining our other test environments where DTC is working.  I did this in Component Services.  After doing this I examined the cluster resources and DTC services and all looked good to me.

I tried a distributed transaction but still got the same error.  So I grabbed the DTCPing tool from the internet and I used it.  To my surprise I got a successful test!

Then I grabbed the DTCTester tool from the internet and tried that.  This tool got an error, but I couldn’t find anything of value on the internet that told me what was wrong or how to fix it.

So I read some more…

From the website above one of the FAQs is “How do I view existing MSDTC mappings across the entire cluster?”.  This question tells you to issue the command:

Msdtc -tmMappingView *

(Use command prompt on your cluster node to view the resource mappings on your cluster.)  When I ran that, instead of getting any results I received an “unknown error; please see the trace file for more details”. I tried other mapping commands (set and clear) and received the same error. So…there is obviously something wrong with the resource mappings, but what is wrong and how do I fix it? None of the mapping commands are working for me; I just get the “unknown error”.  I tried to view the trace file using the MS utilities, but the trace file was blank.

I read some more…

Then, while reading this posting:

https://www.toadworld.com/platforms/sql-server/b/weblog/archive/2015/06/19/xa-transaction-msdtc-and-how-to-configure-multiple-msdtc-instance-on-sql-alwayson-cluster-environment

another possible answer hit me.  This posting tells me where (in the registry) that the resource mappings reside (HKEY_LOCAL_MACHINE\Cluster\MSDTC\).  So I used regedit to look at my registry on my cluster node and I see that there is a resource mapping listed.  With nothing else to lose I delete the entire entry from the registry.  Then I used the command msdtc -tmMappingSet to setup the resource mapping between the SQL Server service (in my case it is the default instance MSSQLSERVER) to the cluster resource for DTC.  I checked the result 2 ways, viewing the new entry in the registry using regedit and also using the -tmMappingView command above and both confirmed that I know have a valid resource mapping.

I tried a distributed transaction, and it finally worked! Problem Solved !

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s