The BAM Star Schema database has not been configured. Run bm.exe setup-databases to configure the database

If you have tried setting up the BizTalk ESB Toolkit 2.1 to work with BizTalk 2010 and you're interested in BAM (Business Application Monitoring) for anything going onto and through the bus using BAM (nice feature) then you may get the following error when attempting to deploy the BizTalk ESB BAM definition files for exception handling and itinerary events:

C:\Program Files (x86)\Microsoft BizTalk Server 2010\Tracking>bm deploy-all -Def initionFile:"C:\program files (x86)\Microsoft BizTalk esb toolkit 2.1\bam\micros oft.biztalk.esb.bam.exceptions.xml" Microsoft (R) Business Activity Monitoring Utility Version 3.9.469.0 Copyright (C) Microsoft Corporation. All rights reserved. Using 'BAMPrimaryImport' BAM Primary Import database on server 'FOO-BAR'. .. Deploying Activity... Done. Deploying View... ERROR: The BAM deployment failed. The BAM Star Schema database has not been configured. Run bm.exe setup-databases to configure the database.

These are the commands required to import the Exception and the Itinerary BAM definitions:

Bm.exe deploy-all -DefinitionFile:"C:\Program Files (x86)\Microsoft BizTalk ESB Toolkit 2.1\Bam\Microsoft.BizTalk.ESB.BAM.Itinerary.xml"

and for exception handling definitions:

Bm.exe deploy-all -DefinitionFile:"C:\Program Files (x86)\Microsoft BizTalk ESB Toolkit 2.1\Bam\Microsoft.BizTalk.ESB.BAM.Exception.xml"

So how do you get the BAMStarSchema database into SQL Server? This is not particually intuitive but it is done during the BizTalk configuration process and in particular when configuring the BAM Tools feature:



If you got the above error, it was probably likely to do with the fact you didn't check the Enable Analysis Services for BAM aggregations checkbox during configuration of BizTalk after setup. When you check that checkbox, two more databases will appear in the Database Name list; BAMAnalysis and BAMStarSchema. Clicking Apply Configuration creates those missing databases ready for the activity import later.

Once you have created those databases, re-run the ESB BAM activity import and you should get something like the following:

C:\Program Files (x86)\Microsoft BizTalk Server 2010\Tracking>bm deploy-all -Def
initionFile:"C:\program files (x86)\Microsoft BizTalk esb toolkit 2.1\bam\micros
oft.biztalk.esb.bam.exceptions.xml"
Microsoft (R) Business Activity Monitoring Utility Version 3.9.469.0
Copyright (C) Microsoft Corporation. All rights reserved.

Using 'BAMPrimaryImport' BAM Primary Import database on server 'FOO-BAR'.
..

Deploying Activity... Done.
Deploying View... Done.
Deploying Alert... Done.
Deploying Security... Done.

The import may still fail as it might be due to the way you have configured SQL Server, then I will write another post as to what the cause of this might be sometime soon...

UPDATE: If deploying Views fails for you, check this post as it might help: http://www.simonrhart.com/2011/03/deploying-view-error-bam-deployment.html

How to package your Windows CE applications into a CAB during a nightly build process

I just wrote a post on how to package up your Windows Mobile/Phone/CE applications into a CAB file as part of your automated build process using either TFS 2010 or TFS 2008 and MSBuild. Check it out here:

http://smart421.wordpress.com/2011/02/24/how-to-package-your-windows-ce-applications-into-a-cab-during-a-nightly-build-process/

My/our other blog

For the regular readers of my blog, I also blog on my companys weblog found over at http://smart421.wordpress.com.

If you're interested in SOA and Enterprise Architecture, you'll like that blog. The stuff I write on there will be more lower code-level in most cases.

Cheers.

Microsoft Notification Services Control Utility 9.0.242.0 Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=9.0.242.0

Have you seen this error recently when attempting to configure BAM Tools as part of a BizTalk 2010 installation:

2011-02-22 13:06:04:2007 [Error] BAMTools Error configuring
Error executing bm.exe with tracing enabled. (Microsoft.BizTalk.Bam.CfgExtHelper.Utility)
Error executing process: C:\Program Files (x86)\Microsoft BizTalk Server 2010\Tracking\bm.exe (Microsoft.BizTalk.Bam.CfgExtHelper.Utility)
Microsoft (R) Business Activity Monitoring Utility Version 3.9.469.0Copyright (C) Microsoft Corporation. All rights reserved.ERROR: Failed to set up BAM database(s). There was a failure while executing nscontrol.exe. Error:"Microsoft Notification Services Control Utility 9.0.242.0╕ Microsoft Corp. All rights reserved.An error was encountered when running this command.Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified." (Microsoft.BizTalk.Bam.CfgExtHelper.Utility)

This screen might be familiar to you if you have seen the above once or twice:

This message is frustrating as you need the BAM Tools configured before you can configure the BAM Portal. So even if you don't care about alerting, but you do care about BAM, you still need this to work before you can continue to the next step.

After following the Installing BizTalk Server 2010 on Windows Server 2008 R2 and 2008.docx file, I was still getting the above exception. You can download this document here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b77d6a4f-8b41-470e-a58c-730dc5859b38&displaylang=en

The BizTalk 2010 installation document mentions that you need to install Microsoft SQL Server 2005 Notification Services if you want BAM Alerts because it is not included in Microsoft SQL Server 2008 R2 - which is the recommended SQL Server version for BizTalk 2010. You need Notification Services if you want any kind of BAM functionality - not just alerting.

As mentioned, the document recommends SQL Server 2008 R2 for BizTalk 2010 and if you want BAM functionality, you need to install SQL Server Notification services. It recommends you install 3 components that form SQL Server 2005 Notifications that can be downloaded from MSDN, they are:

1. Microsoft SQL Server Native Client (sqlncli_x64.msi)
2. Microsoft SQL Server 2005 Management Objects Collection (SQLServer_XMO_x64.msi)
3. Microsoft SQL Server Notification Services Client Components (SQLServer2005_NS_x64.msi)

So this all seems very straight forward. After installing the above components I was still getting the same error when attempting to configure BAM.

If we have a closer look at the error message, notice the program that is generating the error: C:\Program Files (x86)\Microsoft BizTalk Server 2010\Tracking\bm.exe. Also notice the assembly and assembly version bm.exe is looking for: Microsoft.SqlServer.Instapi, Version=9.0.242.0. So I looked for this dependency in the current folder (where bm is running) and couldn't see it. So it will either be on the PATH or in the GAC. Not running any tools to figure out the location where the CLR was looking (I'm lazy!), I looked in the GAC and noticed the file Microsoft.SqlServer.Instapi was present. But it wasn't the version 9.0.242.0 bm.exe was looking for. In the registry was version 10.0.0.0.

So the first thing I thought I'd try was to force the loading of the version 10.0.0.0 assembly instead of the version 9.0.242.0 version it was looking for. As bm.exe is managed code, I could create a probe that forced the load of version 10.0.0.0 by adding this to the bm.exe.config file. After doing this, on start up of the bm.exe, it overwrites the config file! so this change was completely useless. So then I thought I could add this probe to the machine.config file but thought this is getting very silly now.

The next thing I turned to my helpful friend, Google. searching hi and low for a solution to this the only concrete thing I could find was this post on the Microsoft Social forums: http://social.msdn.microsoft.com/Forums/en/sqlsmoanddmo/thread/e85cbf20-4a81-4059-88a9-6666bfeb02dc

A response from James K. Howey in the above post from Microsoft suggests its an issue with the SQL Server 2005 Management Objects Collection and this issue might be fixed in Sp2.

"I have reproduced this issue in house and can confirm that this is a problem with the Microsoft SQL Server 2005 Management Objects Collection. We will consider this issue for inclusion in Service Pack 2."

James also mentions that he tried GAC'ing the version 9 assembly but this failed with different error suggesting that the managed assembly is in fact a wrapper around something else or perhaps has other dependencies.

This was getting very messy, and all I wanted was for the BAM Portal to work - surely this is not much to ask! should it really be this hard...

So the next thing I thought was it seems BizTalk 2010 is designed to talk to SQL Server 2005 but recommends SQL Server 2008 R2. But there seems to be low-level dependencies it requires from the older SQL version. I didn't want to have to install the fully blown SQL Server 2005 on this machine so instead installed Notification Services from the fully blown SQL Server 2005 ISO.

After this I noticed the assembly Microsoft.SqlServer.Instapi version 9.0.242.0 in the GAC!!! Feeling rather upbeat, I re-executed the BAM Tools configuration (bm.exe) and it worked!:




Conclusion:

If you want BAM functionality in your BizTalk 2010 environment, do not install the Notification Services MSI's referenced in the BizTalk 2010 install guide, instead install from the fully blown SQL Server 2005 ISO from MSDN Subscriptions.

Happy BizTalk'in!