As an integration platform, Microsoft BizTalk Server allows communication between various systems and applications. A key component of this communication is accessing and processing data from various sources, which is where SQL queries come into play. SQL queries are essential for BizTalk users because they allow them to retrieve and manipulate data stored in various databases and BizTalk SQL query monitoring aids users in a variety of tasks such as identifying performance bottlenecks, troubleshooting and optimizing the BizTalk environment based on the result.
In this blog, we will explore how BizTalk360 can help users manage SQL queries, securely share them among users, and effectively monitor query results.
In a BizTalk Server support scenario, different members will have their own set of SQL queries that they run on a regular basis to get some insights and reporting on BizTalk or other related databases. However, storing these SQL files in different machines will lead to maintenance issues, such as if a query needs to be updated or modified, it may need to be updated in multiple places, which can be time-consuming and error-prone. That’s why BizTalk360 provides a centralized platform to store and manage SQL queries in a secure way. With Secure SQL Queries, users can create, save, and execute SQL Queries or Stored Procedures directly from the BizTalk360 user interface.
In some cases, if any end-user or a third party wants to investigate the data for a specific period, they need to be provided with full access to the database management system. This can lead to accidental or intentional data loss or corruption. One of the main benefits of using Secure SQL Queries is that it helps to improve security by allowing users to query the metrics without having to grant them explicit access to DBMS. A BizTalk360 Super user can grant other normal users access to specific queries based on their business requirements. This includes granting or denying access to specific database-related queries or even table-related queries within a database.
The following GIF demonstrates how to execute/save a query and provide access to other users.
In addition, it is difficult to read the query output with many columns or columns with long text in a tabular format, to ease this we have added a detailed view option. Clicking on the “eye” icon in the row will display the respective row results in a separate window along with copy option for each value. Users can also use the Next and Previous buttons to move to different rows and view the results.
Users can pin any saved query to the Administration/Analytics dashboard which provides quick and easy access to frequently used queries. They can also customize the position and size of the SQL query widget according to the output of the query.
In some cases, we have some information that is contained in one query that we need to regularly send to users as part of their routine follow-up processes. This process can be automated via Analytics -> Reports feature. You can create a report pointing to any saved SQL query and set your custom schedule for daily, weekly, or monthly. Finally, provide the necessary email addresses or notification details to share the report with relevant users or external systems. This helps to ensure that reports are generated on time and that accurate and up-to-date information is shared among multiple users in a single go. Refer to this blog, to see complete details of Scheduling Query Reports
Apart from maintaining pre-defined queries, BizTalk360 offers the ability to continuously monitor the data (Scalar value) returned from a SQL query. It will proactively monitor the configured query based on the polling interval and send alerts if any violation is detected.
Let’s explore some practical scenarios where you can make use of Database Query Monitoring.
Messages with negative ref counts are messages in the MessageRefCountLogTotals table whose snRefCount is less than zero. When a refcount turns negative, the corresponding message cannot be cleaned up by the MsgBox cleanup jobs. To identify the total number of negative refcounts in the MessageRefCountLogTotals table, you can run the following query.
DECLARE @count bigint SELECT @count = COUNT(*) FROM [dbo].[MessageRefCountLogTotals] WHERE [snRefCount] < 0 IF @count = 0 BEGIN SELECT 'There are no negative RefCounts' END ELSE BEGIN SELECT COUNT(*) Count, [snRefCount] as 'RefCount Value' FROM [dbo].[MessageRefCountLogTotals] WHERE [snRefCount] < 0 GROUP BY [snRefCount] ORDER BY [snRefCount] END
You can also configure this query to monitor at regular intervals, such as every 60 minutes, and set up a condition to send an error alert when the count exceeds a certain value. Based on the alert, you can fix the problem by repairing/removing the messages. Here is a GIF to demonstrate how it is done.
The BizTalk Message Box database utilizes the spool table for all message transactions within the system. The size of this table varies depending on the type of processing you perform. In a well-functioning environment, the size of the spool table should remain relatively small, except for long-running orchestrations and errors. If the size of the spool table is consistently increasing without any reduction, then there may be an issue with the environment. So, it is important to keep track of the spool size.
You can easily monitor the Spool Size by using this SQL query “SELECT count(*) as ‘SpoolSize’ from SPOOL WITH (NOLOCK)” against the BizTalkMsgBoxDb database and setting up warning and error thresholds.
Follow this document, to know more about Database Query Monitoring Configuration.
Scalar query monitoring helps a lot in tracking the storage, performance, or availability of certain business metrics. However, BizTalk users often have to deal with different sets of tables and data. Hence, simply monitoring scalar values is insufficient for their daily requirements. To address this, BizTalk360 came forward and brought a new feature in version 10.6 that offers enhanced monitoring of SQL query results. With the help of this feature, you can create SQL query schedules to run at specific intervals of time and monitor the result. To investigate the output data, you can also send the result of the query as an attachment in alert mail.
We know that SQL Server is a crucial component of the BizTalk ecosystem, serving as a repository for configuration, tracking, and message data. By keeping a close eye on SQL query data, users can gain valuable insights into the health of their environment and take proactive steps to tackle any issues that may arise.
BizTalk often deals with sensitive data, such as personal and financial information, so users can gain additional context by monitoring this data. This will help in ensuring regulatory compliance and maintaining proper control over access to sensitive data. For instance, to verify whether access to the BAMPrimaryImport database is limited to authorized users, one can easily examine user details using the SQL query provided below.
Maintaining visibility into these kinds of data is necessary for surfacing potential threats but manually investigating the data is time-consuming and can be forgotten sometimes. With BizTalk360, you can set up a schedule to automatically monitor the query, also you can specify which user accounts are allowed to access the BAMPrimaryImport database so that you’ll get notified whenever an unauthorized user account is found in the query data.
To better understand this feature, let’s take a quick look at another real-life example. Suppose you need to keep an eye on various BizTalk operations that are carried out by non-privileged users. Especially, if you want to get notified immediately when an unauthorized user has deleted or removed any artifacts/components from your BizTalk Environment.
As users across your organization access multiple artifacts on a daily basis, they generate large volumes of audit logs that can be difficult to effectively monitor manually. In this case, you can automate this process by simply monitoring the default query named “BizTalk Audit” which lists all the operations performed in BizTalk Admin Console (if auditing is enabled). To drill deep only the delete operations performed by certain user accounts, you can add the following filters:
This schedule will execute the query with the configured conditions at specified intervals and check if the data violates the set threshold value i.e., check if any suspicious activity has happened. You can find the result of query monitoring in the “Data Monitoring Dashboard”. In the below result, you can see that the Actual Count is “0” which implies, no delete or remove operations were performed by low privileged/ unauthorized users, resulting in a “Healthy” state.
Similarly, you can utilize different queries to find performance bottlenecks, any increased load in the database or table, potential data breaches, unwanted storage consumption, and more. Once the monitoring is complete, you will be alerted with the results, allowing you to take prompt action. In the email alert, under SQL Query Section the details of schedule execution and its results are displayed.
In this way, you can track any unusual behavior or ensure that expected data is present in the databases. You can check out this document, to find the step-by-step instructions for configuring a SQL Query Schedule.
Note: This SQL Query Data Monitoring feature is only available to Super Users and Normal Users who have full access to the Secure SQL Queries feature.
Whether you use a self-hosted SQL Server or leverage Azure SQL Server, BizTalk360 helps in simplifying the workflow of your team for managing hundreds of SQL queries and provides around-the-clock visibility into your database records. We will continue to provide effective solutions to make BizTalkers’ day-to-day operations easier and less burdensome.
If you aren’t already a BizTalk360 customer, sign up today for a 14-day free trial!
