run-sqlmcpserver-demos

name

run-sqlmcpserver-demos

description

Drive the live demos for the "SQL MCP Server: Bringing AI Agents to Your SQL Data" (Adventure Works scenario, local SQL Server 2025 + Data API builder + VS Code Copilot agent). USE WHEN the presenter says any of: "let's start the SQL MCP Server demos", "let's start the SQL MCP demos", "start the SQL MCP Server demos", "let's do the demos", "let's run through the demos", "run the SQL MCP Server demos", "run the SQL MCP demos", "rehearse the SQL MCP talk", "practice the Adventure Works demo", "let's do the cold open", "run pre-flight", "let's run pre-flight", "pre-flight the SQL MCP demo", "check the demo is ready", or wants step-by-step help executing the AdventureWorks SQL MCP Server demos (pre-flight checks, starting the right MCP server, the Touring-1000 cold open, the REST/GraphQL three-surfaces beat). Scope is RUN/REHEARSE the SQL MCP Server demos ONLY. Do NOT use to build the database/kit (that's the build scripts) or to author slides.

Run the SQL MCP Server demos

Help the presenter execute the SQL MCP Server demos, one beat at a time, doing the right steps in the right order. The single source of truth for the steps is the runbook: sqlmcpserver/demo-runbook.md — read it first, then drive from it. The narrative/timing lives in sqlmcpserver/outline.md.

All paths are workspace-relative to the bobsql root. Kit: sqlmcpserver/build/. Everything is local — no cloud egress.

How to help

1. Read demo-runbook.md first every time — it has the current build state table (which beats are wired vs. TBD) and the exact commands/prompts. Do not invent steps that aren't there.
2. Always run pre-flight before the first demo (runbook "Pre-flight"):
   • Standard pre-flight (restores Demo 2 config + goes live): build/reset-dab-config.ps1. It restores dab-config.json from the committed baseline (the pristine 12-warnings Demo 2 start state), validates it, then calls start-mcp-http.ps1 -Restart (full checks + start + MCP handshake). Run this whenever the demo run includes Demo 2 — Demo 2's live fix edits the config, so restoring the baseline first is what lets it be run again identically. (Idempotent: if the config is already pristine, the restore is a no-op copy.)
   • Lighter path (no config reset): build/start-mcp-http.ps1. Same checks + start + handshake, but does not touch dab-config.json. Use when you're only doing the cold open / not re-running Demo 2. On GO LIVE: GREEN, VS Code connects via .vscode/mcp.json (http://localhost:5001/mcp). Stop with -Stop.
   • Checks only (or for the stdio alternate): build/verify-preflight.ps1 — asserts DB reachable, 504 products, view returns 14 for Touring-1000, dab --version ≥ 2.0.9, dab validate, and that ≤ 1 DAB process is up.
   • Only if using the stdio surface: stop any OTHER SQL MCP server, then Start Adventure Works (SQL MCP) in MCP: List Servers (enabling ≠ starting), and warm up the first call (cold-start race — retry once). The HTTP path above avoids all of this.
3. Drive one beat at a time. State which demo you're on, show/run its steps, confirm the expected result, then ask before moving to the next.
4. Verify with tools when useful. You can call the AdventureWorks MCP tools directly (describe_entities, read_records) to prove a beat works before the presenter runs it in agent-mode chat. Load them with tool_search if deferred.
5. If the agent misroutes (returns tools/data you don't recognize): another MCP server is still connected — stop it and retry. This is the #1 gotcha.

The beats (see the runbook for exact steps)

• Demo 1 — Cold open: prompt "What parts make up the Touring-1000 bike?" → agent calls describe_entities then read_records (filter ProductModel eq 'Touring-1000') → 14 components, no SQL authored by the model.
• Demo 2 — One config, show/validate/query: (1) show & discuss dab-config.json (per-entity surface toggles — REST off on the view, GraphQL/MCP default on), (2) basic DAB commands — dab --version / --help / validate; validate prints "Config is valid" plus 12 "missing fields" MCP warnings; on request, add the fields live (recipe below), (3) a GraphQL productComponents query in Nitro → 14.
• Demo 3 — Under the hood: peel Demo 1 — server-description routing, describe_entities (no SQL), read_records → parameterized T-SQL. TWO panes: Chat Debug view (/debug → System prompt = server description, Tool responses = describe_entities/read_records payloads) + XE via SSMS Watch Live Data. Pre-flight the capture with build/start-xe-capture.ps1 (creates+starts dab_mcp_capture) BEFORE the prompt; describe_entities = no row, read_records = one rpc_completed with bound @param0. Teardown build/start-xe-capture.ps1 -Stop. (Agent Logs preview was thin — not used.) ✅ verified 2026-07-26.
• Demo 4 — Add a tool live: expose dbo.uspGetProductBOM as a custom-tool, restart, the agent discovers get_product_bom — the recursive BOM the view can't do (view = 14 top-level parts; tool = 87 rows, 4 levels). Deterministic payoff: build/reset-dab-config.ps1 -ApplyTool (applies dab/dab-config.tool.json, validates, restarts); reset with plain reset-dab-config.ps1. The proc INLINES the recursive CTE (a nested EXEC breaks describe_first_result_set and fails dab validate). ✅ built + verified 2026-07-26.
• Back pocket (not in the main run): RBAC role-flip, RLS by SalesPersonID, don't-expose , vector search (mention only).

Demo 2 live fix — "add the fields dab validate is warning about"

There is NO single dab command that auto-adds fields. Two ways to do the fix:

FAST PATH (recommended for the live talk) — pre-built, instant, deterministic: When the presenter asks (after dab validate shows the "missing 'fields'" warnings), run:

./sqlmcpserver/build/reset-dab-config.ps1 -ApplyFields

This drops in the pre-authored dab-config.fields.json (all 9 tables + 3 stored procs described), validates to 0 warnings, and restarts DAB — one move, ~seconds, identical every time. Then call describe_entities to show the model now sees the columns. Reset for the next run with ./reset-dab-config.ps1 (restores the 12-warnings baseline).

MANUAL PATH (only if you want to hand-author live to make a teaching point):

1. Note the warned entities — the 9 tables AND the 3 stored procs.
2. For each warned TABLE, get its columns (schema/table = the entity's source.object):

   sqlcmd -S localhost -E -C -W -d AdventureWorks -Q "SET NOCOUNT ON; SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='Sales' AND TABLE_NAME='SalesOrderHeader' ORDER BY ORDINAL_POSITION;"
   

   For each warned PROC, get its result-set columns (that is what a proc's fields describes — NOT its parameters):

   sqlcmd -S localhost -E -C -W -d AdventureWorks -Q "SET NOCOUNT ON; SELECT name FROM sys.dm_exec_describe_first_result_set(N'EXEC dbo.uspGetManagerEmployees @BusinessEntityID=2', NULL, 0) ORDER BY column_ordinal;"
   

3. Edit dab-config.json: add a fields array (sibling of source) to that entity — each column as { "name": "<col>", "description": "<short business description>" }. Valid JSON; don't touch the other entities.
   • Key fact (verified on DAB 2.0.9): the missing 'fields' warning is keyed purely on the presence of a top-level fields array. Adding proc parameters does NOT clear it — only a fields block does. A fields block on a proc is additive metadata; it does not project/restrict the result set (the proc still returns all columns).
4. Re-run dab validate → confirm warnings gone ("Config is valid", 0 MCP warnings).
5. ALWAYS restart so the change takes effect the same way every time: start-mcp-http.ps1 -Restart, then call describe_entities to show the model now sees the columns. (DAB does not hot-reload config — without -Restart it serves stale config. This step is mandatory, not optional, so the beat is identical on every machine.)
6. RESET after rehearsal: git checkout -- sqlmcpserver/build/dab/dab-config.json (returns to the 12-warnings baseline so the warnings come back next run).

Snappy is fine — the fast path adds all 12 entities in one move. Descriptions are short and useful (not one word); they're what the model reads via describe_entities.

Key facts / gotchas

• DB: local SQL Server 2025, localhost, Windows integrated auth (no container, no sa). AdventureWorks restored as [AdventureWorks].
• MCP registration: .vscode/mcp.json (workspace root) — Adventure Works (SQL MCP) is now HTTP on :5001 (started by start-mcp-http.ps1). REST /api, GraphQL /graphql, and MCP /mcp all come from the one :5001 process. A stdio setup still works as an alternate (runbook collapsible section); if you use it, the flag is --LogLevel (capital).
• Opener is a VIEW + read_records (built-in DML tool), deliberately — one question → one set-based query. It is NOT a custom-tool/stored-proc opener.
• After any config edit, use start-mcp-http.ps1 -Restart — the plain command reuses a running DAB and serves stale config (verified: it kept serving the old server description until -Restart). This matters most in Demo 4 (add-a-tool): without -Restart the new get_product_bom tool won't appear.
• DAB CLI stdio flag is --LogLevel (capital), not --loglevel.
• Do not start both MCP servers at once; the tools flyout can't tell them apart.

Do NOT

• Build/restore the database or edit dab-config.json schema here — that's the build kit's job (download-/restore-adventureworks.ps1, build.ps1, sql/01-mcp-views.sql).