Why Are My Integrations Running So Slow?

We've just resolved an issue with one of our production servers which is taking an extremely long time to integrate some very small, infrequent messages. The integration scenario is as follows:-

Source System

• Exports .XML files of between 1k and 5k at a rate of about one file per minute.
• The files contain all information needed in the destination system so no enrichment will be required.
• The maximum node depth in the message is five deep.

Destination System

• Inserts the data present in the XML files into two SQL Server 2005 database tables through a single stored procedure.
• Although the tables have primary keys and other indexes and have around one million records the performance of the insert stored procedure indicates that insertion time should be around one second.

Integration

• A file system receive port is monitoring for .XML files on the source server.
• A WCF-Custom / SQL port is executing the stored procedure on the destination server.
• The source system, destination system and BizTalk server are all on the same LAN which is performing well.
• The source system and destination systems are performing well.
• The integration writes the message to a file send location as an archive as well as processing the message through a simple orchestration to write to the destination system.
• The integration normally executes as soon as the file is written and completes in one to two seconds.

Issue

• The file is being created in the source system folder but is not read by BizTalk for up to twenty minutes.
• No errors are reported in the application event log.
• No running instances are created until the file is read.
• No suspended instances are created.
• Other than the time delay, no other problems exist.

So, how do we diagnose this?

First of all I needed to eliminate problems with the source folder. What if the file system event is not firing?

Well, the folder in question is a sub-folder of an overall "integration" share. Another BizTalk server is happily reading from other sub-folders in that share. Furthermore, the folder is also used by the source application to process incoming files. Files written to this folder are instantly read by the application, indicating that folder performance is unlikely to be an issue.

So, the next problem is, if the source file system is not at fault, why isn't BizTalk picking up the file and removing it?

My next thought is that maybe the source application is holding a lock on the file so BizTalk can't access the file until the lock has been released. It seems unlikely - BizTalk is logging no errors or warnings - but it's something I am lucky enough to be able to check by dropping my own .XML file into the folder. Sure enough it is not picked up by BizTalk either. It looks like the source application is off the hook on this one.

So, next I move my accusing finger to point to the destination application. If BizTalk is reporting no errors, perhaps the message is being delivered successfully but is not visible in the destination application. That this is not the case can be show in a number of ways...

1) The file hasn't left the source directory

2) The archive version of the file is not created

3) No records exist when querying the database using tSQL

4) A SQL profiler trace shows no activity at all from BizTalk

Yup, the destination system is off the hook too. This is a BizTalk issue. So why isn't BizTalk logging the problem?

Well, maybe BizTalk isn't logging the problem because it can't. Is our server out of disk space? Does it have any other issues?

A quick trawl through the event logs, through Windows Explorer and Task Manager show the server to be in tip-top condition. Checking BizTalk Admin console and the event logs also shows that it's not throttling either. It's just running very, very, very slowly.

So we've eliminated the source, the destination and the BizTalk server. What's left? The back end database server. Maybe it's out of space, is running slow, has a problem?

Again, a quick trawl through the event logs, through Windows Explorer and Task Manager show the server to be in tip-top condition. Opening SQL Server Management Studio takes a matter of moments and everything looks to be performing well.

Our mystery is almost at an end folks. We're just a couple of clicks away from the solution.

SQL Agent. Responsible for running the archiving, cleaning up and general performance enhancing jobs for the SQL back end of BizTalk. It's running OK, the service is started. It's a shame that the jobs themselves are all owned by the guy that built the server. Who left a while ago. Who had his AD account deleted a while ago!

Fixing the jobs took a matter of seconds, changing the owner to a much more suitable setting. Each job runs successfully as I watch. And flipping back to the source system I notice the backlog of files has already been cleared.

The lesson? What we do is hard. But we can make it much, much harder by doing the basics badly!!

ASP.NET MVC4 Web API Body Gotcha

If you use Visual Studio 2012 to create a new ASP.NET MVC4 Web API you will be able to create Controller classes pre-populated with stub methods for GET, POST, PUT and DELETE. They'll look something like this...

public HttpResponseMessage Put(string id, [FromBody]string value)
{
}

The trouble is, while they'll happily build and deploy they don't actually work in practice!

Why?

Because the [FromBody] attribute tells Web API to stream the HTTP body into the variable it's about to create. The default variable is "value" and is of type System.String. And Web API has no way to stream any HTTP content types into a string. To help further, some content types will throw an error saying no MediaTypeFormatter is present while other content types will simply process the body and send NULL into the variable.

The trick is to create a new MediaTypeFormatter which recognises the content type text/plain and streams it into a string. Create a new class in your project and add the following text...

using System;
using System.IO;
using System.Net.Http;
using System.Net.Http.Formatting;
using System.Net.Http.Headers;
using System.Threading.Tasks;

namespace RESTfulServices.Helpers
{
    public class TextMediaTypeFormatter : MediaTypeFormatter
    {
        public TextMediaTypeFormatter()
        {
            SupportedMediaTypes.Add(new MediaTypeHeaderValue("text/xml"));
            SupportedMediaTypes.Add(new MediaTypeHeaderValue("text/plain"));
            SupportedMediaTypes.Add(new MediaTypeHeaderValue("text/javascript"));
        }

        public override Task<object> ReadFromStreamAsync(Type type, Stream readStream, HttpContent content, IFormatterLogger formatterLogger)
        {
            var taskCompletionSource = new TaskCompletionSource<object>();
            try
            {
                var memoryStream = new MemoryStream();
                readStream.CopyTo(memoryStream);
                var s = System.Text.Encoding.UTF8.GetString(memoryStream.ToArray());
                taskCompletionSource.SetResult(s);
            }
            catch (Exception e)
            {
                taskCompletionSource.SetException(e);
            }
            return taskCompletionSource.Task;
        }

        public override bool CanReadType(Type type)
        {
            return type == typeof(string);
        }

        public override bool CanWriteType(Type type)
        {
            return false;
        }
    }
}

Now you make this new formatter available to your API by adding the following line to the bottom of your Global.asax file...

GlobalConfiguration.Configuration.Formatters.Insert(0, new RESTfulServices.Helpers.TextMediaTypeFormatter());

All that's left to do is to send your HTTP message with the following header information...

Content-Type: text/plain

This will load the entire body into your "value" string variable, ready for you to parse as appropriate.

(Thanks to https://myadventuresincoding.wordpress.com/2012/06/19/c-supporting-textplain-in-an-mvc-4-rc-web-api-application/ for saving my vacation!)

Unit Testing SQL Databases

So in VS2015 it seems to create database projects at version 11.0 but it creates the database unit test project at version 14.0 which means the tests don't work during the build. The error you'll receive is...

Error Message:

Assembly Initialization method GPFacade.UnitTests.SqlDatabaseSetup.InitializeAssembly threw exception. Microsoft.Build.Exceptions.InvalidProjectFileException: Microsoft.Build.Exceptions.InvalidProjectFileException: The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk. C:\Agent\_work\6\s\GPFacade\GPFacade.sqlproj. Aborting test execution.

Stack Trace:

at Microsoft.Build.Shared.ProjectErrorUtilities.ThrowInvalidProject(String errorSubCategoryResourceName, IElementLocation elementLocation, String resourceName, Object[] args)

at Microsoft.Build.Shared.ProjectErrorUtilities.ThrowInvalidProject(IElementLocation elementLocation, String resourceName, Object arg0)

at Microsoft.Build.Evaluation.Evaluator`4.ExpandAndLoadImports(String directoryOfImportingFile, String importExpressionEscaped, ProjectImportElement importElement)

at Microsoft.Build.Evaluation.Evaluator`4.EvaluateImportElement(String directoryOfImportingFile, ProjectImportElement importElement)

at Microsoft.Build.Evaluation.Evaluator`4.PerformDepthFirstPass(ProjectRootElement currentProjectOrImport)

at Microsoft.Build.Evaluation.Evaluator`4.Evaluate()

at Microsoft.Build.Evaluation.Evaluator`4.Evaluate(IEvaluatorData`4 data, ProjectRootElement root, ProjectLoadSettings loadSettings, Int32 maxNodeCount, PropertyDictionary`1 environmentProperties, ILoggingService loggingService, IItemFactory`2 itemFactory, IToolsetProvider toolsetProvider, ProjectRootElementCache projectRootElementCache, BuildEventContext buildEventContext, ProjectInstance projectInstanceIfAnyForDebuggerOnly)

at Microsoft.Build.Evaluation.Project.ReevaluateIfNecessary(ILoggingService loggingServiceForEvaluation)

at Microsoft.Build.Evaluation.Project.Initialize(IDictionary`2 globalProperties, String toolsVersion, String subToolsetVersion, ProjectLoadSettings loadSettings)

at Microsoft.Build.Evaluation.Project..ctor(String projectFile, IDictionary`2 globalProperties, String toolsVersion, String subToolsetVersion, ProjectCollection projectCollection, ProjectLoadSettings loadSettings)

at Microsoft.Data.Tools.Schema.Sql.UnitTesting.MSBuildWrapper.DeployDatabaseProject(FileInfo databaseProjectFile, String configuration, String targetDatabaseName, String connectionString, Boolean forceDeployment)

at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService.DeployDatabaseProject(String databaseProjectFileName, String configuration, String providerInvariantName, String connectionString, Boolean forceDeployment)

at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService.DeployDatabaseProject()

at GPFacade.UnitTests.SqlDatabaseSetup.InitializeAssembly(TestContext ctx) in C:\Agent\_work\6\s\GPFacade.UnitTests\SqlDatabaseSetup.cs:line 20

To get round this...

1. Right mouse click on the database project and select "Unload"
2. Right mouse click on the unloaded database project and select "Edit"
3. Search the file for a section of text which looks like this...

  <PropertyGroup>

    <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>

    <!-- Default to the v11.0 targets path if the targets file for the current VS version is not found -->

    <SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists>

    <VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>

  </PropertyGroup>

1. Replace the 11.0 with 14.0 and save the file
2. Right mouse click on the unloaded database project and select "Reload"
3. Commit your changes and check they now build

Using IIS Publishing Packages With Visual Studio Online Builds

When you create a publishing profile for a web service in Visual Studio it generates an XML file while looks like this:-

<?xml version="1.0" encoding="utf-8"?>

<!--

This file is used by the publish/package process of your Web project. You can customize the behavior of this process

by editing this MSBuild file. In order to learn more about this please visit http://go.microsoft.com/fwlink/?LinkID=208121. 

-->

<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

  <PropertyGroup>

    <WebPublishMethod>Package</WebPublishMethod>

    <LastUsedBuildConfiguration>Debug</LastUsedBuildConfiguration>

    <LastUsedPlatform>Any CPU</LastUsedPlatform>

    <SiteUrlToLaunchAfterPublish />

    <LaunchSiteAfterPublish>True</LaunchSiteAfterPublish>

    <PrecompileBeforePublish>True</PrecompileBeforePublish>

    <EnableUpdateable>True</EnableUpdateable>

    <DebugSymbols>False</DebugSymbols>

    <WDPMergeOption>DonotMerge</WDPMergeOption>

    <ExcludeApp_Data>False</ExcludeApp_Data>

    <DesktopBuildPackageLocation>bin\MyWebService.zip</DesktopBuildPackageLocation>

    <PackageAsSingleFile>true</PackageAsSingleFile>

    <DeployIisAppPath>Default Web Site/MyWebService</DeployIisAppPath>

    <PublishDatabaseSettings>

      <Objects xmlns="">

        <ObjectGroup Name="MyDatabase" Order="1" Enabled="False">

          <Destination Path="" />

          <Object Type="DbDacFx">

            <PreSource Path="Trusted_Connection=True;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=.;" includeData="False" />

            <Source Path="$(IntermediateOutputPath)AutoScripts\MyDatabase_IncrementalSchemaOnly.dacpac" dacpacAction="Deploy" />

          </Object>

          <UpdateFrom Type="Web.Config">

            <Source MatchValue="Trusted_Connection=True;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=.;" MatchAttributes="$(UpdateFromConnectionStringAttributes)" />

          </UpdateFrom>

        </ObjectGroup>

      </Objects>

    </PublishDatabaseSettings>

  </PropertyGroup>

  <ItemGroup>

    <MSDeployParameterValue Include="$(DeployParameterPrefix)MyDatabase-Web.config Connection String" />

  </ItemGroup>

</Project>

This works well if you are deploying from within Visual Studio or importing an application in IIS.

However if you are setting up a Visual Studio Online (VSO) build and release you will need to make some extra modifications to ensure that the correct connections are used for your different environments.

<?xml version="1.0" encoding="utf-8"?>

<!--

This file is used by the publish/package process of your Web project. You can customize the behavior of this process

by editing this MSBuild file. In order to learn more about this please visit http://go.microsoft.com/fwlink/?LinkID=208121. 

-->

<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

  <PropertyGroup>

    <WebPublishMethod>Package</WebPublishMethod>

    <LastUsedBuildConfiguration>Debug</LastUsedBuildConfiguration>

    <LastUsedPlatform>Any CPU</LastUsedPlatform>

    <SiteUrlToLaunchAfterPublish />

    <LaunchSiteAfterPublish>True</LaunchSiteAfterPublish>

    <PrecompileBeforePublish>True</PrecompileBeforePublish>

    <EnableUpdateable>True</EnableUpdateable>

    <DebugSymbols>False</DebugSymbols>

    <WDPMergeOption>DonotMerge</WDPMergeOption>

    <ExcludeApp_Data>False</ExcludeApp_Data>

    <DesktopBuildPackageLocation>bin\MyWebService.zip</DesktopBuildPackageLocation>

    <PackageAsSingleFile>true</PackageAsSingleFile>

    <DeployIisAppPath>Default Web Site/MyWebService</DeployIisAppPath>

    <PublishDatabaseSettings>

      <Objects xmlns="">

        <ObjectGroup Name="MyDatabase" Order="1" Enabled="False">

          <Destination Path="" />

          <Object Type="DbDacFx">

            <PreSource Path="Trusted_Connection=True;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=.;" includeData="False" />

            <Source Path="$(IntermediateOutputPath)AutoScripts\MyDatabase_IncrementalSchemaOnly.dacpac" dacpacAction="Deploy" />

          </Object>

          <UpdateFrom Type="Web.Config">

            <Source MatchValue="Trusted_Connection=True;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=.;" MatchAttributes="$(UpdateFromConnectionStringAttributes)" />

          </UpdateFrom>

        </ObjectGroup>

      </Objects>

    </PublishDatabaseSettings>

  </PropertyGroup>

  <ItemGroup>

    <MSDeployParameterValue Include="$(DeployParameterPrefix)MyDatabase-Web.config Connection String">

      <ParameterValue>Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True</ParameterValue>

    </MSDeployParameterValue>

  </ItemGroup>

</Project>

In this version of the file we have explicitly set the MSDeployParameterValue for our connection which, as the name suggests, is going to be used by the MSDeploy utility to configure the web service when we release.

Visual Studio Fun With Shift, Ctrl and Alt

Friday afternoon so here's a few cool tips for Visual Studio (especially Visual Studio 2017) in the code window.

• Left Mouse Click - position cursor (obviously)
• Alt Left Mouse Click - position cursor (no excitement yet)
• Shift Left Mouse Click - highlight text between the original cursor position and the new one
• Ctrl Left Mouse Click - open an embedded window with the code definition of the thing you clicked on! And you can edit this while also editing the main code window!!
• Left Mouse Drag - Highlights between the starting drag position and the finishing one, wrapping the highlighting at the end of each line onto the start of the next
• Shift Left Mouse Drag - same as standard left drag
• Ctrl Left Mouse Drag - Highlights all the whole words between the starting word and the ending word, also wrapping at line ends
• Alt Left Mouse Drag - Highlights the text in a rectangle regardless of the shape and structure of the lines you are highlighting. When you start editing it replaces the highlighted block but is intelligent enough to make the same replacement on every line highlighted! You can even highlight a zero character width area to perform an insert when you start typing!!
• Arrow Keys - position cursor (again, obviously)
• Shift Arrow Keys - highlight text between the original cursor position and the new one
• Ctrl Arrow Keys - scroll the screen up and down or jump the cursor word by word left and right
• Alt Arrow Keys - Move the current line or highlighted text up or down relating to the rest of the text!

Sadly the fun ends there because right mouse clicking opens a context menu and absolutely zip all else.

Powershell & Downloaded Scripts

We've changed the way we build and package our BizTalk apps using Visual Studio Team Service. We used to build our MSI's, bindings and PowerShell deployment scripts and then zip them all into a single Deploy.ZIP package on our on site build server. Now we leave the MSI's, bindings and PowerShell scripts in the Artifacts folder in the cloud, ready for a Release to deploy them.

So today I've downloaded the artifacts for the first time. Chrome has kindly downloaded Artifacts.ZIP for me ready for use. I've unzipped it and copied the contents to my target server. I've then run my deployment PowerShell script.

Well blow me, PowerShell has noticed my files have been download from the internet and now asks me if I trust each and every script! Worse, even if I say I would like to (R)un Once for each script they no longer work because various PowerShell commands are unavailable.

The workaround is simple.

Download the zip file from the Artifacts location. Then open Properties on the zip file. Check the "Unblock" checkbox and click OK. Now you can unzip as normal.

Behold, the extracted files are no longer being considered a top security risk. The prompt goes away and suddenly you are able to run commandlets again. Life is good, but...

What we do is hard.

Patchy Patching

(If you are reading this article to fix a specific issue my workaround is at the bottom of this post).

Patching is of course something we all treat as a top priority. All of our servers, desktops, laptops, mobile and infrastructure devices are thoroughly patched at the OS level and also at the application level. We all have robust methods for testing those patches and we are all 100% confident that patches will only improve the security and robustness of our IT systems without causing any unwanted side effects. (Just nod!)

But even with the best possible patching techniques in place there are some risks we must mitigate against. The trouble is, by definition patches are changing small pieces of functionality within a much bigger context. And, to make matters worse, they are probably changing small but critical pieces of functionality to work in a more "secure" or "reliable" way than before. If they fixed an entire system or made no functional changes they wouldn't be "patches". By changing single components we are relying on the developer to have fixed a specific issue without causing knock on issues in other related components.

To use an analogy, it's a bit like taking your car to a garage for a service. They change the oil filter and we rely on them to have made no changes to any other part of the car. We also rely on the manufacturers of the oil filter to have constructed a perfect oil filter that won't break. And we rely on everyone to ensure that the oil we have in our car is compatible with the new oil filter components.

If we look at a simple example of a patch we can see this in action.

In this case we have an application installed on our Windows machine. It's a 64 bit machine and our Windows installation is 64 bit too. On top of that the .NET framework is providing lots of extra functionality. It is 64 bit as well. And we have installed our application in 64 bit mode meaning everything is nice and compatible.

Now we install our patch, in this example to the .NET framework.

Microsoft developed Windows. Microsoft developed .NET. We can be reasonably sure our patch for .NET is compatible with Windows (or if there are going to be issues we can be reasonably sure Microsoft have plenty of documentation on how to resolve them).

This means all we really need to worry about is whether our application is compatible with our patched version of the .NET components. And with millions of .NET based apps out there it's pretty reasonable to assume that any compatibility issues are going to be blogged about within seconds of the patch hitting.

So, as we know, it's a good idea to test our patches. It's a good idea to check the documentation before applying the patches. And it's a good idea to have a way back if a patch causes something unexpected. But otherwise life is simple.

BizTalk is a bit different to this. First lets look at our diagram after we add our out-of-the box BizTalk components.

Immediately we have much more complexity. We are invoking a stack of extra Windows components including MSDTC. Then, instead of simple application layer with some compatible executables and DLLs we now have a stack of components designed to run under service accounts, called from Windows services in a mixture of 64 bit and 32 bit architecture. Our same patch must now simultaneously support the behaviour of all those layers in both 32 bit and 64 bit modes, fixing security, fixing loopholes, fixing issues while ensuring every component of BizTalk is entirely unaffected. It's a big ask.

But that's the easy bit. Now we need to add our BizTalk apps themselves.

Think about how this works. When you build a BizTalk app you use Visual Studio to build a bunch of DLLs which are going to be deployed to your BizTalk server. These can be 32 bit or 64 bit and may take the form of BizTalk templates such as maps and orchestrations or could be chunks of .NET code. And now we are going to expect BizTalk to invoke these DLLs dynamically based on some settings in a SQL database. And, to add to the fun, we want it to flip between 32 and 64 bit on demand while asynchronously invoking these components to handle any message flow we throw at it. In fact it's so dynamic and so unpredictable it almost sounds like the behaviour malware would get up to. Want to bet your house on this stack of goodies working after a quick Windows Update + Restart combo? Hmm, I think not.

If this doesn't make you stop and think about having a robust patching strategy including thorough testing let me give you a specific real world, "happened to me" example.

Microsoft patches KB3110329, KB3121918 and KB3175024 all deal with the way that the Windows Kernel invokes DLLs and, more specifically, all tighten security in relation to how DLLs can be called by other DLLs and executables to reduce the risk that one of the DLLs is some kind of malware. We have already installed these patches on multiple servers without any problems.

Now we put the same patches on our pre-production BizTalk server. This server has a number of WCF-Custom send ports connecting to SQL Servers. The ports are invoked by a number of orchestrations running on a 64 bit host instances called "OrchestrationHost64". However the ports are configured to run on two different host instances called "SendPortHost32" and "SendPortHost64".

So, installation of the patches goes well. The server is restarted. BizTalk restarts successfully. No errors are reported by BizTalk. Messages start flowing. All looks good.

And then a message is processed which sends to two WCF-Custom/SQL ports, one on our "SendPortHost32" and one on our "SendPortHost64".

“System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)”

This is the error in our Windows Application Event Log as one of the two sent messages drops off into a black hole. Helpful isn't it? Try Googling for it now and see how many articles explain what this message means in relation to BizTalk. Any good ones? Any from Microsoft?

We were fortunate. We caught the error before it hit our production environment. We found it because the tests we had in place allowed us to catch this error. We had seen similar errors before outside of the world of BizTalk. And, most importantly, we were lucky enough to have a large enough spread of send ports for a repeatable pattern to emerge quickly. We restored our server to a backup from before the patching, retested using identical tests, proved it was fixed, applied the patches and proved it was broken. Within a day we were back up and running.

Ask yourself the question, would you be as fortunate as us? Take patching seriously everyone!

Anyway, as promised, here is my workaround which fixed our problem (and probably highlights the need to be very consistent in the way you configure BizTalk).

1) Confirm that the error you see is...

“System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)”

2) Confirm that you have ports of the same Port Type running on a mixture of 32 and 64 bit hosts

3) Confirm that you have the capacity to run all ports of that Port Type on a single host and, if not, spin up further host instances as required

4) Amend the port bindings to ensure all ports of the same Port Type are running on host instances of a single processor architecture

5) Restart the host instances (including the host instance that you have moved away from!)

What we do is hard.