SQL Best Practices for Biztalk

Published Mar 23 2019 11:11 AM 578 Views
Microsoft
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!

Version history
Last update:
‎Mar 23 2019 11:11 AM
Updated by: