BizTalk 2010: Notification Services

It is best practice in my opinion when installing BizTalk to follow BizTalk installation manuals. Microsoft provides manuals for each supported operating system or multi-server installation. BizTalk 2010 is supported on:

▪ Windows Server 2008 R2
▪ Windows Server 2008 with Service Pack 2
▪ Windows 7
▪ Windows Vista with Service Pack 2
▪ Windows XP with Service Pack 3

Other requirements are:

▪ Microsoft Internet Information Services (IIS) 7.0 or 7.5
▪ Microsoft Office Excel 2010 or 2007
▪ Microsoft .NET Framework 4 and .NET Framework 3.5 SP1
▪ Microsoft Visual Studio 2010 with Visual C# .NET. Required for BizTalk Server applications development and debugging; not required for production-only systems
▪ SQL Server 2008 R2 or SQL Server 2008 SP1
▪ SQL Server 2005 Notification Services with Service Pack 2
▪ The Windows SharePoint Services adapter Web service requires SharePoint Server 2010, SharePoint Foundation 2010, Windows SharePoint Services 3.0 with Service Pack 1, or Microsoft Office SharePoint Server 2007.

What interesting is SQL Server 2005 Notification Services with Service Pack 2 in combination with SQL Server 2008 R2. If you follow the manual and proceed step by step you eventually are going to configure BizTalk 2010. When you reach step to configure BizTalk BAM Tools and Alerts you can stumble on this kind of error:

Microsoft.BizTalk.Bam.Management.BamManagerException: Failed to set up BAM database(s). ---> Microsoft.BizTalk.Bam.Management.BamManagerException: There was a failure while executing nscontrol.exe. Error:"Microsoft Notification Services Control Utility c Microsoft Corp. All rights reserved. An error was encountered when running this command. Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. " at Microsoft.BizTalk.Bam.Management.AlertModule.ExecNSControlCommand(String commandArg) at Microsoft.BizTalk.Bam.Management.AlertModule.SetupAlertInfrastructure() at Microsoft.BizTalk.Bam.Management.BamManager.SetupDatabases() --- End of inner exception stack trace --- at Microsoft.BizTalk.Bam.Management.BamManager.SetupDatabases() at Microsoft.BizTalk.Bam.Management.BamManagementUtility.BamManagementUtility.HandleSetupDatabases() at Microsoft.BizTalk.Bam.Management.BamManagementUtility.BamManagementUtility.DispatchCommand() at Microsoft.BizTalk.Bam.Management.BamManagementUtility.BamManagementUtility.Run() at Microsoft.BizTalk.Bam.Management.BamManagementUtility.BamManagementUtility.Main(String[] args)

I stumbled on it and so did other people I noticed on BizTalk General Forum (see this thread). Solution to this problem is by installing SP3 for SQL Server 2005. I installed BizTalk 2010 on different environments and what I did when following manual instructions is to install SQL Server 2005 Notifications Services through one of SQL Server 2005 ISO’s and then applied first SP2 and later SP3. When you install SQL Server 2008 R2 later on you will see this screen.

BizTalk R2 SQL NS

When you reach this screen you will first see it has Named instance selected, but I changed it to Default instance and proceeded further with my installation. During configuration of BizTalk 2010 BAM Tools and Alerts everything went smoothly. I guess is that requirement should be  SQL Server 2005 Notification Services with Service Pack 3. At least in the case when you want to use SQL Server 2008 R2. The suggested feature pack mentioned in installation document is not going to work, because you will get an error as displayed above.