Database Watcher for Azure SQL - Deep Dive

Database Watcher is Microsoft's modern, low-friction monitoring service for Azure SQL Database, Azure SQL Managed Instance, and SQL Server enabled by Azure Arc. It replaces the patchwork of "deploy a VM with Telegraf, configure Log Analytics, build Workbooks" (i.e., the legacy SQL Insights story) with a fully managed, near-real-time, KQL-powered observability stack.

Why this matters for DP-300

The April 24, 2026 skills outline explicitly added "Configure Azure SQL Database Watcher" under Domain 3 (Monitor, Configure, and Optimize Database Resources). Expect at least one scenario question - usually framed as "You need near-real-time multi-server SQL telemetry with KQL dashboards and minimal infrastructure. What should you deploy?" -> Database Watcher.

At-a-glance

Property	Value
Status	Generally Available (GA, Sept 2024) for Azure SQL DB & MI; preview for Arc-enabled SQL Server
Targets monitored	Azure SQL DB (single + elastic pool), Azure SQL MI, SQL Server enabled by Azure Arc
Telemetry store	Azure Data Explorer (ADX) cluster or Eventhouse in Microsoft Fabric Real-Time Intelligence
Latency	Seconds (typically < 60s end-to-end)
Query language	KQL (Kusto Query Language)
UI	Azure Portal dashboards + ADX/Fabric for ad-hoc KQL
Authentication	Managed identity only (system- or user-assigned)
Scale	One watcher can monitor up to ~100 targets
Pricing model	Pay only for the underlying ADX cluster / Eventhouse + (small) ingest. Service itself is free.
Resource provider	Microsoft.DatabaseWatcher/watchers

🎯 Exam Focus

If a question contrasts Database Watcher vs SQL Insights (Azure Monitor): SQL Insights is being retired (December 31, 2026). Database Watcher is the recommended successor. New deployments must use Database Watcher.

Architecture

Database Watcher data flow

1

1. Targets

Azure SQL DB / MI / Arc-SQL

No agent - DMV polling over TDS

Authenticates via watcher MI

2

2. Watcher service

Managed control plane in Azure

Polls each dataset on its own cadence

Streams to ADX/Eventhouse

3

3. Telemetry store

ADX cluster OR Fabric Eventhouse

KQL tables, hot+cold cache

Retention configurable per-table

4

4. Visualize & alert

Built-in Portal dashboards

ADX dashboards / Power BI

Azure Monitor alerts on KQL

Datasets collected

Dataset	Source DMV(s)	Default cadence	Notes
sqldb_database_properties	sys.databases, sys.database_service_objectives	1 h	SLO, edition, max size
sqldb_resource_stats	sys.dm_db_resource_stats	15 s	CPU %, log write %, data IO %, memory %
sqldb_resource_governance	sys.dm_user_db_resource_governance	1 h	Workload group limits
sqldb_requests	sys.dm_exec_requests + sys.dm_exec_sessions	30 s	Active sessions, blocking chains
sqldb_query_wait_stats	sys.dm_exec_session_wait_stats	30 s	Per-session wait analysis
sqldb_performance_counters_common	sys.dm_os_performance_counters	30 s	Buffer cache hit, page life expectancy, batch req/s
sqldb_query_runtime_stats	Query Store (sys.query_store_runtime_stats)	15 min	Top queries by CPU/duration/IO
sqldb_query_plan	Query Store	On-demand	Captures plan XML
sqldb_storage_io	sys.dm_io_virtual_file_stats	30 s	File-level IOPS, throughput, latency
sqldb_index_usage_stats	sys.dm_db_index_usage_stats	1 h	Seeks, scans, lookups per index
sqldb_xevents_errors_and_waits	system_health Extended Events session	streaming	Deadlocks, severe errors, long waits

🏢 Real-World DBA Note

Oracle DBA parallel: Database Watcher = a managed equivalent of Oracle Cloud Control / Enterprise Manager + AWR repository, but the warehouse is ADX (Kusto) instead of an OEM repository database, and you query with KQL instead of SQL/PLSQL. There's no agent on each target - the watcher polls DMVs over TDS, similar to OEM agentless monitoring. :::

Prerequisites

1. Azure subscription with permissions to create resources in the target region.
2. Telemetry store - one of:
   • Existing Azure Data Explorer (ADX) cluster + database, OR
   • Existing Microsoft Fabric Eventhouse / KQL database.
3. Managed identity for the watcher (system-assigned is created automatically; user-assigned must exist beforehand).
4. Network connectivity: the watcher must reach each target's TDS endpoint (1433/3342). Private endpoints supported.
5. RBAC on targets: the watcher's MI needs db_datareader on master plus the dataset-specific permissions (granted via the script Microsoft publishes - see below).

Required permissions on each target (Azure SQL DB)

-- Run on master (and on each user database for some datasets)
CREATE USER [my-watcher-mi] FROM EXTERNAL PROVIDER;
ALTER ROLE [##MS_ServerStateReader##] ADD MEMBER [my-watcher-mi];
ALTER ROLE [##MS_DefinitionReader##]  ADD MEMBER [my-watcher-mi];

-- Per user DB (for Query Store & file stats)
CREATE USER [my-watcher-mi] FROM EXTERNAL PROVIDER;
GRANT VIEW DATABASE STATE  TO [my-watcher-mi];
GRANT VIEW DATABASE PERFORMANCE STATE TO [my-watcher-mi]; -- newer perm for Hyperscale

For Managed Instance the equivalent server-level role is ##MS_ServerStateReader## plus VIEW SERVER STATE.

🎯 Exam Focus

The watcher will silently skip datasets it can't read. If "Sessions and requests" returns no rows in the dashboard, your most likely cause is a missing VIEW SERVER STATE (or its modern equivalent) on the target.

Provisioning - Azure CLI walk-through

Step 1 - Create (or reuse) an ADX cluster

RG=rg-dbwatcher
LOC=westeurope
KCLUSTER=ehabkusto1
KDB=sqlwatcher

# Dev tier ADX cluster - perfect for a watcher up to ~50 targets
az kusto cluster create \
  --cluster-name $KCLUSTER \
  --resource-group $RG \
  --location $LOC \
  --sku name="Dev(No SLA)_Standard_E2a_v4" tier="Basic" \
  --capacity 1

az kusto database create \
  --cluster-name $KCLUSTER \
  --resource-group $RG \
  --database-name $KDB \
  --read-write-database location=$LOC soft-delete-period=P30D hot-cache-period=P7D

Step 2 - Create the watcher

az resource create \
  --resource-group $RG \
  --resource-type "Microsoft.DatabaseWatcher/watchers" \
  --name watcher-sqlfleet-prod \
  --location $LOC \
  --is-full-object \
  --properties '{
      "identity": { "type": "SystemAssigned" },
      "properties": {
        "datastore": {
          "adxClusterResourceId":
            "/subscriptions/<sub>/resourceGroups/'$RG'/providers/Microsoft.Kusto/clusters/'$KCLUSTER'",
          "kustoClusterUri": "https://'$KCLUSTER'.'$LOC'.kusto.windows.net",
          "kustoClusterDisplayName": "'$KCLUSTER'",
          "kustoDatabaseName": "'$KDB'",
          "kustoManagementUrl": "https://'$KCLUSTER'.'$LOC'.kusto.windows.net",
          "kustoOfferingType": "adx"
        }
      }
  }'

Step 3 - Grant the watcher MI rights on the ADX database

WATCHER_PRINCIPAL=$(az resource show \
  -g $RG --resource-type "Microsoft.DatabaseWatcher/watchers" \
  --name watcher-sqlfleet-prod --query identity.principalId -o tsv)

az kusto database-principal-assignment create \
  --cluster-name $KCLUSTER --resource-group $RG --database-name $KDB \
  --principal-assignment-name watcherIngestor \
  --principal-id $WATCHER_PRINCIPAL --principal-type App \
  --role Admin --tenant-id $(az account show --query tenantId -o tsv)

Step 4 - Add targets

SQLSRV=ehab-sqlsrv01
SQLDB=salesprod

az resource create \
  --resource-group $RG \
  --resource-type "Microsoft.DatabaseWatcher/watchers/targets" \
  --name watcher-sqlfleet-prod/$SQLSRV-$SQLDB \
  --is-full-object \
  --properties '{
      "properties": {
        "targetType": "SqlDb",
        "connectionServerName": "'$SQLSRV'.database.windows.net",
        "targetAuthenticationType": "Aad",
        "sqlDbResourceId":
          "/subscriptions/<sub>/resourceGroups/'$RG'/providers/Microsoft.Sql/servers/'$SQLSRV'/databases/'$SQLDB'"
      }
  }'

Step 5 - Start the watcher

az resource invoke-action \
  --resource-group $RG \
  --resource-type "Microsoft.DatabaseWatcher/watchers" \
  --name watcher-sqlfleet-prod \
  --action start

After ~2 minutes telemetry begins flowing to the ADX database. Open the Database Watcher blade in the Portal -> Dashboards to see the pre-built views.

Provisioning - Bicep

param location string = resourceGroup().location
param watcherName string = 'watcher-sqlfleet-prod'
param adxClusterId string
param adxClusterUri string
param adxClusterName string
param adxDatabaseName string

resource watcher 'Microsoft.DatabaseWatcher/watchers@2024-10-01-preview' = {
  name: watcherName
  location: location
  identity: { type: 'SystemAssigned' }
  properties: {
    datastore: {
      adxClusterResourceId: adxClusterId
      kustoClusterUri: adxClusterUri
      kustoClusterDisplayName: adxClusterName
      kustoDatabaseName: adxDatabaseName
      kustoManagementUrl: adxClusterUri
      kustoOfferingType: 'adx'
    }
  }
}

resource sqlTarget 'Microsoft.DatabaseWatcher/watchers/targets@2024-10-01-preview' = {
  parent: watcher
  name: 'salesprod-target'
  properties: {
    targetType: 'SqlDb'
    connectionServerName: 'ehab-sqlsrv01.database.windows.net'
    targetAuthenticationType: 'Aad'
    sqlDbResourceId: resourceId('Microsoft.Sql/servers/databases',
                                'ehab-sqlsrv01', 'salesprod')
  }
}

KQL - the queries you'll actually run

Once telemetry lands in ADX/Eventhouse, every dataset is a normal KQL table. Below are the highest-value queries DBAs reach for daily.

CPU usage trend (last 6 h, 1-minute bins)

sqldb_resource_stats
| where TimeGenerated > ago(6h)
| where LogicalServerName == "ehab-sqlsrv01"
| summarize avg(avg_cpu_percent) by bin(TimeGenerated, 1m), DatabaseName
| render timechart 

Top 10 wait types right now

sqldb_query_wait_stats
| where TimeGenerated > ago(15m)
| summarize total_wait_ms = sum(WaitTimeMs) by WaitType
| top 10 by total_wait_ms desc

Blocking chains in the last hour

sqldb_requests
| where TimeGenerated > ago(1h) and BlockingSessionId > 0
| project TimeGenerated, DatabaseName, SessionId, BlockingSessionId,
          WaitType, WaitTimeMs, StatementText = substring(SqlText, 0, 200)
| order by TimeGenerated desc

Top 10 queries by total CPU (last 24 h, Query Store-driven)

sqldb_query_runtime_stats
| where TimeGenerated > ago(24h)
| summarize total_cpu_sec = sum(CpuTimeMs)/1000.0,
            execs = sum(ExecutionCount),
            avg_dur_ms = avg(AvgDurationMs)
            by QueryHash, DatabaseName
| top 10 by total_cpu_sec desc

Deadlocks captured by system_health

sqldb_xevents_errors_and_waits
| where TimeGenerated > ago(7d)
| where EventName == "xml_deadlock_report"
| project TimeGenerated, DatabaseName, EventData

Storage IO hot files

sqldb_storage_io
| where TimeGenerated > ago(1h)
| summarize avg_read_ms = avg(IoStallReadMs/maxof(NumOfReads,1)),
            avg_write_ms = avg(IoStallWriteMs/maxof(NumOfWrites,1))
            by DatabaseName, FileType, FileName
| order by avg_write_ms desc

Alerting on KQL

Database Watcher itself does not generate alerts - you configure Azure Monitor scheduled query rules against the ADX/Eventhouse cluster.

# Example: alert if avg CPU > 85% for any DB over the last 5 minutes
az monitor scheduled-query create \
  --name "DB CPU > 85%" --resource-group $RG \
  --scopes "/subscriptions/<sub>/resourceGroups/$RG/providers/Microsoft.Kusto/clusters/$KCLUSTER" \
  --condition "count 'union sqldb_resource_stats | where TimeGenerated > ago(5m) | summarize avg(avg_cpu_percent) by DatabaseName | where avg_avg_cpu_percent > 85' > 0" \
  --window-size 5m --evaluation-frequency 5m \
  --action /subscriptions/<sub>/.../actionGroups/dba-pager

Comparison vs other Azure SQL monitoring options

Capability	Database Watcher	SQL Insights (deprecated)	Azure Monitor Metrics	Query Performance Insight
Multi-target fleet view	Yes	Yes	Limited	No (single DB)
KQL ad-hoc queries	Yes (ADX)	Yes (Log Analytics)	Yes (Log Analytics)	No
Near-real-time (< 1 min)	Yes	No (~5 min)	Yes (1 min)	No (~1 h)
Query Store integration	Yes	Partial	No	Yes
Blocking chains	Yes	Yes	No	No
Wait stats per query	Yes	Yes	No	Partial
Setup effort	Minutes (managed)	High (VM + Telegraf)	Built-in	Built-in
Custom dashboards	ADX, Power BI	Workbooks	Workbooks	No
Cost	ADX/Eventhouse	LA ingest + monitoring VM	Included in Azure SQL	Included in Azure SQL

🎯 Exam Focus

Decision rules for the exam:

• "Built-in, zero-cost, single DB, last 24-48 h" -> Query Performance Insight
• "Quick metric chart + standard alert" -> Azure Monitor metrics
• "Custom KQL across many servers, near real-time" -> Database Watcher
• "Anything legacy that mentions Telegraf VM" -> SQL Insights (and recommend migrating to Database Watcher) :::

Operational playbook

Scenario	Action
Add a new server to the fleet	Add target -> grant MI permissions -> restart the watcher
Reduce ingest cost	Lower per-dataset polling cadence in the watcher properties
Long retention	Configure ADX hot/cold cache & ADX retention policies per table
Fleet upgrade impact analysis	Pin a baseline KQL query as ADX dashboard tile, compare windows
Migrate from SQL Insights	Stand up watcher in parallel -> validate queries -> decommission monitoring VM

Limitations to remember

• Authentication is Microsoft Entra (managed identity) only - no SQL logins.
• A single watcher can monitor up to ~100 targets; create more watchers for larger fleets.
• No Azure Synapse, no Cosmos DB, no PostgreSQL/MySQL flexible servers (different services).
• The watcher stores no telemetry itself - if you delete the ADX/Eventhouse, history is lost.
• Per-region availability is rolling out; check the Microsoft Learn doc for the current region list.

---

Anti-Patterns

• "Database Watcher replaces Query Store." It consumes QS data. Turn QS off and Watcher loses its richest source. Watcher = transport + storage + cross-server query; QS = capture.
• "Use SQL logins to keep it simple." Watcher only authenticates with managed identity. Turning on "SQL authentication only" on a target makes it un-monitorable.
• "One watcher for the whole estate." Soft cap ~100 targets. At 300 SQL DBs you need 3 watchers, plus careful target distribution. Plan the watcher topology like you'd plan a monitoring agent fleet.
• "ADX is just storage - leave it on default retention." Default ADX hot cache is 31 days. Cross-quarter trend analysis silently disappears off the hot tier and queries get slow. Set explicit retention + caching policies per Watcher table.
• "Drop the Eventhouse to save money during the freeze." It deletes all history. Watcher does not re-collect from sources - telemetry is forward-only.
• "Use Watcher for PostgreSQL/MySQL." Not supported. Use Azure Monitor + Log Analytics + per-engine insights instead.

⚠️ Watch Out

Watcher requires Network Watcher visibility into target SQL endpoints. A target behind a Private Endpoint with no DNS resolution from the Watcher subnet silently fails to collect - you'll see the target as "Unhealthy" with no rows in sqldb_database_query_store_runtime_stats.

---

Migration Between Monitoring Stacks

From -> To	Path	Cost
SQL Insights (preview) -> Database Watcher	Stand up Watcher + ADX in parallel; validate KQL parity; decommission Insights VM	Adds ADX cost; removes Log Analytics ingest cost for the same telemetry
Per-server Query Store dashboards -> Database Watcher	Keep QS on; add Watcher; migrate dashboards to ADX/Workbook	Watcher gives cross-server view QS can't
3rd-party agent (Redgate / Datadog / SolarWinds) -> Database Watcher	Run side-by-side; compare alert fidelity; cut over	License savings vs ADX + Watcher cost - evaluate per-server
Custom DMV polling jobs -> Database Watcher	Watcher built-in collectors replace most scripts	Free up DBA time; lose ad-hoc collector flexibility
Database Watcher (ADX) -> Database Watcher (Fabric Eventhouse)	Re-point destination at create time	Eventhouse pricing follows Fabric capacity, not per-cluster
Cross-region targets <-> single watcher	Confirm watcher region supports the targets	If not supported, deploy regional watcher pairs

Most expensive switch: leaving a 3rd-party APM and proving alert fidelity at scale before cutover.

---

Real Scenarios

1. Fleet of 60 Azure SQL DBs across 3 subscriptions -> Single Watcher + ADX cluster D11_v2. Driver: cross-DB query, central dashboards. Trade-off: ADX minimum ~$300/mo.
2. Migration from on-prem Redgate SQL Monitor -> Watcher + Eventhouse for new Azure estate; keep Redgate for the residual on-prem boxes. Driver: split lifecycle, no big-bang. Trade-off: two panes of glass during the transition.
3. Compliance team needs 13-month query trend -> Watcher + ADX with explicit 400-day retention and 90-day hot cache. Driver: defaults wouldn't have kept it. Trade-off: ADX storage cost grows linearly.
4. MI estate hosting an ISV product, want zero local agent footprint -> Watcher (agentless, MI-based). Driver: no permission to install Connected Machine agent on the host (PaaS - there isn't one). Trade-off: limited to what DMVs / QS expose.
5. POC for 5 DBs before fleet rollout -> Watcher + Fabric Eventhouse (no ADX cluster to provision). Driver: lowest entry cost. Trade-off: Eventhouse capacity tied to Fabric SKU sizing.

---

Flashcards

🧠 Smart order (weak cards first)

What query language does Database Watcher use?

Click to reveal answer

KQL (Kusto Query Language) - telemetry lives in Azure Data Explorer or a Microsoft Fabric Eventhouse.

1 / 6

Practice quiz

Q1/5

0 correct

You manage 40 Azure SQL Databases across 3 subscriptions and need a single, near-real-time view of CPU, waits, and blocking with custom KQL dashboards and alerting. Which solution should you deploy?

Sources & further reading

• Database Watcher overview - Microsoft Learn
• Create and configure a Database Watcher (quickstart)
• Database Watcher data collection reference
• SQL Insights retirement notice
• DP-300 study guide (April 24, 2026)

Last verified against MS Learn: April 28, 2026.