Microsoft BizTalk Server – BizTalk musings

BizTalk Health Monitor – Backup BizTalk Server (BizTalkMgmtDb) Disabled

BizTalk Health Monitor – Critical Warnings

If you run BizTalk Health Monitor and get the above warning, take it seriously and escalate it to the database support. This usually occurs when the database support team decides to use it’s own database backup methods. But Microsoft have made it very clear that this is not supported.

The Backup BizTalk Server job is the only supported method for backing up the BizTalk Server databases. Use of SQL Server jobs to back up the BizTalk Server databases in a production environment is not supported.

If the Backup BizTalk Server job is not run, the BizTalk Server database transaction logs will grow unbounded. The backup job truncates the transaction logs, which keep them from growing unbounded. If the BizTalk Server database transaction logs continue to grow, they could at some point fill the disk they are housed on.

Note

Using both the Backup BizTalk Server job and log shipping is currently the only fully documented and supported method for performing BizTalk Server database backup and restore.
Advantages of the Backup BizTalk Server Job

Do you require BizTalk Server licenses for Enterprise SSO on SQL Server?

This question “Do you require BizTalk Server licenses for Enterprise SSO on SQL Server?” has come up several times for me at work recently, as apparently some software licence monitoring tools pick up the fact that SSO is installed on the SQL Server used for BizTalk, and that it does not have a licence.

As per the the answer on StackOverflow on the question What are the licencing requirements to run Microsoft Enterprise Single Sign On service(SSO) on a seperate server? and the as per the Microsoft Article it links to High Availability for the Master Secret Server which has the following note, no, you do not need BizTalk licences for your SQL server, just the OS and SQL licences.

To reduce the hardware resources for a highly available solution, you can add the master secret server as a cluster resource in your SQL Server cluster. Note that you do not need to purchase additional BizTalk Server licenses to install the SSO service on the computer running the SQL Server.
https://docs.microsoft.com/en-us/biztalk/technical-guides/high-availability-for-the-master-secret-server

Other references

BizTalk Server 2020 Licensing Datasheet – Microsoft (PDF download)
Understand BizTalk Server Licensing (2013 and above) (BizTalk 360)

Book Review: Migrating to BizTalk Server 2020

Review of the Book, Migrating to BizTalk Server 2020

This book is written by Sandro Pereira, Tom Canter and Lex Hegt, all well known people in the BizTalk sphere. After the dedication about the Authors the introduction starts with an interesting brief history of the various BizTalk Server and then dives straight into what this book is for and what it covers, which unsurprisingly is about how to migrate from earlier version of BizTalk to the latest version Microsoft BizTalk Server 2020. And that is just the introduction.

The next chapters give an Overview of BizTalk Server 2020, what might be some of the reasons why you would want to upgrade; what you need to do to prepare for the migration; the options of doing an in-place upgrade (but also covers why you shouldn’t do this); migrating in a side by side way; and also looks at the option of migrating to Azure. Lastly it has an Appendix that list the BizTalk Dependencies their EOL (End Of Life), which can be one of the justifications you need for an upgrade.

All in all a very comprehensive book and something that you should read if you do have some older BizTalk environments that you are planning to migrate away from. As I’m currently in the midst of a project to migrate of both BizTalk 2010 and BizTalk 2016 to BizTalk 2020, this book has already proven valuable.

This book is available on Shopify

Book: Migrating to BizTalk Server 2020

Migrating to BizTalk Server 2020 by Sandro Pereira, Tom Canter & Lex Hegt is now available now on Shopify

Book: Migrating to BizTalk Server 2020

As per this post on linked in Sandro Pereira, Tom Canter & Lex Hegt have written a book called Migrating to BizTalk Server 2020.

Available now on Shopify

BizTalk: Max Degree of Parallelism & MaxConnections

I was recently asked to diagnose an issue with timeouts in BizTalk connecting to an API. So I ran BizTalk Health Monitor on the environment. There were two items that jumped out at me.

Max Degree of Parallelism

First that a DBA had twiddled with a setting that should not be changed for a BizTalk SQL Instance.

Critical Warnings: 1

Item Caption	Item Value	URLs	Rule ID
BizTalk Databases (General)
Max Degree of Parallelism for masterDB on DBSERVERNAME	4 (NOT SUPPORTED for MsgBox Dbs)	KB 917845	52

BHM Critical Warning

To fix this you have to do the following as per To configure the max degree of parallelism option

To configure the max degree of parallelism option

Using SQL Server Management Studio

In Object Explorer, right-click a server and select Properties.
Click the Advanced node.
In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.

Using Transact-SQL

Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. This example shows how to use sp_configure to configure the max degree of parallelism option to 16.

USE AdventureWorks2012 ;  
GO   
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism', 16;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO

We of course want to set it to 1.

Further reading on Max Degree of Parallelism

Maxconnections

Secondly, probably the root cause of the timeouts, BizTalk performs very poorly under load if it is only allowed the default two connections to web services.

Other Warnings: #

Item Caption	Item Value	URLs	Rule ID
Tuning
‘maxconnection’ property	Is not present in PRDCMSBIZ1: BTSNTSVC.EXE.CONFIG – You can configure the number of concurrent connections that the SOAP adapter opens for a particular destination server by adding “maxconnection” entry	SOAP Adapter Configuration and Tuning Parameters	404
‘maxconnection’ property	Is not present in PRDCMSBIZ1: BTSNTSVC64.EXE.CONFIG – You can configure the number of concurrent connections that the SOAP adapter opens for a particular destination server by adding “maxconnection” entry	SOAP Adapter Configuration and Tuning Parameters	404
‘maxconnection’ property	Is not present in PRDCMSBIZ2: BTSNTSVC.EXE.CONFIG – You can configure the number of concurrent connections that the SOAP adapter opens for a particular destination server by adding “maxconnection” entry	SOAP Adapter Configuration and Tuning Parameters	404
‘maxconnection’ property	Is not present in PRDCMSBIZ2: BTSNTSVC64.EXE.CONFIG – You can configure the number of concurrent connections that the SOAP adapter opens for a particular destination server by adding “maxconnection” entry	SOAP Adapter Configuration and Tuning Parameters	404

BHM: Other Warnings

The link for SOAP Adapter Configuration and Tuning Parameters doesn’t exist anymore, but see my blog post Configuring maxconnection in BizTalk. It’s is a simple fix, and the throughput of your BizTalk server to web services will be markedly improved.

Microsoft BizTalk Server 2016 Feature Update 3 with CU9

Microsoft BizTalk Server 2016 Feature Update 3 with CU9 is available

I’ve already written about CU9 here.

MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb failing

I had a test environment where the SQL Agent job MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb had been failing for a while. This job runs exec bts_CleanupDeadProcesses in the message box DB. It was failing with the error

Executed as user: Domain\Account. Could not find stored procedure ‘dbo.int_ProcessCleanup_OLDHOSTNAME’. [SQLSTATE 42000] (Error 2812). The step failed.

Now the clue in this was that the stored procedure was trying to execute another stored procedure with a name of an a host that had been decommissioned, along with several others, after there had been some issues with them and replacement host and host instances had been created.

In that stored procedure it had this

DECLARE btsProcessCurse CURSOR FAST_FORWARD FOR
SELECT s.uidProcessID, s.nvcApplicationName FROM dbo.ProcessHeartbeats AS s WITH (ROWLOCK READPAST)
WHERE (s.dtNextHeartbeatTime < @dtCurrentTime) OPTION(KEEPFIXED PLAN)

So to see what that was getting I ran the below

declare @dtCurrentTime datetime

set @dtCurrentTime = GetUTCDate()

select *
FROM dbo.ProcessHeartbeats WITH (ROWLOCK READPAST)
WHERE (dtNextHeartbeatTime < @dtCurrentTime) OPTION(KEEPFIXED PLAN)

This gave a list of very old heartbeats for all the old host names. To resolve the issue I changed the select * into a delete, and ran that, and the SQL Agent Job is now running correctly.

BizTalk 2020 CU1 released

BizTalk 2020 CU1 was released on July 28th 2020 as announced here

As per previous version of BizTalk it has a number of SAP/IDOC fixes.

Fixes

4 SAP/IDOX
2 fixes related to the WCF-SQL adapter (order of elements and FILESTREAM operations)
1 for the Oracle DB adapter to handle Database artifacts with nested types in Oracle 18c and later versions
Fix for predefined WCF Bindings not taking in account a default Web proxy configuration
2 JSON Encoder fixes (1 one which was fixed for BizTalk 2016 in CU7)
Fix for XLTS errors not being reported in XSLT stylesheet
Fix for EDI receive location with named SQL instance
Fix for ESB Toolkit to use gMSA.
Fix to remove dependency on VS 2019 version 16.3.9
FIX: Mapper toolbox not working for WCF Workflow Service Application after upgrade to BizTalk 2020

Improvements

FTP improvement (macros for Before Put and After Put)
Dynamic send ports able to use Officer 365 Outlook adapter types.

Visual Studio Extension

In addition to installing CU package, also update/install new version of BizTalk Server Visual Studio extension (3.13.2.0). The extension can be installed from https://marketplace.visualstudio.com/items?itemName=ms-biztalk.BizTalk or within Visual Studio – Manage Extensions.

As per other CUs, you can find them on the page Service Pack and cumulative update list for BizTalk Server

Max worker threads for SFTP, MQSC or DB in BizTalk

A about March or earlier, the latest rules for BizTalk Health Monitor started to show the following important warning.

BizTalk Health Monitor Important warning – The host instances of ‘ReceivingSFTP’ need more worker threads per cpu to run correctly SFTP Receive Locations. Increase so the “Maximum Worker Threads” property to 500 for these host instances and be sure they are dedicated for this SFTP Receive Locations

I had already been advising clients to run SFTP receive locations on a separate host instance due to issues where it would silently stop receiving requiring a host instance restart to resolve, possibly related to FIX: SFTP adapter receive locations stop working intermittently during higher load in BizTalk Server

BHM rule 637 – Suggest to Increase MaxWorkerThreads for SFTP, MQSC or DB2 Receive Locations

The rule itself also talks about MQSC and DB, but as I didn’t have any receive locations for those it was only warning about SFTP.

I’ve seen that warning even on a BizTalk 2010 environment that has a Custom SFTP adapter installed, possibly because it was also called simply SFTP. However it does not warn about third party SFTP adapters such as the nSoftware one which has a different name.

BizTalk Host Instance Settings – Maximum worker threads.

However, even after changing the Max worker threads to 500 and restarting the Host Instance and re-running BHM, the warning did not go away. Even moving the receive locations running on the nSoftware onto another host instance, and un-configuring the adapter on that host instance didn’t make any difference.

The BizTalk adapter’s default configuration is to poll every 5 seconds. This is rather excessive and should be reduced.

Recommendations for SFTP Receive Locations

Have a dedicated host for receive locations using SFTP
Increase the Maximum Worker threads setting to 500 on that host instance
Check how frequently you poll (the default is 5 seconds), increase the delay if that is not needed.
Put a schedule on to only poll during the periods you need.
Disable message body tracking if it is not needed.