SQL Query

Keep the BizTalk Environments healthy with SQL Query Monitoring

Secure SQL Queries functionality in BizTalk360 acts as a secure platform to store predefined queries and provide access to BizTalk members to execute the queries. It's pre-loaded with a set of queries and also allows the users (with permissions) to build their own queries that are more appropriate to the organization's business. Also, these query results can be monitored and alerted to the user at the scheduled time.

One of the biggest advantages of using this feature is you do not want to switch between multiple external tools. The SQL Query can be centrally stored in your BizTalk environment and shared among the eligible team members. A few scenarios you can use this feature are to monitor the spool or host tables in Message Box, Message In-out table in the tracking database, look for some values in BAM database tables, check for large messages, monitor send port URI and get notified if it changes. 

For instance, the spool table within the BizTalk Message Box database serves as the repository for all message transactions in the system. Its size can vary based on the nature of processing activities performed. Ideally, in a well-operating environment, the spool table should maintain a relatively small size, except during periods of prolonged orchestrations or error conditions. Consistent growth in the size of the spool table without any reduction could indicate potential issues within the environment. Therefore, it's crucial to monitor the spool size closely. Spool size can be easily monitored via SQL query Data monitoring by just selecting the query and configuring the schedule.

Setting up an SQL Query Monitor

Scenario (BizTalk Audit) - Say, you want to monitor your BizTalk environment and receive audit details at the end of the business day to know more about the changes made in the BizTalk environment. For the above scenario, Auditing must be enabled in the BizTalk admin console, and you can use the default secure SQL query "BizTalk Audit ".

Create a new schedule by navigating to Monitoring -> Data Monitoring -> More->SQL Query-> Schedule. Click "Add Schedule" and follow the below steps. 

1. Choose Alarm - The first step to set up SQL Query data monitoring is to create an alarm. Once you have created the alarm, the alarm name will be displayed in the schedule configuration drop-down. Select the alarm that should be used for SQL Query data monitoring
2. Schedule Name - A meaningful name for the Query monitor. As per the above scenario, we are creating the schedule to monitor the BizTalk audit so the schedule name can be provided as "BizTalk Audit"
3. Enable the schedule for monitoring. you can disable this to stop monitoring.
4. Query- The query options list all the secure SQL queries created on the Administration side, select the query for monitoring from the list. To monitor the BizTalk auditing you already have a predefined query "BizTalk Audit" select that.
   (Refer to this doc to create a secure SQL query in BizTalk360)
5. Define the condition for monitoring by clicking add filter option, which lists the available columns in the selected query. 
6. How do you want to check - Choose whether you want to monitor all the data from the beginning or only the past few sets of data based on the date-time range. This date-time range is listed based on the date-time columns available in the chosen query. In the BizTalk audit query "Activity Date Time " column is there so you can select that.
7. Set the Threshold Condition 
   1. Warning Threshold - Set the value when you want to be notified when there is a warning. First, select the Operator (> or =) and then set the warning value.
   2. Error Threshold - Set the value when you want to be notified when there is an error. First, select the Operator (> or =) and then set the error value.
8. Send Query Result - Enable this to receive the configured query result as an attachment in the mail
   
9. Monitoring Schedule
   1. Frequency (How often you want to monitor) -Select Daily to monitor the daily activities of the environment and send the audit details at the end of the day. If monthly is chosen, there is an additional option called "End-of-Month." By selecting this option, the schedule will be executed at the end of the month, considering the complete month's data for monitoring purposes.
10. When during the day - Select when the data monitor must execute on a particular day during business hours. You can customize your business hours by selecting the values from the Business Day Start and Business Day End options.
11. At what time? - 
    1. End Of the Business Day - For e.g., if the business end time specified is 5 PM (default value), the data monitor will execute exactly at 5 PM
    2.  Frequency - Select the number of minutes/hours after which the data monitor should execute. E.g., For 1 hour means the data monitor will execute once every 1 hour. 
       1. Start Date / End Date - When you want to start/stop the scheduled execution. If you want the schedule to execute all the time, choose No end date
12.  Summary - The summary information will automatically be listed based on the information selected in the previous steps. Click the Save button to store the schedule configuration.

SQL Query Monitoring Execution Results

Once the schedules are created, it will get executed on the specified time frame. Once after the execution of schedules, it will be displayed in the data monitoring dashboard. You can go to the data monitoring dashboard and click on the respective schedule to view the data monitoring schedule execution results.

Once the SQL Query Data Monitor is created and configured, it will execute during the specified time intervals. The Data Monitoring dashboard will display the status of the SQL Query Data monitor on a specific date at a specific time interval in the calendar chart control.

Based upon how you configured your Data Monitoring alarm, emails might get triggered to the email id(s) which are specified while creating that Data Monitoring alarm.

The email alert(s) will contain the SQL Query Data Monitor Execution Results of the data monitor. By default, if you only selected the 'Use this alarm for Process Monitor Alerts' option, you will only receive the Failure emails to the configured email address(es). Alternatively, if you also selected the 'Notify on Success as well' option, you will receive the success alert emails as well to your mailbox. The following screenshot depicts a successful email notification received for a configured SQL Query Data monitor.

User Access policy

The super user of BizTalk360 provides access to the custom user for data monitoring and the secure SQL queries which they required to monitor.