Monitor BizTalk Database files using BizTalk360

Introduction

Closely looking after the size of database files is an exhausting process for DBAs. However, in complex BizTalk environments, it is important to keep track of database growth and execute the data purging policies whenever necessary. BizTalk360 has the capability to monitor the DB Growth with the combination of DB Query monitoring and Custom Widgets.

Database Query Monitoring

Using database query monitoring, you can setup the threshold limit to database scalar values. For every BizTalk360 Monitoring cycle, the monitoring service will validate the results of SQL query against configured threshold values. If any violation happens, it will trigger the notification to the configured emails in the alarms.

Below we can see how we configure the DB queries to keep track of database file size.  The SQL queries to monitor the Data and Log files of Tracking Database is as follows


--Data File
SELECT (size*8)/1024/1024 SizeINGB
  FROM sys.master_files where Name = 'BizTalkDTADb'

--Log File
SELECT (size*8)/1024/1024 SizeINGB
  FROM sys.master_files where Name = 'BizTalkDTADb_log'

Query results will produce the size of the database files in GB. When the query results meet the threshold violation (Warning or Error), it will notify the user with the appropriate status. For instance, if the size of data file of the Tracking DB is above 5GB, BizTalk360 shows a Warning and greater than 10GB means it will show the Error status.

DatabaseQuery - monitor biztalk database files

Other database files (Message Box, BizTalk360) can be configured in DB Query Monitoring as well, enabling you to monitor those databases too.

Custom Widgets

Custom Widgets is another option available to keep track of database files. With the Dashboard features, you can customize the solutions you want in your daily activities. If you are interested in monitoring a bunch of databases, this feature will help you to do so.

Create the Custom Widget using the DB Query monitoring API call in BizTalk360.


 <div id="WidgetScroll" style="top:30px;" data-bind="addScrollBar: WidgetScroll, scrollCallback: 'false'">
 <table class="table table-lists"> 
 <thead>
<tr>
 <th style="width:60%">Data File </th>
 <th style="width:20%">Size in GB </th>
 <th>Status</th>
 </tr>
 </thead>
 <tbody>
 <!-- ko foreach: dataFiles -->
 <tr>
 <td data-bind="text: name"></td>
 <td data-bind="text: currentReturnValue"></td>
 <td><span data-bind="text: monitorEnum[currentMonitoringStatus]"></span></td>
 </tr>
 <!-- /ko -->
 </tbody>
 </table>
</div>
 <script>
 dataFiles = ko.observableArray();
 
 monitorEnum = {
 "0":"Critical",
 "1":"Error",
 "2":"Warning",
 "4":"Healthy"
 };
 

 activate = function () {
 var _this = this; 
 _this.getDataFileSize(function (data) { 
 _this.dataFiles(JSON.parse(data.serializedMonitorConfig));
 });

 };
 getDataFileSize = function (callback) {
 var url = 'http://localhost/BizTalk360//Services.REST/AlertService.svc/GetAlertMonitorSerializedConfig';
 $.ajax({
 dataType: "json",
 url: url,
 type:"GET",
 username: "BizTalk360", 
 password: "*********",
 data: { environmentId: 'cb763cb7-61f2-4539-a8da-04dcc3cabb0d',alarmName:'DMAlarm',monitorGroupType:'BizTalkEnvironment',monitorGroupName:'BizTalk Environment',monitorName:'Database Query' },
 cache: false,
 success: function (data) {
 callback(data);
 },
 error: function (xhr, ajaxOptions, thrownError) { //Add these parameters to display the required response
 alert(xhr.status);
 alert(xhr.responseText);
 },
 });
 };
 activate();
</script>

<pre>
</pre>

After the creation of the Custom Widget as described above, add the Custom Widget to the dashboard. Thereafter you can see the results as below –

DBFileWidget monitor biztalk database files

Summary

With the combination of the BizTalk360 API and DB Query Monitoring, users can derive their own cases. It will be a useful feature to keep track of the day to day activities.

Author: Senthil Palanisamy

Senthil Palanisamy is the Technical Lead at BizTalk360 having 12 years of experience in Microsoft Technologies. Worked various products across domains like Health Care, Energy and Retail.

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top