At my dayjob, we have a sizable Biztalk installation. We are currently running Biztalk 2016 / SQL Server 2016, and it ran well for half a year or so.
But, some months ago, we started getting deadlocks in the MessageBox database, causing substantial problems.
We are currently running a Microsoft case (of the “Production down” sort), and in that case we have learned, that at least two things, that seem to be an “accepted” way to setup biztalk, may not be supported by Microsoft.
Setting up MSDTC with a limited port range
Setting up MAXDOP on the SQL-server, on the database-level instead of the instance-level (this has only been possible since SQL Server 2016):
vs. the official MS guide (which is clearly written with regards to sql-server versions before 2016):
Question regarding MAXDOP:
I think the Biztalk Health Monitor tool is only checking the MAXDOP value on the Instance-level. While the instance-level MAXDOP value is indeed set to 4, The MAXDOP has been set to 1 on the Messagebox database
As far as we can read, only the MessageBox requires MaxDOP 1. Do you see a problem with doing it this way?
We use TempDB a lot in our queries and we have never tested with this configuration only per database so please change the SQL instance also so we make sure TempDB and other system databases are running ok as well.
MS comment with regards to the limited port range for MSDTC:
I see you have a very tight range of ports. This is not recommended. I would recommend to use full range of ports and instead do firewall on IPs instead of ports to not block BizTalk/SQL batches:
Ports range A range of TCP ports have been specified: 5000-5100
The default is 49152-65535 (around 16000 ports) . we increased from Windows 2003 which only had 4000 ports (1024-5000) up to the new range. Also if a range is needed it is recommended to use high range to not interfere with lower known ports, e g 20000 and higher range.
I should point out, that the reason for these deadlocks in MessageBox is still not determined. The system after all ran well with the database-specified MAXDOP and limited MSDTC port range for half a year or so.
For the MAXDOP setting, we are currently considering reversing the current setup – ie. setting the MAXDOP to 1 on the SQL instance and to a specific MAXDOP on our custom databases, which should satisfy Biztalks demand.