The Microsoft Distributed Transaction Coordinator, or MSDTC, is an important part of the Windows operating system in Microsoft SQL Server. It lets a transaction on one server access objects on multiple servers. This can be done using the BEGIN DISTRIBUTED TRANSACTION statement. By using .NET code, a single transaction can be run across multiple SQL Server instances or multiple databases from various vendors. No matter how the transaction...
is started, it can be committed or rolled back on all servers at one time.
The Distributed Transaction Coordinator is a feature of the operating system used by the database engine to ensure multiserver transactional consistency. It is installed by default and configured for local access only. MSDTC must be configured for remote access so that instances on two separate servers can contact each other. For how to configure MSDTC, check out two separate articles on my blog, one for Windows 2003 and one for Windows 2008. If you are using MSDTC to connect two instances on the same server, remote configuration isn’t needed. When installing MSDTC in a clustered environment, it will be clustered automatically and only a single MSDTC instance will be used.
Troubleshooting MSDTC issues through SQL Server can be rather tricky; there is no real surface area to work with and you can’t issue commands directly against MSDTC as you can against a SQL Server instance. Microsoft provides two diagnostic tools that can be downloaded and used to ensure that two MSDTC instances can talk to each other successfully. The first, more basic tool is called DTCPing. The second tool is called WinRM or “Windows Remote Management.” When troubleshooting MSDTC issues, start with the DTCPing tool. Once that begins working, begin testing with WinRM.
For more on the Distributed Transaction Coordinator and SQL Server
Applications can begin distributed transactions manually as they connect to the database engine, or they can be started through Transact-SQL, or T-SQL , when using a linked server or an open query command. This is the more common scenario. If a distributed transaction needs to be manually started from within T-SQL, use the BEGIN DISTRIBUTED TRANSACTION command. If MSDTC is configured correctly on both machines, SQL Server will begin a distributed transaction and leave that transaction open until it’s committed or rolled back. Nondistributed transactions can be escalated to distributed transactions by the database engine as needed when working over remote instances. This happens when rows are updated on the remote instance, such as when doing an insert or an update against a remote server. This transaction escalation includes not only local transactions explicitly started using the BEGIN TRANSACTION command but also transactions that are started automatically when there was no explicit transaction started.
You can monitor MSDTC from the Component Services Microsoft Management Console (MMC) snap-in. After opening the snap-in, drill down through Console Root > Component Services > Computers > My Computer > Distributed Transaction Coordinator > Transaction Statistics. This screen, shown in Figure 1, shows the number of transactions currently running, the number of transactions that have “Committed, Failed,” plus the total number of transactions.
Figure 1: Component Services MMC snap-in showing current and aggregate transaction connections.
If more information is needed than the overall statistics in the bar graphs shown in Figure 1, then select the Transaction List from the left side of the menu. It lists all currently running transactions that MSDTC is processing, shown in Figure 2.
Figure 2: Component Services MMC snap-in showing the current list of running transactions.
How MSDTC communicates
When configuring MSDTC it’s important to know how MSDTC services will communicate between computers, especially when there is a network firewall. MSDTC uses Remote Procedure Calls, or RPC, to communicate between machines. RPC generally uses all network ports over 1024 to communicate. You can, however, limit the number of ports that RPC uses to communicate through the firewall. Do this by configuring RPC to allow 20 ports through the firewall. The number of ports will need to be increased if there are many simultaneous MSDTC transactions, or if features such as the cluster service or network file shares will use the RPC protocol, as all RPC traffic must be processed through this limited number of ports. It is not recommended to limit the TCP ports over which RPC can travel unless there is a need to, say, pass RPC traffic through a network firewall.
Not every database server needs to have MSDTC configured for network access. When it is needed, however, you shouldn’t have to configure many machines just so that two can talk to each other. Instead, MSDTC should be properly configured on all servers so that those machines can communicate efficiently.
ABOUT THE AUTHOR
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Check out his blog at SQL Server with Mr. Denny.