The purpose of the following FAQ (Frequently Asked Questions) is to address common questions with MSDTC (Microsoft Distributed Transaction Coordinator) when used with SQL Server Failover Clustered instances to include current recommendations and best practices.
MSDTC is a separate Windows service which coordinates distributed transactions across SQL Server instances.
When deploying SQL Server in a highly available environment like Windows Failover Clustering, there are certain best practices that can make the MSDTC service’s behavior more predictable.
- MSDTC is an acronym for Microsoft Distributed Transaction Coordinator. The Microsoft Distributed Transaction Coordinator service (MSDTC) tracks all parts of the transactions process, even over multiple resource managers on multiple computers. This helps ensure that the transaction is committed, if every part of the transaction succeeds, or is rolled back, if any part of the transaction process fails.
- SQL 2005 does require MSDTC for setup, since it uses a transactions to control setup on multiple nodes. However, SQL Server 2008/2008R2/2012 and SQL 2014 setup does NOT require MSDTC to install SQL.
- MSDTC is only needed for transactions etc. after installation.
- When the topic of cross-database and/or DTC transaction support, under an Availability Group, comes up the quick response is NOT SUPPORTED!
- More information at Not-Supported: AGs With DTC/Cross-Database Transactions
- With Windows 2008 Failover cluster and later you do not need to cluster MSDTC to utilize the functionality of the MSDTC service. This is because MSDTC was re-designed in Windows 2008 and unlike Windows 2003 if Windows Failover Cluster was installed you had to cluster MSDTC. This is no longer the case when using Windows 2008, since by default MSDTC service is running locally, even with Failover Clustering installed.
- Currently there is some documentation that states you need to have a MSDTC Resource in your cluster.
I want to start out by emphasizing that the statement "You must install MSDTC is not 100 percent accurate." The statement does not take into account the OS and the changes with MSDTC in Windows 2008 and later.
- You only need to create the MSDTC resource in the Failover Cluster if you are installing on Windows 2003 Failover Cluster.
- The reason you needed to create a MSDTC resource was because of the design of MSDTC in Windows 2003.
- In 2003 when the MSDTC service starts as local service it checks to see if cluster is installed. If Cluster is installed the MSDTC Service would not allow you to run it outside of the Cluster Server Service. So it would shut down and to get the service to start or you need to ensure that the MSDTC was started by the cluster service via the Distributed Coordinator Clustered Resource.
- This is why you needed to create an MSDTC resource in a Windows 2003 Cluster to use the MSDTC functionality.
- The down side to this functionality is that in Windows 2003 Cluster you can only have one MSDTC resource for your entire cluster which can cause a performance bottle neck.
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.
- In Windows 2008 and later you either Create a clustered instance of the MSDTC resource for EVERY SQL Server instance /Group that requires its functionality or DO NOT CLUSTER MSDTC at all.
- If there is no MSDTC resource in the cluster then SQL Server will use the MSDTC service that is running locally on the node.
- If you create only one MSDTC resource then it becomes the cluster default and revert to the Windows 2003 functionality listed above, which is not recommended.
- On Windows Server 2008 or later Failover Cluster setup, you can install multiple instances of MSDTC on a single failover cluster.
The first instance of MSDTC that is installed will be the cluster default instance of MSDTC. This can be changed via the Component Services Management
For the purpose of this FAQ I have a Cluster with 2 MSDTC (Microsoft Distributed Transaction Coordinator)
- First MSDTC Resource is in a Group MSDTC-ONE
- Second MSDTC Resource is in a Group MSDTC-TWO
- To modify this, perform the following actions:
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.
- SQL Server 2008 and later will take advantage of an instance of MSDTC installed to the SQL Server’s local cluster resource group by automatically using the instance of MSDTC in the SQL Server's Cluster Group. This is a feature of SQL 2005 and above and does not hold true for other applications.
- When using SQL 2005 or other applications they can be mapped to the MSDTC Resource in the same group.
- Note: You can Map any application including SQL 2008 and later to any specific instance of MSDTC that is any group on the cluster.
- For more information about this, see “How to Mapping an Instance of SQL Server 2008 to an Instance of MSDTC” below.
- The list below shows the order in which SQL Server will choose which instance of MSDTC will be used.
- Note: The First Option 'Use MSDTC instance installed to the local group ONLY applies to SQL 2008 and later only. For all other Versions of SQL or Other Applications will start with 'Use the mapped instance of MSDTC'
- The order of precedence is as follows:
- Note: This is figured out as part of the SQL Service Startup, Changes made to this configuration after SQL is started will require you to re-start SQL.
- If the MSDTC instance that is being used by a specific SQL Server Group fails, SQL Server does not automatically attempt to use the next available MSDTC Resource such as the default cluster instance or the local machine
instance of MSDTC.
- You would need to completely remove the failed instance of MSDTC from the SQL Server group to use another instance of MSDTC. Likewise, if you create a mapping for SQL Server and the mapped instance of MSDTC fails, your distributed transactions will also fail. If you want SQL Server to use a different instance of MSDTC, you must either add an instance of MSDTC to the local cluster group of the SQL Server or delete the mapping.
Where can I find more information on creating an MSDTC Resource in Windows Server 2008 Failover Clustering?
- For more information about creating an MSDTC resource for Windows Server 2008 failover clustering, see Checklist:
- After creating the MSDTC resource(s) for your cluster, you must enable network access for the MSDTC resource.
- This is done from the Component Services Management Console. In the Component Services Management Console, perform the following actions:
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.
- These configuration options enable MSDTC to access resources on the network and allow applications to access this instance of MSDTC from remote machines including
other cluster nodes.
- It is not necessary to perform these actions on each node of the cluster because the changes will propagate to all nodes of the cluster for a clustered instance of MSDTC.
- To map a SQL Server instance to an instance of MSDTC, open an administrative command prompt and use the msdtc.exe command.
- The following command is used to create a mapping between an instance of SQL Server and an instance of MSDTC.
- msdtc -tmMappingSet -name <MappingName> -service <SQLServerServiceName> -ClusterResourceName <MSDTCResourceName>
- Is an arbitrary name that you choose to identify this mapping.
- Is the service name for your SQL Server instance. If this is the default instance, use MSSQLServer as your service name. If this is a named instance, use MSSQL$<InstanceName> as your service name.
- Is the resource name for the instance of MSDTC to which you want to map SQL Server.
Note: If you create an incorrect mapping, the MSDTC command still succeeds, but your mapping will not work correctly.
- The following command is used to view all MSDTC mappings across the entire cluster.
- msdtc -tmMappingView *
- The following command is used to delete and existing MSDTC mapping.
- msdtc -tmMappingClear -name <MappingName>
- Is an arbitrary name that you choose to identify this mapping.
- You can test access by executing BEGIN DISTRIBUTED TRAN from a SQL Server Management Studio query window.
- If it fails to enlist the transaction, you can retrieve the error from the SQL Server error log.
- For example:
- An error of XACT_E_CONNECTION_DOWN at this point generally indicates that your MSDTC instance is not running, your mapping is incorrect, or you are being blocked by a firewall.
- As we mentioned briefly above you should either create a MSDTC resource for each SQL Instance or do not cluster MSDTC at all.
- Dedicated instances of MSDTC for each SQL Server or application can help to prevent failures of one instance of MSDTC from affecting distributed transactions for other applications.
- This configuration can add complexity to your Windows Failover configuration, in addition will have much higher resource requirements for your cluster.
- This is because each MSDTC Clustered instance requires a Physical Disk, IP and Network Name Resource.
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
- This option provides the best performance. It guarantees that your instance of MSDTC will always run on the same physical node as the SQL Server, which reduces communications overhead.
- It is suitable for the widest array of requirements.
- It does not require mappings. SQL Server 2008 automatically uses the local cluster group MSDTC resource by default.
- This configuration conserves drive letters for clusters that have a limited number of drive letters available.
- You can make use of mount points to keep disk I/O separate from other SQL Server files without using up a drive letter on the cluster.
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
- This configuration is recommended only if the primary concern is maximum availability of SQL Server and MSDTC and if the performance of remote MSDTC has been verified to be acceptable for the application and load.
- MSDTC requires a dedicated physical disk resource in this configuration. If the cluster hosts many instances of SQL Server, you may not have enough disk resources to give every instance of SQL Server a dedicated MSDTC in a separate resource group.
- Manageability can be a challenge if you have many SQL Server-to-MSDTC mappings.
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
- Windows Server 2003 failover clusters can only have a single clustered instance of MSDTC. It is recommended that you install this instance to a dedicated resource group. This configuration prevents failures of MSDTC from affecting other applications.
- On Windows Server 2008 failover clusters, the first MSDTC Resource becomes the default MSDTC. It is recommended for this configuration that you have the default MSDTC instance installed to a dedicated resource group.
Note: The Cluster default MSDTC instance will provide MSDTC services for all applications that are not specifically mapped to another instance.
- SQL Server instances that rarely use MSDTC can use the default MSDTC instance on an ad-hoc basis.
Local MSDTC Instance
- This option provides no high availability for MSDTC in the event of a failure, because the MSDTC log is stored on a local disk instead of a cluster disk.
- Even though no mappings exist for the local MSDTC instance, it is still required to have the local MSDTC instance running.
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.
- This configuration is recommended if you chose not to create an MSDTC instance for every SQL Server.
Written by: Shon Hauck