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.
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.
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.
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.
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.
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.
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.
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.