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.
Advertisements

,

  1. #1 by Thiago on April 15, 2009 - 9:54 pm

    Wow Ben, what a mission! Don’t like this at all. They should change the BAM alerts to not use notification services at all.

  2. #2 by Ben on April 16, 2009 - 1:39 am

    Great point Thiago! Hopefully this dependency will go away soon because it would be really annoying to have to keep SQL 2005 in the picture long-term.

  3. #3 by Jason on May 11, 2009 - 12:21 pm

    I have been able to get SQL Notifications working with Biztalk 2009 and SQL Server 2008. In the installation doc for BizTalk 2009 under the "Install SQL Notification Services" section there are a couple of hotfixs to download. SQLServer2005_XMO and Yukon_SP2_CU9_SNAC. You receive an email with the download link and a password. The Install doc say to leave the password blank. Enter the password and an msi will be extracted to your directory. Install the msi and for each of the 2 downloads. Extract and install the Yukon_SP2_CU9_SNAC first.Once this section of the doc is complete you can now configure the BAM Alerts (Which will create the Databases. You will have to ensure that your BAM users (BAM Notifications Services User, BAM Management Web Servcies User) have the correct access to the database tables. Check out this link for windows groups and user accounts (http://msdn.microsoft.com/en-us/library/aa577661.aspx)

  4. #4 by Ben on May 11, 2009 - 3:23 pm

    Jason, thanks for the tip. I will try this out. I used your comment on an MSDN forum thread for someone who was having trouble with this same issue. Thanks! http://social.msdn.microsoft.com/Forums/en-US/biztalkgeneral/thread/7f4c1060-c47c-4467-8ada-778bb47f7c1c

  5. #5 by Anthony on December 15, 2011 - 5:09 pm

    Guys I ran into the same issue installing BizTalk Server 2010 on Win 2008 R2 with SQL 2008 R2. It turned out in my case the installation document was pointing to an “older” set of Notification Services. I uninstalled the 3 MSIs, searched MS website and found the absolute latest and installed and the BAM Tools Installation then went fine.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: