by Thomas | Jul 7, 2010 | BizTalk
When we recently configured the ESB Portal website, we encountered a number of permissions-related issues. Our initial experience was the same as that of many others who have discovered that the Portal’s included permissions script is inadequate. Once we granted additional permissions to the existing database roles the permission errors cleared up – but we couldn’t overcome one last error: Invalid object name ‘BizTalkMsgBoxDb.dbo.ProcessHeartbeats’.
As most of you know, Microsoft decided not to ship the source code for the ESB Toolkit 2.0 aside from the Management Portal “sample”. In order to diagnose this error, I pulled out Red Gate’s .NET Reflector and started digging through disassembled code. The source of this particular issue lies in the ESB.BizTalkOperationsService.
In our environment, as in most high-performance BizTalk installations, the message box database is on a different SQL Server instance than the other BizTalk databases. In a great oversight, the BizTalkOperationsService was hard-coded to expect the message box database to be present on the same server as the management database. The operations service attempts to run this SQL query on the database that holds the management database: SELECT 1 FROM BizTalkMsgBoxDb.dbo.ProcessHeartbeats with (nolock) where uidProcessID='{0}’.
You’ll note another potential issue here: the message box database name is hard-coded in the query. That has also caused trouble for people.
To solve this problem, I first used .NET Reflector to re-create Visual Studio 2008 projects for the ESB.BizTalkOperationsService ASMX web service and Microsoft.Practices.ESB.BizTalkOperations.dll class library. Once the projects were cleaned up and building successfully, I modified the code to query the management database for the primary message box database name and server using the existing stored procedure adm_MessageBox_Enum. With that information in hand, I updated the code to create a connection string to the message box database and execute the ProcessHeartbeats query there. I also removed the hard-coded database name.
I tested my version of the BizTalkOperationsService using the ESB.BizTalkOperations.Test.Client included with the Toolkit source code and verified that everything still worked as expected.
Since this was a fairly time-consuming issue to fix and it is a problem that should affect a good percentage of the installations out there, I decided to post my updated service and source code (download link at the end of this post). I cannot make any guarantees about the correctness of the code, so consider it as-is and use at your own risk. (That said, I believe that it works just fine.)
Let’s hope that Microsoft reconsiders its unfortunate decision not to ship source code.
ESB.BizTalkOperationsService.zip
Like this:
Like Loading...
by Thomas | Apr 29, 2010 | BizTalk
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:
- Receive a file containing multiple data records (i.e. an interchange) in XML format
- Use the standard XML Disassembler pipeline component to split the interchange into multiple messages
- Assign a static ESB Toolkit 2.0 itinerary to each message (still in the pipeline)
- Execute the itinerary as follows:
- Map to canonical format (itinerary step 1 – messaging)
- Execute a custom orchestration “service” to send the message to a SQL Server stored procedure (itinerary step 2 – orchestration)
- 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.
Like this:
Like Loading...
by Thomas | Jul 18, 2009 | BizTalk
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.
Like this:
Like Loading...
by Thomas | Jan 30, 2009 | BizTalk
I neglected to post about this at the time, but back in June 2008 I had an article published in .NET Developer’s Journal titled “A Walk Through the Process: Creating Services with Contract-First Design Using BizTalk Server 2006 R2 and Windows Communication Foundation.” The article is available on the .NET Developer’s Journal website and the sample code is attached to this post.
BizTalk makes a great platform for true contract-first service development because it is designed around messaging. One of the first things one usually does on a BizTalk project is to load or create XML schemas. With BizTalk Server 2006 R2’s support for Windows Communication Foundation, it’s a natural platform upon which to build services using contract-first design.
The article assumes that you have worked with BizTalk before, but otherwise it is a step-by-step walkthrough of the process to create schemas, create sample orchestrations to carry out the work behind the service interfaces and to publish the schemas as WCF services.
designserviceswithbts2006r2-wcfsample.zip
Like this:
Like Loading...
by Thomas | Jul 18, 2008 | BizTalk
One of the things I’ve been doing over the last six months is enhancing two well-known BizTalk Server tools: the Deployment Framework for BizTalk and the GenerateTypedBAMAPI tool. I also created a CodePlex project of my own, Environment Settings Manager, which was incorporated into the Deployment Framework and is very useful in its own right.
I never took the time to stop and mention all of the changes and new features, so here’s a summary:
Deployment Framework for BizTalk:
- Contributed the Environment Settings Manager project, which includes an environment settings spreadsheet that looks like the original one from Loren Halvorson, but does not use any macros and supports saving as either binary XLS or XML (SpreadsheetML). The companion command-line utility can take in either type of file and export the environment-specific XML settings files that the Deployment Framework uses to create environment-specific configuration files on the fly.
- Added initial support for side-by-side deployment (multiple BizTalk app versions installed at once): added a “project” version property, added the version to the installer title and default install folder path, added version number to the BizTalk application name and SSO affiliate app name
- Added initial support for deployment of BAM activity definitions
- Updated WiX (for MSI generation) to 3.0.x, in the process drastically simplifying GenericBizTalkMSI.wxs and improving the MSI user interface
- Modified the installer to completely clean up on uninstall, removing all dynamically generated files
- Modified SetEnvUI.exe (the wizard) to open the file browse dialog in the current directory vs. C: and replaced a P/Invoke call with a Framework call
- Modified the installer to install “for all users” vs. only for the current user
- Simplified and improved the clarity of the checkbox captions in the server deploy/undeploy wizard
- Added an attribute to the UpdateSSOConfigItem Nant task for the BizTalk Application name
- Made minor documentation updates and replaced the HTML version of the help with a PDF version
- Updated to NAnt 0.85 release from RC1
- Included the Visual Studio Tools menu install scripts in the Sample ZIP file
- Cleaned up the project a bit, removing unused files, etc.
GenerateTypedBamAPI:
- Checked in the source code for the first time
- Added support for a relative path to the Excel workbook vs. explicit path only
- Added the partial keyword to the generated API class
- Added a method to the activity classes to enable continuation and return a continuation ID
- Added a command-line parameter to specify the .NET namespace of the generated code
- Changed “ESApi” to EsApi to conform to .NET capitalization conventions
- Added “Activity” to the end of the activity class names
- Explicitly passed “en-us” culture info to the Excel methods to avoid a globalization bug reported by users
- Made other minor code improvements, removed XSLT resource class in favor of Visual Studio’s built-in resource support
While I was at it, I added to the GenerateTypedBamApi project a completely new command-line utility program called ExportBamDefinitionXml that can extract the BAM definition XML from an XLS file and write it out to a file. This utility can be used to automatically keep a BAM XML in sync with a BAM XLS file during local or automated builds. The code reuses some of Darren’s code that was already present in GenerateTypedBamApi.
The majority of the changes listed above are not yet built into official “releases” on CodePlex, but hopefully that will happen soon — I just need to coordinate with the project owners. In the meantime, hop over to the Source Code tabs and grab the latest from there. Please feel free to enter new Issues and Discussions topics in the projects with your ideas and bug reports.
Enjoy!
Like this:
Like Loading...
