Published on : Feb 3, 2017
Category : BizTalk360 Update
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.
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', alarmId:'e3468777-b6a1-4db2-b3bf-3d788c6a8452',
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 –
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.
