Configuring BAM Tools on W2K8 R2

Introduction

To continue my tradition of trying BizTalk in unsupported (or not fully supported) environments, I have been working with BizTalk 2009 on Windows Server 2008 R2 on 64-bit. This OS is being used at my current client and I have been able to configure everything in BizTalk to work except for the BAM Tools / BAM Alerts functionality.

I had posted on setting up BAM with SQL 2008 a few months ago and once the 4 hotfixes were released, I was able to get everything working on a single machine after applying the hotfixes and reconfiguring BizTalk. The current release of the BizTalk install/setup guides only go so far as supporting W2K8 R1 but do not officially announce support for R2. This is due to the way the dates lined up around the BizTalk 2009 release, but I always wonder if there are any issues, especially breaking ones.

This post walks you through a hurdle I encountered configuring BAM Tools in a multi-server environment.

System Configuration

Here is the configuration I am trying now:

• W2K8 R2
• SQL 2008 on separate server
• VS 2008 w/ SP1
• 4 Hotfixes for Notification Services, etc. (64-bit version)
• BizTalk 2009

Configuration Issues

After applying the 4 hotfixes I try to configure BAM Tools. I am getting the following error:

Microsoft SQL Server 2008 Data Transformation Services (DTS) for BAM Archiving is not installed on the local machine.  Please install Microsoft SQL Server 2008 Integration Services. (Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper)

For help, click: http://go.microsoft.com/fwlink/events.asp?ProdName=Microsoft+BizTalk+Server+2009&ProdVer=3.8.368.0&EvtSrc=Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper&EvtID=error_DTS2008Archive

——————————
ADDITIONAL INFORMATION:

Could not load file or assembly ‘Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified. (Microsoft.BizTalk.Bam.CfgExtHelper)

This error looks similar to the one I had back on October 30, 2008 in this post: http://msinnovations.spaces.live.com/blog/cns!62E68922E47BC425!400.entry. With the changes to SQL 2008, the workstation tools were moved into a different package component known as the Management Tools. The similar error was resolved with SQL 2005 by installing the Workstation Tools on the BizTalk server. I was able to resolve the above error by checking the Management Tools (Basic & Complete) SQL 2008 features as shown in the screenshot:

Conclusion

As software products change, their names and subsystems also change. But the documentation for the products usually changes slower so there is often a small gap to watch out for. I found one when configuring BAM Tools for BizTalk 2009 with SQL 2008 on Windows Server 2008 R2. This post can also serve as some of the scant evidence that BizTalk 2009 runs successully on Windows Server 2008 R2, but the issue described above might be one of the supportability gaps.

Thanks,

More BizTalk 2009 Install Guide Info Updates

I checked in the Windows Server 2008 English BizTalk 2009 pre-requisites redistributable and it DOES now include the ADOMD v10 install. Yahoo!

 

So after exploring the new information available on BizTalk 2009, I noticed that the whole SQL Server Notification Services documentation story has been updated dramatically. I had posted a few days ago about how the link from the 2009 Beta install guide for SSNS went to a version from SQL 2005 RC1 did not provide enough files and resulted in lots of errors. Now the documentation sends you to a Microsoft support site where you can download the missing SQL dependency files (SMO among them): http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=953752. This should be a huge improvement – this should enable BAM Alerts to work without needing to install a secondary SQL 2005 instance. The result of my configuration research shows there is more than one supportable configuration for using SSNS – either as a scaled down version or on a separate SQL 2005 instance.

 

For the last couple of months I have been playing with BizTalk 2006 R2 on Windows Server 2008 and SQL 2008 to get a better understanding of software compatability between these products. I had posted on the gap of information about what server roles and features would be necessary to use BizTalk in a Windows Server 2008 environment. Fortunately, the recently released BizTalk 2009 install guide for Windows Server 2008 does mention the required roles and features for Windows Server 2008 under the section describing what should be installed with IIS. The install guides have now been split up by OS, but there is unfortunately not one for installing onto Windows 7 – perhaps we will have to wait for this to be released later.

 

It looks like the updated documentation does not include any mention of the two separate BizTalk 2009 RFID install images – one bundled with the BizTalk server software, and one as a separate ISO. The separate ISO has a lot more samples on it so be sure to get the separate ISO too. Both are available on MSDN.

Getting BAM Alerts to Work on SQL 2008

With the release of BizTalk 2009 RTM, I wanted to have a development VM to do all of the latest and greatest in BizTalk development. So I decided to build a VM with SQL 2008, BizTalk 2009, and all of the adapters I could pull together. I used the BizTalk 2009 beta install guide and installed SQL Server Notification Services (SSNS) 2005 RC1 which was the version linked to the beta install guide. When I tried to configure BAM Alerts (which depends on SSNS), I got an error, then another error and I realized there must be a gap in the 2009 beta install guide. Since a 2009 RTM install guide has not been released, I was stuck trying to figure out a way to get BAM Alerts to work. BAM Alerts configures fine on SQL 2005, but due to SQL 2008 not including SSNS, there is a cliff you can fall off trying to get BAM Alerts to work properly. This post describes the errors I received and the final workaround I came up with to get BAM Alerts to configure properly and work on a server with BizTalk 2009 and SQL 2008.

When I used the SSNS 2005 RC1 version, I started encountering errors that indicated there must be other dependencies that this install relied on. Here is the first error I was getting while configuring BAM:

The main error here is “Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=9.0.242.0′ …”. So I tried copying all of these SQL 2005 assemblies to my VM and then dropped them in the GAC. Then I tried to configure the BAM Alerts again and got this message:

The main error here is “This SQL version (10.0) is not supported.”. So obviously at this point I was steaming mad – what is the problem here? I did some searching on the nscontrol.exe named in the error and found this http://download.microsoft.com/download/f/4/e/f4e80c76-3b69-4a42-a90b-79aeaca1177d/ReadmeSQL2005SP3NotificationServices.htm which mentions that there are 3 dependencies of SSNS and all must be loaded in order for SSNS to function properly. I tried download all three but still could not get BAM Alerts to configure properly. At this point I realized the next option to try would be to install a named SQL 2005 instance because SSNS really requires the 9.0 database version to work properly. This turned out to be what worked for me. I later found out that in order for BAM Alerts to configure properly, all of the BAM databases have to be on the same SQL instance so you need to have the SQL 2005 database services there anyway in order for BAM to all configure properly. Below I give the final install order if you want to use BAM Alerts with SQL 2008.

Final successful install order:

VM Base: Windows Server 2003 R2 SP2, SQL 2008, VS 2008 (minus SQL Express) installed in this order.

VM Diff:

• Don’t use the SQL 2005 SSNS RC1 linked in the 2009 beta install guide. Use a SQL 2005 full verson.
• Install SQL 2005 as a named instance (installing after SQL 2008 was the default instance worked fine for me), install the database services, notification services, and analysis services.
• Run SQL 2005 SP2. If you forget this step, the BAM Alerts tells you the current SQL 2005 instance is not supported.
• Install BizTalk 2009 full
• Configure BizTalk 2009, use the named instance of SQL 2005 for all of the BAM databases.

More Tips for BizTalk ManagedDTS Issue

I was working on tracking down an issue with MDNs not being received for some AS2 communications. So I decided to enable the AS2 status reporting capabilities in BizTalk. I needed to configure BizTalk so that the BAM Tools functionality was available in order for the AS2 status reports to be available. During the process of using the BizTalk configuration wizard I received an error, which is given below:

 

"BizTalk 2006 Configuration: Microsoft SQL Server 2005 Data Transformation Services (DTS) for BAM Archiving is not installed on the local machine.  Please install Microsoft SQL Server 2005 Integration Services.  (Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper" … Additional information: “Could not load file or assembly ‘Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0”…

 

This error occurred when trying to configure the BAM Tools and I was specifying the database server names and the names of the databases. A red icon with an "X" occurred to the left of the database name. If you did not know, you can double click on the red icon on all of the pages of the BizTalk configuration wizard to see the error. 

 

After looking on the internet I came across two blog posts (http://blogs.msdn.com/mattlind/archive/2006/08/30/730575.aspx, http://geekswithblogs.net/andym/archive/2006/11/27/99246.aspx) that gave me a few hints on what needed to be done but I still felt there were gaps in documentation and the blog posts so I wanted to give a little more information here for other people so that they would not need to come up with their own ideas of what needed to be done. I will also be referring to the multi-server BizTalk installation guide which can be found among the guides here: (http://www.microsoft.com/downloads/details.aspx?FamilyID=df2e8a88-fb23-49a4-9ac7-d17f72517d12&DisplayLang=en). The AS2 and EDI Status Reporting features of BizTalk Server 2006 R2 do provide a huge help in doing diagnostics of EDI interchanges and AS2 communications, but the process of enabling this feature is complicated.

 

A multi-server install usually means that BizTalk is on one (or more) servers and the SQL Server engine components are on some other (one or more) servers. In the BizTalk multi-server install guide, if you are trying to setup BAM Tools (one of the prerequisites for the AS2 Status reporting), it recommends that you install SQL Server Integration Services (SSIS) on the BizTalk box. This is wrong and could represent a license violation if you actually did this. This is one solution but there is actually a different solution which is better. BTW, the BAM install guide does not mention anything about the AS2 Status reporting so you can just skip this document if you are trying to setup the AS2 reporting functionality.

 

Although the error message indicates that the problem is a DLL named Microsoft.SqlServer.ManagedDTS is missing, this does not mean that something about the DTS functionality outside of SSIS is not functioning properly. This refers to a DLL that is installed on the SQL Server box relating to SSIS but not part of it that also needs to be on the BizTalk server. If you open up the SQL Server system and look in the GAC (at c:Windowsassembly), you will see that this DLL exists on a SQL Server where the "Workstation Tools" have been installed. So all that is required is that you install the SQL Server client tools on the BizTalk server which in the SQL installer are designated as "Workstation Tools". BTW, there is not a way to export a copy of the DLL from the GAC and I would advise against trying to individually copy this DLL out of a CAB from the SQL install because there are also additional DLLs that may need to be on the BizTalk server as well. It is important to note that it is not enough to just have the Workstation Tools installed only on the SQL Server itself. As one of the previous posters mentioned, you should select all of the Workstation Tools components on the install on the BizTalk server. This may require that you insert the install media into the BizTalk server or that you copy the installation software over to the BizTalk server.

 

In Andy Morrison’s post, he mentioned he had received an error when installing the SQL client tools (Workstation Tools), regarding OWC11. This is a variation on the install of the client tools. You could get other errors too – the key is to ensure the client tools run through successfully. Right before the client tools are installed you will see a window that goes through a series of prequisite checks for the SQL client tools install and you may need to ensure all of these tests pass with a warning or are successful before the client tools install will work. When I ran the client tools on the BizTalk server I did not receive an error so you may not need to reinstall OWC11 and you may not need to do a repair on the BizTalk installation. Ok, there are just a few more steps.

 

Then after you have run the Workstation Tools on the BizTalk server you will need to close the BizTalk configuration wizard and re-open it in order for the BAM Tools page to successfully see the Workstation Tools and connect to SQL Server appropriately. You can then specify the BAM Tools options and then recheck the AS2 status reporting option under the EDI/AS2 Runtime page. After running through the BizTalk configuration wizard successfully you will then need to open up the BizTalk administration console and go to the Party properties. On your AS2 party you will need to open up the AS2 properties and then on the General tab check to enable the AS2 status reporting. You may also want to check the other boxes on the Sender and Receiver pages of AS2 properties for whether to store the wire and decoded messages of the AS2 communications so that you can drill into this data from the Group Hub page.

 

After all of this configuration, you should restart your BizTalk applications and hosts and then wait a while so that the AS2 status reporting can collect some data. Then go to the Group Hub page and click refresh. The Group Hub page will now have some additional links to reports but you will probably need to scroll down on the group hub page in order to see them. Normally all of the group hub page fits within the standard size window but the additional AS2 status reporting enables the use of the scroll bar so be aware that these links may be off-screen initially when you view the Group Hub page.

 

Thanks,