First published on MSDN on Jan 15, 2014
Let's assume you have a 2 (or More Node) Windows 2003 Failover Cluster installation.
You have created a MSDTC resource, Network Name, IP Address resource and a Disk resource set as dependent resources all contained in its own group, which is owned by Node 1.
Now in addition you have 1 or more SQL Server installations each in their own group. In this case one of the SQL Failover Cluster Groups is running on Node 2.
To use the functionality of the MSDTC Service for an application, in this case SQL that is running on Node 2, the SQL Server must communicate with a MSDTC proxy
agent, which reroutes the request to Node 1 where the MSDTC service is running so the MSDTC service on that node can facilitate your request.
To appreciate the impact, say there are four nodes with three SQL Server instances. MSDTC is running on Node 1 and the SQL Server instances are divided on all the nodes (Nodes 2, 3 and 4), they must all talk to Node 1 for MSDTC functionality.
This can create a bottleneck.
For this reason (and several other reasons) MSDTC was completely re-written in Windows 2008 and above so that by default the MSDTC service is allowed to run locally on each node.
In addition it allows for multiple MSDTC resources in the cluster instead of just the one.
Again I want to put emphasis on the fact that for Windows Server 2008 and later it is NOT required to cluster MSDTC to utilize the features of the MSDTC service.
For the purpose of this FAQ I have a Cluster with 2 MSDTC (Microsoft Distributed Transaction Coordinator)
1. On the Start menu, click Run, type dcomcnfg and then press ENTER to launch the Component Services Management Console.
2. Expand Computers, and then right-click My Computer.
3. Click Properties, click the MSDTC tab, and then select the default coordinator for your cluster.
1. Expand Component Services, expand Computers, expand My Computer, expand Distributed Transaction Coordinator, and then expand <Your instance of MSDTC>.
(Because I have each of the MSDTC Resources Depend on SQL Network Name you will see the SQL NetBIOS name for the MSDTC Name in component services. Example SQLAG1, SQLAG2)
2. Right-click the instance that you want to configure, and then click properties.
3. Under Security Settings, select the Network DTC Access, Allow Inbound, and Allow Outbound check boxes, and then click OK to complete the configuration.
Note: If you create an incorrect mapping, the MSDTC command still succeeds, but your mapping will not work correctly.
Note: When you add a MSDTC resource to a SQL Server Group you can use one of the SQL Server Disks and SQL IP and Network Name for the MSDTC resource dependencies.
However, when configuring MSDTC for an instance of SQL Server 2008 or later, the specific needs of your environment—in terms of performance, availability, or manageability—will determine the best practice for your MSDTC configuration.
MSDTC in Each SQL Server Resource Group
Caution: When you use this configuration, you must determine the correct setting for the "If restart is unsuccessful" action. By default a failure will failover all resources in this service or application setting of the MSDTC resource. If the function of MSDTC is critical to your environment, you set MSDTC to restart is unsuccessful and fail over all resources in this service or application or put MSDTC in its own resource group and create a mapping that directs SQL Server to that instance of MSDTC.
In most cases, you will not want to set If restart is unsuccessful action, to not fail over all resources in this service or application (Or Affect the Group, depending on the version of your Windows Failover Cluster.
The setting enables MSDTC to be restarted on the same node if it fails, but if it cannot be restarted, it will not cause a failover of the entire SQL Server resource group.
Note: As mentioned previously, if this local resource is offline or failed, distributed transactions will fail for this instance of SQL Server, and you will need to delete or move the MSDTC resource to make use of another instance of MSDTC on the cluster.
Dedicated Group for Each MSDTC Instance
Since MSDTC is running in a separate group there is no way to Guarantee that the SQL group using this instance MSDTC are going to be hosted on the same node.
The performance of distributed transactions might be better when SQL Server and the MSDTC resource to which it is mapped are running on the same physical node. Be sure to consider this factor when testing application performance, and test with MSDTC on remote nodes.
Single Default Cluster MSDTC Instance
Note: The Cluster default MSDTC instance will provide MSDTC services for all applications that are not specifically mapped to another instance.
Local MSDTC Instance
Note: During application/MSDTC initialization, the local MSDTC instance is used to determine the identity of the correct instance of MSDTC for this application (local, cluster default, or a specifically mapped instance). It is also used for non-cluster-aware applications on this node.
Written by: Shon Hauck
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.