DTC configuration for Windows 2008 SQL 2005 Clusters
Windows 2008 Clusters have a lot of new features that make clustering better. You don't have to wait for SQL 2008 to take advantage of some of them. One of the changes is in the DTC (Distributed Transaction Coordinator) options.
With Windows 2000 and 2003 clusters, you got only one DTC instance per cluster. For a long time, we just added the DTC resource to the cluster group, anchored it to the Quorum disk and forgot about it. Starting with SQL 2005, some systems that used DTC heavily started to fail the Quorum disk and crash the cluster under heavy load. So Microsoft recommended creating a dedicated DTC resource group for a Clustered SQL Servers. This later got modified to only be necessary for "heavy" DTC users. "Heavy" has been very loosely defined and left to the judgment of the DBA creating the cluster.
All of this was due to a Windows limitation of only one DTC resource per cluster. You could not even have a local non-clustered DTC instance on a cluster node. Windows 2008 takes that recommendation away. Now you can have multiple DTC instances per cluster.
The Windows 2008 documentation shows various configurations:
The big question is "What is best for SQL Server?" Microsoft has always told us to keep the SQL Resource groups "pure" and not add extra resources, especially ones that tweak SQL dependencies. I posed this question to the Microsoft SQL Development Team recently and they pried themselves away from SQL 2008 preparations to help me out. While they have no formal recommendations at this time, they did acknowledge the need to come up with something AND they gave me some advice. They suggested creating a DTC resource for each SQL Virtual Instance on a cluster. Put the DTC resource in the targeted SQL group and change the log file to be dependent on a group disk. Do NOT make SQL Server or SQL Agent dependent on the DTC resource. The reasoning is that the restrictions on SQL dependencies is so that the installer for patches and hotfixes can stop and start the SQL Engine as necessary during the patching process. Since we are not affecting SQL Server starting, we are OK. This configuration also makes sure each SQL Virtual Instance has a local copy of DTC, thus saving cross-server communications that may cause bottlenecks on high-volume systems.