BAM, BizTalk, Installation

BizTalk Server and named SQL Server Analysis Services instances

The documentation for BizTalk Server 200X is very clear, and has been since BizTalk Server 2006. See the 2010 versions of technet wiki and download. It states:

Named instances of SQL Server 200X Analysis Services are not supported

But why is that? I set out to investigate if I could find a reason for it still being true.

Examining the history

In the beginning, there was a limitation with SQL Server 2000 Analysis Services (SSAS). It was not instance or cluster aware (much like SSIS today). Some time later with the help of service packs (if I can recall and use my search engine correctly) SSAS became cluster aware, but only supported running on the default instance. Thus the requirements and the reason that was true with BizTalk Server 2006 is crystal – due to the support of the underlying technology, namely SQL Server 2000, named instance of Analysis Service was not supported.

Present

I later versions of SQL Server, and the latest of SQL Server 2008 R2 specifically, there is no such limitation. Analysis Services is both cluster and instance aware and can be installed in a named instance. So the underlying limitation just isn’t there. But what about BizTalk? Does it have some built in limitation to how it uses Analysis Services that doesn’t allow working against a named instance?

The scenario

Two BizTalk Server Group installations – connected to their respective database instance – side-by-side. Both SQL Server instances has the database, agent and analysis services services installed. In the default instance I have a BizTalk Server Group configured with BAM tracking and Analysis, and a deployed activity and view when we start our journey. For the named instance I wanted to configure the same thing to see that there were no issue with

  • running Analysis Service in a named instance, and
  • running it side by side with the default instance and its Analysis Services on the same SQL Server machine.

Configuring BizTalk Server against a named instance of SQL Server 2008 R2 Analysis Services

First configuring the feature using BizTalk Server Configuration.

BTS2_BAM_config_seperateAS_2

Then applying the configuration and view the result.

BTS2_BAM_config_seperateAS_2_configDone

No issues.

Deploy BAM activities and views

Using bm.exe to deploy my BAM activity and view to my named instance.

BTS2_BAM_config_seperateAS_2_deployOK

No issues.

Using Tracking Profile Editor to connect my activity to an orchestration.

BTS2_BAM_config_seperateAS_btt

No issues.

Running some data through the integration

BizTalk Server behaves as expected. After running some sample data through the integration I could open my BAMPrimaryImportDb and run a Select against it to view my data.

BTS2_BAM_config_seperateAS_Select

Running my BAM_AN_<View> SSIS package

This was one of my prime candidates beforehand of where it could possibly go wrong. However as I used Business Intelligence Development Studio to inspect the package I could see that it was configured with datasources for the databases that it worked with, including the Analysis Services database, and it clearly points to the correct configured instance.

BTS2_BAM_config_seperateAS_BIstudio_BAM_AN_OrderView

Running it caused no incidents that I could detect. After having run the package and processed the cube I was ready to look at the data in the cube to see if it got there as expected.

Viewing the Live Workbook

The first thing to check was the live workbook (useless as I find this feature I wanted to see if it could read the data as expected).

BTS2_BAM_config_seperateAS_ExcelLiveWoorkbook

It could. And again, if we view the data connection properties here it point to my named instance.

BTS2_BAM_config_seperateAS_ExcelLiveWoorkbook_ConnectionProperties

(sorry about the swedish locale here, but you get the point)

Viewing data in the BAM portal

Next up, what about the BAM portal – would it be able to understand the named instance of SSAS and retrieve my data?

BTS2_BAM_config_seperateAS_BAMPortalView BTS2_BAM_config_seperateAS_BAMPortalActivitySearch

Yes. Both the Activity Search and the Aggregations show the data expected.

Conclusion

I can find no reason why SQL Server 2008 R2 Analysis Service cannot be used on a named instance with BizTalk Server 2010. Granted, this was just a small test and I certainly haven’t investigated this through every aspect (as per the usual disclaimer), but it “Works on my Machine”.

Do you know a reason that this will not work? Are you running this configuration yourself and can confirm further that it does work? Let me know…

HTH
/Johan

BAM, BizTalk, Pipeline Components

XmlDisassemble in a passthrough pipeline?

So, I a couple of weeks ago now I opened a case on Connect, and this is just to wrap it up in a blog-post for discovery and archive purposes.

Background

We use BAM for infrastructure tracking. We apply a simple tracking profile to all our ports for all our customers in all our environments. BAM does this like a charm, and handles the clean up, indexing, and archiving of this data automatically by schedulable jobs – tunable to customer requirements. Many times BizTalk will be used to do file transports without really caring about the content, alongside the more traditional transformation or orchestration work. Other times the requirements might cause me to want to bring in a file in BizTalk to handle things like processing, disassemble, splitting or something else in one (or more) orchestrations. To me, nothing strange with this, and I’m hardly alone in this.

The problem

Even though the passthrough pipeline is used, with BAM tracking, disassemble of the incomming file will be attempted or performed. This might result in disassemble errors and/or debatching of message with envelopes were that was not intended.

If you do BAM tracking and apply that to a port that has the passthrough pipeline on it the code will create a xmldisassembler as part of the BAM tracking process in the passthrough pipeline. If this does not find what it considers XML through it’s Probe method, things seem fine. Otherwise this can create issues. Two that we have identified is:

  1. If the message passed through the passthrough pipeline matches that of an envelope schema deployed to BizTalk it will be debatched into the first document by the pipeline.
  2. if the document contains faulty xml – pipeline processing will get an exception.
    image

Why does this happen?

Looking through the code with Reflector gives us some insights. The ReceivePipeline class, base class of the PassThruReceive class implements the GetNext member. Towards the end of that code the below code is present:

IBaseMessage ReceivePipeline.GetNext()

if (base.PipelineContext.InvokedFromMessaging)
{
  msg = base.DoBamTracking(msg, Pipeline.BamTrackingMode.BamTrackingOnly);
}

The DoBamTracking method (which resides in the even more generic Pipeline class) decides whether or not BAM tracking is necessary be looking at some context properties (supposedly populated by the fact that I’ve applied a Tracking Profile and mapped it to the port in which context the pipeline is running). If it is it then creates a XmlDasmComp component and Probes the message. If Probe returns false, then we are, as stated above, fine. If not… later in the code it will use the (XmlDasmComp) component to run Disassemble followed by its GetNext implementation and return the result. The result: A disassemble message – if it succeeds that is.

IBaseMessage Pipeline.DoBamTracking(IBaseMessage, BamTrackingMode)

IBaseComponent component = PipelineManager.CreateComponent(
"Microsoft.BizTalk.Component.XmlDasmComp, Microsoft.BizTalk.Pipeline.Components,
Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, processorArchitecture=MSIL"
); IProbeMessage message2 = (IProbeMessage) component; … if (!message2.Probe(this.pipelineContext, msg)) { return next; } … IDisassemblerComponent component2 = (IDisassemblerComponent) component; component2.Disassemble(this.pipelineContext, msg); next = component2.GetNext(this.pipelineContext); ... return next;

Steps to reproduce

  1. Deploy a envelope schema and a document schema.
  2. Set up a BAM activity with at least one field.
  3. Connect the BAM element in the tracking profile to something.
  4. Create a receive port with a passthrough pipeline.
  5. Connect the tracking profile to the receive port.
  6. OPT 1) Send a message through with badly formed xml, like a one line message with only the text "<TestMessage>" (xmldisassembler exception). OR

    OPT 2) Send a valid envelope with two document in it through (debatch will occur and leave only the first document).

Code to reproduce

(PassThruProblem.zip contains artifacts built for 2010)

(PassThruPipeline2006.zip contains 2006 (R2)/VS.NET 2005 artifacts.)

Versions affected

I have tested this in 2010, 2009, 2006 R2 SP1 CU3 – all have the issue.

Are you experiencing this issue?

If you are having this issue and want it fixed, vote it up on Connect and mark is as reproducible.

Further, we have found that to stop this from happening on a port the only way is to to re-create it. Removing the port mapping in the tracking profile for some reason is not sufficient.

BAM, Readings

Book Review: Pro BAM in BizTalk 2009

A while back now I got the Pro BAM in BizTalk Server 2009 book. I have always liked BAM and we always try to use it in our solutions, if nothing else then for infrastructural logging purposes. However BAM has never been something that has been described in any detail or highlighted within the BizTalk documentation. There are also a great deal many BizTalk solutions and developers out there that have never used BAM, perhaps in part because they haven’t had a good source to learn about it. When we had a user group meeting and talked about BAM last year we did a short put-your-hand-up poll and, if my memory serves, only about one out of five did put their hand up. And this in a group that to a large part I would judge as pretty progressive. I didn’t ask how many had used BAM outside of BizTalk, but I am pretty sure that if I had the answer might have been one or two, out of the whole group, if that.

If the issue is that it’s hard to find a source that covers BAM, one that is decently complete in its coverage, then that is one issue that is now resolved. Pro BAM in BizTalk Server 2009 succeeds in being that source. It covers both development, administration and business aspects of BAM. And with Business I don’t solely mean the Business Analyst role, but also where BAM fits, where it makes sense, and how you can get your data into the observation model as well as how you can get it out and report and research on it.

Although BAM presently is a BizTalk bundled technology the book approaches BAM from a BizTalk independent way, and talks as much about BAM in relation to other connected system technologies like WCF and WF as it does BizTalk. But that’s in line with the trends of BizTalk in general, where WCF more and more is taking on a very central role. Not everything is 100% up to date, but that’s not to be expected – change happens so fast that yesterday can be old news today, but the book still strives to put things in context of the latest technology and concepts and touches on topics such as Dublin and Oslo.

The book also goes into great detail about how to use the different types of tooling that comes along with BAM aimed for the different roles of Business Analyst, Developer, Administrator, and Information Worker (or Data Consumer as the book calls it). I also like how the book has specific sections on troubleshooting, should everything not work as expected, and tips that goes beyond just configuring it but also living with it.

It’s a really complete book in its coverage of BAM, and pointing out what’s missing is not an easy task, and isn’t really fair to the authors. If anything a discussion on BAM and performance could have been present. Although BAM has a highly performing infrastructure, a performance discussion is always of interest, especially from a BizTalk perspective when comparing it to for example the DTA tracking. The book also doesn’t go into much detail about when different tables are used, or what they contain and what flags have what meaning. Such things are however not need to know for you too call yourself a BAM wiz, something which this book may very well help you become.

Thanks Jeff and Geoff, it’s a great addition to my library. And I’m a better BizTalker for reading it 😉

BAM, BizTalk, Configuration, Installation

BAM and SSIS notes worth repeating

SQL Server Integration Services (SSIS) is not a clustered resource. Connecting to SSIS generally means connecting to any of the SQL Server servers in your cluster environment directly, not to a virtual cluster address. Configuring SSIS for a named BAM instance still involves configuring it against your clustered BAM database instance. The BizTalk install documentation (Multicomputer) also claims that SSIS needs to be installed on the BizTalk Servers. That’s incorrect. You could install SSIS and that’d be good, but I much rather install the management tools, as explained and outlined here.

While talking about BAM I can’t help but to mention an excellent article recently written by Saravana Kumar here.

BAM, BizTalk, Download, Swedish, Usergroup

BUGS – BAM Session 2 – Samples

Here is the demo code, and presentation (in swedish) from the BAM talk I gave at the BizTalk User Group Sweden event on the 20th. Thank you all who attended, and to Precio for hosting it. Please contact me with any questions you might have about the samples, how to get them running and how best to go forward to utilize the benefits they demostrate. I’ll add an additional blogpost as soon as possible, describing the sample code. But until then I just wanted to put the code out there for you to look at if you would like to. This download does not include the Purchase Order demo that I used to demonstrate aggregations, that’s part of the Business Activity Monitoring Training kit, which I highly recommend, and that you can get here. It also does not include the WCF Calculator Interceptor sample, which is part of the BizTalk Server samples that Microsoft provides, get that one here. Also check out Mikaels post to get the BAM Session 1 slides and demo code, and read the explanation at the biztalkusergroup.se site (also in swedish).