MessageBox Queries

BizTalk Server uses a publish-subscribe messaging engine architecture and the heart of this architecture is the BizTalk MessageBox database (BizTalkMsgBoxDb). The message box is a Microsoft SQL Server database that is responsible to store the messages, message properties, subscriptions, orchestration states, tracking data, and much more of such information. A BizTalk Server group may have one or more MessageBox databases into which the messages are written.

Service Instances and States

When a message enters the BizTalk server, the receive ports, orchestrations, and send ports process the messages and there are occasions when the service instances can move to different states such as:

• Ready to Run
• Active
• Dehydrated
• Completed
• Suspended (Resumable)
• Suspended (Non-Resumable)
• Pending

For instance - when the message is being sent to an external web service and the service is unavailable; the send port service instance will try its maximum to send the message to the endpoint. If the message can't be sent, the send port will use the Retry mechanism to send the message again after some time. If the retry mechanism fails as well, the send port service instance will suspend itself with the Suspended (Resumable) state. This means that when the web service is up and running at a later point in time, the administrator can resume the service instance to send the message. There may be situations when the service instance will suspend itself to a non-resumable state, which means the message can no longer be processed. In this case, the administrator has no other choice but to terminate the service instance.

BizTalk360 offers similar capabilities as the BizTalk Server Administration Console that allows administrators to query the Message Box database for all running and suspended service instances. The major difference from the admin console is the web-based look and feel of the Message Box (Queries) section. In addition, BizTalk360 offers few extended capabilities such as better security capabilities like limit viewing content/context and limit operations against the service instances, and an integrated knowledge base where users can attach knowledge base articles next to a suspended service instance.

Building Query Expressions for Service Instances

BizTalk360 allows users to execute queries on the BizTalk Message Box database (BizTalkMsgBoxDb) to get the list of suspended service instances and running service instances. When the query is executed from the UI, BizTalk360 triggers a service call that retrieves the list of service instances and their current state. In addition, the service call will retrieve other information related to the service instance such as creation time, the error code associated with the error, and its description. Follow the steps as shown below to query the message box database and retrieve the information:

1. Log in to the BizTalk360 application
2. Select an environment from the Landing page
3. Click the Administration tab in the Navigation panel
4. Next to the BizTalk Services tab, click the expand button and select Message Box Queries
5. In the Message Box Queries screen, under Build Query Expression:
   • Select Query - Choose one of the options from the drop-down (All In-progress service instances, running service instances, Suspended Service Instances) to retrieve the status of service instances
   • Max Matches - Select the number of records to be fetched from the message box database. Ex. 10, 25, 50, 100....
   • Add a filter - To filter the results based on different parameters. The parameters will vary while retrieving the status of In-progress service instances, running service instances, and suspended service instances. Once the filter criteria are selected, you will automatically see the values for the filter listed in the drop-down. Ex. Host Name, Is Equal To, BizTalkServerApplication. When the hostname is selected from the drop-down, the value BizTalkServerApplication & other host names will automatically be listed in the drop-down. The user can select the required value. 
   • By Selecting the group by options filter you can filter the application name, 
6. X - To delete the filter criteria
7. Click Run Query. The Query Results section displays the list of All In-progress service instances, Running service instances, and Suspended Service Instances
8. The user can organize and categorize the data by grouping the column

This example shows you build a query for all in-progress service instances with filter conditions set for Messaging service class.

When the queries are executed against the MessageBox database, BizTalk360 retrieves the list of suspended and running service instance information from the database. The query result section contains a Properties icon that will contain the properties of the service instance. The service properties section will contain general information about the service instance, the current state, information about the error and the error code, and the associated messages that are referenced by the service instance. To view the service instance properties information, follow the steps as shown below:

1. In the Query Results section, click the Properties icon (represented as an eye icon) to view the properties of the service instance
2. The Service Properties window contains information about the service instance
   • The General tab contains information about the service instance, the current state, and the creation time
   • The Error Information contains the error code and error description information
   • The Messages tab contains the information of the messages that are referenced by the service instance. Click the Properties icon to view the message context and content properties

This example shows you how to build a query for all in-progress service instances and view the Service properties for one of the Service Instances from the query results.

Building Query Expressions for Messages

In addition to retrieving the service instance state information, BizTalk360 allows users to execute queries and retrieve the information of the messages from the message box database. When the query is executed, BizTalk360 triggers a service call that will retrieve the message information such as Message ID, Message Status, Service Status, Service Name, Class, Instance ID, and so on.
Follow the steps as shown below to query the message box database and retrieve the information:

1. Log in to the BizTalk360 application
2. Select an environment from the environmental panel
3. Click the Administration tab in the Navigation panel
4. Click the expand button next to BizTalk Services tab and click the Message Box Queries link
5. In the Message Box Queries screen, under Build Query Expression:
   • Select Query - Select Messages from the drop-down to retrieve the status of messages
   • Max Matches - Select the number of records to be fetched from the message box database. Ex. 10, 25, 50, 100....
   • Add a filter - To filter the results based on different parameters such as creation time, error adapter, error code, error description, host name, instance status, message ID, message status, message type, service class, service instance ID, service name, service type ID, and URI. Once the filter criteria are selected, you will automatically see the values for the filter getting listed in the drop-down. Ex. Host Name, Is Equal To, BizTalkServerApplication. When hostname is selected from the drop-down, the value BizTalkServerApplication & other host names will automatically be listed in the drop-down. The user can select the required value.
6. X - To delete the filter criteria
7. Click Run Query. The Query Results section displays the list of messages and their status

This example shows you build a query for Messages with a filter condition set for the Orchestration service class.

BizTalk360 has the capability where users can query the messages in the MessageBox database and view the message properties, content, and context information. However, the users need to have the permissions to view this information. The Messages tab will be visible only if the 'Messages Content/Context' option is selected for the current user profile. To view the message information, follow the steps as shown below:

1. In the Query Results section, click the Message Properties option to view the properties of the message
2. The Message Properties window contains information about the message
   • The General tab contains information about the message such as message ID, creation time, host name, service name, class, instance ID
   • The Context Properties and Content Properties contain additional information about the message

Click the video, this example shows you build a query for Messages and view the Message properties and content.

Downloading the Messages

BizTalk360 offers the identical capabilities as the BizTalk server administration console with respect to querying the message box to list the suspended and running service instances. In addition, BizTalk360 offers an additional capability that allows users to download the message content and context (in zip format) and email to their team members to diagnose specific issues. The download and e-mail functionality is made available both at the Service Instance level and Message level.

Downloading the message as a file

1. Log in to the BizTalk360 application
2. Click the Administration tab in the Navigation panel
3. Click the expand button against the BizTalk Services tab and select the Message Box Queries link
4. Select the appropriate filter criteria (if any), and click Run Query
5. Once the query results are populated, select the check box against a query result row. You will notice a Download as a button with a drop-down arrow in the right corner
6. Click the Download as a button and select As File from the drop-down. A file will be downloaded that will contain the XML file output of the message, and an associated OUT file

Emailing the message information

1. Once the query results are populated, select the check box against a query result row. You will notice a Download button with a drop-down arrow next to the Resume, Suspend, Terminate buttons
2. Click the Download button and select Send as Email from the drop-down
3. In the Send messages as Email attachments pop up screen, enter the email addresses of the recipients to whom the email has to be sent
4. BizTalk360 allows the users to enter a maximum of 10 email addresses. The email addresses should be separated by a comma
5. Once the email addresses are entered, click Send Email
6. The SMTP settings need to be configured in BizTalk360 in order to be able to trigger email alerts

Operations that can be performed on Service Instances/Messages

In addition to querying the service instances, users can perform operations such as Suspend, Resume, and Terminating the service instances depending on the requirement. For instance, when a service instance is in the Suspended (Resumable) state, the administrator can choose to Resume the service instance or Terminate the service instance. The reason for performing the service instance operation can be added as a comment, the same will be audited for future reference. Follow the same procedure to suspend/resume/terminate a service instance and message.

Resuming a Suspended Service Instance
When a service instance gets suspended for some reason, it can either move into a resumable or a non-resumable state. When in the resumable state, the administrators have an option to manually start the service instance through the portal. Similarly, the administrator can choose to terminate the service instances that are in the non-resumable state.

1. In the Query Results section, select the checkboxes against the service name(s). Click the Select All checkbox option to select all the service instances
2. Click the Resume button to resume the selected suspended service instances
3. The user can perform the required operation in 2 different ways:
   • Selecting the checkboxes against the service name(s) and clicking on the Operation buttons (Resume/Suspend/Terminate) at the top of the grid
   • Selecting check box against the service name. The user will notice an inline operation showing up right next to the checkbox with the possible operations that can be performed on the service

Terminating a Suspended Service Instance

1. In the Query Results section, select the checkboxes against the service name(s). Click the Select All check box to select all the service instances
2. Click the Terminate button to terminate the selected suspended service instances

Suspending a Service Instance

1. Select the query as Running Service Instances from the drop-down and appropriate filter criteria, and click Execute Query
2. In the Query Results section, select the checkboxes against the service name(s). Click the Select All check box to select all the service instances
3. Click Suspend button to suspend the selected suspended service instances

Word Wrapping Option for Message Contents

BizTalk360 provides an option to enable word wrap for the message contents and search the message contents. 

• In the Query Results section, click the Message Properties option to view the properties of the message. 
• In the Message Content section, a toggle is available to enable word wrapping for the message contents.
• You can also search the message contents by clicking CTRL + F.
• An option to copy the message contents is also available.

Integrated Knowledgebase 

Let us imagine that there is a service instance for a BizTalk application called "Contoso" and the service instance has moved to the Suspended (Resumable) state because the published message could not be routed as no subscribers were found for the message. The error code connected to this error is '0xC0C01B52 '. This information is available to the support team under the 'Error Description' section. With this information, the support team may/may not be able to identify the root cause of the error and fix it. The support team can find a solution/workaround by referring to multiple blogs, support articles, and so on. BizTalk360 provides the flexibility to the support team to document these solutions (as knowledge base articles) which will be useful in the long run. 

The articles can be accessed in the following ways:

1. From the grid, by clicking on the Knowledge Base icon in the row corresponding to the particular error code

2. By clicking on the knowledgebase icon present in the MessageBox Queries section

Creating a new Knowledge Base article

Knowledge Base articles can be created directly from the MessageBox Queries feature. To create such an article, use the following steps:

1. Log in to the BizTalk360 application
2. Click the Administration tab in the Navigation panel
3. Click the expand button against the BizTalk Services tab and select the MessageBox Queries link
4. Click on the Knowledgebase icon present in the left corner of the page
5. Click on the Create New Article button
6. Provide the details such as the error code, description, environment
7. Click on the Save button to save the details