By: Allan Hirt on March 12, 2013 in Distributed Transaction Coordinator, DTC, Featured, PowerShell, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, Windows Server 2008 R2, Windows Server 2012
Configuring the Microsoft Distributed Transaction Coordinator (DTC) for clustered SQL Server instances (FCIs) with Windows Server 2008 and later has been a confusing topic for many. The “definitive” word on the subject for awhile has been both of Cindy Gross’ blog posts ( “Do I need DTC for my SQL Server?” and “How to configure DTC for SQL Server in a Windows 2008 cluster”). In my opinion, with W2K8+, you really should create one DTC for each FCI and put it in with the SQL Server instance. This way it always lives on the node where the instance is currently running.
Having said that, what Cindy wrote doesn’t always work. In fact, the only truly reliable way some of us have found was talked about by my friend and fellow Cluster MVP (also a big SQL guy), Mike Steineke (blog | Twitter) in a post last week entitled “Clustered DTC and Multiple SQL Instances” where he shows how he had to configure DTC. Part of this is ensuring that even though DTC is in the group with SQL, it still gets its own name and IP address; using the SQL Server name can be problematic. To get this done, you really need to script it. I’ve done the work for you. Easy!
This updatated post stems from the blog post “Creating a Clustered DTC for SQL Server Redux“.
A sign that you need to use DTC for a given FCI can be found right in the SQL Server error log. If SQL Server cannot connect to a clustered DTC, you will see an error message like the following:
2013-03-12 10:23:51.670 spid1001 QueryInterface failed for “DTC_GET_TRANSACTION_MANAGER_EX::ITransactionDispenser”:0x8007138f(The cluster resource could not be found.).
2013-03-12 10:23:51.680 spid1001 QueryInterface failed for “ITransactionDispenser”: 0x8007138f(The cluster resource could not be found.).
This blog post now applies to Windows Server 2008, 2008 R2, and 2012 as well as SQL Server 2005, 2008, 2008 R2, and 2012 since depending on the OS you’re running, you may have a little of each …
Step One – Create the Clustered DTC in the Resource Group with the FCI (revised 3/12/2013)
The script below I actually created today and is based on my original blog post I wrote back in 2009 for Windows Server 2008 and takes into account what’s in Mike’s post. I actually used this script on a customer installation, so this isn’t something I wrote just for the hell of it. All you have to do is modify the variables up front and run the script. If you don’t know the name of some of the resources, you can use the PowerShell cmdlet Get-ClusterResource to show a list of everything.
For this update, instead of embedding the code, you can download the script from here. Much easier!
A sample execution is shown in Figure 1.
I also tested this script in Windows Server 2012 and it works just fine.
Step Two – Enable Network Access for the Newly Created DTC (revised 3/12/2013)
Once DTC is created, you must enable network access. This is where if you did things another way (i.e. not the way shown in this post), there’s a very high probability that when you went to go restart DTC (Step 11 below),the process may decide to step out for lunch and then stay away for a permanent vacation.
Step Three – Testing DTC (added 3/12/2013)
Microsoft has a tool that will create a dummy transaction and can be found in KB 293799. While the best test is always your application, this will at least see if DTC is working. It requires an ODBC DSN to work.
WARNING Do not create a System DSN. If you do, you may see an error similar to the one shown in Figure 12. This error stems from the fact that dtctester is a 32-bit program and the System DSN created is 64-bit.
Step Four – Optional Configuration Steps (added 3/12/2013)
The steps in this section are optional. Read to see if they apply to you.
xSetting the DTC Resources to Not Cause a SQL Server Failover
There are four resources associated with DTC: name, IP, disk, and of course, DTC. If you right click on any one of those and select the Policies tab, you will see what is in Figure 13.
Notice the option “If restart is unsuccessful, fail over all resources in this service or application.” What this means is that if any one of those resource fails and cannot be restarted, it will cause SQL Server to fail and move to another node. If you are not sure if DTC is necessary but are creating it anyway OR you are OK with DTC failing but SQL still being up and living with whatever not working that DTC affects, deselect this option.
Adding DTC as a Dependency of SQL Server
In the testing my friend Mike did, the only way he could get things to work was to ensure that DTC was up before SQL Server. The only surefire way to do this is to add DTC as a dependency to SQL Server (see the previous section for possible implications of doing this). One of the problems that may occur and was noted in the blog post “” is that there is a chance that SQL Server Agent may go offline. We think this is possibly related to another bug that was fixed, but what is happening is that SQL Server Agent is trying to figure out the name of the instance it is using. If it gets confused, it will go offline. It may start, but it also may not work correctly. The only way it seems to work around this is to make sure that DTC is up before SQL Server starts, hence adding it as a dependency.
Once you get DTC up and running in the group with SQL Server, test your application against it and check in the SQL Server error log as noted above. If you see no DTC errors and everything looks fine in the app, you’re golden. You shouldn’t have to restart SQL Server to take advantage of the newly mapped DTC, but without an application right now, I can’t say this to be 100% true.
Adding a dependency to a resource in Windows Server 2008 and later does not require any downtime.
To add DTC as a dependency of the SQL Server resource in Failover Cluster Manager:
To do this in PowerShell, execute the following:
Add-ClusterResourceDependency SQLResourceName DTCResourceName -Cluster WSFCName
A sample execution is shown in Figure 15 along with verifying that it was done correctly.
Mapping DTC to a Specific FCI
Right now I do not have an application to test specifically the combo of DTC and the FCI in the same group that all is well without adding the DTC resource as a dependency. Based on what we know, it may not work. One thing which may solve that issue and/or if you want to truly be anal about things, DTC provides you with a way to specifically map an instance of DTC to only be used with a specific application or service such as a given SQL Server instance. This is done via command line. If you really want to ensure that DTC is mapped to the FCI, you use the tmMappingSet option of the command line for DTC.
NOTE This particular aspect/option most likely will NOT be necessary but I’m putting it in here for the sake of being complete.