Performance Tips for the WCF SQL Adapter for BizTalk Server

I’ve recently experienced (and largely solved) some serious performance issues with the BizTalk WCF Adapter for SQL Server (aka WCF-SQL).  This post describes the problem and the solution that I discovered.

The BizTalk application in question has a fairly simple data flow:

  1. Receive a file containing multiple data records (i.e. an interchange) in XML format
  2. Use the standard XML Disassembler pipeline component to split the interchange into multiple messages
  3. Assign a static ESB Toolkit 2.0 itinerary to each message (still in the pipeline)
  4. Execute the itinerary as follows:
  5.   Map to canonical format (itinerary step 1 – messaging)
  6.   Execute a custom orchestration “service” to send the message to a SQL Server stored procedure (itinerary step 2 – orchestration)
  7.   Route the message to an off-ramp

In this application I was doing things “the ESB Toolkit way” so everything was fairly dynamic.  The maps were identified and executed on the fly and the stored procedure was called through a dynamic one-way port configured on the fly by an ESB resolver.  If you’re not using the ESB Toolkit, keep reading – these tips still apply to you.

There’s really not much to the application.  A batch of records comes in, gets split up into individual messages, and each message gets sent to a stored procedure in SQL Server using the WCF SQL adapter.  Except the performance was terrible.  On my (not-so-quick) machine, a batch containing 100 records was taking over one minute to process!

I ruled out stored procedure performance as a factor by simply changing it to immediately return without doing any work.  Surprisingly, that barely increased the speed (a few seconds at most) even though the stored procedure call now returned instantly.

I discovered a couple of things with SQL Profiler that led to the solution.

First, we were sending an XML message to the stored procedure, so the parameter was typed as ‘xml’ (the SQL Server XML data type).  However, BizTalk can’t send messages to SQL Server in that format.  It always sends them as a Unicode string.  SQL Server (or the .NET SQL client that underlies the adapter) was automatically inserting a CONVERT() on each call to turn the Unicode string into a variable of type ‘xml’, then executing the stored procedure.  To avoid this “magic” conversion, we converted the stored proc parameter to NVARCHAR(MAX) and added a CONVERT() inside the stored proc.  That moved the CONVERT() into the stored proc where SQL Server could pre-compile, optimize and cache it along with the rest of the code.

Always type your stored procedure parameter(s) as NVARCHAR(MAX) when sending an XML message to SQL Server.

Second, the major performance loss was related to the fact that this adapter is based on WCF and the fact that we were using a dynamic send port.  I realized that for every call to the stored procedure, there was also a second dynamic SQL call to obtain metadata about the stored proc’s parameters.  This was effectively doubling the number of calls to SQL Server, and running a relatively slow query to boot.

For those of you who have worked with WCF, hopefully you know that creating WCF proxy clients is a relatively expensive operation.  It is always best to cache proxy objects or at least a ChannelFactory, or take advantage of the built-in caching added in .NET 3.0 SP1.  Details on all of that are here.  The important thing is that if BizTalk is not able to cache the WCF proxy objects that it uses to talk to the WCF SQL adapter, then performance is definitely going to be bad.

That’s where the dynamic port comes in.  Since the port is dynamically configured on every call to SQL Server, the proxy objects are not cached.  This explained a lot!  On every call we were taking a hit from creating and setting up a WCF proxy object, then taking a second hit because the WCF adapter has to obtain metadata about the stored procedure before it calls it.

Avoid dynamic ports with the WCF adapters, and in particular the WCF SQL adapter, in favor of static ports with a dynamic Action.

The solution in my case was to create a static WCF-Custom port configured for the SQL adapter, leaving the Action setting blank (because we call multiple stored procedures).  Instead of fully configuring the port on the fly, I now dynamically configure only the Action property.  This produced a 45-50% increase in performance.

The end result of these changes was that processing 100 messages went from over 65 seconds to about 20 seconds.

The final tip is only relevant when you are using a fully dynamic send port with any of the WCF adapters on BizTalk 2009 and is described in this post.  Here’s another post on how to do it with the ESB Toolkit.  Performance can be modestly improved by explicitly setting the EnableTransaction and IsolationLevel context properties.  In my fully dynamic scenario, this improved performance by about 25%.  I am not clear how these settings interact with the SQL binding’s own useAmbientTransaction property.

When using dynamic ports with the BizTalk 2009 (only) WCF adapters, set the EnableTransaction and IsolationLevel context properties.

Our application is now performing at the speed that we expected, and hopefully these tips will give your own apps a nice speed boost too.

Orchestration Designer Views of the BizTalk ESB Toolkit 2.0 Transform and Routing Services

If you’ve worked with the BizTalk ESB Toolkit 2.0, you’ve probably used Orchestration Extenders in your itineraries.  The Toolkit includes two default orchestrations (services) named Microsoft.Practices.ESB.Services.Transform and Microsoft.Practices.ESB.Services.Routing.  These service names correspond to two orchestrations installed in the Microsoft.Practices.ESB BizTalk application, named Microsoft.Practices.ESB.Agents.Transform and Microsoft.Practices.ESB.Agents.Delivery, respectively.

These two orchestrations/services are somewhat mysterious since Microsoft chose not to release the Toolkit source code, and there is no detailed step-by-step documentation that describes what each orchestration does – not even a picture of the orchestration design surfaces.

After running some itineraries through BizTalk that used these two orchestrations, I launched the resulting instances in the Orchestration Debugger.  As you probably know, the Orchestration Debugger shows a close representation of the Visual Studio Orchestration Designer view, albeit without ports and with no ability to drill into Expression shapes, etc.

I screen captured the orchestration views in the debugger and made some annotations to make the Send and Receive shapes more apparent.  Since these will probably be useful to many other BizTalk developers, I’m sharing them below in PDF and PNG formats:

Microsoft.ESB.Agents.Transform.png
Microsoft.ESB.Agents.Transform.pdf
Microsoft.ESB.Agents.Delivery.png
Microsoft.ESB.Agents.Delivery.pdf

UPDATE (04/27/2010): After my original post, I was able to extract the original ODX XML’s and re-create an ODX file for each orchestration. This will let you open up either orchestration in the Orchestration Designer and view all of the code in Expression shapes, etc. They are posted below.

Microsoft.ESB.Agents.Transform.odx
Microsoft.ESB.Agents.Delivery.odx

Using the BizTalk ESB Toolkit 2.0’s ESB Configuration Tool on Windows XP

Have you ever tried to configure the BizTalk ESB Toolkit 2.0 on Windows XP?  If so, you probably didn’t get too far.  According to the system requirements, the ESB Toolkit requires Windows Server 2003 or newer.  However, the original release – ESB Guidance 1.0 – ran just fine on Windows XP.

If you’ve tried to use the ESB Configuration Tool (EsbConfigurationTool.exe) on Windows XP, you probably made it through the first step which creates the Exception Management database.  The next step, Exception Web Services, probably didn’t go so well.  You may have gotten an error like The following exception occurred while retrieving member “Create”: “The system cannot find the path specified.”

The reason for the error is that the tool assumes that you’re using Windows Server 2003 or newer – which implies IIS 6 or newer – all of which have the concept of IIS Application Pools.  Since IIS 5.1 (Windows XP) does not have AppPools, the Configuration Tool’s script blows up.

This is hardly a reason to keep us from using the ESB Toolkit 2.0 on Windows XP!  Who wants to create all of those vdirs and databases by hand?  Not I…  All that was necessary to make the tool work on XP was to slightly modify the built-in PowerShell scripts to avoid referencing IIS AppPools.  I decompiled EsbConfigurationTool.exe, tweaked the scripts, removed the strong name key (since it’s no longer signed by Microsoft) and recompiled the EXE.  Voila!  The tool now works perfectly on Windows XP.

I am attaching the modified version of the tool to this post in the hopes of saving some of you from the same hassle.  Just unzip and overwrite your existing EXE.  Enjoy!

Error Upgrading a Solution to BizTalk 2009: Child element <BIZTALK> of element <VisualStudioProject> is not valid

If you are planning to upgrade a BizTalk solution from 2006 to 2009 RTM, don’t expect that it will be simple. I recently took a good BizTalk 2006 R2 solution and opened it in VS2008 w/ BT2009, got the project upgrade wizard, stepped through it, and at the end of the process all of the BizTalk projects showed as “unloaded.” They all failed conversion with the error Error converting project file. Child element <BIZTALK> of element <VisualStudioProject> is not valid.

<VisualStudioProject><BIZTALK> etc. etc. is the normal, valid structure of an old btproj file!

I did some quick searches and other people had reported this during the beta. Microsoft did not offer any thoughtful, tested solutions to the problem (one solution was to make sure you are opening your actual BT2006 SLN file — duh.) They closed the bug report on Connect as “fixed.”

Some people reported problems with conversion when the project files were bound to source control. I don’t have the solution bound to source control and it still failed. I created a brand new SLN file in VS 2008 and added the projects to it, to no avail. Not wanting to waste any more time, I created a new SLN and all new project files, then copied over the BT2006 artifacts and included them in the new project files. Everything was OK after that, as one would expect.

I created a new issue on Microsoft Connect regarding this issue. If you are encountering it, please vote on it.

There’s no better way to put a sour taste in a customer’s mouth than to run into a barrier like this right out of the box.  Microsoft should not have shrugged this off so quickly when it was reporting during the beta period.

UPDATE: The most common reason for this problem is renaming the default project configurations Development and Deployment — a perfectly normal activity that can be done through Visual Studio. According to Microsoft, changing the names is unsupported for BizTalk projects before 2009. See related posts here.

%d bloggers like this: