MS DTC Configuration for SQL 2005 in a Distance-Cluster on Windows Server 2003 Datacenter IA64
As does everyone from time-to-time, we had a lovely "learn as you go" moment in the middle of a deployment recently. As it turns out there are some very specific rules for setting up MS DTC in a clustered environment. As always, individual results may very.
First of all, most DTC changes don't require an operating system restart. Unfortunately, it would appear, in a clustered environment this is not true. We did, however, find that if you bring down services in the correct order, you can successfully make changes to DTC without a system restart.
-
Offline SQL Instances
-
Offline DTC
-
Offline Backup Agents(we use SQL Safe from Idera)
-
Make DTC changes
-
Restart Server (we discovered this the hard way after numerous attempts)
-
Online Backup Agents
-
Online DTC
-
Online SQL Instances
Now that we have that out of the way, we can get to the settings. For this example, lets assume we have 3 servers, 2 of them are distance-clustered, the 3rd is not a member of the cluster but will be executing distributed transactions to the cluster itself.
First of all, if you don't know where the DTC settings are they exist in the "Component Services" snap-in for MMC(run:mmc; add snap-in:"Component Services"). For me, I RDP into the console on the server itself, so the default "My Computer" node that appears works, otherwise connect to the appropriate server. Then, right-click 'Properties" on the server name and you have a window which gives you a tab labeled "MSDTC".
This tab looks like this:
So this screen tells us some things. First make sure that DTC is started on all the servers involved(unless you brought them offline purposely as described above). If not, nothing is going to work. You will probably want to make sure that service starts automatically. Your cluster group should probably handle this.
Now the fun stuff. Like I said, individual results may vary but these settings worked for us.
Open the Security Configuration dialog:
This dialog shows the configuration as works for our Server 1 & 2(the clustered servers), "Mutual Authentication Required" will NOT work in a clustered environment. Not sure if "Incoming Caller Authentication Required" works, we couldn't make it work properly.
On the non-clustered server, you must also NOT use "Mutual Authentication Required", however "Incoming Caller Authentication Required" authentication seems to be sufficient for the non-clustered servers.
What this means then is that all servers that will be opening or receiving distributed transactions to or from a clustered environment, must have their DTC settings changed. We found this out the hard way, as DTC transactions were working in one direction, and not the other. So it is possible that if your transactions are only being initiated in one direction you may not need to mess with certain servers. Test it, you'll be glad you took the time.
If you are reading this post, you've probably already seen the DTCPing.exe app that is on the Microsoft site. It is important to note that DTCPing happily returned no errors even when our authentication settings were jacked. So, while this utility may help diagnose certain DTC issues, i'm not sure that it helps in this scenario.
Some of this information is outlined very obscurely in this KB article http://support.microsoft.com/kb/899191/
Notes:
- I am not sure as to the security implications of these settings in an exposed environment. As our servers are safely sitting in an internal network, I'm not too concerned. You will have to research and evaluate that decision on your own.
- I did not have to change any settings on the second machine in the cluster, as cluster manager did it for me when I initiated a test fail-over. Once again, run your own test.