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.
Then applying the configuration and view the result.
No issues.
Deploy BAM activities and views
Using bm.exe to deploy my BAM activity and view to my named instance.
No issues.
Using Tracking Profile Editor to connect my activity to an orchestration.
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.
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.
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).
It could. And again, if we view the data connection properties here it point to my named instance.
(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?
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
Blog Post by: Johan Hedberg