by Praveena Jayanarayanan | Jul 2, 2020 | BizTalk Community Blogs via Syndication
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.
by Raja Sreenivasan | Oct 15, 2019 | BizTalk Community Blogs via Syndication
In any BizTalk environment, it is important to keep track of database growth and execute the data purging policies whenever necessary. When the database size grows, the SQL server needs more memory and CPU to read data from the tables, which slows down the database operation. Besides that, there is also the risk that, due to the growing database(s), you are running out of disk space. Database Administrators spend a lot of their time dealing with the problem of database processes consuming too much disk space. So, it’s very important to monitor the database size to ensure the database is not seizing the memory and CPU for smooth BizTalk server operation.
The ability of Database Size Monitoring is a feature request we picked up from our feedback portal. This feature will be available for production use from BizTalk360 version 9.0 phase 3 on.

What can be monitored with Database Size Monitoring?
With Database Size Monitoring, you can monitor the data file and log file sizes of below BizTalk Server and BizTalk360 databases, by simply configuring the error and warning threshold conditions for the data and log file sizes.
- BizTalkDTADb
- BizTalkMgmtDb
- BizTalkMsgBoxDb
- BizTalkRuleEngineDb
- BAM databases (BAMPrimaryImport, BAMAlertsApplication, BAMArchive, BAMStarSchema)
- SSODB
- BizTalk360
For example, if the size of the BizTalkDTADb is greater than a configured threshold value, then you will get notified.
Scenario
OutOfMemoryException in BizTalkDTADb
BizTalkDTADb contains the health monitoring data tracked by the BizTalk Server tracking engine. The size of the database can grow relatively quickly depending on the load of your server, which in turn results in OutOfMemory Exceptions.
This scenario can be overcome by proactively knowing the size of the database and acting against the exponential growth of the database by configuring the threshold limit in database size monitoring.
Monitor BizTalk360 database Growth
There might be a situation where the BizTalk360 database grows due to the amount and size of event log data and performance counter data. The BizTalk360 purging policy will take care of the data growth, but it is also important to monitor the size regularly to ensure BizTalk360 is working seamlessly.
Configuring Database for monitoring size
We wanted to simplify the process of configuring database size monitoring. To achieve this, we list all the BizTalk and BizTalk360 related databases. You can start monitoring by specifying the threshold details from the SQL instance.

To configure a database for monitoring, navigate to Monitoring->BizTalk environment-> Database Size, which lists all the BizTalk and bizTalk360 databases under the respective SQL Instances. Click on the config gear icon, as shown in the above image, to configure the threshold values to monitor the size of the database. Based on the threshold configuration, the user will get notified if there is any threshold violation occurring.

The available Threshold types, logical condition, and unit prefixes are:
Threshold Type
|
Logical Condition
|
Unit Prefixes
|
Data File Size
Data File Used Space
Data File Available Space
Log File Size
Log File Used Space
Log File Available Space
|
Equals
Not-Equals
Greater-than
Greater-than-or-Equal
Less-than
Less-than-or-Equals
|
Bytes
Kilo-Bytes (KB)
Mega-Bytes (MB)
GigaBytes (GB)
|
Following are the threshold types on which you can monitor a database:
Threshold Type
|
Explanation
|
Data File Size
|
This threshold type represents the reserved space for the data file.
For e.g.:
Warn Me if Data File Size is Greater-than 200 GB
Error if Data File Size is Greater-than 250 GB
|
Data File Used Space
|
This threshold type represents the size used by the data file.
For e.g.:
Warn Me if Data file Used Space not-equals 250 GB
Error if Data file Used Space not-equals 250 GB
|
Data File Available Space
|
This threshold type represents the available space for the data file
For e.g.:
Warn Me if Data file Available space less-than-or-equals 99 GB
Error if Data file Available space less-than-or-equals 99 GB
|
Log File Size
|
This threshold type represents the size reserved for the transaction log file.
For e.g.:
Warn Me if Log File Size is Greater-than 200 GB
Error if Log File Size is Greater-than 200GB
|
Log File Used Space
|
This threshold type represents the space used by the log file.
For e.g.:
Warn Me if Log File Used Space not-equals 250 GB
Error if Log File Used Space not-equals 250 GB
|
Log File Available Space
|
This threshold type represents the available space for the log file.
For e.g.:
Warn Me if Log File Available Space Greater-than 250 GB
Error if Log File Available Space Greater-than 250 GB
|
Once the threshold values are configured and saved, the monitoring conditions will be evaluated, and the status of the database size will be displayed in the grid.

The Monitoring Dashboard and BizTalk Group Dashboard display the overall status of the alarm including the Database size shown in the picture below.

The user will get notified through mail or notification channel when any threshold violation happens.

Quick Alarm
You can also configure database size for monitoring simply by configuring Quick alarm. In this case, an alarm will be created and all the BizTalk and BizTalk360 databases will get configured for database size monitoring with default threshold conditions.
Conclusion
Considering the feedback provided by our customers, via the feedback portal, BizTalk360 will continue to provide more useful features every single release. In the upcoming release v9.0 phase 3, the ability to monitor Database Sizes is only one of the features we bring. If you have any suggestions for a =n upcoming version, please write to us at [email protected].
The post Database Size Monitoring appeared first on BizTalk360.
by Lex Hegt | Apr 15, 2019 | BizTalk Community Blogs via Syndication
One of the components of BizTalk360 is a SQL Server database. This database is used for all kind of configuration like user permissions and all the monitoring settings. When that data got corrupted or lost, you would have to do all the configuration all over from scratch. To prevent this from happening, you should frequently take backups of that database. Besides creating manual backups, you can also have these backups being created automatically.
There are two different approaches to making automated backups of this database, both are shown below:
- Create a SQL Server Maintenance Plan
- Extend the Backup BizTalk Server job
The difference between these 2 methods is, that with the Maintenance Plan approach you’ll have a backup which is not in sync with the backup of your BizTalk databases, while with the latter option your BizTalk360 backup will be in sync with the BizTalk backups. This could make restoring your databases in one go easier.
In a previous post, Rochelle has already explained how to create a Maintenance Plan to take care of the BizTalk360 database backups. In this article, we’ll explain how to add the BizTalk360 database to the Backup BizTalk Server job.
Adding custom databases to the BizTalk Server Backup job
What we basically are going to do is using a feature from BizTalk Server. As you probably are aware of, BizTalk Server contains multiple databases and to be able to restore them in sync, the backup needs to be created in sync. The only by Microsoft supported way to create such backups, is by using the Backup BizTalk Server job, which is a SQL Server Agent job. You can read more about that topic in the below articles:
BizTalk users can extend the backup job with other databases which are considered important to the integrations which are deployed in BizTalk Server. This is exactly what we will be doing with the BizTalk360 database.
The process exists of the following steps:
- Prepare the BizTalk360 database
- Add the BizTalk360 database to the BizTalk Server backup job
- Start making backups
Let’s take these steps one by one and have that database added to the BizTalk Server backup job!
Prepare the BizTalk360 database
In this first step, we’ll make sure a table and some Stored Procedures will be created in the BizTalk360 database. The table which becomes created is called MarkLog. You will find this table in all the databases which are being backed up via the BizTalk Server backup job.
Perform below steps to create that table and the needed Stored Procedures:
- Open SQL Server Management Studio and connect to the SQL Server instance which contains the BizTalk360 database

- Click Open File, navigate to folder C:Program Files (x86)Microsoft BizTalk Server 2016Schema and select query Backup_Setup_All_Tables.sql
- From the Databases dropdown, select the BizTalk360 database

- Click the Execute button or hit F5 to execute the script. If the database has been created successfully, you can proceed with the next step
- Click Open File, navigate to folder C:Program Files (x86)Microsoft BizTalk Server 2016Schema and select query Backup_Setup_All_Procs.sql
- If not yet selected, select the BizTalk360 database from the Databases dropdown
- Click the Execute button or hit F5 to execute the script
If both SQL scripts have been executed successfully, an important part of the configuration has been completed. The BizTalk360 database is ready and in the next step, it will be added to the BizTalk Server backup job!
Important: Ensure yourself that the BizTalk360 is in Full Recovery Model, otherwise the backup will fail! You can check this by:
- Right-click the database then select Properties
- Select Options
- Check if the Recovery Model is set to Full

Add the BizTalk360 database to the BizTalk Server backup job
In the previous step, we prepared the BizTalk360 database to be able to be backed up by the BizTalk Server backup job. In this step, we’ll make sure that that database becomes added to a table in BizTalk Server’s management database, which will make sure that the database will be picked up by the BizTalk Server backup job.
Follow below steps, to make sure that the BizTalk360 database will be picked up by the BizTalk Server backup job:
- In SQL Server Management Studio, connect to the SQL Server instance which contains the BizTalkMgmtDb
- In the Object Explorer, expand the Databases, BizTalkMgmtDb, Tables and find the dbo.adm_OtherBackupDatabases table

- Right-click that table and, from the menu that appears, select Edit Top 200 Rows. As you are in Edit mode, you can add a new row which will contain the information about the BizTalk360 database.

- In the last row, which now only shows NULL values, enter below values
- DefaultDatabase: BizTalk360
- DatabaseName: BizTalk360
- ServerName: <Name of the SQL Server Instance which contains the BizTalk360 database>
- BTSServerName: <Name of the SQL Server Instance which contains the BizTalk360 database>
- Hit Enter to save the record in the table
The BizTalk360 database is now part of the BizTalk Server backup job. The last step we need to do is forcing a full backup, to make sure that also incremental backups can be created.
Start making backups
We are almost there; we have seen how the BizTalk360 database has been prepared to accommodate the BizTalk Server backup job. In the previous step, we have added the BizTalk360 database to the BizTalk Server backup job. In this last step, we will force a full backup, to make sure that after that, also incremental backups can be created. By default, a full backup will be created once every 24 hours; Incremental backups will be created, by default, every 15 minutes.
Perform below steps to force a full backup:
- In SQL Server Management Studio, under the BizTalkMgmtDb, expand Programmability and expand Stored Procedures
- Scroll through the Stored Procedures until you have found sp_ForceFullBackup

- Right-click that Stored Procedure and, from the menu that appears, select Execute Stored Procedure… As the Stored Procedures doesn’t need any parameter values, just click OK
- If the Stored Procedure has been executed successfully, the next time the BizTalk Server backup job runs, it will perform a full backup
There are a couple of ways to check whether the backups are really being created. You can:
- Check the output of the BizTalk Server backup job (in SQL Server Management Studio)
- Check if the backup files have been created (in Windows Explorer)
Check the output of the BizTalk Server backup job
To perform this check, perform these steps:
- In SQL Server Management Studio, you need to navigate to the SQL Server instance which contains the BizTalk Server backup job
- Next, expand SQL Server Agent
- Right-click the Backup BizTalk Server job and select View History

Check if the backup files have been created
To check the availability of the backup files, you firstly need to check where these files are located. Follow the below steps, to find that location and then check the actual location:
- In SQL Server Management Studio, you need to navigate to the SQL Server instance which contains the BizTalk Server backup job
- Next, expand SQL Server Agent
- Double-click the Backup BizTalk Server job
- In the Job Properties dialog which appears, under Select a Page, select Steps

- Now, under Job step list, double click BackupFull
- In the Job Step Properties dialog, at Command, scroll to the right to find the backup path

- Copy the backup path and close all dialog screens
- Next, open a Windows Explorer and paste the backup path in the Address bar

Now the backup files should show. Although we only checked the backup path for the full backup files, this folder might also contain the backup files of the incremental backups.
Conclusion
The BizTalk360 database contains valuable information about, amongst others, your monitoring configuration and the people who have access to BizTalk360. If in case of a disaster, you need to easily restore a backup of your BizTalk360 database, you need to have a recent backup of that database. The BizTalk Server backup job creates such backups. In this article, we have seen how to extend the BizTalk Server backup job to incorporate the backup of the BizTalk360 database.
The post Backup your BizTalk360 database via the BizTalk Backup job appeared first on BizTalk360.