# Health Audit Sqlserver

> Use when the user asks for a health audit of a SQL Server or Managed Instance (not Azure SQL Database): what is failing or risky now: availability, integrity, resources, storage, backups, HA, jobs, security.

- Skill: `microsoft/health-audit-sqlserver` (Agent Skill, multi-file: 11 files)
- Install (CLI): `npx get-skill add microsoft/health-audit-sqlserver`
- Raw SKILL.md: https://api.skillmd.com/api/skills/microsoft/health-audit-sqlserver/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: Microsoft (https://skillmd.com/u/microsoft)
- Updated: 2026-11-18
- Page: https://skillmd.com/skills/microsoft/health-audit-sqlserver

---


# SQL Server Health Audit

## Purpose

Use this skill to answer: **What is unhealthy, failing, anomalous, or operationally risky now
or within the requested lookback window?**

This skill is an orchestration layer. It collects bounded health evidence and routes confirmed
signals to specialist skills. It does not replace configuration governance, workload tuning, or
specialist incident investigations.

Supported targets:

- SQL Server 2012-2022, including virtualized and Azure VM deployments
- Azure SQL Managed Instance, with managed-service limitations called out explicitly

Not supported:

- Azure SQL Database: use a database-scoped Azure SQL health workflow
- EngineEdition 6 or 11: use Synapse/Fabric warehouse-specific tooling
- Fabric SQL Database and Azure SQL Edge: future capability-gated extensions

## Safety Contract

- Execute read-only SQL only.
- Never create, alter, drop, kill, fail over, shrink, rebuild, reconfigure, clear caches, reset
  waits, or change Query Store.
- Run Step 0 before any feature-specific query.
- Never execute a query unless its platform, version, object, column, permission, cost, and
  sensitivity gates are satisfied.
- Permission denial, inaccessible data, empty retained history, or missing visibility is
  `UNKNOWN`, never `PASS`.
- Redact SQL text, plans, paths, identities, addresses, job commands, and log text unless
  `IncludeSensitiveText = true`.
- Expensive checks require `IncludeExpensiveChecks = true`. Error-log, dump, registry, and other
  privileged checks require `IncludePrivilegedChecks = true`.
- On query failure, capture the error, mark that check `ERROR`, and continue only with independent
  checks. Do not retry with broader permissions or an unbounded alternative.

## Required tools

- A read-only SQL query tool connected to the intended SQL instance and database. Prefer, in order:
  the SQL Tools MCP server that ships with the `microsoft-sql-admin` plugin
  (`mssql_list_database_connections`, `mssql_connect_database`, then `mssql_execute_query`); any
  other SQL tool the agent host provides for running T-SQL and reading result sets;
  [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the
  SQL Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup`
  skill, which asks before installing or configuring anything. If no SQL tool can be made available,
  provide the T-SQL for the user to run and ask for the results.
- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that
  holds only `VIEW SERVER PERFORMANCE STATE` (`VIEW SERVER STATE` before SQL Server 2022) or, on Azure
  SQL Database, `VIEW DATABASE PERFORMANCE STATE` or `##MS_ServerStateReader##`, plus any other read
  permission these queries need. A tool is only as read-only as the login it connects with.

## Parameters

| Parameter | Default | Description |
|---|---:|---|
| LookbackHours | 24 | Recent evidence window |
| DatabaseName | null | Optional single-database scope |
| IncludeSystemDatabases | false | Include master, model, msdb, and tempdb where applicable |
| IncludeExpensiveChecks | false | Allow bounded medium/high-cost checks |
| IncludePrivilegedChecks | false | Allow error-log and dump evidence |
| IncludeSensitiveText | false | Allow sensitive values in evidence and output |
| SampleCount | 3 | Number of resource snapshots; minimum 2, maximum 10 |
| SampleIntervalSeconds | 5 | Delay between snapshots; minimum 2, maximum 60 |
| TopN | 20 | Maximum rows per ranked query; maximum 100 |
| TimeoutSeconds | 30 | Per-query timeout |
| CriticalOnly | false | Run only Step 0, baseline, and critical checks |
| DeploymentHint | auto | `auto`, `onprem`, or `azure-vm` |

Clamp out-of-range values to the documented limits and disclose that in the report.

## Loading References

When a step names a file, read it from `references/` relative to this `SKILL.md` before
executing the step.

## Finding Contract

Normalize each check to:

```text
check_id
status: PASS | FAIL | WARN | INFO | UNKNOWN | NOT_APPLICABLE | ERROR
severity: CRITICAL | HIGH | MEDIUM | LOW | INFORMATIONAL
scope_type
scope_name
observed_value
expected_condition
evidence
evidence_time_utc
data_window
permission_state
capability_state
confidence
recommendation
verification_action
```

## Workflow

Execute in order. Do not skip Step 0 and do not run extra queries.

### Step 0: Detect Platform and Capabilities

Load [references/step-0-platform-capabilities.md](references/step-0-platform-capabilities.md).
Build the complete `EnvironmentProfile` and eligibility plan before continuing.

- EngineEdition 2, 3, or 4: SQL Server path. Virtualization does not prove Azure hosting.
- EngineEdition 8: Azure SQL Managed Instance path.
- EngineEdition 5, 6, 11, 12, 9, or unknown: follow the stop/limited-routing rules in the
  reference.
- If platform identity cannot be established, stop.
- If a capability or permission cannot be established, block only its dependent checks.

### Step 1: Scope and Baseline

Load [references/step-1-scope-and-baseline.md](references/step-1-scope-and-baseline.md).
Establish UTC time, uptime, reset boundaries, database scope, and evidence windows. Missing
baseline evidence lowers confidence for cumulative DMV findings.

### Step 2: Critical Health

Load [references/step-2-critical-health.md](references/step-2-critical-health.md).
Run critical availability and integrity checks first. Promote active outage, recovery, suspect
page, severe HA, or dump/error evidence to the top of the report.

If `CriticalOnly = true`, skip Steps 3-6 after Step 2, evaluate eligible Step 7 routing, and
continue to synthesis.

### Step 3: Resource Health

Load [references/step-3-resource-health.md](references/step-3-resource-health.md).
Collect `SampleCount` bounded snapshots at `SampleIntervalSeconds`. Compare samples; do not infer
sustained pressure from one point. Return dominant signals and routing candidates, not speculative
root cause.

### Step 4: Storage and Tempdb Health

Load
[references/step-4-storage-and-tempdb-health.md](references/step-4-storage-and-tempdb-health.md).
Assess capacity, file state, log usage, VLF state, tempdb layout, and bounded IO evidence using
only eligible variants.

### Step 5: Backup, HA, and Jobs

Load [references/step-5-backup-ha-jobs.md](references/step-5-backup-ha-jobs.md).
Assess backup freshness, current HA/log-shipping state, recent Agent failures, and notification
coverage. Do not claim backup or HA compliance without workload RPO/RTO requirements. Treat
Managed Instance built-in HA and managed backups as platform-managed controls.

### Step 6: Security Exposure

Load [references/step-6-security-exposure.md](references/step-6-security-exposure.md).
Report immediate exposure with minimum disclosure. Redact principal names by default and separate
confirmed dangerous state from policy questions.

### Step 7: Targeted Drill-down

Load [references/step-7-targeted-drilldowns.md](references/step-7-targeted-drilldowns.md).
Invoke only specialists justified by captured evidence. Pass identifiers, database names, time
windows, and dominant signals. Do not repeat specialist query sets.

### Step 8: Synthesis

Load [references/decision-tree.md](references/decision-tree.md), classify each finding and the
overall health state, then load [references/report-template.md](references/report-template.md)
and produce the report.

## Completion Rules

- Cite a stable `HA-*` check ID for every finding.
- Distinguish active incidents from durable risk and optional follow-up.
- Show `UNKNOWN`, `ERROR`, `NOT_APPLICABLE`, and skipped coverage explicitly.
- Overall status cannot be `HEALTHY` if any mandatory check is `UNKNOWN` or `ERROR`.
- Report the installed SQL build, but do not claim it is current unless an authoritative,
  maintained servicing source was consulted.

