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

 

Advertisement

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

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

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 CaptionItem ValueURLsRule ID
BizTalk Databases (General)   
Max Degree of Parallelism for masterDB on DBSERVERNAME4 (NOT SUPPORTED for MsgBox Dbs)KB 91784552
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

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

Using Transact-SQL

  1. Connect to the Database Engine.
  2. From the Standard bar, click New Query.
  3. 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 CaptionItem ValueURLsRule ID
Tuning   
‘maxconnection’ propertyIs 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” entrySOAP Adapter Configuration and Tuning Parameters404
‘maxconnection’ propertyIs 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” entrySOAP Adapter Configuration and Tuning Parameters404
‘maxconnection’ propertyIs 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” entrySOAP Adapter Configuration and Tuning Parameters404
‘maxconnection’ propertyIs 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” entrySOAP Adapter Configuration and Tuning Parameters404
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.

BizTalk 2020 CU 3

BizTalk 2020 CU 3 is now available at, KB5007969 – Cumulative Update 3 for Microsoft BizTalk Server 2020

Fixes for adapters include

  • WCF fix for proxy settings
  • 2 fixes for MQSeries
  • 2 fixes for WCF-SAP
  • 1 fix for the SharePoint adapter
  • 1 fix for the Office365 Outlook Email adapter

Improvements

On the blog post that announced it, they also say

As a quick reminder, for BizTalk Developer Tools please also update/install version 3.13.2.0 of the BizTalk Server Visual Studio extension in addition to installing the CU package. The extension can be installed from https://marketplace.visualstudio.com/items?itemName=ms-biztalk.BizTalk or from within Visual Studio – Manage Extensions. Please note that we have not updated the extension since CU1 so you can skip this step if you have already installed it previously.

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

PURGE Everything in the DTA #msbts

Due to too much message body tracking after a release of an application to Production, the DTA size grew too large in BizTalk 2013 R2.

DTA Size

This also caused backups to become too large, and the DTA Purge and Archive job running for too long and not reducing the size of the DTA database fast enough.  The large DTA database size could also be impacting BizTalk performance.

We intend to use the BHM Task “PURGE Everything in the DTA”

One of the pre-requisites to running BHM “All BizTalk SQL Agent jobs are stopped”.  Does this include the Backup jobs?  Yes

The DBA is also concerned that the Log files will blow out in size and wants to set the Recovery Model to Simple, would this approach be correct and supported by Microsoft?

BizTalk Team

  1. Do a partial stop all applications and allow running processes to finish
  2. Turn off host instances and set to manual on the BT Prod server.
  3. Stop IIS
  4. Inform SQL team to proceed with their task.

SQL Team to take back up of the SQL database with the following steps

  1. Run BizTalkMgmtDb.dbo.sp_ForceFullBackup stored procedure
  2. Run the Backup BizTalk Server (BizTalkMgmtDb) job to back up the database
  3. Stop all BizTalk SQL Agent jobs
  4. Set the Recovery Model to Simple
  5. Inform BizTalk team that Backup is complete

BizTalk Team

  1. Run BHM task to clean Orphaned Instances (step not needed as Purge will take care of those)
  2. Run BHM task to PURGE Everything in the DTA
  3. Inform SQL team that purge is complete

SQL Team

  1. Shrink the BizTalkDTADb
  2. Set the Recovery Model to Full
  3. Start the SQL Agent jobs
  4. Inform the BizTalk team that Shrink is complete and SQL Agent jobs are running.

BizTalk Team

  1. Start SSO and Host Instances and set to Automatic Start
  2. Start IIS
  3. Enable Receive locations (Except those with DIS prefix)
  4. Monitor for any errors or issues

Question also posted on Technet

Mike the Tester

A blog about all things testing. Views are my own

Nick's Blog

Biztalk gotcha!

Whatever

FURIOUSLY REASONABLE

A Different Kind of Query

Technology, music, life, and musings

Vierodan IT Space

Spread up technology

BizTalk Server Help book!

Made easy and simple

nethramysooru

Blog on BizTalk Server

Blog Of the Serverless Spirit

Stray Notions on All Things Microsoft Azure and BizTalk

Microsoft Azure/BizTalk_Read

Let's learn and share !

Boutaleb Hicham

Biztalk & Azure Integration Architect : Logic Apps, Serverless, Azure Service Bus, BizTalk Server, and Hybrid Integration

Pieter Vandenheede

Stories, tips & tricks for BizTalk Server, Azure, Data Science & Machine Learning

Glenn Colpaert

Blogging with my head in the (Hybrid) Cloud and my feet on premises! Azure / IoT / Integration

BizMunch

BizTalk blog by Knut Urke

Dragon's BizTalk Blog

A blog about BizTalk, and other integration tools.

The Deployment Bunny

OS Deployment, Virtualization, Microsoft based Infrastructure...

Integration Made Easy

Demystify Integration Development