[MSDTC] How to Setup Microsoft Distributed Transaction Coordinator

Ever faced a situation where you need to make changes to a record in 2 different SQL Server databases that are located in two different instances in different servers and need to ensure the changes made to the both of the databases are successful before commit? Before you can do so, you need to configure Microsoft Distributed Transaction Coordinator (MSDTC) and this post will guide you on how to do so.

This post will not go into what is MSDTC, but if you're curious and want to know what it is all about, do check out the following links.
https://msdn.microsoft.com/en-us/library/aa940166(v=winembedded.5).aspx
https://blogs.msdn.microsoft.com/florinlazar/2004/03/04/what-is-msdtc-and-why-do-i-need-to-care-about-it/

There are few things that you need to know before configuring MSDTC.

• The MSDTC need to be configured on all servers that are involved in the transaction.
• If you're using transaction in C# / VB with TransactionScope and requires to make changes to 2 databases located in two different instances in different servers, in this case, MSDTC has to be configured on server that launch the application & the 2 database servers. (Total 3 Servers)
• If you're using transaction in trigger or stored procedure that make changes to its own database and another database located in another server, in this case, MSDTC has to be configured on the 2 database servers.
• Firewall clearance for all servers that configure MSDTC.

The following steps will only shows the minimum settings required for the distributed transaction to work. If you need or requires the other settings, do not hesitate to make changes accordingly.
1) Open up run window, type mmc and click OK button.

2) Once the MMC console launched, select File which is located at the top menu, click Add/Remove Snap-in...

3) On the Add or Remove Snap-ins window, under Available snap-ins list box, look for Component Services, select it and click Add > button or double click it. It will then appear under Selected snap-ins list box. Once you are done, click OK button.

4) Back to the MMC console window. On the left pane, expand Component Services node, Expand Computers node, right click on My Computer and click Properties.

5) On the My Computer Properties window, select MSDTC tab, tick Use local coordinator and click OK button.

6) Back to the MMC console window, further expand My Computer node, expand Distributed Transaction Coordinator, right click on Local DTC and click Properties.

7) On the Local DTC Properties window, select Security Tab.
Tick Network DTC Access.
Tick Allow Inbound,
Tick Allow Outbound (This is not required for servers that which did not initiate the transaction). Once you are done, click OK button.

Note: The following figure shows the minimal settings required. If you requires the other settings, do not hesitate to change it.

The following steps guide you on how to allow MSDTC to pass through firewall

1) Open up firewall window and select Allow an app or feature through Windows Firewall located at the left pane.

2) Look for Distributed Transaction Coordinator, tick private / public / both depending on whether your transaction is in private / public / in both network. Click OK button.

That is all for the configuration part. Now, to verify whether your MSDTC has been configured successfully, you can test it out based on one of the following.

1) Connect to one of the database server with SQL Server Management Studio, Write a BEGIN TRANSACTION statement to make data change to current database and another database in different server with linked server.

2) Use the TransactionScope Class in C# / VB to make changes to 2 different database servers.