Share via

BizTalk: WCF-based SQL Adapter and Troubleshooting

  • Article

Overview and Architecture

There are two SQL adapters:

  • WCF-based SQL adapter available with the BizTalk Adapter Pack 2.0 and BizTalk Adapter 2010.
  • SQL adapter available with BizTalk Server, which is deprecated since BizTalk Server 2009, as documented at Deprecated Features, Tools, and APIs.

The WCF-based SQL adapter does not require BizTalk Server to be installed. It can be used to communicate with SQL Server by using the following methods:

  • A BizTalk application
  • A WCF service model
  • A WCF channel model

For a list of operations that can be performed with the WCF-based SQL adapter, see What Operations Can Be Performed Using the Adapter?

Troubleshooting

1. To troubleshoot run-time issues, you can enable WCF tracing by modifying the <configuration> section in the BTSNTSvc.exe.config or BTSNTSvc64.exe.config file. Diagnostic Tracing and Message Logging provides an example.

  1. You may notice deadlocks and/or blocking on the computer running SQL Server. By default, the WCF-based SQL adapter starts a serialized transaction. You can set the UseAmbientTransaction property to false and define the desired transaction isolation level within the stored procedure.

  2. The legacy SQL adapter can be used to insert parent-child records, as described in Working With BizTalk Adapter for SQL Server. The WCF-based SQL adapter does not provide out-of-the box functionality for inserting parent-child records. As a result, you can use the approach described in Inserting parent child records with Identity column using WCF-SQL Adapter in one transaction.

  3. When using the WCF-based SQL adapter on a receive location, you might see the following behavior:

  • If the adapter continually polls but doesn’t receive any data, process memory usage might grow and cause throttling.
  • The receive location might shut down after 10 minutes with the following error:

System.Transactions.TransactionAbortedException: The transaction has aborted.

In these situations, you can set the ReceiveTimeout property to the maximum value, which is 24.20:31:23.6470000 (24 days). If there is a slow network or slow server on the send side, the SendTimeout property can also be increased.

See Working with BizTalk Adapter for SQL Server Binding Properties.

  1. You should use the new WCF-based SQL adapter. The BizTalk Adapter Pack Migration Tool available at http://www.microsoft.com/download/details.aspx?id=7840 can help developers migrate from the legacy SQL adapter to the new WCF-based adapter.

BizTalk Adapter Pack 2010 - WCF SQL Adapter
BizTalk Server 2010 Adapter Pack - SQL Azure
BizTalk: WCF Adapters and Troubleshooting

See Also

Another important place to find a huge amount of Azure BizTalk Services related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.