Azure Database watcher

 

Azure Database watcher is a managed monitoring service designed to provide insights and diagnostics for Azure databases and Azure Managed Instance. It helps database administrators and developers track performance, detect anomalies and troubleshoot issues efficiently. By collecting telemetry data, logs and metrics, Azure Database watcher enables proactive monitoring and alerting, ensuring database health and optimal performance. Moreover, Database watcher is currently in preview.

Service components

• Database watcher
• The component responsible for collecting data using (for example) DMVs
• Targets
• The components to monitor (supported components) :
• Azure SQL Database
• Elastic pool
• Azure SQL Managed Instance
• Data store
• The component that will store our data :
• Azure Data Explorer
• Real-Time Analytics in Microsoft Fabric
• Supported Azure SQL targets

o   Database watcher supports all service tiers, compute tiers, and service objectives in Azure SQL Database and Azure SQL Managed Instance. This includes vCore and DTU purchasing models, provisioned and serverless compute tiers, single databases and elastic pools, and Hyperscale.

o   Database watcher can monitor all types of secondary readable replicas, including high availability replicas, geo-replicas, and Hyperscale named secondary replicas.

o   For a given watcher, the SQL targets can be in any subscription within the same Microsoft Entra ID tenant.

 

·        Database watcher price

     Database watcher costs are incurred by its individual components, as follows:

Component	Price	Notes
Watchers	Free
Dashboards	Free
Azure Data Explorer cluster 1	Pricing details	The optimal cluster SKU depends on the number of monitoring targets and the query workload running on the cluster. For cluster sizing considerations, see Manage Azure Data Explorer cluster.
Real-Time Analytics in Microsoft Fabric	Included in the Power BI Premium workspace consumption model. Billing per use.	Use either Azure Data Explorer or Real-Time Analytics. Only one of these offerings is required.
A vault in Azure Key Vault	Pricing details	Required only if the optional SQL authentication is used instead of the default Microsoft Entra authentication.
Azure network bandwidth	Pricing details	Cost is not incurred if a watcher, its targets, and its data store are deployed in the same Azure region.
Alerts	Pricing details	Database watcher uses Log Alerts. Monthly price is variable and depends on the number of alert rules you create, the number of SQL targets that have generated alerts during the month, and the evaluation frequency of each alert rule.

Limits

There is a limit on the number of SQL targets per watcher, and the number of watchers per subscription. Deployments exceeding these limits are not supported.

Parameter	Limit
SQL targets per watcher1	100
Watchers per subscription	20

 

 

 

 

 

 

 

 

 

 

What does it look like in our demo ?

Deployment

We have an Azure SQL Database component at our disposal, which hosts a database called demo-sql-1. This database, along with our SQL server demo-sqlsrv-1, will be monitored.

The Database Watcher component connects to our instance using a system-assigned identity to collect the necessary information.

Database watcher creation :

The service is not yet available in the “Switzerland North” region.

The name of the system-assigned service principal is always the same as the name of the Azure resource it’s created for. In our case, we will need to grant some specfic privileges on the SQL database to the service principal (identified as demo-watcher-1).

If the Azure Data Explorer resource is not created beforehand, one is suggested (E2d v5) :

Azure Data Explorer cluster and database :

Add targets :

The resource is deployed :

Currently, the service is not accessible because it’s not started and we did not grant the necessary privileges.

 

 

 

 

 

 

 

 

 

We are going to grant the related privileges :

To collect monitoring data, a watcher requires specific, limited access to each monitoring target, as described in the following table. These role memberships and permissions give a watcher the necessary access to the system monitoring data, but not to any other data in your databases.

Azure SQL Database	Azure SQL Managed Instance
Membership in all of the following server roles: ##MS_ServerPerformanceStateReader## ##MS_DefinitionReader## ##MS_DatabaseConnector##	The following server permissions: CONNECT SQL CONNECT ANY DATABASE VIEW ANY DATABASE VIEW ANY DEFINITION VIEW SERVER PERFORMANCE STATE The SELECT permission on the following tables in the msdb database: dbo.backupmediafamily dbo.backupmediaset dbo.backupset dbo.suspect_pages dbo.syscategories dbo.sysjobactivity dbo.sysjobhistory dbo.sysjobs dbo.sysjobsteps dbo.sysoperators dbo.syssessions

 

Privileges to grant in the case of an Azure SQL Database component :

CREATE LOGIN [demo-dbwatcher-1] FROM EXTERNAL PROVIDER;

 

ALTER SERVER ROLE ##MS_ServerPerformanceStateReader## ADD MEMBER [demo-dbwatcher-1];

ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER [demo-dbwatcher-1];

ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER [demo-dbwatcher-1];

The created login on SQL side :

As specified earlier the name of the system-assigned service principal is always the same as the name of the Azure resource it’s created for :

https://learn.microsoft.com/en-us/azure/azure-sql/database-watcher-manage?view=azuresql&tabs=sqldb

Permission issues ?

It is possible that after starting the Azure Database watcher component, it appears blank. In this case, you may encounter a permission issue with the Azure Data Explorer component (in our case).

We assign the right permissions on the Azure Data Explorer component :

Once is done you will need to start the service:

We select the Dashboards link :

Database watcher uses Azure Workbooks to provide monitoring dashboards at the estate level and at the resource level.

Here is an example of a database CPU utilization heatmap on the estate dashboard. Each hexagon represents a SQL target. There are two logical servers, one with six databases and one with three databases. The high availability secondary replicas are shown on the heatmap as separate targets. Select the image to see additional details, including data ingestion statistics.

:::image type="content" source="media/database-watcher-overview/database-watcher-sql-database-estate-dashboard-cropped.png" alt-text="Screenshot that shows an example of a CPU utilization heatmap on the database watcher estate dashboard." lightbox="media/database-watcher-overview/database-watcher-sql-database-estate-dashboard.png":::

Here is an example showing a partial view of the Performance tab of an Azure SQL database dashboard. Select the image to zoom into details.

:::image type="content" source="media/database-watcher-overview/database-watcher-sql-database-resource-dashboard-cropped.png" alt-text="Screenshot that shows an example of a database watcher dashboard for an Azure SQL database." lightbox="media/database-watcher-overview/database-watcher-sql-database-resource-dashboard.png":::

The following table describes the capabilities of database watcher dashboards in the Azure portal.

Capability	Description
Estate dashboards	Visualize high-level monitoring data for multiple monitored resources in a common view. Use heatmaps to find top resource consuming databases, elastic pools, or SQL managed instances. Use the top queries view to find top resource consuming queries across your Azure SQL estate, ranking queries by CPU, duration, execution count, etc. Use the subscription, resource group, and resource name filters to focus on subsets of your Azure SQL estate. Drill through to detailed dashboards for specific resources.
Resource dashboards	Visualize detailed monitoring data for a database, an elastic pool, or a SQL managed instance, including: - Active sessions - Backup history - Common performance counters - Connectivity probes - Database and instance properties and configuration - Geo-replication - Index metadata, usage statistics, warnings, and suggestions - Resource usage - Session and connection statistics - SQL Agent job state and history - Storage consumption and performance - Table metadata - Top queries - Wait statistics Use resource dropdowns to quickly switch from one resource to another. Use the estate link to zoom out to an estate dashboard.
Filter by time range	On each dashboard, set the time range to focus on the desired time interval. Use standard or custom time ranges. Narrow down the time range to an interval of interest by "brushing", or dragging the mouse cursor over a chart to select a shorter time range.
Historical data	Depending on the dataset, dashboards show either a summary for the selected time interval, or the latest sample collected in the time interval. Toggle between the latest and a historical view to look at data samples earlier in the selected time range. For example, instead of looking at the currently active sessions, review a previous sample of active sessions collected when a spike in resource usage occurred.
Secondary replicas	Monitor all types of replicas, including high-availability (HA) secondary replicas on estate dashboards. Toggle between viewing the primary replica and its HA secondary replica on resource dashboards.
Download data to Excel	Download data from charts and grids as csv files and open them in Excel for additional analysis.
Data refresh	Retrieve the latest data from the monitoring data store when you open a dashboard and as you switch from tab to tab. After a dashboard has been opened for some time, refresh it manually to see the latest data, or enable automatic dashboard refresh.
Ad hoc KQL query	Use a link on each dashboard to open the Azure Data Explorer web UI and query your monitoring data with KQL. For more information, see datasets and Use KQL to analyze monitoring data.
Descriptions	Toggle the Show descriptions parameter to see descriptions that help you interpret displayed data and include relevant documentation links.
Tooltips	Hover over a field to see more details and context for displayed data.
Ingestion statistics	Use the Ingestion statistics link to see data ingestion latency and other ingestion statistics per dataset.
Dark mode	Switch the Azure portal appearance to use the dark theme to have database watcher dashboards use dark mode.

 

 

 

 

Our database is being monitored. Initially, we accessed the heatmap:

However, by clicking on the database name “demo-sql-1”, we gain access to more information:

We also have easy access to waits. They are categorized by type :

We also have access to performance counters :

At the same time, we initiated a series of insertions on a sample table to generate activity. We can see that it has been identified and what type of waits it generates :

We also have access to sessions. The displayed view shows which sessions consume the most resources :

We were also able to easily retrieve the query that generated the most activity via the “Top queries” tab :

We also have some information about waits :

We have access the Storage section :

We also have access to the tables and indexes related to our database :

Finally, we can easily retrieve the properties of our database without having to query the tables and system views :

We can clearly see that we have a highly efficient and easy-to-use monitoring tool at our disposal.

Pricing

The “Database Watchers” and Dashboards components are free however the storage part via “Azure Data Explorer” or “Real-time analytics in Microsoft Fabric” is not free. Data transfer between different components is free as long as all components (target, watcher and data store) are in the same region. Unfortunately, this service is not yet available in Switzerland, but it is possible to deploy it in another region to monitor databases hosted in the “Switzerland North” region.

 

 

 

 

 

 

 

Reference Links:

https://www.dbi-services.com/blog/an-introduction-to-azure-database-watcher/

Pricing - Azure Monitor | Microsoft Azure – For Alerts Pricing

Monitor Azure SQL workloads with database watcher - Azure SQL Database & SQL Managed Instance | Microsoft Learn