Contents x
    Database Query
    • 27 Jun 2024
    • 3 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Contents

    Database Query

    • Updated on 27 Jun 2024
    • 3 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Article summary

    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. And itis crucial to monitor such queries and get to know the issues periodically.

    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.

    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.

    Monitoring the Spool Size can be easily achieved by executing the SQL query "SELECT count (*) as 'SpoolSize' from SPOOL WITH (NOLOCK)" against the BizTalkMsgBoxDb database and establishing warning and error thresholds to flag any abnormal growth.

    Setting up monitoring for Database queries

    • Log in to the BizTalk360 application.
    • Navigate to Monitoring -> Manage Mapping -> BizTalk Environment -> Database Query.
    • Select the Alarm name (see Manage Alarms) from the drop-down for which users would like to associate the alerts.
    • Click 'New Query' button to set up monitoring for the database query.
    • Enter the Name for the Alert, add the SQL Instance name, select the 'Authentication Mode' (Windows Authentication/ SQL Server Authentication/ Azure AD Authentication) and the 'SQL Database name'.
    Authentication mode
    • Windows Authentication - BizTalk360 connects to the SQL instance against windows system users.
    • SQL Authentication - BizTalk360 Connects to the SQL instance using the username and password provided by the user.  Users can also connect to the Azure database using SQL authentication.
    • Azure Active Directory Authentication-BizTalk360 Connects to the Azure SQL servers using Azure Active Directory password/ Azure Active Directory service principal /Azure Active Directory Access Token based on the user selection.
    • Click 'Next' to add the Query and Threshold details.
    • Enter the 'SQL query' in the space provided.
    • Select the 'Warning' and 'Error' Conditions when BizTalk360 must return an alert.
    • Click 'Validate & Save' to Save the monitoring information.

    Note:
    • Users can monitor the Database Query results by configuring the query that returns the scalar value. If there is any deviation in the query results, then BizTalk360 will trigger an alert notification.
    • Database Query Monitoring Polling Interval - By default, the BizTalk360 monitoring service checks the status of all configured database queries every 1 min. From v10.1 users are provided with an option to update the polling interval time, so based on the configured polling interval time only the monitoring service will check and determine the monitor status of a configured database query.

    Dashboard and Notifications

    Once the database query is configured for monitoring, the health status of it will be shown in dashboard in a graphical tree view. If the query configured is violated, it will be listed in the errors and warnings along with the associated alarms. You can also navigate to the respective query on clicking them.

    In order to receive email notifications from BizTalk360 on the threshold violation alerts (and auto-correction alerts), you need to configure the SMTP settings under BizTalk360 Settings. Follow the steps in this article to be able to configure the SMTP settings in BizTalk360. You will receive an email notification with the exact details of the status of the database query.

    Was this article helpful?
    What's Next