DBPilot

Self-hosted database autonomous diagnostics platform.

DBPilot continuously samples your instances and serves the day-to-day DBA workflow in one web console: performance trends and insight (AAS load decomposition), Top SQL, query plan change tracking, missing-index advice, index usage & fragmentation, blocking analysis, deadlock analysis, and slow query logs.

简体中文

Contents

• Highlights
• Screenshots
• Quick start
• Password and master key
• Configuration
• Building from source
• Architecture
• Features
• Engine support matrix
• AI diagnostics (MCP Server)
• Overhead on monitored instances
• Embedding via NuGet
• FAQ
• License

Highlights

• Single-process deployment — one .NET 10 process + one metadata DB, schema auto-created on startup (SQL Server / MySQL / PostgreSQL / SQLite).
• Monitors three engines — SQL Server 2008–2022, MySQL 8.0+, PostgreSQL 13+. Monitoring and metadata-store engines are independent axes: any combination works.
• <1% CPU overhead — collectors read only in-memory metadata views (DMVs / pg_stat_* / performance_schema); business tables are never scanned.
• AI-ready — built-in read-only MCP Server exposes 11 diagnostic tools to AI agents (Claude Code, Codex CLI). Just ask: "did any SQL slow down in the last hour?"
• Capability-driven UI — features without an equivalent data source hide or degrade per engine (PostgreSQL gets deadlock trends instead of event details); APIs answer with actionable messages.

Screenshots

Performance insight (AAS)	Performance trends

Deadlock analysis	Deadlock event detail

Blocking analysis	Blocking chain detail

Index usage	Slow query log

Quick start

Fastest path: NuGet packages (frontend embedded — no Node.js). Requires .NET SDK 10.0+.

1. Create a host project and add the metapackage:

mkdir dbpilot-demo && cd dbpilot-demo
dotnet new web
dotnet add package DBPilot

Don't name the host project dbpilot or DBPilot.* — it collides with the NuGet package and restore fails (NU1108).

2. Replace Program.cs with:

using DBPilot.AspNetCore.Extension;
using DBPilot.Core.Providers;

var builder = WebApplication.CreateBuilder(args);
builder.AddDBPilot(o => o.PlatformEngine = DbpilotEngine.Sqlite);  // metadata store: SQLite, zero dependencies

var app = builder.Build();
app.UseDBPilot();
app.Run();

3. Replace appsettings.json with (SQLite flavor):

{
  "Serilog": {
    "MinimumLevel": {
      "Default": "Information",
      "Override": { "Microsoft": "Warning", "System": "Warning" }
    }
  },
  "DBPilot": {
    "ConnectionString": "Data Source=dbpilot_platform.db",
    "Mcp": { "ApiKey": "<random key; non-empty enables MCP, empty = off>" },
    "Auth": {
      "Username": "admin",
      "PasswordHash": "pbkdf2$100000$IOaBWezPYRnzEbBWYwLxZA==$xBWm8jjDfanDmyRttfnZoG4MIk8lcF0UnDbpjJuWaEU=",
      "Secret": "<your master key (>= 32-char random string)>"
    }
  },
  "AllowedHosts": "*"
}

• ConnectionString and Auth:Secret are required — the SQLite file and schema are created on startup; the master key is generated as shown below.
• PasswordHash is the hash of the default password dbpilot@2026; keep it to log in with the default.

4. Run:

dotnet run    # → http://localhost:5000

5. Log in with admin / dbpilot@2026 (change it after deployment), then register your first instance: Instances → Add → Test connection → Enable. Real-time pages work immediately; history accumulates.

Metadata store: for SQL Server / MySQL / PostgreSQL, switch PlatformEngine and the connection string — that's all; registered instances are unaffected. SQLite suits evaluation and single-machine use; prefer a server engine for production (and multi-process roles). Ready-made hosts: samples/ (SqlServer :5200 / MySql :5201 / Sqlite :5203 / PostgreSql :5204).

Password and master key

Change the login password: passwords are PBKDF2 hashes in DBPilot:Auth:PasswordHash — replace the hash to change the password.

1. Create a two-line hash.cs outside any project directory (your home dir works). Inside a project, dotnet run would treat it as an argument:

#:package DBPilot.Core@0.5.3
Console.WriteLine(DBPilot.Core.Auth.PasswordHasher.Hash(args[0]));

2. Run it — the whole output line is the new hash:

dotnet run hash.cs <new-password>

3. Paste it into DBPilot:Auth:PasswordHash, restart.

Repo clones can skip the steps: dotnet run --project samples/DBPilot.Sample.SqlServer -- --hash <new-password>.

Master key (Auth:Secret, or env DBPILOT_MASTER_KEY) — required, exactly two uses:

Purpose	What it does	If you change it
Login-cookie signing	issues/validates login tickets (HMAC-SHA256)	all sessions logged out; log in again
Instance-credential encryption	instance passwords stored AES-256-GCM-encrypted in the metadata DB	old ciphertext is undecryptable, no recovery — re-enter each instance's password in the UI

Hence startup fails without it, and you should not change it once in use. Generate one:

openssl rand -base64 32      # Linux / macOS / Git Bash
# PowerShell: [guid]::NewGuid().ToString("N") + [guid]::NewGuid().ToString("N")

Configuration (appsettings.json)

Two required keys + one required value — DBPilot:ConnectionString, DBPilot:PlatformEngine, and a master key (Auth:Secret or env DBPILOT_MASTER_KEY). Missing → startup error; everything else is optional with defaults.

Key	Required	Notes
DBPilot:ConnectionString	yes	metadata DB connection string; schema auto-created on startup
DBPilot:PlatformEngine	yes	sqlserver / mysql / postgresql / sqlite; in code: o.PlatformEngine = DbpilotEngine.SqlServer. Independent of which engines you monitor
DBPilot:Auth:Secret	yes (or env)	master key — signs login cookies, encrypts instance credentials; ≥32-char random string
DBPILOT_MASTER_KEY (env)	yes (or Secret)	env form of the master key, container-friendly; config value takes precedence
DBPilot:Auth:Username / PasswordHash	no	login account (default admin) / password hash (default dbpilot@2026)
DBPilot:Mcp:ApiKey	no	MCP Server switch (non-empty = on)
DBPilot:Roles	no	process roles (Web / Collector, both by default); multi-process = 1 collector + N web fronts — two collectors on one metadata DB double-collect
DBPilot:Jobs	no	per-collector switch & cron; empty value = disable that collector
DBPilot:Retention / DBPilot:Collect	no	retention days (auto-clean) / parallelism & backoff
DBPilot:AutoInitSchema	no	auto-create schema on startup (default true; set false when DBAs own the schema)
DBPilot:TopSqlExcludePatterns	no	Top SQL noise filter (LIKE patterns); default set built in, explicit empty array clears it

Monitored instances are never configured here — they live in the UI, credentials encrypted in the metadata DB.

Building from source

Additionally requires Node.js 20+ (the embedded web UI comes from the frontend build; NuGet packages ship it prebuilt).

git clone https://github.com/SkyChenSky/DBPilot.git
cd DBPilot
scripts\run\setup.bat      # frontend build + compile + sample appsettings.json (default SqlServer host; pass MySql / Sqlite / PostgreSql to switch)
# edit samples/DBPilot.Sample.SqlServer/appsettings.json — fill in the connection string and Auth:Secret
dotnet run --project samples/DBPilot.Sample.SqlServer    # → http://localhost:5200

Non-Windows / manual equivalent:

cd web && npm install && npm run build
cd .. && dotnet build
cp samples/DBPilot.Sample.SqlServer/appsettings.template.json samples/DBPilot.Sample.SqlServer/appsettings.json

Day-to-day dev: scripts\run\dev.bat, or two terminals — dotnet watch --project samples/DBPilot.Sample.SqlServer (backend, Swagger at /swagger) + cd web && npm run dev (frontend → http://localhost:5173). Tests: scripts\run\test.bat (compile + unit tests + frontend build). Self-test load scripts in scripts/test/ — never run them against production databases.

Architecture

flowchart LR
    subgraph browser["Browser"]
        ui["Web console"]
    end

    subgraph host["DBPilot service (single process)"]
        api["Web API + MCP Server"]
        sched["Background collectors (Quartz)"]
        webhost["Static UI hosting"]
    end

    subgraph platform["Metadata DB (schema auto-created)"]
        tables["Historical data"]
    end

    subgraph monitored["Monitored instances (any number)"]
        dmv["DMV / XE / pg_stat_* / performance_schema"]
    end

    ui --> webhost
    ui -->|API| api
    sched -->|scheduled sampling| dmv
    sched --> tables
    api --> tables
    api -->|real-time queries| dmv

Loading

The browser talks only to the DBPilot service: real-time pages query instances directly, history pages read the metadata DB — both share the same data conventions (noise exclusion, fingerprinting, time windows).

Features

Page	What it answers
Overview	instance health at a glance — metric cards with sparklines, recent events, Top SQL digest
Performance trends	CPU / memory / PLE / QPS·TPS / IO / disk, 10s granularity (30-day retention, auto down-sampling); event overlay for deadlocks / slow SQL / plan changes
Performance insight	Average Active Sessions decomposed into CPU / lock / IO / waits — which resource is saturated, and the SQL statements contributing the load
Top SQL	real-time leaderboard + history trends, merged by fingerprint; one-click noise exclusion
Query plans	plan versions snapshotted automatically; changes raise events with before/after comparison, plan tree and XML
Missing indexes	optimizer recommendations ranked by impact, with CREATE scripts and overlap hints
Index usage / fragmentation	unused-index detection with drop scripts; fragmentation scan with REBUILD / REORGANIZE scripts
Blocking analysis	real-time blocking tree (head blocker, chain, wait times) + historical statistics
Deadlock analysis	captured automatically; graph view of the cycle, statements and lock relationships
Slow query log	above-threshold statements archived automatically — full text, duration, IO, fingerprint

Engine support matrix

Capability	SQL Server	MySQL 8.0+	PostgreSQL 13+
Sessions / blocking (real-time tree + history)	✅	✅	✅ (pg_stat_activity + pg_blocking_pids)
Top SQL (fingerprinted)	✅	✅ (performance_schema digest)	✅ (pg_stat_statements)
Slow query log	✅ (XE events)	✅ (mysql.slow_log table)	◐ template leaderboard
Performance trends	✅	◐ (no OS CPU/memory, PLE, compile counters)	◐ (QPS is transaction-scope)
Deadlock analysis	✅ event details + graph	❌	◐ trend only
Query plan snapshots / change tracking	✅	❌	❌
Missing index advice	✅	❌	❌
Index usage	✅	◐ (unused-index detection conservative)	◐ (unused indexes reliably detectable)
Fragmentation scan	✅	❌	❌
Index disable script	✅	✅ (INVISIBLE)	❌
Disk usage	✅ volume-level	◐ database-level	◐ database-level

Metadata store: SQL Server / MySQL / PostgreSQL / SQLite — independent from the monitored engines, any combination (SQLite = single-executable + single-file embedded deployment).

Prerequisites are minimal and self-checked by the connection-test wizard: MySQL needs performance_schema + slow-log settings; PostgreSQL needs the pg_stat_statements extension; SQL Server works from 2008 up (deadlock capture rides the built-in system_health session). The wizard tells you exactly what to change when something is missing.

AI diagnostics (MCP Server)

A read-only MCP Server (/mcp, Streamable HTTP + API key) hands the platform's evidence — metrics, slow SQL, deadlocks, blocking, indexes — to AI agents as 11 read-only tools. Agents never connect to your databases directly; every call is audit-logged.

Enable (off by default; non-empty ApiKey = on):

"DBPilot": { "Mcp": { "ApiKey": "a sufficiently random key" } }

claude mcp add --transport http dbpilot http://localhost:5200/mcp --header "X-Api-Key: <your-key>"

Then just ask: "use dbpilot to check the instance load over the last hour — any SQL getting slower?" Add --scope user to make it global.

Codex CLI (~/.codex/config.toml):

[mcp_servers.dbpilot]
url = "http://localhost:5200/mcp"

[mcp_servers.dbpilot.http_headers]
X-Api-Key = "<your-key>"

Example projects live in examples/: DBPilot.McpConsole (command-line diagnostic console) and DBPilot.Scenarios (fault-drill project), both referencing the official NuGet packages.

Overhead on monitored instances

<1% CPU, zero disk pressure. Collectors read in-memory metadata views only — no business-table scans, no physical IO, no locks on user objects.

Collector	Frequency	Cost
Sessions / metrics / Top SQL delta / deadlocks / slow SQL	10–60s	millisecond-level metadata queries; XE cursors near zero when idle
Query plan snapshots	5 min	plan XML fetched once per fingerprint (XML generation is the expensive part)
Index snapshots (incl. fragmentation)	daily 03:10	heaviest tick, scheduled at night
History writes	—	always to the metadata DB, never to monitored instances

Built-in mitigations: XE predicates exclude the platform's own traffic, every cron is configurable, instances disable individually, failed connections back off. Same DMV-polling path as SQL Server's own system_health and AWS Performance Insights.

Verify it yourself — after a day of running, the monitoring account's accumulated CPU seconds is the true cost:

SELECT login_name, SUM(cpu_time)/1000 AS cpu_seconds_total
FROM sys.dm_exec_sessions
WHERE host_process_id IS NOT NULL
GROUP BY login_name;

Embedding via NuGet

Published on nuget.org:

dotnet add package DBPilot            # metapackage: AspNetCore + all engines, one line
# or pick what you need:
dotnet add package DBPilot.AspNetCore # API / MCP / auth / scheduling / embedded frontend
dotnet add package DBPilot.SqlServer  # engines — SqlServer / MySql / PostgreSql (monitor + storage)
dotnet add package DBPilot.MySql      #   and Sqlite (storage only, embedded deployments)
dotnet add package DBPilot.Sqlite

using DBPilot.AspNetCore.Extension;
using DBPilot.Core.Providers;

var builder = WebApplication.CreateBuilder(args);
builder.AddDBPilot(o =>
{
    o.PlatformEngine = DbpilotEngine.SqlServer;  // no default — set explicitly (or via DBPilot:PlatformEngine)
    // o.WebOnly();                               // delegate sets only what you want to change
});

var app = builder.Build();
app.UseDBPilot();
app.Run();

• Zero wiring — referenced engine packages register themselves (output-directory scan); instances route by their engine column. Explicit registration (AddDbpilotSqlServer()) mixes in and is required for single-file publishes.
• Package graph: DBPilot.AspNetCore → Core → Storage → Common; engine packages depend on Core + Storage.
• Frontend assets ship twice — buildTransitive targets copy into wwwroot, plus an embedded-manifest fallback so the UI works even with an empty wwwroot.
• Fine-grained methods (AddDbpilotWeb / AddDbpilotMcp / AddDbpilotQuartz / ...) remain available.

FAQ

Symptom	Fix
Startup warning 平台库结构初始化失败 (schema init failed)	check DBPilot:ConnectionString and DB reachability; ignorable without persistence
Home page 404 / stale UI	frontend not built — cd web && npm install && npm run build, rebuild, restart
Startup error "DBPilot 主密钥未配置" (master key missing)	set DBPilot:Auth:Secret or env DBPILOT_MASTER_KEY
Collector error The computed authentication tag did not match...	master key doesn't match the stored credential ciphertext (changed after instances were registered) — re-enter each instance's password in the UI
Performance insight empty	instance enabled and collecting? Insights need ~1 minute of samples
Deadlock / slow SQL events not showing yet	event files buffer ~1 minute — wait and refresh

License

Apache-2.0