Last year, I wrote a blog post entitled “How to Properly Configure DTC for Clustered Instances of SQL Server with Windows Server 2008 R2”. Today, I was helping a customer and found a few things. I’ve since updated that original post to reflect these findings and to fix a few things. I also took Windows Server 2008 R2 out of the title and the post’s name is now “How to Properly Configure DTC for Clustered Instances of SQL Server (Revised)” since it applies to Windows Server 2012 as well. The URL is the same as the old one, and that does reference W2K8 R2.

As was originally written, that article was assuming a new, greenfield installation. Not everyone is looking to create DTC with a brand new installation, and I realized that there were some gotchas I needed to outline.when you are already up in production. I also fixed a few things in the script.

1. In the script as it was written, one step added the resource  as a dependency to SQL Server. That’s all well and good (more on that in a bit), but this had two issues with a non-greenfield implementation:

  • When you make sure that DTC has the right permissions (“Step Two – Enable Network Access for the Newly Created DTC”), it needs to take it offline. When you add DTC as a dependency to SQL Server, it will take it offline as well. This is fine for a new installation, but not for one that is already up and running. I revised the script to NOT do this and make it a separate thing later.
  • It doesn’t happen all the time, but we saw it on their installation and I also saw it once on my test WSFC (but not again – so it seems to be phantom). SQL Server Agent went offline. See Figure 1. That’s definitely not good.This is apparently a timing issue – DTC needs to be up before SQL Server. I explain all of this in a bit more detail in the revised post linked above.
FailedAgentDTC

Figure 1. SQL Server Agent down

  • If DTC goes down, it will cause a SQL Server failover by default. You may or may not want this behavior. I’ve revised the original blog post with more information on this.

2. I forgot to put one important line in the script – namely moving the disk resource into the group with SQL Server! The original script did have a gotcha line in there, but I made sure it was done in this version.

My customer was trying to fix a critical path issue that needed DTC up and running for their application, so they wound up putting DTC in its own resource group which is a valid option still in Windows Server 2008, 2008 R2, and 2012. They only have one instance installed in the WIndows Server failover cluster, so they can ensure DTC lives where the SQL Server instance is all the time to reduce network traffic. This saved them from another possible outage to ensure DTC was up before SQL.

Hope this helps some of you out there …