I’m creating my own checklist of things to watch out for on a BizTalk install, beyond the basic BizTalk hardware/software prerequisites.
Sites below are for BizTalk 2010 – but same applies for most any other release:
https://msdn.microsoft.com/en-us/library/aa577661.aspx – List of Active Domain (AD) groups and service accounts.
I took this, put it in MS/Word and added a column to the left with a suggested name for the client. Then I asked the client to verify my suggest name to fit their existing AD group/account naming conventions. I included an “X” for each environment, e.g. (D=DEV, T=Test, Q=QA, P=Prod), or a 3-character (DEV, TST, QUA, PRD). Example, BTSPRDADMINS (or if you like mixed case, BtsPrdAdmins).
https://msdn.microsoft.com/en-us/library/gg634639(v=bts.10).aspx Windows Configuration Checklist –
– turn off hyperthreading,
– Ensure Windows Server processor scheduling is set to “Background services”
– Place the Windows paging file on a separate local physical drive.
– Stop real-time virus scanning of database files and .xml, .csv, etc…
https://msdn.microsoft.com/en-us/library/gg634450(v=bts.10).aspx – IIS Configuration Checklist –
https://msdn.microsoft.com/en-us/library/gg634642(v=bts.10).aspx – SQL Configuration Checklist
– enable Trace Flag 1118 (TF1118) – to reduce contention
– sp_configure ‘Max Server memory (MB)’,(max size in MB)sp_configure ‘Min Server memory (MB)’,(min size in MB)
– Autogrow to fixed MB not % for BizTalk DBs
https://msdn.microsoft.com/en-us/library/gg634496(v=bts.10).aspx – SQL Server Settings that Should Not Be Changd
1) Max Degree of Parallelism (MDOP) is set to “1” during the configuration of BizTalk Server for the SQL Server instance(s) that host the BizTalk Server MessageBox database(s). This is a SQL Server instance-level setting. This setting should not be changed from the value of “1”. Changing this to anything other than “1” can have a significant negative impact on the BizTalk Server stored procedures and performance. If changing the parallelism setting for an instance of SQL Server will have an adverse effect on other database applications that are being executed on the SQL Server instance, you should create a separate instance of SQL Server dedicated to hosting the BizTalk Server databases.
2) Auto create statistics and auto update statistics are turned off for the BizTalk MessageBox DB.
zTalk Server stored procedures have exact joins and lock hints specified on the queries. This is done to ensure that the optimal query plan is used by the BizTalk Server queries in SQL Server. The distributions and expected results for the queries are known; the approximate number of rows returned is known. Statistics are generally not needed.
3) BizTalk Server does not support defragmenting indexes. “DBCC INDEXDEFRAG” and “ALTER INDEX … REORGANIZE …” are not supported since they use page locking, which can cause blocking and deadlocks with BizTalk Server. BizTalk Server does support database index rebuilds (“DBCC DBREINDEX” and “ALTER INDEX … REBUILD …”), but they should only be done during maintenance windows when BizTalk Server is not processing data. Index rebuilds while BizTalk Server is processing data are not supported.
Index fragmentation is not as much of a performance issue for BizTalk Server as it would be for a DSS system or an OLTP system that performs index scans. BizTalk Server does very selective queries and updates and BizTalk Server stored procedures should not cause table or index scans.
https://blogs.msdn.microsoft.com/biztalk_core_engine/2007/01/04/what-you-can-and-cant-do-with-the-messagebox-database-server/ – What you can and cannot do with the MessageBox Database
The next very, very important thing to understand is that while the database is installed on your server, it is not “your” database. It is our (or if you catch me on the wrong day, not thinking, “my”) database
A quick list of things which can effect the query plans:
– Statistics (don’t enable these)
– Parallelism (don’t turn this on )
– Table structure (don’t add indexes, columns, triggers, … If you do you will hear silence when you call for help)
– Stored procedures (don’t change them. You can look all you want, but no touching)