Bora Bora

This is the view from our honeymoon hotel in Bora Bora, French Polynesia (Tahiti):

MsgBoxViewer to quickly find BizTalk production issue

We’ve had a recent deployment to production that did not go according to plan, or so we thought at first. It is an upgrade of a BizTalk solution from BizTalk 2004 to 2006 R2. It integrates a website, a CRM system, a bank system, and a fulfillment house.

We deployed the solution to our test environment, fixed some issues that were found, and everything worked fine. The testers and business users were happy and the system was working as expected. The BizTalk environment had already been built and configured the same as in test by the client. There was already another solution running on the test and production servers, and it seemed to be running fine.

So came the day of the deployment to production. Procedures and release notes were followed, end systems were switched over, MSI files imported, and… BizTalk was running very slowly. The website and the CRM systems were receiving timeouts from BizTalk. A decision was made to rollback and investigate the cause.

We started by comparing the configurations on the new BizTalk 2006 R2 server in production and the new BizTalk 2006 R2 server in test. Then we compared the new BizTalk 2006 R2 server in production and the old BizTalk 2004 server in production. Most ideas we had like “could it be an issue with the different .NET versions?” or “could an end system be running slower in production?” did not work because of one of these two questions: then why does it work in test? Or: then why does it work in BizTalk 2004?

So I asked our system administrator to run the MsgBoxViewer tool on the new test and production servers. The MsgBoxViewer is an awesome tool developed by one of the top Microsoft BizTalk support engineer. It analyses the current configuration and state of both your BizTalk and SQL machines, performs a list of checks and raises warnings (in yellow) and critical alerts (in red). It also gives you a lot of information about what is currently happening on your server. For example, a list of row counts for the tables in your MsgBox database, or the TCP/IP registry configurations on your servers. The latest version is available here.

Anyways, what the MsgBoxViewer tool came back with was pretty alarming: in the BizTalk 2006 R2 production, all the hosts had message publishing throttling turned on due to reason 6: DB Size. From the resulting HTML file, you can scroll down to the end to see the Summary Report, and at the end of the report it had the throttling settings:

So if you have a look at the inbound host throttling page on MSDN you can see that the message publishing throttling is enabled with reason 6 when “Host message queue size, the spool table size or the tracking table size exceed the specified threshold.” This is the reason why BizTalk was running so slow, it was throttling down its message publishing and taking much longer than normal.

This led me to have a look at the the “MsgBox Db : Get MsgBox Tables Size” section of the MsgBoxViewer report and, sure enough, the Spool table had over 511 thousand rows(!!!). This should usually be under 500 rows. The usual reason why this happens is that the SQL Server Agent jobs created by BizTalk are not running, or aren’t keeping up.

In our case the culprits were:

• “TrackedMessages_Copy_BizTalkMsgBoxDb” job was failing.
• DTA Purge and Archive (BizTalkDTADb) job was disabled in production but enabled in test.

These had been configured correctly by the client in the test system a few weeks before, but they did not realize that not implementing it into production straight away would make such an impact. After mentioning to them this issue, the purge and archive job was configured and enabled, and the tracked messages job was also configured correctly. Now that these ran without failing a few times, we ran the MsgBoxViewer tool again and there are only 128 rows in the Spool table and none of the hosts are being throttled. Nice!

A good lesson to learn or to be reminded of is to always always make sure the BizTalk SQL jobs are running correctly.

Note: running the MsgBoxViewer tool is pretty safe, but it links to a few pages that recommend changes to the system. Make sure you know what you are doing and have tested before making any changes to your production system.

BAM Deployment Gotchas

This is a quick post for my future reference and I hope it helps someone in the future…

If you deploy a view to BAM then try to add a user or windows group to the view using the “bm.exe add-account” command and it fails telling you that “The SQL role for the specified view is missing”, then you need to remove the view and re-add it. As the message says, the role for the view does not exist on the server. Prior to adding the account to the view BAM calls the bam_Metadata_ManageSecurity stored procedure in the BAMPrimaryImport database. The first thing this does is check if the SQL database role called “bam_viewname” exist… For some obscure reason it wasn’t created the first time and removing and re-adding the view worked.

The second one I ran into today was that my login had correctly been added to the BAM Portal users windows group but I still kept getting asked to enter my credentials when browsing to it and it still failed with “URL authorization failed for the request”messages in the event log from ASP.NET 2.0. To fix this I needed to update the w3svc service to use NTLM as the authentication provider:

c:\Inetpub\AdminScripts\cscript adsutil.vbs SET w3svc/NtAuthenticationProviders “NTLM”

Updating repeating nodes in an XML Document with the same value using XPathMutatorStream

 It’s been a while since I’ve posted some BizTalk coding tidbit… Busy times!

 I often have to set the value of an element under a message’s repeating node with the same value. Of course, normally you’d use a map, but what if the value isn’t in the source message? The last time I ran into this was in a project where orders coming into BizTalk via a web service had to be inserted into an AS400 system. The way the already existing AS400 tables were designed to link the order header and the order details was to have the same GUID inserted into both tables, so the order items schema had a GUID field for each line of the order.

 I couldn’t use a distinguished field to set the value of the GUID in the order lines schema. This is, of course, because it’s under a repeating node. Even if you have a second message with the the value (say, for example, you use the SQL Adapter and a stored procedure to insert the order header and it returns the newly generated ID in a message), you would then have to use a map with multiple inputs… So it ocurred to me that this could be a great opportunity for the XPathMutatorStream class! This class is usually used in custom pipeline components. It has been blogged about by Martijn Hoogendoorn and Patrick Wellink amongst others and I wanted to share how I used it. Say we have the following schemas and map:

And we make sure the WEBGUID element on the destination schema is populated with a blank value by selecting it and setting its “Value” property to <empty> (this will create the element with an empty value on the destination message):

 Then, I created the following helper c# class library to perform the update. Don’t forget to reference Microsoft.Biztalk.Streaming and Microsoft.BizTalk.XPathReader (get them from the GAC). 

using System;

using System.Xml;

using System.IO;

using Microsoft.BizTalk.XPath;

using Microsoft.BizTalk.Streaming;

 

namespace MyCompanyUtilities

{

    [Serializable]

    public class XPathUtil

    {

 

        private string newValue = “”;

  

        public string NewValue

        {

            get { return newValue; }

            set { newValue = value; }

        }

 

        private string xpathToUpdate = “”;

 

        public string XpathToUpdate

        {

            get { return xpathToUpdate; }

            set { xpathToUpdate = value; }

        }

 

        //Updates an xml document with all matches in an xpath with a value

        public XmlDocument updateWithXpath(XmlDocument xDoc)

        {

            //Check for valid arguments

            if (newValue == “”)

                throw new ArgumentException(“NewValue”);

            if (xpathToUpdate == “”)

                throw new ArgumentException(“XpathToUpdate”);

 

            XmlDocument retXml = new XmlDocument();

 

            //Setup the stream and rewind

            using (MemoryStream memstream = new MemoryStream())

            {

                //Copy xml document to memory stream and rewind

                xDoc.Save(memstream);

                memstream.Position = 0;

 

                //Define the XPathMutator

                XPathCollection queries = new XPathCollection();

                queries.Add(new XPathExpression(xpathToUpdate));

                ValueMutator xpathMatcher = new ValueMutator(this.XPathCallBack);

 

                //Get resulting stream into response xml

                retXml.Load(new XPathMutatorStream(memstream, queries, xpathMatcher));

            }

 

            return retXml;

        }

 

        private void XPathCallBack(int matchIdx, XPathExpression matchExpr, string origVal, ref string finalVal)

        {

            finalVal = newValue;

        }

    }

}

 So from there it was just a case of referencing the c# class library from my BizTalk solution, and calling the helper class from the orchestration in a construct shape (in the same construction shape as the map). Here’s how the sample orchestration looks like:

  I created a variable in the orchestration for the class above. The “Set GUID” construct shape has the following: 

xpathHelper = new MyCompanyUtilities.XPathUtil();

xpathHelper.XpathToUpdate = “/*[local-name()='Root' and namespace-uri()='http://MyCompany.OrderItems']/*[local-name()='TBWEBITEM' and namespace-uri()='']/*[local-name()='WEBGUID' and namespace-uri()='']“;

xpathHelper.NewValue = msg_WebOrder(BTS.MessageID);

msg_OrderItems = xpathHelper.updateWithXpath(msg_OrderItems);

  So when I input the following message: 

<ns0:Root xmlns:ns0=“http://MyCompany.WebOrder“>

  <WebOrder>

    <OrderHeader>

      <OrderNumber>1</OrderNumber>

    </OrderHeader>

    <Items>

      <Item>

        <ItemNumber>23</ItemNumber>

        <ItemDescription>Cheese</ItemDescription>

        <Quantity>10</Quantity>

        <UnitPriceExcTax>5.4</UnitPriceExcTax>

      </Item>

      <Item>

        <ItemNumber>14</ItemNumber>

        <ItemDescription>Ham</ItemDescription>

        <Quantity>10</Quantity>

        <UnitPriceExcTax>4.0</UnitPriceExcTax>

      </Item>

      <Item>

        <ItemNumber>12</ItemNumber>

        <ItemDescription>Bread</ItemDescription>

        <Quantity>1</Quantity>

        <UnitPriceExcTax>3.30</UnitPriceExcTax>

      </Item>

    </Items>

  </WebOrder>

</ns0:Root>

 The output message is: 

<ns0:Root xmlns:ns0=“http://MyCompany.OrderItems“>

  <TBWEBITEM>

    <WEBGUID>d181486e-649b-4d29-87ab-a811bb175ef3</WEBGUID>

    <WEBITMNBR>23</WEBITMNBR>

    <WEBITMDSC>Cheese</WEBITMDSC>

    <WEBQTY>10</WEBQTY>

    <WEBPRC>5.4</WEBPRC>

  </TBWEBITEM>

  <TBWEBITEM>

    <WEBGUID>d181486e-649b-4d29-87ab-a811bb175ef3</WEBGUID>

    <WEBITMNBR>14</WEBITMNBR>

    <WEBITMDSC>Ham</WEBITMDSC>

    <WEBQTY>10</WEBQTY>

    <WEBPRC>4.0</WEBPRC>

  </TBWEBITEM>

  <TBWEBITEM>

    <WEBGUID>d181486e-649b-4d29-87ab-a811bb175ef3</WEBGUID>

    <WEBITMNBR>12</WEBITMNBR>

    <WEBITMDSC>Bread</WEBITMDSC>

    <WEBQTY>1</WEBQTY>

    <WEBPRC>3.30</WEBPRC>

  </TBWEBITEM>

</ns0:Root>

 So the helper class updated all the WEBGUID elements with the same value without us having to use a map with multiple inputs, or performing a loop inside the orchestration, or browsing through the message using XpathNavigator. Of course you can use the XPathMutatorStream with any repeating node you want or with a list of different xpath queries that will match somewhere in the message. You can also add some funky logic in the XPathCallBack method to have it use different values depending on the original value.

Dowload the sample here.

Note: I could have also used a custom pipeline functoid to get the Message ID from the source schema by using the “Custom GetContextProperty functoid“.

ACSUG meeting with Ron Jacobs

Ron Jacobs is a legend. He spent a little less than a week over here in New Zealand and presented at four meetings or more every day. When it came to closing it with a golden key  by presenting at the inaugural Auckland Connected Systems User Group meeting he was as active and enthusiastic as ever. As he pointed out the “V” energy drink also helped! And all this was after doing a similar tour around Australia.

His talk was on creating and consuming RESTful Web Services with WCF. As a lot of you know REST is an emerging architecture for exposing services over the Web that takes advantage of the features of the original HTTP protocol like caching, stateless services and easy requests (even notepad can call them). It is perfect for high user load and is being used by big names like Flickr, Yahoo, Google amongst others. I will be posting a few of my notes in my next post.

Thanks to all that attended, the meeting was a success (and congratulations to those that won the two games drawn at the end of the meeting!). The meeting slides can be found on the meeting page under attachments, and the sample code are on Ron’s blog. We will upload some pictures taken during the meeting in the coming week and put them on the photo galleries page of the user group website.

I’d like to thank Darryl Burling for noticing that the user group and Ron’s dates coincided and making it happen, and Mark Carroll for hosting Ron while he was over in New Zealand and helping me with setting up the meeting.

If you are in Auckland this coming week make sure you come to the Ellerslie .NET User Group meeting on Wednesday 28 May to see Alex Henderson present on the REST in .NET topic. It will be valuable for those that came to the ACSUG meeting to really understand the subject and an opportunity to learn it for those that couldn’t make it to Ron’s talk. Don’t forget to register by following the link on the user group website.

BizTalk WCF Adapter Pack Articles on TopXML.com

 Richard Seroter has finished writing his articles on TopXML.com about BizTalk and WCF. He goes into a lot of detail on all that can be done with the WCF adapters in BizTalk and also the WCF Adapter Pack for Line of Business Applications. If you work with BizTalk and are interested in WCF the articles are a *must read*. His post “Article Series on BizTalk and WCF: Part IX, BizTalk Adapter Pack BizTalk Patterns” has the links to each article.

Auckland Connected Systems User Group - Creating and Consuming RESTful Web Services with WCF - Ron Jacobs

 

 It is my pleasure to to introduce you to the Auckland Connected Systems User Group. This is a new .NET User Group in Auckland, New Zealand. The meetings will be held at Datacom and at Microsoft (our sponsors), we try to meet once every two months unless we have an impromptu speaker. The technologies covered will be around BizTalk, WCF, WF and any other integration technologies from Microsoft.

  If you are interested please check out the website and subscribe to the newsletter or subscribe to the News RSS feed to learn about upcoming meetings. You can also join the user group as we might add sponsor offers and member downloads in the future after meetings.

  To kick it off in a big way our first meeting details are below:

  Event: Creating and Consuming RESTful Web Services with WCF - Ron Jacobs

  Description: RESTful Web services are emerging as a new way to make your web service available to even the simplest of client apps. Learn how to design, build, create and consume these services with WCF.

  About Ron Jacobs:
  Ron Jacobs is a Sr. Technical Evangelist in the Microsoft Platform Evangelism group based at the company headquarters in Redmond Washington. Ron’s evangelism is focused on Windows Communication Foundation (WCF) and Windows Workflow Foundation (WF). He is also the former host of the ARCast series on Channel 9 about IT Architecture.

  Date:
 Frida, 23 of May 2008 5:30 p.m.

  Location:  

 MPC Room
 Microsoft New Zealand
 Level 5, 22 Viaduct Harbour Avenue
 Auckland, New Zealand 1010

 Please register for the meeting by filling out your details on the meeting page.

BizTalk Server R3 trickle feed

Isn’t it fun how Microsoft trickle feeds us BizTalk information hungry souls with details about BizTalk Server R3? It is already in a TAP program so surely they know a lot about it…

Like Nick Heppleston’s “BizTalk to Incorporate Covast B2B Capabilities” post I am also concerned about them acquiring Covast’s B2B capabilities. It is on one side quite exciting because Covast will add support to industry specific standards (Retail, Manufacturing, etc) and new adapters, but it will certainly get everyone thinking: what? Didn’t Microsoft just introduce their own EDI and AS2 adapters and tools? Hopefully they’ll be able to merge the two, as improbable as this may sound.

Another one is UDDI and SOA patterns and practices. This is the direction the ESB Guidance was going, so it’s nice to see it incorporated into a BizTalk release. But we can only wonder for now.

At any rate, because Oslo is still far away, it is without a doubt a required release to support the 2008 gang: Windows Server 2008, SQL Server 2008, Visual Studio 2008. Maybe it should have been called BizTalk Server 2008. And of course, as Steven Martin himself says, they might as well introduce all of the additional capabilities they have available at the moment.

BizTalk Server Tutorials Updated

 Microsoft released some great tutorials after BizTalk Server 2006 was released, and also updated them with BizTalk R2. At the beginning of this month they seem to have reviewed these tutorials, so what better time to go over them if you want to learn it in step-by-step instructions? They implement the most common BizTalk solutions, and the download page is here. The tutorials source files were shipped with BizTalk Server R2 in the SDK, so make sure you read the “Before You Begin” instructions first to unpack them into c:\Tutorials. The files in “updated tutorial files.exe” are updates to some of the files that were shipped with R2.

 Here is an excerpt of the tutorials from the download page:

Tutorial 1: Enterprise Application Integration.
Provides step-by-step instructions for implementing a BizTalk solution that receives inventory replacement request messages from a warehouse and evaluates the request messages. If the solution denies a request, it sends a denied message to the warehouse. If the solution approves a request, it forwards the message to an Enterprise Resource Planning (ERP) system.

Tutorial 2: Purchase Order Process.
Provides step-by-step instructions for implementing a BizTalk solution for the ERP system that generates purchase orders from the request messages and sends them to an external partner.

Tutorial 3: Invoice and Payment Process
Provides step-by-step instructions for extending the solution you create in Tutorial 2 to incorporate a payment process that includes verifying that the partner received the purchase order, receiving the invoice from the partner, constructing a payment voucher for the partner using rules to determine the payment policy, sending the payment and receiving confirmation that the partner received the payment, and forwarding the payment acknowledgment internally.

Tutorial 4: Trading Partner Management.
Provides step-by-step instructions for updating an existing BizTalk solution to incorporate trading partner management. You use the solution to implement a trading partner relationship.

Tutorial 5: Business Activity Monitoring.
Provides step-by-step instructions for defining and viewing data of interest.

Get the size of an XLANG message

Here’s a little snippet of code I use to get the size of an XLANG message in bytes. Very efficient and always correct, it loops through each message part and adds up each part’s Size property. This is useful for when you need to know the size of the message inside an orchestration.

 

 //Returns the sum of the size of the message parts

 public static int GetMessageSize(Microsoft.XLANGs.BaseTypes.XLANGMessage msg)

 {

   int msgSize = 0;

   try

   {

     foreach (Microsoft.XLANGs.BaseTypes.XLANGPart xp in msg)

     {

       msgSize += Convert.ToInt32(xp.GetPartProperty(typeof(Microsoft.XLANGs.BaseTypes.Size)));

     }

   }

   catch (Exception ex)

   {

     System.Diagnostics.EventLog.WriteEntry("XMLUtils GetMessageSize",

     ex.Message, System.Diagnostics.EventLogEntryType.Error);

   }

   return msgSize;

 }