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:
- 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.
MS Innovations Blog 
Wow Ben, what a mission! Don’t like this at all. They should change the BAM alerts to not use notification services at all.
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.
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)
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
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.