Every DBA should consider database growth as a key factor because it has a negative impact on business operations. If this goes unchecked even for a minute, environments with high transaction volumes will typically become dump stuck. Due to the fact that BizTalk360 is a mission-critical enterprise product, we have given careful consideration to using scenarios involving data growth and purging.
This blog will give you an insight into this topic from different perspectives. BizTalk360 is an on-premises solution that provides users complete control over the software and their data once they have paid for it. The organisation’s data is entirely safe in every way, we never get access to any of it.
This is one of the main factors contributing to enterprises continued long-term use of BizTalk360. Additionally, we are making sure that the tool requires the least amount of work from BizTalk users and is compatible with all recent versions of the BizTalk Server.
A database serves as a place for all data to store in its space. Any data misplacement or overgrowth will have an impact on the system’s overall performance.
You’ll undoubtedly be asked at some point how quickly your SQL server database is expanding.
If the data file was initially set to be sufficiently large, no auto growths would be triggered, thus you may try to generate a report based on auto growths that occurred.
Let’s see some real-time use cases with solutions to solve the BizTalk360 database growth:
Our customer service team receives this question the most often. Typically, they will provide the inquiry along with the screenshot seen below.
As a further step, we will ask for the database reports on disc usage by top tables in order to gain more understanding. Knowing the tables’ data volume will be made much easier with the use of this information.
You may check the aforementioned example tables to see which one utilised how much data size.
The above sample screenshot makes it clear that the BizTalk360 databases took up about 31 GB in total. “Eventlogdata” is the only table that uses more than 200 MB of space. Hence there will be more free space captured in the database.
Then, in order to free up more space, we advise downsizing the database to manage the SQL server efficiently.
The database will be freed up as a result, and BizTalk360’s performance will increase significantly. Additionally, in the part that follows, we’ll look at the features and capabilities of the tool that allows for effective database administration.
The following are the steps to shrink the database:
We have a stored procedure in BizTalk360 that deletes historical data according to the data purging timeframe set in BizTalk360 every two hours.
The monitoring service runs this automated job to manage database growth.
However, you may also manually execute the stored procedure sp b360 admin CleanupDB, which will clean up the database by deleting the historical data from the BizTalk360 database.
Following is the command you can use to execute the clean-up jobs manually.
USE [BizTalk360] GO DECLARE @RC int -- TODO: Set parameter values here. EXECUTE @RC = [dbo].[sp_b360_admin_CleanupDB] GO
Please see the article below for further information.
https://docs.biztalk360.com/docs/data-purging#large-data-growth-in-biztalk360-database
This is another scenario we will receive most often.
In our test environment, the BizTalk360 Analytics Service has a high CPU burden.
The CPU load remains at about 80%.
The tracking data is available to users in BizTalk360, allowing them to gain a better understanding of the failure rate and the performance of the messaging. The analytics service will begin collecting tracking data for all selected counter types. For each category, users can select the tracking data metrics that are required for optimal tracking data collection. Analytics will automatically collect all data and store everything in a database if this is not done correctly. This will cause the database to grow rapidly.
Yes, organizations will worry about their system performance burden if anything consumes more load. Now, BizTalk360 has enriched UI to enable the performance counters as needed to collect them accordingly instead of loading all the instances and counters in a single go.
Using the below screen in the tool, you can have control of which is the most needed counter for your business requirement and enable only the necessary “Metrics”. This will eventually reduce the load of the analytics service and the CPU utilization will be drastically reduced.
Right- click the BizTalk360 database in SQL Management Studio, then select Reports > Standard Reports > Disk Usage by Top Tables. A report will appear as follows:
You will be impacted if the record count reaches very high levels, such as over a million records.
You must fix this issue right away or you will fast run out of disc space.
This condition is more likely to occur when the Throttling collection service is activated, there are many hosts or host instances present, or there are issues with the functioning of the Throttling collection service.
The collection of throttle values is not enabled by default.
According to your data purging preferences, BizTalk360 has its own built-in processes to clean up the data right out of the box (in the UI, go to Settings > BizTalk360 Health > Data Purging). You can manually clean the database in the event of significant database expansion by using the stored procedure sp b360 admin CleanupDB.
A white paper on best practice is available for your reference. Please see http://blogs.biztalk360.com/biztalk360-deployment-and-configuration-best-practices/ in case you missed it.
So far, we have seen the use cases of BizTalk360 database growth and what are inbuilt capabilities the tool has and what is the best practice to resolve those issues.
The next section is a very important area what are all the feature set BizTalk360 has to resolve the database size growth of the BizTalk tables and what sort of monitoring the toll is providing?
There may be times when calling the stored procedure(s) results in a timeout error because of performance concerns. The size of the BizTalk360 database is the cause of this timeout problem.
In general, the BizTalk360 database doesn’t have any information about the environment. instead, it mostly contains data relating to BizTalk360 configuration settings, dashboard monitoring information, log data, and a few other things like alert history and environment settings.
The BizTalk360 database expands on average at a pace of about 200 MB every day. The following features make the BizTalk360 database larger:
You must specify the minimum number of days to purge the data under “Purge policy settings” for these features in order to stop the database from growing. Refer to the page on how to set the purge data duration for further information.
For better performance, it is advised to keep purge days low (about 5 days) for advanced event viewer as Event log data, while throttling is limited to 7 days.
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.
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.
To know more about this feature, please see the below page https://docs.biztalk360.com/docs/database-size-monitoring
Using this blog, we hope to have explained the breadth and length of features and capabilities offered by this tool, as well as how BizTalk DBAs may use them to manage BizTalk360 efficiently.
Use trial keys for 30 days to explore all the features.
