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.

Let’s see now how to install.

How To Re-Install MSDTC

The service will need to be uninstalled, the server rebooted, then installed. You need to perform the above operating from the command line running as an administrator! Stop the service: net stop msdtc Uninstall the service: msdtc -uninstall In the registry, remove the following keys if they exist: HKEY_CLASSES_ROOT\CID HKEY_CLASSES_ROOT\CID.Local for Windows 2008 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MSDTC HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC Reboot the server Install the service: msdtc -install Check the Event Viewer (Windows Application Logs) for the following message: Event Source: MSDTC Event ID: 4104 Description: The Microsoft Distributed Transaction Coordinator service was successfully installed. Run: msdtc -resetlog Start the service: net start msdtc Check the msdtc configuration and correct if necessary

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:

Open Component Services Click Start > Administrative Tools > Component Services

NOTE: …or perform this via the command line – “dcomcnfg” Expand Component Services Go to Computers > My Computer > Distributed Transaction Coordinator > Local DTC Right click on Local DTC > Properties > Security Tab On this tab choose the following options Network DTC Access Allow Remote Clients Allow Remote Administration Allow Inbound Allow Outbound No Authentication Required Enable SNA LU 6.2 Transactions DTC Logon Account: NT Authority\Network Service Click OK Restart the service

Testing MSDTC

This simple query can be used to test the service:

BEGIN DISTRIBUTED TRANSACTION SELECT * FROM <INSTANCE NAME>.<DATABASE NAME>.<SCHEMA>.<OBJECT NAME>; --Check the output to see is an error is thrown, then run the next line COMMIT;

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:

Test the connection to the linked server – right click on the linked server > Test Connection

Verify security settings – under the Security tab make sure the radio button is selected for “Be made using the login’s current security context”

Set the Server Options on the linked server – right click on the linked server > Properties

Set RPC, RPC Out, and Enabled Promotion of Distributed Transactions to TRUE Alternatively run the following 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'; 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: 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 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!

Yes I want to Subscribe!