This post was originally published here

BizTalk360 is the one-stop monitoring product for your BizTalk environment. Installing and upgrading BizTalk360 is a seamless process when all the permissions and configurations are in place. Each software product has a frontend and a backend. Most often people focus on the frontend more to give a rich user experience. On the other hand, the backend would be considered only when it comes to the performance of the product. Of course, it is the DBAs responsibility to check the health of the database.

BizTalk360 being an on-premise product, it has its own database which becomes created while installing the product. It is always a good practice to take care of the database and the data, to avoid the performance problems as per the saying,

“Data is a precious thing and will last longer than the systems themselves”

In this blog, I will give in-depth insight into the BizTalk360 database and some useful tips to take care of the database and the data to make the DBA’s work a little easier.

The BizTalk360 Database

The BizTalk360 application comes with the BizTalk360 database which contains about 116 tables, and a few stored procedures and views in it. Let us dig in and see what the important tables in the database are. One main point to be noted is that no changes should be done at the database level manually unless and until it is important and required.

The below table gives a clear picture of the important database tables related to the BizTalk360 modules.

Modules

Table Names

Installation

b360_admin_BizTalkEnvironment

b360_admin_GlobalProperties

Database information

b360_admin_DBMigration_Status

b360_admin_DBVersion

b360_admin_DBVersion_History

License information

b360_admin_LicenseActivation

Monitoring

b360_alert_Monitor

b360_alert_MonitorExecution

b360_ev_MonitoredServers

b360_mon_Service

Data Monitoring

b360_st_DataMonitorTasks

b360_st_DataMonitorResults

b360_st_DataMonitorTaskActionResults

b360_st_ProcessMonitorResults

b360_st_Schedules

Analytics – Performance

b360_perf_PerfCounter

b360_perf_PerfCounter_Data

b360_perf_PerfCounter_Expanded

Analytics – Messaging Patterns

b360_analytics_flows

b360_analytics_artifact_versions

Analytics – Reports

b360_report_Schedules

b360_report_Dashboards

 

Let us look at a few scenarios where updating of the tables would be required.

Connecting the BizTalkMgmtDb Database on a New Installation

It is often suggested as a best practice to install BizTalk360 in a separate standalone server over a BizTalk server, for better performance. In this case, to monitor the BizTalk server, the BizTalkMgmtDb details must be updated in the b360_admin_BizTalkEnvironment table in the BizTalk360 database or through the UI, else the following error message would appear in BizTalk360.

Connecting BizTalkMgmtDb database

BizTalk360 database

Migrating the BizTalk Server

Consider a scenario where your BizTalk environment is migrated to a higher version. When the BizTalk server is upgraded, the BizTalk360 server also needs to be upgraded with the same version of the admin components. If not, the following error will be seen in the BizTalk360 UI.

Migrating the BizTalk Server

The below tables need to have the BizTalk version changed appropriately and the MS_BizTalk_install_location can be updated.

  • b360_admin_BizTalkEnvironment -> BizTalk Version
  • b360_admin_GlobalProperties -> MS_BIZTALK_VERSION & MS_BIZTALK_INSTALL_LOCATION

Monitoring Scenarios

Monitoring is considered a core functionality of BizTalk360. There may be some cases where the alerts may not be triggered. When checked in the logs, there might be timeout exceptions.

Monitoring Scenarios

When there are a large number of data monitoring alarms and if much of their historical data is present in the database, it may result in timeouts and the exception can be seen in the Data monitoring alarm grid, for example when trying to delete an alarm. Maintaining the historical data for a long time may result in large database growth. This can be reduced by minimizing the purge duration.

The artifacts information which is mapped for monitoring can be found in the b360_alert_monitor table.

Data Purging

BizTalk360 has its own purging procedure to take care of purging data in the BizTalk360 database. In the Settings side, you can configure after how much time you want specific data to be purged. If you feel, the database grows with huge volumes of data, you can decide to change the purge policies.

purge policies

Data Purging

The Event Log Data Collection

BizTalk360 can collect event log information from the BizTalk and SQL servers which are connected to the BizTalk environment. The event log data can be viewed in BizTalk360 by configuring and using the Advanced Event Viewer. The data is collected from the servers and stored in the b360_ev_EventLogData table. By default, there are many sources configured in BizTalk360.

The Event Log data collection

This will collect the data for all the event levels and sources and store it in the table. However, this may also result in huge database growth thereby affecting the performance of the product.

To resolve this problem, the Event Log levels and sources, for which the data needs to be viewed, can be configured in BizTalk360 and the remaining sources can be removed from the configuration.

This will reduce the amount of data being polled for and stored in the database. Also, the purge duration can be reduced to remove the historical data.

Analytics Performance Data Collection

The Analytics module in BizTalk360 mainly deals with the performance-related information of the BizTalk and SQL servers. The performance data gets collected through the perfmon counters and stored in the performance-related tables mentioned above. The BizTalk360 Analytics service collects the data by contacting the servers through PerfMon and store it in the BizTalk360 database. By default, all the available metrics would be collected. This may also result in huge volumes of data which in turn might affect the performance.

To enhance the performance and to reduce the database growth, there has been added an option in BizTalk360 to choose the metrics for which the data needs to be collected and used in BizTalk360.

Analytics performance data collection

Data purging is also available for performance-related information. The purge duration can be minimized to reduce the database growth.

There are different stored procedures available in the database to take care of the purging action. The purging subservice, running under the BizTalk360 monitoring service, is responsible for executing the purge stored procedures.

In SQL Server Management Studio, there is the Disk usage by the top tables database report. This report helps to find out the tables which are containing high volumes of data.

BizTalk360 database

Database Permissions for the Service Account

As a prerequisite to installing BizTalk360, it is recommended to provide sysadmin permission for the BizTalk360 service account for smooth installation. Since there would be inserts, updates, creates, and alters, sysadmin permission is recommended.

Small Talk on the BizTalk Databases

Having said about the BizTalk360 database, let us now talk shortly about the BizTalk databases.  BizTalk360 communicates with the BizTalkMgmtDb database to fetch the data of the application and its artifacts for monitoring. The service instances information would be obtained from the BizTalkMsgBox database. Hence, it is necessary to keep an eye on the growth of the BizTalk databases as well in the dba perspective.

There might be a case where the Process monitoring results in the actual count -1. The reason might also be a timeout exception. This will happen when the BizTalkDTADb database is huge in size. This can be found out by checking the database reports to find out the disk usage of the top tables. We can check for the purging of the tracking database. The recommendations to control the size of the databases can be found here.

Conclusion

We hope this article gives you some more information about the BizTalk360 database. When the purging is in place, the database growth can be controlled, and it will be healthy. Happy monitoring with BizTalk360!

The post BizTalk360 Database – An In-Depth Insight appeared first on BizTalk360.