SQL Best Practices for Biztalk
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
- 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:
- You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004
- BizTalk Server Database Optimization
- Maintaining BizTalk Server Databases