Windows 2008 Clustering, SQL and MSDTC

I have often receive queries around the configuration of MSDTC on a Windows 2008 cluster, especially when there are multiple SQL instances involved; and yesterday was a case in point.

Before the introduction of Windows Server 2008 it was only possible to have a single instance of the Distributed Transaction Coordinator on a cluster irrespective of how many nodes of virtual servers this cluster supported. This resulted in an availability characteristic that when hosting an N+1 SQL cluster there could be impact on all instances involved in a distributed transaction even if only a single node (the node host the DTC service at the time) failed.

So with the introduction of SQL Server 2008 I am very pleased to see that multiple instances of DTC are now supported, which leads to the question of how best to configure a cluster with multiple DTC instances.

Well it turns out to be quite easy. All that needs to be done is to add a Microsoft Distributed Transaction resource to each cluster group (virtual server) containing a SQL instance. The SQL service will then enlist the support of its “local” DTC service when participating in distributed transactions automatically. I have tested this with both SQL Server 2005 and SQL Server 2008. I have a couple of personal preferences in that I allocate a small LUN to the MSDTC service and add it to satisfy the disk dependency  rather than sharing the SQL resource LUNs but this is definitely a preference and not a requirement.

For Windows 2003 clusters I continue to create a dedicate cluster group with dedicate disk, IP and network name resources for the DTC service.

Towards the end of last year I spent some time working through the various configuration and failure scenarios and may in future post document more of the detail, but for now this should get you going with your multi-instance Windows Server 2008 cluster deployments.

<Gary>