Condividi tramite

MSDTC Recommendations on SQL Failover Cluster

  • Articolo

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.

 

Index

FAQ's

 

Purpose of this MSDTC FAQ.

  • 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.

Back to Index

 

What is MSDTC?

  • 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.

Back to Index

 

Do we need MSDTC for SQL Installation?

  • 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.

Back to Index

 

Is MSDTC Supported when using AlwaysON Availability Groups?

Back to Index

 

Do I Need to Cluster MSDTC after SQL installation?

  • 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.

Back to Index

 
Why did I need to Cluster MSDTC in Windows 2003?

  • 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.

Back to Index

 

What is the downside in clustering MSDTC in Windows 2003?

  • 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.

For example:

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.

Current Configuration

 

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.

Back to Index

  

What is the Simple recommendation for MSDTC with Windows 2008 and later?

  • 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.

Back to Index

  

What will SQL do if I do not Cluster MSDTC in Windows 2008 and above?

  • If there is no MSDTC resource in the cluster then SQL Server will use the MSDTC service that is running locally on the node.

Back to Index

  

What if I only create one MSDTC Resource in my cluster?

  • 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.

Back to Index

                                                                                                                                         
How do I change the Cluster Default MSDTC that is used by Applications?

  • 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
    Console (dcomcnfg).

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.

Back to Index

 

Do I need to Tell SQL Server which MSDTC Instance to use (Clustered or Local)?

  • 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.

 

Back to Index 

 

What happens if the MSDTC Resource that was picked at SQL Service startup fails?

  • 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.

Back to Index

 

Where can I find more information on creating an MSDTC Resource in Windows Server 2008 Failover Clustering?

Back to Index

 

How do I enable Network Access for MSDTC?

  • 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.

Back to Index

  

How do I map a specific instance of SQL Server to a specific 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>

 

 

  • <MappingName>
    • Is an arbitrary name that you choose to identify this mapping.

 

  • <SQLServerServiceName>
    • 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.

 

  • <MSDTCResourceName>
    • 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.

Back to Index

 

How do I View existing MSDTC mappings across the entire cluster?

  • The following command is used to view all MSDTC mappings across the entire cluster.
    • msdtc -tmMappingView *

 

Back to Index

 

How do I delete an existing MSDTC mapping or incorrect mapping?

  • The following command is used to delete and existing MSDTC mapping.
    • msdtc -tmMappingClear -name <MappingName>

 

  • <MappingName>
    • Is an arbitrary name that you choose to identify this mapping.

Back to Index


How can I test MSDTC functionality?

  • 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.

 Back to Index

 

What is the Best Practices for MSDTC Configuration with SQL Server 2008/2008R2/2012?

  • As we mentioned briefly above you should either create a MSDTC resource for each SQL Instance or do not cluster MSDTC at all.

 Back to Index

 

Why would I create a MSDTC instance for each SQL Server Instance?

  • 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.

 Back to Index

  

What are the different possible MSDTC Configurations and the Benefits of each?

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.

 Back to Index

 
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.

Note:
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.

 Back to Index

  

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.

Back to Index

  

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.

 Back to Index

 

 

 Written by: Shon Hauck

Comments

  • Anonymous
    September 20, 2014
    Thanks, Many practical informations here. I was always looking for such an article!Could you ensure me with following statements/questions;If MSDTC fails in "MSDTC in Each SQL Server Resource Group" scenario, it can cause the whole group to failover, so SQL and MSDTC will be started on another node. It is also possible to configure it so, that MSDTC after failed restart will stay on the same node (without failover), so SQL will not be able to create distributed transactions. In case both SQL and MSDTC have failed over to another node, no manual reconfiguration is requred for SQL to start using MSDTC on that node.If MSDTC fails in "Dedicated Group for Each MSDTC Instance" scenario, only the MSDTC group  will fail over to the second node. SQL will automatically (withut manual work) start talking to the MSDTC on another node (where it failed over). This can guide to performance problems. Mappings are required here, because we need to decide which MSDTC shall be used by which SQL instance. In "Single Default Cluster MSDTC Instance" no need for mappings exists, because we have ONLY ONE MSDTC group. If that group fails over, SQL server will start to talk to the new node. No manual reconfiguration is requeried. In "Local MSDTC Instance" scenario in case of MSDTC failure, SQL server has trouble, because it cannot start/commit distributed transactions. Manual restart of MSDTC on local node will be needed or manual failover  of SQL server to next node will help it. Your acknowledgement or showing me errors in my points will help me to make sure, I have understood the topic. Thanks in advance!Andy

  • Anonymous
    September 23, 2014
    Hi,We have 2 node cluster setup in our environment by windows admin with msdtc configured using ip address. I as a sql server dba asked my admin to provide one ipaddress for active/passive sql server cluster instance but he in turn replied and asked me to use the msdtc ipaddress. Is it possible to configure active/passive sql server cluster instance with the same ipaddress of msdtc?Windows 2012 Enterprise and SQL 2012 standard edition's were involved in this scenarioThanks,Sandy

  • Anonymous
    October 14, 2014
    We have a two node SQL 2014 Std WFSC configuration running on W2012R2. The SQL WSFC Role includes an MSMQ resource. When setting up this configuration I was unable to get DTC working with SQL talking to MSMQ without clustering DTC and ended up clustering DTC in a separate role. It now works.However when SQL and DTC run on different nodes distributed transactions fails if Windows Firewall is enabled. I have configured the Windows Firewall to allow all DTC rules, both incoming and outgoing on both nodes but no go. If I turn of Windows Firewall it works.Any ideas?

  • Anonymous
    September 09, 2015
    SQL2016 seems will be supporting MSDTC with AlwaysOn but it will need to be run on Windows Server 2016 Technical Preview 2,seems a long way to go. msdn.microsoft.com/.../ms366279.aspx

  • Anonymous
    November 15, 2015
    Hi All, please help me for my sql server environment problem. for my 2 node cluster (windows 2012 r2) ,SQL server is having owner node as Node0 and DTC having Node1. master data , user Data and log disks are in same SAN. 1.Is sql server instance and DTC should be with same owner node?  

  1. If they are with same owner node,while I am doing failover do I need to move both to other node or just moving     SQL Server instance is fine?
  • Anonymous
    March 16, 2016
    I am not sure if all above statements are correct by Shon Hauck.
    • Anonymous
      May 09, 2016
      @JayantDass - Which statements do you feel are incorrect?
      • Anonymous
        June 14, 2016
        A MSDTC cluster resource use a cluster disk so if you don't configure one and it failover to the other node, all those distributed transaction are stored locally will be loss wouldn't it? which means possible data corruption?
    • Anonymous
      August 14, 2016
      The information is correct for SQL 2014 FCI and earlier on Windows 2012 R2 and below. There are changes in 2016 Windows and SQL that will change several of the recommendations due to some significant architectural changes / improvements specifically for AlwaysOn. Currently I am working with the SQL and MSDTC development teams to write the new recommendations. The update to the blog will be in the near future.
      • Anonymous
        February 23, 2017
        Do we have a up-to-date MSDTC recommendation for 2016 Windows and SQL yet?
        • Anonymous
          March 29, 2017
          I'm also looking for recommendations for a Windows Server 2016 cluster running SQL 2014 instances. Do you know when they will be available?Thanks,-Luke.
  • Anonymous
    February 17, 2017
    The comment has been removed
  • Anonymous
    July 21, 2017
    si muy buena la referencia pero falta mas contenido tecnico para la parte it para hacer mas clara la actividad closter en windows server 2008