SQL Best Practices for Biztalk

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

%3CLINGO-SUB%20id%3D%22lingo-sub-383522%22%20slang%3D%22en-US%22%3ESQL%20Best%20Practices%20for%20Biztalk%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383522%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jan%2025%2C%202009%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EAs%20promised%20in%20my%20last%20post%2C%20here%20is%20the%20post%20for%20SQL%20Best%20practices%20for%20a%20Biztalk%20installation.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAlthought%20the%20majority%20of%20best%20practices%20for%20a%20SQL%20Server%20instance%20are%20still%20valid%20for%20a%20Biztalk%20implementation%20we%20should%20consider%20some%20points%20that%20are%20different.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20a%20Biztalk%20instance%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EAuto%20create%20statistics%20must%20be%20disabled%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EAuto%20update%20statistics%20must%20be%20disabled%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EMAXDOP%20(Max%20degree%20of%20parallelism)%20must%20be%20defined%20as%201%26nbsp%3Bin%20both%26nbsp%3BSQL%20Server%202000%26nbsp%3Band%20SQL%20Server%202005%20in%20the%20instance%20in%20which%26nbsp%3BBizTalkMsgBoxDB%20database%20exists%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EIndexes%3C%2FLI%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EMost%20of%20the%20indexes%20in%20BizTalk%20Server%20databases%20are%20clustered%20(index%20ID%3A%201).%20The%20DBCC%20SHOWCONTIG%20command%20can%20be%20used%20to%20display%20fragmentation%20information%20for%20tables%20in%20the%20BizTalk%20Server%20databases.%20These%20indexes%20are%20GUID-based%20so%20it%20is%20normal%20for%20fragmentation%20to%20occur.%20If%20the%20Scan%20Density%20value%20of%20DBCC%20SHOWCONTIG%20is%20less%20than%2030%25%2C%20the%20indexes%20can%20be%20rebuilt%20during%20downtime.%20Many%20tables%20in%20the%20BizTalk%20Server%20databases%20contain%20columns%20that%20use%20DataType%20definitions%20where%20online%20indexing%20cannot%20be%20done.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EOn%20BizTalk%20Server%202006%20and%20on%20BizTalk%20Server%202004%2C%20the%20only%20supported%20method%20to%20rebuild%20an%20index%20in%20the%20BizTalkMsgBoxDb%20database%20is%20to%20run%20the%20bts_RebuildIndexes%20stored%20procedure.%20On%20BizTalk%20Server%202006%2C%20you%20can%20run%20the%20dtasp_RebuildIndexes%20stored%20procedure%20to%20rebuild%20indexes%20in%20the%20BizTalkDTADb%20database.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EMicrosoft%20only%20supports%20rebuilding%20database%20indexes%20during%20BizTalk%20Server%20downtime.%20You%20should%20stop%20all%20data%20processing%20that%20is%20related%20to%20BizTalk%20Server%20before%20you%20rebuild%20an%20index.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CLI%3ESQL%20Server%20agent%3C%2FLI%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EIn%20a%20SQL%20cluster%20implementation%2C%20consider%20the%20%22Affect%20the%20group%22%20setting%20on%20the%20SQL%20Server%20Agent%20resource%2C%20as%20it%20is%20a%20essential%20piece%20in%20a%20Biztalk%20implementation.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EFor%20more%20information%20please%20read%20the%20following%20articles%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Fsupport.microsoft.com%2Fdefault.aspx%2Fkb%2F917845%22%20mce_href%3D%22http%3A%2F%2Fsupport.microsoft.com%2Fdefault.aspx%2Fkb%2F917845%20%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20You%20experience%20blocking%2C%20deadlock%20conditions%2C%20or%20other%20SQL%20Server%20issues%20when%20you%20try%20to%20connect%20to%20the%20BizTalkMsgBoxDb%20database%20in%20BizTalk%20Server%202006%20or%20in%20BizTalk%20Server%202004%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fbb743398.aspx%22%20mce_href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fbb743398.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20BizTalk%20Server%20Database%20Optimization%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fcc296892.aspx%22%20mce_href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fcc296892.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Maintaining%20BizTalk%20Server%20Databases%20%3CBR%20%2F%3E%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3ECheers!%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383522%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2025%2C%202009%20As%20promised%20in%20my%20last%20post%2C%20here%20is%20the%20post%20for%20SQL%20Best%20practices%20for%20a%20Biztalk%20installation.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383522%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerTiger%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 11:11 AM
Updated by: