How To Configure MSDTC For SQL Server

In this article, we will explain what is MSDTC and configure MSDTC(Microsoft Distributed Transaction Coordinator) for an application that will connect to SQL Server with MSDTC.

What is MSDTC?

SQL Server DTC is used to manage a single transaction in instances on different servers. If you need to manage a single transaction on different instances on same server, you dont need MSDTC.

For example you started a transaction on machine1 and then you want to execute some statements on machine2 in same transaction.

So if the statements executed on machine2 has failed, whole transaction will be rolled back. SQL DTC provide this functionality.

What is Msdtc used for?

We talked about what is MSDTC? But why do I need MSDTC?

You may need MSDTC if you want to use one of belows.

• Linked Servers
• OPENROWSET
• OPENQUERY
• OPENDATASOURCE
• RPC (Remote Procedure Calls)

Check MSDTC Service is Running

Many people wonders to check that msdtc is installed and running on the database server.

To check the msdtc is configured on the database server, you should check below steps on your system.

To check msdtc service is running or not running on your server first write service.msc to open services as follows.

Then go to the Distributed Transaction Coordinator and look at the service.

Start, Stop or Restart MSDTC Service

To start or stop MSDTC Service, you should check the msdtc service is running at first as I mentioned above. Then right click the DTC service and click stop or start.

Enable MSDTC

To enable distributed transaction cordinator in sql server,first we should open component services.

Click Start->Run and type dcomcnfg to open component services or  go to Server Manager->Tools->Component Services.

Configure MSDTC

Msdtc configuration can be local or cluster based. If the server on which SQL Server is located is not Windows cluster, and SQL Cluster DTC is not available, you should configure Local DTC.

Go to the following tab and right click on “Local DTC” anc click Properties.

You can see msdtc settings in below screenshot. Configure Local DTC as follows.

It will not be very safe to open Allow Remote Administration.

Configure MSDTC in Cluster

If the server where SQL Server is located is windows cluster, you should configure Cluster DTC.

To configure MSDTC SQL cluster,Right-click the Cluster DTC that appears as deleted from the following screenshot and click Properties. Then configure Cluster DTC as follows.

It will not be very safe to open Allow Remote Administration.

MSDTC Ports

MSDTC does not use the default ports of SQL. This section contains information about msdtc firewall settings.

There are special msdtc firewall ports that it uses.

You can find a list of these ports on the link below.

https://msdn.microsoft.com/en-us/library/ee784913(v=cs.20).aspx

It may also use ports other than these ports.

Therefore, it is necessary to check whether there is a closed port between the application servers that will use ms DTC and the database servers.

You must grant the necessary access privilege from the firewall.

If you do not do this, the transactions that come with ms dtc will abort as follows.

You may want to read other article related with MSDTC

How To Move Msdtc Disk To Another Storage,

How To Add MS DTC To The Cluster