Using a Named SQL Instance with the ESB Toolkit

Introduction

Lately I have been working on building out some VMs for working with BizTalk 2009/ESBT 2.0 and for BizTalk 2010/ESBT 2.1. I am working on a project that is targeting BizTalk 2010/ESBT 2.1 but there is a feature we would like to use which is currently only compatible with BizTalk 2009/ESBT 2.0. So I am working on managing the feature migration as well as planning for how things will work with BizTalk 2010.

One of the proprietary systems I work with uses a named instance in development for the SQL database and no default SQL instance exists. To keep everything relatively simple I have been installing BizTalk on the same named SQL instance. For most of the BizTalk prerequisites and setup this is straight-forward, but once I start working on the ESB Toolkit there are so many changes to make because the ESB Toolkit samples are all based on a default SQL instance.

It seems that the documentation is quite poor in this regard so I wanted to make a list of the things I have had to change to get a named instance to work properly. I did see Andy Morrison’s article mentioning bits of my content at http://geekswithblogs.net/andym/archive/2010/02/02/137753.aspx but it does not mention all of the relevant items specifically for a named instance. Some of the items listed below are steps done before the ESB Toolkit itself but I am including them here for completeness.

Pre-Requisite Setup

• Install SQL using a named instance
• Configure BizTalk using the named instance. Most of the steps in the wizard will work automatically(if SQL is local) but the BAM Tools lines for BAM Analysis and BAM Star Schema databases will still require you to manually enter the named instance information.
• The UDDI configuration wizard will pick up the named instance automatically (if SQL is local) so no extra work here.

ESB Toolkit Setup

• During the ESB Toolkit Configuration Tool you will need to specify the name of the named instance. If SQL is local you can use “.\<instance name>”.

ESB Toolkit Management Portal Setup

• Edit the web.config in c:\Projects\Microsoft.Practices.ESB\Samples\Management Portal\ESB.Portal
• Edit the App.Config in c:\Projects\Microsoft.Practices.ESB\Samples\Management Portal\ESB.UDDI.PublisherService
• Edit the web.config in c:\Projects\Microsoft.Practices.ESB\Samples\Management Portal\ESB.Exceptions.Service\ESB.Exceptions.Service
• Edit the App.config in c:\Projects\Microsoft.Practices.ESB\Samples\Management Portal\ESB.Exceptions.Service\ESB.Exceptions.Service.Implementation
• Edit the web.config in c:\Projects\Microsoft.Practices.ESB\Samples\Management Portal\ESB.BAM.Service\ESB.BAM.Service
• Edit the App.config in c:\Projects\Microsoft.Practices.ESB\Samples\Management Portal\ESB.BAM.Service\ESB.BAM.Service.Implementation
• Edit the App.config in C:\Projects\Microsoft.Practices.ESB\Source\Samples\Management Portal\ESB.AlertService
• Open the ESB.Portal solution in C:\Projects\Microsoft.Practices.ESB\Source\Samples\Management Portal\ESB.Portal and do a find on “data source=” to find a few other locations where the default SQL instance is used with Entity Data Models and other files
• Open the ESBFunctions.ps1 file in C:\Projects\Microsoft.Practices.ESB\Source\Install\Scripts and search for “sqlcmd” to find places where SQL is being called using the default instance. I did a find/replace on “sqlcmd” and replaced it with “sqlcmd -S .\<instance name>” because my named instance was local. You can enter something different if your named instance is remote.

As you can see, there are so many different places that must be changed in order to use the ESB Toolkit with a named SQL instance. This list holds true for both BizTalk 2009 and BizTalk 2010.

Thanks,