BizTalk360 Database – An In-Depth Insight

BizTalk360 Database – An In-Depth Insight

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.

Database Size Monitoring

Database Size Monitoring

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.

Backup your BizTalk360 database via the BizTalk Backup job

Backup your BizTalk360 database via the BizTalk Backup job

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:

  1. Prepare the BizTalk360 database
  2. Add the BizTalk360 database to the BizTalk Server backup job
  3. 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:

  1. Open SQL Server Management Studio and connect to the SQL Server instance which contains the BizTalk360 database
     

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

     

  4. 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
  5. Click Open File, navigate to folder C:Program Files (x86)Microsoft BizTalk Server 2016Schema and select query Backup_Setup_All_Procs.sql
  6. If not yet selected, select the BizTalk360 database from the Databases dropdown
  7. 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:

  1. Right-click the database then select Properties
  2. Select Options
  3. 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:

  1. In SQL Server Management Studio, connect to the SQL Server instance which contains the BizTalkMgmtDb
  2. In the Object Explorer, expand the Databases, BizTalkMgmtDb, Tables and find the dbo.adm_OtherBackupDatabases table

     

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

     

  4. 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>
  5. 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:

  1. In SQL Server Management Studio, under the BizTalkMgmtDb, expand Programmability and expand Stored Procedures
  2. Scroll through the Stored Procedures until you have found sp_ForceFullBackup

     

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