Microsoft BizTalk Database Tips for Microsoft SQL Server DBA's

  • Article

I recently had a Microsoft SQL Server expert ask me about some tips regarding Microsoft BizTalk Server 2006 databases, so I thought I'd share this information on my blog.

First of all BizTalk database are not your normal SQL databases. Yes, I said databases… as in plural. BizTalk comes with about 6 core databases (MsgBox, Tracking, MgmtDB, SSODB, BAM, and RulesEngine). When you add on other bolt on products like SharePoint, and custom databases, you can see that there is a *lot* to manage. It’s common to have BizTalk databases are on a SAN due to its thirst for disk I/O. It’s like this because BizTalk must not lose any messages or transactional states.

BizTalk Database Backups: They *must* be backed up using a specific SQL job created by BizTalk during configuration. Normal backups don’t work because BizTalk has the potential to do DTC transactions across databases, therefore all of the databases must be log marked, then backed up to ensure a good restore point.

BizTalk Clustering versus NLB: Generally speaking, when BizTalk is *pulling* in messages, then cluster the BizTalk service using Microsoft Cluster Services. When messages are *pushed* to BizTalk, then use Network Load Balancing (NLB).

BizTalk Clustering and SQL Clustering: Without going into much detail, a BizTalk cluster and a SQL cluster should not exist on the same servers/cluster at the same time. This primariliy depends on if the SSO service is clustered on the same cluster. Almost always you have a backend SQL cluster. The BizTalk servers don’t always need to be clustered – see the BizTalk Clustering versus NLB section above.

Tuning of BizTalk Databases (SQL Tuning): BizTalk database are *not* tunable from a SQL Server perspective. BizTalk places hints on all of its queries, so tuning of indexes and such would be very difficult. Effectively, you perf tune BizTalk by changing BizTalk’s configuration.

Max Degree of Parallelism: The BizTalk MessageBox Database requires a Max Degree of Parallelism of 1. Anything else is not supported. With that said, any of its other databases can have a non-1 setting.

I hope this helps clear up some things.