If you haven’t worked with MSDTC before you might mistake it for a simple straight forward service which is easy to setup, configure, and troubleshoot. I too once thought that until I stumbled into the thick forest of MSDTC with those promises and didn’t return for years all grizzly and worn (ok it wasn’t that bad).

What comes next is my contribution to those who follow into the dark denizens of MSDTC hoping to fix things so they can go home.

What is MSDTC and Why Do We Care?

MSDTC is a distributed transaction coordinator created by Microsoft. A frequent use of MSDTC is on a SQL Failover Cluster. Much of what is written below can be directly applied to this situation.

MSDTC comes with Windows by default – in the sense that you don’t need to install any roles or features to get it. The most basic function it performs is to keep transactions ACID. The most popular implementation of this is 2 phase commit. The transaction manager makes sure all is well and compliant with ACID for the transaction to commit and then allows it to commit.

MSDTC Background

Microsoft Distributed Transaction Coordinator (MSDTC) is a Windows service that coordinates transactions spanning multiple databases. It is a transaction manager that allows applications to include several different sources of data in one transaction. MSDTC coordinates committing the distributed transaction across all servers enlisted in the transaction.

An example here would be a process on one machine calling a stored procedure on another machine which in requires data that changes together in a transaction.

The MSDTC service is running on each of the servers to manage the successful commit (or rollback) of the transaction across all servers enlisted in the transaction.

If distributed transactions are being used with SQL Server then it is required that MSDTC be installed and used for distributed queries, two-phase commit transactions, and some replication functionality.

Here is another example to show how it integrates with an Application Server forming a distributed transaction with SQL Server:

The MSDTC Service

It looks like this and it should appear on every machine which will participate in a distributed transaction.

The service startup type should be Automatic or Automatic (Delayed Start)

MSDTC Installation / Reinstallation

MSDTC needs to be installed, configured, and running on each host that contains a SQL instance. It must work bi-directional from each host to the other.

How To Re-Install MSDTC

When making changes to the registry it is a good practice to first take a backup of the registry!

How to reinstall MS DTC for a nonclustered Windows 2000 Server

MSDTC Configuration

Remember that it needs to be installed, configured, and running on each host that contains a SQL instance for our products.

It must work bi-directional from each host!

i.e. Server A > Server B and Server B to Server A

Let’s see how to configure it now.

How to Configure MSDTC

On each server the service runs and can be configured via Component Services:

1. Open Component Services
   1. Click Start > Administrative Tools > Component Services

   
   NOTE: …or perform this via the command line – “dcomcnfg”

2. Expand Component Services
3. Go to Computers > My Computer > Distributed Transaction Coordinator > Local DTC
4. Right click on Local DTC > Properties > Security Tab
5. On this tab choose the following options
   1. Network DTC Access
   2. Allow Remote Clients
   3. Allow Remote Administration
   4. Allow Inbound
   5. Allow Outbound
   6. No Authentication Required
   7. Enable SNA LU 6.2 Transactions
   8. DTC Logon Account: NT Authority\Network Service

   

6. Click OK
7. Restart the service

Testing MSDTC

This simple query can be used to test the service:

If no error is listed in the output window then it handled the query just fine.

NOTE: the other SQL instance must be on a different machine!

Validating Linked Server Configuration

The above query tested a remote query. This is possible because a linked server has been setup to connect to the remote SQL Instance. There are a few things we should confirm about the linked server connection:

1. Test the connection to the linked server – right click on the linked server > Test Connection
   
2. Verify security settings – under the Security tab make sure the radio button is selected for “Be made using the login’s current security context”
   
3. Set the Server Options on the linked server – right click on the linked server > Properties
   
   1. Set RPC, RPC Out, and Enabled Promotion of Distributed Transactions to TRUE
   2. Alternatively run the following SQL:
   Transact-SQL

   EXEC sp_serveroption @server=@MyServerName, @optname='rpc', @optvalue='true'; EXEC sp_serveroption @server=@MyServerName, @optname='rpc out', @optvalue='true'; EXEC sp_serveroption @server=@MyServerName, @optname='remote proc transaction promotion', @optvalue='true';

   1 2 3

   EXEC sp_serveroption @server=@MyServerName, @optname='rpc', @optvalue='true';   EXEC sp_serveroption @server=@MyServerName, @optname='rpc out', @optvalue='true';   EXEC sp_serveroption @server=@MyServerName, @optname='remote proc transaction promotion', @optvalue='true';

   Reading the MSDTC Log

   The log is located by default at: %windir%\System32\Msdtc

   NOTE: Open this via command prompt using admin privileges! Do not try to access via file explorer.

   The logs are in binary and are not viewable by a text editor such as notepad. You’ll need to view the data a different way. Here’s an example of how to view the trace data:

   • Using command prompt with elevated admin rights, type:
     
     MS DOS

     msdtcvtr {-MODE 1 | -tracelog <tracelogfilename> } [options]

     1	msdtcvtr {-MODE 1 | -tracelog <tracelogfilename> } [options]

   • msdtcvtr.bat converts the binary trace file to human readable text
   • Default location is: C:\Windows\System32\Msdtc\Trace
   • You must include “tracefmt.exe” in the path when running msdtcvtr.bat
     • • Find this in the Windows Driver Kit (WDK) – See Download Windows Drivers Kit
       • Download, unpack, and install full development environment
       • Search for “tracefmt”, “msdtcctr.mof”, “msdtcvtr”, “traceprt.dll” and put all files in the directory of the trace log to be read
     MS DOS

     Msdtcvtr.bat -tracelog "<trace log file name>" -o <output file name>

     1	Msdtcvtr.bat -tracelog "<trace log file name>" -o <output file name>

   • See view trace data in references for more details

    

   ---

    

   What’s next? Now that some of the basics have been covered we are ready to discuss some more common and specific failures which are apt to occur. See part 2 here!

Like what you are reading? Please subscribe!