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:
Cheers!
