At my company, we have recently refreshed some of our physical hardware for our SQL failover clusters. During our building of these new servers (Windows 2012 R2 and SQL Server 2012), we decided to change the setup of the distributed transaction coordinator (DTC) from how it was on the older servers. The motive behind the change was to follow some of Microsoft’s newer best practices and increase our performance when using distributed transactions.
What is the DTC?
The Distributed Transaction Coordinator (DTC) is a transaction manager that allows client applications to include several different sources of data in one transaction. The DTC coordinates committing the distributed transaction across all the servers enlisted in the transaction. This helps ensure that the transaction is committed when every part of the transaction succeeds, or is rolled back when any part of the transaction process fails. It is a separate Windows service and sometimes is referred to as Microsoft Distributed Transaction Coordinator or MSDTC.
How should the MSDTC be configured in a SQL failover cluster?
There are four main ways to configure SQL Server to use the MSDTC:
- Use the MSDTC inside the SQL Server resource group (recommended best practice) – By having a dedicated instance of MSDTC for each SQL Server instance, it can help to prevent failures of one instance of MSDTC from affecting distributed transactions for other instances. Setting up the MSDTC in this manner will require configuring the dependencies of this resource to use an available physical disk and SQL Server network name. This option provides the best performance because it guarantees that your MSDTC will always run on the same physical node as the SQL Server, which reduces communications overhead.
- Use the local machine’s MSDTC – With this configuration, SQL Server will use the service hosted on the node that the SQL Server is running on. 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.
- Use multiple clustered MSDTC’s, one per SQL Server instance – This configuration addresses the primary concern of maximum availability of SQL Server and MSDTC. Setting up the MSDTC in this manner will require the use of an available physical disk and an IP address. Additionally, you have to manage the mapping of a SQL Server instance to a specific MSDTC (http://technet.microsoft.com/en-us/library/cc742483.aspx). Also, since the MSDTC is running in a separate group, there is no way to guarantee that both the SQL Server instance and a specific MSDTC are going to be hosted on the same node, which can negatively affect performance.
- Use single clustered MSDTC for all SQL Server instances – This option is similar to the option of having multiple DTC’s in the way it is setup. It requires the use of an available physical disk and an IP address. However, there are no mappings, since all SQL Server instances will use the same clustered MSDTC. So, this could negatively affect performance when the SQL Server instance and MSDTC are on different nodes and could be a bottleneck since every instance would use the same MSDTC.
In our situation, we moved from having a single clustered MSDTC for all SQL Server instances to using the MSDTC inside of the SQL Server group. This has simplified some of our setup and should lead to better performance with our distributed transactions.
*** Please read the following references which give more detail about these best practices. ***
- How to Cluster MSDTC: http://msdn.microsoft.com/en-us/library/dd897479.aspx
- MSDTC Recommendations on SQL Failover Cluster: http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/15/msdtc-recommendations-on-sql-failover-cluster.aspx
- How to configure DTC for SQL Server in a Windows 2008 cluster: http://blogs.msdn.com/b/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx