Servers for BizTalk are fundamentally stateless. All the configuration, tracking, and processing data needed for BizTalk to function normally are stored in the SQL Server databases.
The MessageBox is the brain of every BizTalk system and is essential to its operation. The BizTalk MessageBox is used for all inbound and outbound messages, orchestration execution, dehydration, rehydration, and message tracking. We will have a deeper understanding of the BizTalk MessageBox in this blog.
The core of the publish-subscribe messaging engine architecture used by the BizTalk server is the BizTalk MessageBox database. This message box must store the messages, message characteristics, subscriptions, orchestration states, tracking data, and other information of a similar nature.
Whenever messages are posted into an endpoint location that is configured in a BizTalk Receive Location, an adapter will pick them. If the messages are not in XML format, they will be converted into XML format, to make sure BizTalk understands tehm. Internal XML messages are Published into the MessageBox and then are further processed by Subscribers. The BizTalk Server Publish-Subscribe Architecture completely relies on this database. The logical routing of messages, fulfilling subscriptions and message tracking is carried out through this database.
One or more Microsoft SQL Server databases and the Messaging Agent are the two parts of MessageBox. The persistence store for a variety of items, including messages, message portions, message characteristics, subscriptions, orchestration state, tracking data, host queues for routing, and others, is provided by the SQL Server database. One or more MessageBox databases may be maintained by the BizTalk Server group, into which it publishes messages and from which recipients of those messages extract messages.
The database provides some of the logic for routing messages and fulfilling subscriptions. However, the component that isolates and abstracts the database component, as well as the interface via which BizTalk Server communicates with the MessageBox, is the Message Agent. The Message Agent is a Component Object Model (COM) component that offers interfaces for sending and receiving messages as well as publishing and subscribing to them. The adapter framework and orchestrations, as well as other BizTalk Server components, do not communicate with the MessageBox in any other way than through this interface.
BizTalk Server stores messages associated with suspended pipelines in the MessageBox database. If a failure occurs in the pipeline, the BizTalk Server suspends the instance of a message. There are two types of suspended service instances:
Depending on the reason for the suspension, you might be able to successfully resume messages that were suspended by BizTalk Server. For instance, if an orchestration encounters a Suspend shape or if a transport was unable to deliver a message, BizTalk Server does not automatically remove the suspended instances. Before deleting a service instance from the suspended queue, you can opt to save the related messages to a disc.
Each BizTalk server in a BizTalk group has access to the BizTalk MessageBox, which is implemented as a SQL Server database. In some extremely specialised high-load or latency-critical applications, multiple MessageBoxes may be used.
In terms of processing, BizTalk’s storage engine is called the MessageBox, a sizable database. The most crucial distinction to be made is that the MessageBox cannot be altered in any way.
The MessageBox database contains four tables called Spool, MessageParts, Parts, and Fragments that hold all the physical messages used by BizTalk. Because each message can be divided into one or more parts, each of which can be further divided into one or more fragments, this is the case.
BizTalk allows for some degree of control over how messages are fragmented. When the Messaging Engine overflows a message into the MessageBox before it has finished processing, it is determined by the big message fragment size setting for the BizTalk group. By doing this, processing in the context of a receive port is made possible by the BizTalk process, hence preventing out-of-memory errors.
In the BizTalk Message Box database, queue tables are created for each host. The BizTalkServerApplication default host is generated when BizTalk is configured. The following tables for this host can be found in the MessageBox database:
Only references to the actual messages are stored in these host queues. If there is work on the queues to read and resources are available to process the work, each instance of the host will poll its primary queue table within its BTSNTSVC.EXE process.
The polling period will gradually shorten until it reaches the maximum polling interval if there is no work in the queue.
The Spool, MessageParts, Parts, and Fragments tables are where a host instance gets the physical message data when processing a message. Each message may contain one or more subscribing services, which may be hosted on various hosts, which accounts for the distinction.
Each message can be handled by several hosts while only having a single instance saved by keeping a reference to it in the host queues. It includes the message reference, and each instance of that host will check its main queue table for updates. The tables Spool, MessageParts, Parts, and Fragments will allow access to the actual message content.
When a message is sent to a single subscriber, a reference is instantly added to the MessageZeroSum table, which is used to identify the physical messages for clean-up by a job running inside of SQL Server Agent. This indicates that the MessageBox database will swiftly be cleared of these kinds of messages.
As soon as a message intended for many subscribers has been processed, the BizTalk service won’t delete the message from the database or add a reference to it in the MessageZeroSum table since it is unsure of whether there are any additional recipients (for example, if it is referenced in another queue). These messages’ reference counts (or subscriber counts) are kept up to date using a mix of the tables MessageRefCountLog1, MessageRefCountLog2, and MessageRefCountLogTotals. As an additional table, the ActiveRefCountLog table is also utilised.
The reference counts in these tables are combined to identify which messages can be erased using a job that runs inside SQL Server Agent. Following their insertion into the MessageZeroSum table, the Message IDs that can be removed are subsequently cleaned up by another SQL Server Agent operation using the data from the MessageZeroSum table.
Instead of writing tracking data straight to the BizTalk Tracking database, BizTalk Server first caches it in the MessageBox database before moving it to the BizTalk Tracking database. At any stage of processing BizTalk messages, data could be lost in case of failures. This makes backing up the databases necessary.
The only by Microsoft supported way to make backups of the BizTalk databases, is by using the Backup BizTalk Server job in SQL Server. That SQL job is triggered from scripts in the BizTalkMgmtDb and it takes full and incremental backups of all databases that show up in a view called admv_BackupDatabases. By default, the following list of BizTalk databases will be backup up by the job.
We can use the below stored procedure and manually purge data from the MessageBox bts_CleanupMsgbox
The database subsystem’s performance will reduce if the BizTalk Message Box database (BizTalkMsgDb) expands too much. For large systems with lengthy transactions, the BizTalkMsgDb should, as a general rule, never increase in size over 5 Gb. The BizTalkMsgDb size should be substantially less than 5Gb for environments with high traffic but no persistent transactions.
To reduce the database size, use the below stored procedure on your message box database. If the stored procedure does not exist, create it by running the sql script found in the directory “\Schema\”.
The way to truncate the log file on SQL Server 2019 in case of emergency is the following:
An example is taken from DBCC SHRINKFILE T-SQL reference:
USE [AdventureWorks2019] GO DBCC SHRINKFILE (N'AdventureWorks2019_Log' , 0, TRUNCATEONLY) GO
To maintain a database from a DBA perspective, refer to the blog here.
The BizTalk health monitor (BHM) application extracts data from a BizTalk System, searches for numerous potential problems that may be urgent or require attention, and simply shows them. To an extent, this tool is comparable to the BizTalk Best Practices Analyzer (BPA), a health assessment tool for a BizTalk system that produces reports. The BHM and the BPA are complementary to one another. The BHM can assist administrators in quickly obtaining information on the system’s production with little to no influence on the current environment. Administrators can use it to find potential problems that might be serious or require attention.
The BHM tool is included with BizTalk Server out of the box to create reports for activity related to performance and health monitoring. It gathers information about the configuration, database, and performance of the BizTalk Server.
BHM has over 400 rules to verify different things on the BizTalk server environment and gathers the results that provide information on the environment’s health. The BizTalk MsgBox database and other BizTalk databases are analysed by BHM, which then creates an HTML file with several REPORTS, including a “WARNINGS REPORT” that includes some reports with yellow and red warnings.
Since 2013, BizTalk Health Monitoring (BHM) has been deployed in its place, replacing the MBV utility. An MMC snap-in called BizTalk Health Monitor (BHM) enables users to perform maintenance procedures and keep track on the condition of BizTalk Server installations. BHM can be downloaded here.
When messages enter the BizTalk Server, they are stored in the MessageBox database. The service instances may change states at any time, including Active, Ready to Run, Dehydrated, Suspended (Resumable) and Suspended (Non-Resumable).
In case of suspended service instances, the BizTalk Server administrator needs to manually log in to the BizTalk Server Administration Console and resolve the issue with the suspended service instances. Let’s say BizTalk Server compels the administrator to perform a manual intervention because we have 50k suspended instances.
This issue is resolved by BizTalk360’s MessageBox (Queries) feature, which allows administrators to search the MessageBox database for such messages and take appropriate action without logging into the BizTalk Server Administration Console. Despite giving BizTalk administrators this option, BizTalk360 takes it a step further to lessen the amount of manual involvement needed from the administrators. Administrators can decide what needs to be done when a service instance becomes suspended by using the Message Box Data Monitoring concept.
E.g., the administrator can set up an alarm like “If there are more than 200 Suspended Service Instances between 09:00 AM and 05:00 PM, resume all the instances”. He can simply log in to the BizTalk360 Data Monitoring Dashboard to see the status of the MessageBox data for the day. He can also set up email notifications for the alarm. By doing so, the administrator eliminates the need to often log in to the BizTalk360 application and check for the status of the service instances.
Similar functionality is provided by BizTalk360 and the BizTalk Server Administration Console allowing administrators to search the Message Box database for all running and suspended service instances. The Message Box (Queries) section’s web-based aesthetic sets it apart from the admin console. Additionally, BizTalk360 offers a few extra features like improved security features like restricting access to content and context and operations against service instances, as well as an integrated knowledge base that enables users to attach knowledge base articles next to suspended service instances.
Also, BizTalk360 has integrated with MBV/BHM for many years. This integration enables the BizTalk360 user to schedule MBV/BHM runs and view the output of the different runs of the tool directly from within BizTalk360. The integration of MBV/BHM with BizTalk360 adds to the One platform philosophy. By also monitoring the output of MBV/BHM, we remove some of the clutter which appears in the mailbox of the user, providing a good focus on the topics that matter. With some customization, you could have even more fine-grained monitoring of the output of BHM.
BizTalk360 is a product that provides advanced monitoring and management capabilities for Microsoft BizTalk Server. If you would like to try BizTalk360, why not give it a go?
It takes about 10 minutes to install on your BizTalk environments and you can witness and check the security and productivity of your BizTalk Environments. Get started with the free 30 days trial.
