Monitor Azure SQL workloads with database watcher (preview)

Applies to: Azure SQL Database Azure SQL Managed Instance

Database watcher is a 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 into a central data store in your Azure subscription. 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.

To store and analyze SQL monitoring data, database watcher can use either Azure Data Explorer or Real-Time Analytics in Microsoft Fabric. Azure Data Explorer is a fully managed, highly scalable data service, purpose-built for fast ingestion and analytics on time-series monitoring data. A single Azure Data Explorer cluster can scale to support monitoring data from thousands of Azure SQL resources. Real-Time Analytics uses the same core engine as a SaaS offering within Microsoft Fabric.

You can query data in an Azure Data Explorer or Real-Time Analytics database using KQL or T-SQL, build custom visualizations using Azure Data Explorer dashboards, Power BI or Grafana, and analyze data in Excel. You can set data retention policy per database or per table, and scale your Azure Data Explorer cluster automatically or manually to achieve the optimal price/performance ratio.

To start monitoring your Azure SQL resources, create a watcher resource in your Azure subscription. Configure the watcher by selecting a data store and a set of databases, elastic pools, or SQL managed instances to monitor, called SQL targets. Grant the watcher access to targets, and start the watcher.

• For the steps to get started quickly, see Quickstart: monitor Azure SQL with database watcher. For setup and configuration details, see Create and configure a database watcher.

• For answers to frequently asked questions about database watcher, see Database watcher FAQ.

• For a video overview of database watcher, watch a Data Exposed episode:

Supported Azure SQL targets

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.

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

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:

*You can use a free Azure Data Explorer cluster when a service level agreement is not required and when query performance and storage requirements are satisfied by the free cluster capacity specifications. The free cluster trial period is for a year and can be extended automatically.

There is no charge per monitored Azure SQL resource or per user, making database watcher an inexpensive monitoring solution for larger Azure SQL environments and larger teams.

Regional availability

At this time, you can create database watchers in the following Azure regions:

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.

¹A high availability replica of a database, elastic pool, or SQL managed instance is monitored independently of its parent replica, and is considered a separate target.

Dashboards

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.

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.

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

SQL monitoring data

Database watcher collects monitoring data from more than 70 SQL catalog views and dynamic management views (DMVs). Data from one or multiple related views is transformed into a dataset. For example, data from sys.dm_exec_sessions, sys.dm_exec_requests, and other views forms the Active sessions dataset. For each dataset, there is a separate table in the Azure Data Explorer or Real-Time Analytics database.

Database watcher has separate dataset groups for databases, elastic pools, and SQL managed instances. There are 10 to 30 datasets in each group, providing a detailed view of database performance, configuration, and health for your Azure SQL resources.

For more information, see Database watcher data collection and datasets.

Network connectivity

Database watcher uses a remote data collection agent that connects to targets, data store, and key vault over the network. Depending on your network security requirements and the configuration of your Azure resources, database watcher can use either private or public connectivity. You always fully control network connectivity from database watcher to your Azure resources.

For more information about network connectivity in Azure SQL, see Azure SQL Database connectivity architecture and Azure SQL Managed Instance connectivity architecture.

Private connectivity

To provide private connectivity, database watcher uses Azure Private Link. When you configure a watcher, you can create managed private endpoints to let the watcher connect to databases and elastic pools on logical servers, or to SQL managed instances. You can also create a private endpoint for the Azure Data Explorer cluster, and for the key vault storing SQL authentication credentials. At this time, private connectivity is not available for connections to Real-Time Analytics in Microsoft Fabric.

A resource owner must approve a private endpoint before database watcher can use it. Conversely, resource owners can delete any database watcher private endpoint at any time to stop data collection.

Once a private endpoint for an Azure resource is created and approved, all network traffic between a watcher and the resource uses private connectivity, even if public connectivity for the resource remains enabled.

For more information about private endpoints in Azure SQL, see Azure Private Link for Azure SQL Database and Azure Private Link for Azure SQL Managed Instance.

Public connectivity

If private connectivity is not required, database watcher can use public connectivity to connect to Azure resources. To enable a watcher to connect to databases and elastic pools on an Azure SQL Database logical server, public access to the server must be enabled, and the IP-based firewall must allow connections from all Azure services.

To enable a watcher to connect to a SQL managed instance using public connectivity, the instance must have its public endpoint enabled. Additionally, a Network Security Group (NSG) rule that allows inbound traffic on TCP port 3342 to the managed instance subnet must have its source set to AzureCloud. For more information, see Configure public endpoints in Azure SQL Managed Instance.

To enable a watcher to connect to an Azure Data Explorer cluster or a key vault using public connectivity, the cluster or vault must have its network access enabled from all networks.

Data access

Just like network connectivity, you fully control database watcher access to your databases. You grant access by creating dedicated database watcher logins on logical servers and SQL managed instances, and then granting specific, limited permissions to collect monitoring data from SQL system views.

Watcher authentication

Database watcher supports Microsoft Entra authentication (previously known as Azure Active Directory authentication). This is the preferred and recommended way for a watcher to authenticate to a SQL target. You create a Microsoft Entra authentication login for the managed identity of the watcher on all logical servers and SQL managed instances that you want to monitor.

Database watcher also supports password-based SQL authentication. You might use SQL authentication if Microsoft Entra authentication is not enabled on your Azure SQL resources. For more information, see Additional configuration to use SQL authentication.

Watcher authorization

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

If you deny the required permissions to the database watcher login or user, or to a role that has the database watcher login or user as a member (including the public database role), then database watcher might not collect monitoring data. Depending on which permissions are denied, this might affect some or all datasets.

Conversely, if you grant unnecessary permissions to the database watcher login or user, or to a role that has the database watcher login or user as a member, then database watcher might not collect monitoring data for some or all datasets. Similarly, data might not be collected if you add the database watcher login or user to a built-in server or database role.

What's new

This section describes recent database watcher fixes, changes, and improvements.

Limitations

This section describes database watcher limitations. Workarounds are provided if available.

Known issues

During preview, database watcher has the following known issues.

Troubleshoot

This section describes the steps you can take to solve common problems. If the steps in this section don't solve the problem, open a support case.

Data is not collected

If you create a new watcher and do not see monitoring data on dashboards and in the data store, or if you only see older data, review this section.

• On the watcher Overview page, check the Status field to see if the watcher is running. If not, use the Start button on the same page to start data collection. A new watcher is not started automatically.

• Check that the watcher has access to the data store.

• If you use an Azure Data Explorer database as the data store, check that the Azure Data Explorer cluster is started. For more information, see Stopped Azure Data Explorer clusters.

• Check that the watcher has the specific, limited access to SQL targets. Additionally, if using SQL authentication for any targets, check watcher access to key vault, or use the recommended Microsoft Entra authentication instead.

• If you want the watcher to use Microsoft Entra authentication to connect to SQL targets, make sure that Microsoft Entra authentication is enabled on the logical servers hosting the database and elastic pool targets, and on the managed instance targets.

• If you created any private endpoints for the watcher, make sure that they are approved by the resource owner.

• If you are using public connectivity, make sure that the requirements to allow the watcher to connect to targets, data store, and key vault are met.

• The Azure Data Explorer cluster or database, or the Real-Time Analytics database might have been deleted after it was selected as the data store for your watcher. Navigate to the cluster and the database, and confirm that they exist.

• If you are using the free Azure Data Explorer cluster, make sure that you haven't reached the storage capacity of the cluster. For more information, see Free Azure Data Explorer cluster.

If you make changes to watcher access or connectivity as part of troubleshooting, you might need to stop and restart the watcher for the changes to take effect.

Dashboards are blank

If you select the Dashboards page of a watcher, but do not see a summary of SQL targets on the page, expand the Data store section. If you see a Cannot connect ... error, review this section.

• You might not have access to the data store. For more information, see Grant users and groups access to the data store.

• You might not have network connectivity to the data store. For example, this happens if connections from your browser to the Azure Data Explorer cluster use public connectivity, but you disable public access to the cluster. In that case, you also cannot connect to the cluster from Kusto Explorer or the Azure Data Explorer web UI.

  To resolve this, establish private connectivity from your machine to the Azure Data Explorer cluster as described in Private connectivity to the data store.

To validate that you have access and can connect to the data store, and that the data store database exists, follow these steps:

• On the Dashboards page of a watcher, expand the Data store section, and copy the Kusto query URI value. Make sure to copy the entire URI string. Make a note of the Kusto database value as well.

• Open the Azure Data Explorer web UI. Sign in if prompted.

• Select Add, Connection, and enter the copied URI as the Connection URI.

• Select Add to create a new connection.

• Once a new connection entry is added, expand it to view the databases.

• Select the database referenced as the Kusto database on the Dashboards page of your watcher, and select the + sign on the tab bar to open a new query tab connected to this database.

• Run the following KQL command:

  .show database principals;
  

  Check that a row for a Viewer or a higher privileged role exists for your user account, or for a Microsoft Entra ID group that contains your user account.

Send feedback

The database watcher team at Microsoft is looking forward to your comments and suggestions. You can send product feedback in one of the following ways:

• Post a new idea in the SQL feedback forum. On the Post a new idea page, use SQL as the forum, select the Azure SQL group, and include database watcher in the title. The feedback you submit in the feedback forum is public. Other community members can upvote and comment on your ideas and suggestions. Community votes and comments help the database watcher team plan and prioritize product improvements.
• Use the feedback button on one of database watcher pages in the Azure portal. For example, you can find the feedback button on the watcher Overview page, or on dashboards next to the refresh button. The feedback you send this way is not public. When you submit feedback, you can optionally allow Microsoft to email you regarding this feedback for follow-ups and clarifications.

For technical support or help solving a problem with database watcher, open a support case.

Related content

• Quickstart: Create a database watcher to monitor Azure SQL (preview)
• Create and configure a database watcher (preview)
• Database watcher data collection and datasets (preview)
• Analyze database watcher monitoring data (preview)
• Database watcher FAQ