Consolidate to a single BizTalk database? Yes you can!

  • Article

I ran into a interesting and undocumented feature of BizTalk 2006 a couple weeks ago that I thought I'd share. A colleague of mine, Jim Bowyer, had found out that with BizTalk 2006, you can consolidate most of your BizTalk database into a single one. 

 

For those of you that aren't completely familiar with the internals of BizTalk, the following SQL Server databases are used to power a BizTalk environment (Note: some may not appear in your environment if you chose not to install all available BizTalk features)

  1. MessageBox Databases
  2. SSO Credential Database
  3. BizTalk Analysis Database
  4. BizTalk Configuration Database
  5. Human Workflow Services Admin Database
  6. BizTalk Rules Engine Database
  7. BizTalk Base EDI Database
  8. BizTalk Tracking Database
  9. Notification Service
  10. Windows SharePoint Services Configuration
  11. Windows SharePoint Services Content
  12. BAM Star Schema
  13. BAM Primary Import
  14. BAM Archive
  15. BAM Analysis (OLAP)
  16. BAM Notification

  (For a more complete outline of these DB's (and what they are used for) check out this page )

 

Now that's quite a number of databases to maintain and manage, so it's natural that smaller organizations might be slightly intimidated about having to install, manage and support so many. I found out last week that a new feature has been introduced to BTS 2006 that will allow smaller customers to consolidate nearly all of these databases into single one. (The exceptions being the BAM Notification and EDI DBs).

Now since this feature was introduced post Beta 2, the documentation surrounding it is pretty weak. Additionally, the guidance around this concept is still almost non-existent. So here's what I've been able to find out from our product team.

 

Reasons for:

  • You are looking for a simplified install
  • You are looking for simplified management post install
  • You are looking for new variations of disaster recovery beyond the standard ones recommended with a multi-DB installation (i.e. you might be able to use database mirroring instead of having to rely on log shipping. Note: our product team has fully tested this and therefore doesn't support it yet)

Reason against:

  • You are really going to limit your ability to scale out your DB tier. Under a normal install, you would be able to place different DBs on different machines. You lose that ability under this type of set
  • The testing that has been done for this type of install is admittedly very small. I haven't seen any real numbers around potential performance impacts that this might have.
  • The lack of documentation, testing and formal support for this feature

It should be noted that this feature is included for small BTS installations and shouldn't be considered for larger enterprises. 

 

If you do decide that you do want to go ahead with this, here's how:

 

During the configuration stage of your install, you can specify that you would like to install all features into the same database. (All except for the EDI and BAMAlertsNSMain databases - as stated above). When you fire up the BizTalk Server Configuration tool, and you're at the stage where you can select the features you want to install. This is where you can specific the consolidation. You'll need to specify the same database name for each feature (except EDI and BAM Alerts). After you do this, remember to adjust the connection settings when you use the BizTalk Server 2006 Administration MMC Snap-in. By default, it will want to connect to a database called BizTalkMgmtDb. If you consolidate your databases into a one new database then you need to specify that new one in the MMC. You do this by right-clicking the BizTalk Server 2006 Administration node and selecting “Connect To Existing Group“. This gives you the option to select a SQL Server and Database that the management db lives on.

 

Hope that helps and keep on BizTalking...