Resolving Distributed Transaction Issues with BizTalk, MS DTC and Clustered MSSQL Servers
Introduction
After a recent storage migration of a Windows 2008 R2 cluster that involved removing the clustered DTC (Distributed Transaction Coordinator) starting seeing SQL transaction errors from the BizTalk server that was connecting to the SQL database instance running on the clustered servers. These are the steps followed to resolve the issue.
Microsoft Distributed Transaction Coordinator (MSDTC) is an important component in a BizTalk environment. Especially in multi-server environment it is mandatory and needs to setup and configured properly before one starts configuring BizTalk features like group, BRE or BAM. During configuration of these BizTalk features, databases like BizTalkMsgBoxDb, BizTalkMgmtDb, BizTalkDTADb (tracking archiving), BizTalkRuleEngineDb, or BAMPrimairyImport and other (BAMStar, ect) are created on database server. MSDTC is component inside Component Services (Windows 2003, Windows 2008, Windows 2008 R2).
BizTalk DTC Settings
After doing a bit of digging found several articles that describe the changes to Local DTC that need to be done in a standalone server configuration. In this article at MSDN it describes the post installation changes needed for BizTalk Server 2013 and 2013 R2. There is also another article on MSDN that explains the different errors that can occur if this setting is not correct.
One thing missing from the articles is what to do with a clustered MSSQL server and these required setting. If you change the settings on the Local DTC and the Clustered DTC the clustered DTC service will fail to start in the cluster and you will not be able to go back and undo the changes. Only way to fix it is to stop all SQL instances running on the cluster and delete and recreate the DTC clustered service. The solution to the settings dilemma is easy to correct. The changes needed are only done to the Clustered DTC component.
What are the changes required for BizTalk to function connecting to a clustered MSSQL server? According to the articles referenced previously they are:
Configuration Option | Default Value | Recommended Value |
Network DTC Access | Disabled | Enabled |
Client and Administration | ||
Allow Remote Clients | Disabled | Disabled |
Allow Remote Administration | Disabled | Disabled |
Transaction Manager Communication | ||
Allow Inbound | Disabled | Enabled |
Allow Outbound | Disabled | Enabled |
Mutual Authentication Required | Enabled | If running MSDTC on cluster. No Authentication Required, otherwise Enabled. This is a cluster so Disabled |
No Authentication Required | Disabled | Enabled |
Enable XA Transactions | Enabled | Disabled |
Enable SNA LU 6.2 Transactions | Enabled | Enabled |
Clustered DTC Settings
Now that we know the requirements for BizTalk to work how do we make the changes so that we don’t break DTC on the cluster. As we see the above requirements they refer to Local DTC component settings, so how do we make these changes for the cluster.
When the DTC service is added / defined to a cluster it creates a Clustered DTCs folder and adds the clustered DTC object that was created. This is where we need to make the recommended changes for BizTalk and not to Local DTC.
Note: After applying these changes, the MSDTC service will be restarted.
To access the MSDTC security configuration options follow these steps:
- Click Start, click Run, and type dcomcnfg to launch the Component Services Management console, or if you prefer menu selections to open click Start, click Administrative Tools and click Component Services.
- Click to expand Component Services and click to expand Computers.
- Click to expand My Computer, click to expand Distributed Transaction Coordinator, click to expand Clustered DTCs and right-click <ClusterDTC Object>DTC, and click Properties.
Click the Security tab of the <ClusterDTC Object>DTC Properties dialog. Upon viewing the properties we see the default settings for this component.
Now it is time to make the changes defined in the section above.
- Network DTC Access = Enabled
- Allow Inbound = Enabled
- Allow Outbound = Enabled
- No Authentication Required = Enabled
- Enable SNA LU 6.2 Transactions = Enabled
After the changes have been made we should now look as such.
Click OK to apply the changes and you will receive a popup stating that the service will be restarted, click yes and another popup box will appear, click OK. Next we should check the DTC clustered service to make sure it is online and running.
To open Failover Cluster Manager click Start, click Administrative Tools, click Failover Cluster Manager. Expand the cluster object, expand Services and Applications and double-click on the DTC service. On the screen look for the Microsoft Distributed Transaction Coordinator and verify it is online.
At this point retry the BizTalk server which should be now correctly communicating to the clustered SQL server.
Conclusion
As we can see we now have a correctly working DTC service on a clustered MSSQL server for use with Biztalk using the recommended settings for Local DTC applied to Clustered DTC’s.
Leave a Reply