SQL Best Practices for Biztalk

SQL-Server-Team

Microsoft

Mar 23, 2019

First published on MSDN on Jan 25, 2009

As promised in my last post, here is the post for SQL Best practices for a Biztalk installation.

Althought the majority of best practices for a SQL Server instance are still valid for a Biztalk implementation we should consider some points that are different.

In a Biztalk instance:

Auto create statistics must be disabled

Auto update statistics must be disabled

MAXDOP (Max degree of parallelism) must be defined as 1 in both SQL Server 2000 and SQL Server 2005 in the instance in which BizTalkMsgBoxDB database exists

Indexes

Most of the indexes in BizTalk Server databases are clustered (index ID: 1). The DBCC SHOWCONTIG command can be used to display fragmentation information for tables in the BizTalk Server databases. These indexes are GUID-based so it is normal for fragmentation to occur. If the Scan Density value of DBCC SHOWCONTIG is less than 30%, the indexes can be rebuilt during downtime. Many tables in the BizTalk Server databases contain columns that use DataType definitions where online indexing cannot be done.

On BizTalk Server 2006 and on BizTalk Server 2004, the only supported method to rebuild an index in the BizTalkMsgBoxDb database is to run the bts_RebuildIndexes stored procedure. On BizTalk Server 2006, you can run the dtasp_RebuildIndexes stored procedure to rebuild indexes in the BizTalkDTADb database.

Microsoft only supports rebuilding database indexes during BizTalk Server downtime. You should stop all data processing that is related to BizTalk Server before you rebuild an index.

SQL Server agent

In a SQL cluster implementation, consider the "Affect the group" setting on the SQL Server Agent resource, as it is a essential piece in a Biztalk implementation.

For more information please read the following articles: