SQL Server: How to configure and enable Microsoft Distributed Transaction Coordinator (MSDTC)?

The Microsoft Distributed Transaction Coordinator mostly use for distributed transactions.

A Distributed Transaction is a very simple transaction which executes between two or more servers.
For example, We started one transaction in Server one and same transaction is going to finish in Server two.

In this kind of situation, we require MSDTC enabled in SQL Server.
To enable distributed transactions across different servers, you need to configure MSDTC on both the servers.

The following is a list you need to check:

1. DTC service is running:

Please run “services.msc” from windows run dialog to check if it is running.
If it is stopped, please start it by right-clicking on it and choose “Start”.

2. Network DTC access is enabled:

Please open Distributed Transaction Coordinator using Control Panel -> Administrator Tools -> Component Services.
Right click on LocalDTC and make sure that Network DTC should be enabled.

3. DTC service is configured to be allowed to access network in firewall in the linking server:

Please make sure this service could access the network and could receive connection from other server.

4. XACT_ABORT is enabled:

We could enable XACT_ABORT with the following statement:

5. Please execute the status of “Ad Hoc Distributed Queries”:
“Ad Hoc Distributed Queries” should enabled

If the value under run_value is 0, please execute the following script to enable it:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of