About Data Transaction Coordinator

About Data Transaction Coordinator

About Microsoft Data Transaction Coordinator (DTC)

MSDTC (or DTC) is a Microsoft service that is used by SQL and other applications when two (or more) machines each need to do part of some work and both must succeed or no changes are made. The common scenario is that machine 1 starts a transaction (“Distributed Transaction”), and does some work, then connects to machine 2 that also does some work.  The work can go back and forth.  If either machine fails then everything in the transaction is cancelled and no changes are made. PypeServer does not determine when a Distributed Transaction is required.  This is handled by SQL.  Empirically, it is often required when multiple changes must be made to the database in an all-or-nothing manner.  This prevents the database from being corrupted if only a portion of the changes are made and something goes wrong, or if one of the machines fails to do its part.  Distributed Transactions are essential to data integrity and are used in all types of SQL transactions where even one mistake can cause problems. 

What can go wrong during a Distributed Transaction? 
  1. The computer could be powered down or crash.
  2. The logic of the processing could become impossible.  For example in pipe importing a part may lack sufficient data, or the data does not logically define a part.
  3. The processing could encounter bad file formatting.

DTC only starts when a Distributed Transaction is required.  A PypeServer user may have no problem performing many tasks because no distributed transactions are required. Distributed Transactions are commonly required in both importing and nesting. 

Note: The use of the DTC is not optional. The Distributed Transactions must be used to ensure data integrity in SQL client-server transactions.

Configuration

The Distributed Transaction Coordinator is a core Windows network service and must be running on both the SQL Server and the remote seat to support Distributed Transactions when requested. Please see The Microsoft Network Stack for a background on DTC and networking. 

DTC at the SQL Server

DTC support is installed and starts on boot at the Kiosk computer which usually is the SQL Server.  This allows remote (network client) DTC services to establish Distributed Transactions with the PypeServer SQL Server.

At a Remote Seat (client system)

When PypeServer is installed on a remote seat, DTC is installed (if it is not there) and is set to Automatic start at startup.  Firewall ports are also opened to allow DTC to reach out past its own local firewall to establish Distributed Transactions with the PypeServer computer.

·        DTC could have been uninstalled or turned off by some company security configurations

·        DTC can be blocked by the firewall by company security configurations.  See Configuring MSDTC and Troubleshooting sections below. 

·        It is possible that network routers may also block DTC communications to the server.

Configuring MSDTC

Configure MSDTC

  1. From Start, search for dcomcnfg and press Enter on your keyboard.
  2. Expand the nodes in the Console pane to locate the DTC (e.g. Local DTC).

  1. Right-click the DTC then click Properties.
  2. Click the Security tab.
  3. Under Security Settings, select the Network DTC Access
  4. Under Transaction Manager Communication, select the Allow Inbound and Allow Outbound checkboxes.
  5. Under Transaction Manager Communication, click the No Authentication Required radio button.
  6. Select the Enable XA Transactions checkbox.
  7. Click OK.

Using PowerShell

  1. Right-click on the Start Menu and select "Windows PowerShell (Admin)".
  2. Select your configuration below and paste the script into the PowerShell window and execute it.
Kiosk/SQL Server
Notes
Set-DtcNetworkSetting -AuthenticationLevel "NoAuth" -InboundTransactionsEnabled $true -OutboundTransactionsEnabled $true -LUTransactionsEnabled $true -RemoteClientAccessEnabled $true -XATransactionsEnabled $true
 
Remote Seat
Notes
Set-DtcNetworkSetting -AuthenticationLevel "NoAuth" -InboundTransactionsEnabled $true -OutboundTransactionsEnabled $true -LUTransactionsEnabled $true -XATransactionsEnabled $true
 
Allow DTC through the firewall on both systems
netsh advfirewall firewall set rule group="Distributed Transaction Coordinator" new enable=yes
Enable-NetFirewallRule -DisplayGroup "Distributed Transaction Coordinator"

Note: It’s recommended that you review your Windows Firewall exceptions to avoid any DTC issues.  Though system and security settings will vary, you can investigate the appropriate exceptions by going to Control Panel > Windows Firewall > Allow apps to communicate through Windows Firewall and to review the exceptions for Distributed Transaction Coordinator. 

Troubleshooting

Some general issues to consider before using diagnostic tools in an investigation are:
  1. The DTC service could have been uninstalled or turned off by some company security configurations.
  2. DTC communications can be blocked by the firewall by company security configurations.
  3. DTC access permissions may not be set correctly to allow users to access the remote server.
If there are additional network and security rules required in order for the service to communicate, there are a number of Microsoft tools that can help your team troubleshoot some common issues. 
Here are some for DTC problems:

HealthCheck

The Enterprise HealthCheck tool provides a DTC check that will check access permissions, execute a synthetic nested DTC transaction, and report a pass/fail result. It also can attempt to reset the DTC access rights to a PypeServer supported configuration. It also allows one to run the Test-Dtc tool below.

DTC-Ping

This Microsoft tool verifies transaction support between two computers if SQL Server is not installed on either computer. The DTCPing tool must be run on both the client and server computer. 
More Information: How to troubleshoot connectivity issues in MS DTC by using the DTCPing tool

Test-Dtc

The PowerShell cmdlet Test-Dtc tests whether two computers can participate in networked transactions.  Here’s the link to tool use, examples and related links. This tool provides a lot of detail regarding where the failure is occurring.
More Information: Test-Dtc

    • Related Articles

    • About SQL Server

      About SQL Server Microsoft SQL Server is a relational database management system (RDBMS). PypeServer Enterprise and tools connect to a SQL Server instance or database, where all part, pipe, and other shared data is stored. The PypeServer Kiosk ...

    • Troubleshoot Connections with HealthCheck

      PypeServer Enterprise requires reliable network communications between each user and the distributed services it uses to store, share and license the data and application. Network communications involves several different layers of software on both ...

    • System Requirements

      The article lists the recommended configurations for PypeServer Enterprise in a production environment. Hardware Kiosk The PypeServer computer is stored in a industrial kiosk stationed next to the cutting machine. I has the following minimum ...

    • Getting Started with PypeServer

      Getting Started with PypeServer (and Frequently Asked Questions) This document covers what to do in preparation for using the PypeServer software, and maintaining the PypeServer system. Contents 1 User Information 3 1.1 Overview 3 1.2 General Layout ...

    • About Networking

      About Networks Enterprise supports multiple systems working together in a distributed environment connected to one or more Enterprise site servers. The best performance is achieved within a high bandwidth/low latency local area network. High latency ...