About MSDTC and Troubleshooting it
MSDTC – Microsoft Distributed Transaction Coordinator. This fellow has caused me many hours of troubleshooting. This post is a set of troubleshooting step I have successfully been able to perform. This is my notes regarding MSDTC.
Index
- About MSDTC
- MSDTC – CID issues
- Uninstall MSDTC
- Install MSDTC
- Install MSDTC – Could not install
- MSDTC – Settings
- RPC – Settings
- TCPView – Connectivity Testing
- DTCPing – Connectivity Testing
- DTCTester – Connectivity Testing
- Summary
About MSDTC
MSDTC stand for Microsoft Distributed Transaction Coordinator. It is a service that makes it possible for applications to perform atomic transactions over different data sources. That means if an application (e.g. a BizTalk orchestration) wants to insert data in different databases and keep the transactions in one scope, it will use MSDTC to accomplish this. If one data source fails, the MSDTC service can roll back the previous transactions in the same scope so you don’t end up with incomplete transactions.
Info. Working with Windows Fail over Clusters, you often need to cluster MSDTC as a clustered Service.
Back to Index.
MSDTC – CID issues
If the servers are created from a clone, MSDTC will have the same CID values in the registry. MSDTC can’t communicate with the same CID values, you must reinstall it.
You can view the CID values from the registry. Start-> type: regedit -> Enter-> Registry Editor will open. In Registry Editor Browse to: HKEY_CLASSES_ROOT\CID :
This CID values must be different on the servers that uses MSDTC.
Note. As far as I can tell, the CID values will be the same if you cluster the DTC. Look at these screen shoots. Each server started with unique CID values, after I clustered the DTC in a Windows Failover Cluster the registry looks like this now:
My-sql01 – Node 1.
My-sql02 – Node 2.
And this is working in the cluster with two nodes running a SQL instance.
Back to Index.
Uninstall MSDTC
Note! If the server/node is in a cluster, take it offline first!
Run msdtc -uninstall from the command prompt as Administrator:
You won’t get any feedback in the command prompt if the command was executed at all or any status report.
To get some feedback, we can read it in the windows log. Start-> Event Viewer-> Windows Logs-> Application:
After you have uninstalled MSDTC, reboot.
You can do a reboot with this command, shutdown -r -t 00:
Note! After reboot make sure that the node is still offline in the cluster before you try to reinstall MSDTC.
Back to Index.
Install MSDTC
Note. If node is in a cluster, make sure it is offline first.
Open a command prompt as Administrator. Run command: msdtc -install or msdtc.exe -install.
Once again, no feedback in the command prompt. Go to event viewer:
After you have successfully installed MSDTC, reboot the server again.
shutdown -r -t 00
Note! After the MSDTC Service is reinstalled, the startup type will be set to manually. We don’t want this, it must be Automatic. Change this under: Start-> Services-> Right click Distributed Transaction Coordinator:
Back to Index.
Install MSDTC – Could not install
Note! First thing to double check if you encounter problem is that your run the command prompt as Administrator. Try also to run with the full command: msdtc.exe -install.
On one node when I tried to install MSDTC I encountered this problem:
“Could not install the MS DTC service”
Two blogposts helped me to troubleshoot the problem:
The second suggestion to delete the registry keys under the CID folder was the one that helped for me. Reboot the server after each step!
Back to Index.
MSDTC – Settings
Settings for MSDTC are made in Component Services.
Start-> type: Component Services Enter-> Expand to Local DTC and right click to bring up Local DTC Properties-> Go to Security Tab and make settings there.
This should be the must reliable settings to make MSDTC work:
Note! If DTC is clustered, you must make the same changes there, clustered DTC is located in the folder under Local DTC.
Note! You must have the same settings on all the servers that will make use of DTC.
Back to Index.
RPC – Settings
By default, the DTC transaction will communicate over a large number of random ports in the span from 49152 to 65535. You often would like to narrow this span. You can do this setting in the Component Services.
Start-> Component Services-> right click My Computer-> open tab Default Protocols-> Connection-oriented TCP/IP Properties-> Add-> Type your port range. See Image:
Note! You must do the same settings on all the servers!
Back to Index.
TCPView
TCPView is a tool to see which dynamic ports are in use. Can be good for troubleshooting MSDTC. You can have it open while using the tool DTCping.exe (see next heading).
You can download TCPView (TCPview.exe) here:
Example from a test:
Back to Index.
DTCPing
DTCPing is a tool that tests the connectivity for DTC between two servers.
You can download DTCPing (DTCPing.exe) here:
You must start DTCPing on both servers you are testing and start them both simultaneously or short after each other. It can matter which server you start the test from, so try the test two times and change starting server.
Here are two screenshots from the test between MY-BTS01 and MY-SQL01:
Note! You must use the NetBIOS name see: https://www.raspberryfield.life/2019/03/22/dns-names-vs-netbios-names/ [2019-04-27]
Info. You will have more information in the logs produced by the tool. They are created in the same folder as DTCping.exe.
Test from MY-BTS01 to MY-SQL01:
Test from MY-SQL01 to MY-BTS01:
Info. Don’t forget to try the test again but start with the other server this time.
Back to Index.
DTCTester
DTCTester is the third and last tool to test MSDTC.
You can download DTCTester (dtctester.exe) here:
To use this tool, we must create an ODBC connection to the database we are communicating with. Here is a short guide how to create the ODBC connection:
Open ODBC Data Sources (32-bit) From Start:
Select Add and then SQL Server:
A new window will open. Name the connection and specify the server you want to connect to:
Leave the default values:
Use the default settings:
Use the default settings in the final settings window, and click Finish:
After you clicked finish, a new window will open. Here you should test the connection, click Test Data Source.. . Another window will open with result of the test:
After the test, you newly created connection will show up in the ODBC Data Source Administrator:
Now when we have an ODBC connection, we are ready to use the DTCTester tool.
Open a commend prompt as Administrator and change the directory to the same location as the dtctester.exe:
The command to test a DTC transaction is: dtctester <odbc name> <username> <password>. Type that and the test will begin. During the test, you are prompted to type enter to start the transaction, press Enter.
After you have pressed Enter, you will see result from the test:
If you don’t get any errors, the test was successful.
Back to Index.
Summary
MSDTC is an important component that can cause a lot of troubleshooting. I hope this post have given some pointers to handle DTC.
Back to Index.