Azure SQL Database Watcher and Azure Data Explorer Integration
Published Mar 21 2024 02:54 AM 1,499 Views

Azure SQL family users can now take advantage of an enhanced monitoring solution for their databases and leverage Azure Data Explorer or the Microsoft Fabric integration. With the introduction of the new Database Watcher for Azure SQL (preview), users gain access to advanced monitoring capabilities. 

 

Database watcher is a new managed monitoring solution for database services in the Azure SQL family. It supports Azure SQL Database and Azure SQL Managed Instance.

 

Database watcher collects in-depth workload monitoring data to give you a detailed view of database performance, configuration, and health. Monitoring data from the databases, elastic pools, and SQL managed instances you select is collected in near real time into a central data store. To store and analyze SQL monitoring data, database watcher uses Azure Data Explorer . You can also trial a free Azure Data Explorer cluster. As a complement option, Eventhouse KQL database in Fabric Real-Time Analytics is also supported as a destination.


Out-of-the-box dashboards in Azure portal provide a single-pane-of-glass view of your Azure SQL estate and a detailed view of each database, elastic pool, and SQL managed instance.

 

 

 

 

A conceptual diagram of a database watcher deployment, showing the flow of monitoring data from Azure SQL resources to a database watcher. The monitoring data is stored in Azure Data Explorere or Fabric Real-Time Analytics. Dashboards in the Azure portal show you a single-pane-of-glass view across your Azure SQL estate, and a detailed view of each database, elastic pool, and managed instance.A conceptual diagram of a database watcher deployment, showing the flow of monitoring data from Azure SQL resources to a database watcher. The monitoring data is stored in Azure Data Explorere or Fabric Real-Time Analytics. Dashboards in the Azure portal show you a single-pane-of-glass view across your Azure SQL estate, and a detailed view of each database, elastic pool, and managed instance.

 

From the DB Watcher resource page in Azure, you can easily select an Azure Data Explorer cluster and databsase to stream the data to it.From the DB Watcher resource page in Azure, you can easily select an Azure Data Explorer cluster and databsase to stream the data to it.

 

Optimal Data Analysis with KQL (Kusto Query Language)

To analyze collected monitoring data, the recommended method is to use KQL. KQL is optimal for querying telemetry, metrics, and logs. It provides extensive support for text search and parsing, time-series operators and functions, analytics and aggregation, and many other language constructs that facilitate data analysis. You can find examples of analyzing database watcher monitoring data with KQL in the documentation.

 

Evolution of Azure SQL Database Monitoring

Over the past decade, our customers have emphasized the importance of reliable, low-latency, and comprehensive monitoring for cloud database services. Initially, Azure Monitor metrics and diagnostic telemetry sufficed for many Azure SQL users. However, as large and complex mission-critical applications became prevalent, it became evident that more robust monitoring was necessary. While Azure SQL Analytics and SQL Insights were introduced to address this need, feedback revealed gaps in the monitoring data coverage, an excessively high data latency, and high cost at scale. Despite attempts to improve with SQL Insights, concerns persisted, especially regarding the setup and maintenance of IaaS VMs to monitor PaaS database services. Reliability issues further underscored the need for a better solution.

 

Thus, Database Watcher was born.

 

This managed monitoring solution offers extensive data coverage, collecting information from over 70 SQL system views and presenting it directly in the Azure portal. With minimal latency, typically in single-digit seconds, and leveraging Azure Data Explorer or Fabric Real-Time Analytics, Database Watcher empowers users to derive actionable insights from rich, real-time data that database watcher collects.

 

Summary

The dashboards, complemented by KQL queries, enable you to delve deep into the performance and configuration of your databases. This means you can detect, investigate, and troubleshoot a wide variety of database performance and health issues.

Whether you’re tackling resource bottlenecks or fine-tuning your Azure SQL resources for the best balance of cost and performance, Database Watcher equips you with the insights needed to make informed decisions. It’s your pathway to optimizing your Azure SQL setup for peak efficiency and cost-effectiveness.

 

Next steps

One effective approach to grasp the potential and the power of Database Watcher is to give it a try yourself. Set up your Azure SQL resources with Database Watcher, explore the dashboards, and start running some queries with KQL.


To read more about Database Watcher, check out the documentation.

 

 

Version history
Last update:
‎Mar 21 2024 03:33 AM