Introduction

In this article I would like to explore the different OS, SQL Server and SSIS options to set up distibuted transactions. There is already a lot of information on the internet and through this article I've tried to gather as much data as possible to compile and list them in one place, making it easier and less of a headache to set up distributed transactions using SSIS.

A good definition of distributed transactions can be found here

Technology used

I use these versions of software in this article.

• Windows Server 2008 R2
• SQL Server 2008 R2

Security configuration

Start with the Windows Server DTC Security Settings since by default, these are disabled.

• Log on to all interested servers and type dcomcnfg in the search box, then press Enter.
• This will open the Component Services window.
• Expand the Component Services node
• Expand the Computers node
• Expand the My Computer node
• Expand Distributed Transaction Coordinator node
• Expand Local DTC properties window

Check the Network DTC Access checkbox and the required Transaction Manager Communication options. I don't know exactly what the security options control however with the setup above, DTC worked fine for me.

NOTE: This has to be set upon all servers (SQL Server Source and Destination) including the server which will execute the SSIS package.

DTC Service

Make sure that the DTC service is started on all participating servers including the machine which will execute the SSIS Package.

NOTE: The DTC service has to be running on all servers including the server which will execute the SSIS package.

Update Firewall Settings

Here I would suggest to temporarily relax the server firewall settings just to get the DTC working. Once the DTC is working satisfactorily, security can be re-tightened up again. In my case, being on a Domain network, I just temporarily disabled the domain firewall security.

Test connection using the DTCPing Tool

1. Open the tool on both Servers.
2. Try to ping (using the tool) from one of the servers first. I'm starting from MT-SQL03, pinging server MT-SQL01

and the results below indicate a successful connection between the servers.

 Please send following LOG to Microsoft for analysis:

 Partner LOG: MT-SQL035232.log

 My LOG: MT-SQL013304.log

++++++++++++Validating Remote Computer Name++++++++++++

Please refer to following log file for details:

 C:\Temp\MT-SQL013304.log

Invoking RPC method on MT-SQL03

RPC test is successful

++++++++++++RPC test completed+++++++++++++++

++++++++++++Start DTC Binding Test +++++++++++++

Trying Bind to MT-SQL03

Received reverse bind call from MT-SQL03

Binding success: MT-SQL01-->MT-SQL03

++++++++++++DTC Binding Test END+++++++++++++

++++++++++++Start Reverse Bind Test+++++++++++++

Received Bind call from MT-SQL03

Trying Reverse Bind to MT-SQL03

Reverse Binding success: MT-SQL01-->MT-SQL03

++++++++++++Reverse Bind Test ENDED++++++++++

3. Ping from the second server, i.e. MT-SQL01

 and the results below which indicate a successful connection between the servers.

Please refer to following log file for details:

 C:\Temp\MT-SQL035232.log

Invoking RPC method on MT-SQL01

RPC test is successful

++++++++++++RPC test completed+++++++++++++++

Please start PING from MT-SQL01 to complete the test

Please send following LOG to Microsoft for analysis:

 Partner LOG: MT-SQL013304.log

 My LOG: MT-SQL035232.log

++++++++++++Start Reverse Bind Test+++++++++++++

Received Bind call from MT-SQL01

Trying Reverse Bind to MT-SQL01

Reverse Binding success: MT-SQL03-->MT-SQL01

++++++++++++Reverse Bind Test ENDED++++++++++

++++++++++++Start DTC Binding Test +++++++++++++

Trying Bind to MT-SQL01

Received reverse bind call from MT-SQL01

Binding success: MT-SQL03-->MT-SQL01

++++++++++++DTC Binding Test END+++++++++++++

Instance setting

Enabling the server's safety mechanism to ensure the servers can stay in sync.

This concludes the list of tasks needed to enable a successful distributed transaction.

Below I have detailed the steps to create test tables and a test package to confirm that the DTC works well.

Servers and Databases and Tables.

First I created 2 tables on both servers/instances in the tempdb database. The servers are MT-SQL01 and MT-SQL03. Both SQL Server instances are also seperate physical machines.

On server MT-SQL03 I executed the below statement:

USE [tempdb]
GO
CREATE TABLE [dbo].[TestTable_SQL03](
[TestColumn] [varchar](50) NULL
) ON [PRIMARY]
GO

while I executed the below statement on server MT-SQL01

As can be noted, on server MT-SQL01 table TestTable_SQL01 I have created a Primary Key constraint on the Test Column. Therefore I cannot insert duplicate entries into this table.

USE [tempdb]
GO
CREATE TABLE [dbo].[TestTable_SQL01](
[TestColumn] [varchar](50) NOT NULL,
CONSTRAINT [PK_TestTable_SQL01] PRIMARY KEY CLUSTERED 
(
[TestColumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SSIS Package

I have created a simple package to demonstrate how distributed transactions work.

The SSIS package below will be used to test the Distributed transaction. The plan is that on running the package the first time, everything works OK, i.e. an entry is inserted into both tables. However on running the package the second time, task MT-SQL01 will fail due to the Primary Key constraint and this will roll back also the MT-SQL03 transaction.

Execute Task MT-SQL03 is connecting to server MT-SQL03 and executing a simple INSERT statement.

INSERT INTO [tempdb].[dbo].[TestTable_SQL03]
           ([TestColumn])
     VALUES
           ('TestColumn_03')

Execute Task MT-SQL01 is connecting to server MT-SQL01 and executing another simple INSERT statement.

INSERT INTO [tempdb].[dbo].[TestTable_SQL01]
           ([TestColumn])
     VALUES
           ('TestColumn_01')

To get the distributed transactions to work, an important setting which is the TransactionOption must be correctly configured.

In this case, this is how I've set it up for all the required components:

On running the package the first time, it will all execute successfully, a record being inserted into both the TestTable_SQL03 and TestTable_SQL01

On running the package again, the MT-SQL01 will fail because the package is trying to insert a duplicate entry. Therefore, the whole container transaction is rolled back and no entry is inserted into the TestTable_SQL03 (on which there is no PK constraint).

This proves that the Distributed transactions are working correctly.

Common Errors

Below are some errors which may be encountered while setting up DTC.

[Connection manager "MT-SQL03\DEV_MSSQLSERVER"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

This means that the Distributed Transaction Coordinator Service is not running on the MT-SQL03 Server. The service must be Started!

[Connection manager "MT-SQL03\DEV_MSSQLSERVER"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions.".

This means that the Network DTC is currently disabled. Please refer to the server's Local DTC properties (Security Page) on server MT-SQL03 to properly configure access.

[Connection manager "MT-SQL03\DEV_MSSQLSERVER"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Login timeout expired".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ".

This normally indicates that the firewall is blocking the connection

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

The error message is self explanatory! the DTC Service is not running.

References

Mastering SQL Server 2008 By Michael Lee & Gentry Bieker

You can download DTCPing from here

You can download the DTCTester tool from here

Enable Network Access Securely for MS DTC

Configuring Microsoft Distributed Transaction Coordinator (DTC) to work through a firewall

How to troubleshoot MS DTC firewall issues