Distributed transactions are essential for ensuring data consistency across multiple databases or systems. In a Windows environment, the Microsoft Distributed Transaction Coordinator (MSDTC) is a service that facilitates distributed transactions. This article will guide you through setting up and managing distributed transactions using MSDTC on Windows.

Understanding Distributed Transactions

A distributed transaction involves multiple networked databases or systems. It ensures that all participating databases commit or roll back changes in a coordinated manner, maintaining data integrity.

Setting Up MSDTC on Windows

Step 1: Install and Configure MSDTC

1. Open Server Manager:

   • Press Win + R, type ServerManager, and press Enter.

2. Add the Distributed Transaction Coordinator feature:

   • Navigate to Manage > Add Roles and Features.
   • Proceed through the wizard until you reach the Features section.
   • Check the box for Distributed Transaction Coordinator.
   • Complete the wizard and restart the server if prompted.

3. Configure MSDTC:

   • Open Component Services by pressing Win + R, typing dcomcnfg, and pressing Enter.
   • Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator.
   • Right-click Local DTC and select Properties.
   • Configure the security settings as needed, ensuring that Network DTC Access is enabled.

Step 2: Enable MSDTC on the Firewall

1. Open Windows Firewall:

   • Press Win + R, type wf.msc, and press Enter.

2. Create Inbound Rules:

   • Navigate to Inbound Rules.
   • Create a new rule for MSDTC.exe (typically located in C:\Windows\System32\msdtc.exe).
   • Allow the rule for all profiles (Domain, Private, Public).

3. Create Outbound Rules:

   • Similarly, create a new outbound rule for MSDTC.exe.

Example: Using MSDTC in a .NET Application

Here's a practical example of how to use MSDTC in a .NET application to manage a distributed transaction involving two SQL Server databases.

Sample Code

using System;
using System.Data.SqlClient;
using System.Transactions;

class Program
{
    static void Main()
    {
        string connectionString1 = "Data Source=Server1;Initial Catalog=Database1;Integrated Security=True;";
        string connectionString2 = "Data Source=Server2;Initial Catalog=Database2;Integrated Security=True;";

        using (TransactionScope scope = new TransactionScope())
        {
            using (SqlConnection connection1 = new SqlConnection(connectionString1))
            {
                connection1.Open();
                SqlCommand command1 = new SqlCommand("INSERT INTO Table1 (Column1) VALUES ('Value1')", connection1);
                command1.ExecuteNonQuery();
            }

            using (SqlConnection connection2 = new SqlConnection(connectionString2))
            {
                connection2.Open();
                SqlCommand command2 = new SqlCommand("INSERT INTO Table2 (Column2) VALUES ('Value2')", connection2);
                command2.ExecuteNonQuery();
            }

            scope.Complete();
        }

        Console.WriteLine("Transaction completed successfully.");
    }
}

Testing the Setup

1. Deploy the .NET application on a machine with MSDTC configured.
2. Run the application to ensure that the transactions are coordinated across the databases.

Troubleshooting

• Check MSDTC Logs:

  • Open Event Viewer and navigate to Applications and Services Logs > Microsoft > Distributed Transaction Coordinator.
  • Review logs for any errors or warnings.

• Verify Network Communication:

  • Ensure that the machines involved in the transaction can communicate over the network.
  • Use ping and telnet commands to test connectivity.