build-sqlmcpserver-demo

name

build-sqlmcpserver-demo

description

Build / provision the LOCAL demo environment for the "SQL MCP Server: Bringing AI Agents to Your SQL Data" (Adventure Works scenario) on this machine, so the demos will run. USE WHEN the presenter says any of: "build the sqlmcpserver demo environment on this machine", "set up the SQL MCP Server demo kit", "build the SQL MCP Server demo environment", "provision the AdventureWorks demo on this laptop", "get the SQL MCP demo ready on this box", or moves the talk to a new machine and needs AdventureWorks + the view + a validated DAB config in place. Scope is DOWNLOAD + RESTORE + SCHEMA/VIEW + VALIDATE CONFIG + VERIFY + check MCP registration ONLY. Do NOT use to run/rehearse the demos (that's run-sqlmcpserver-demos) or to author slides.

Build the SQL MCP Server demo environment

Stand up everything the SQL MCP Server demos need on this machine, then verify. Everything is local — a SQL Server 2025 instance on localhost with Windows integrated auth, the stock AdventureWorks database, and Data API builder (DAB) as the SQL MCP Server. No cloud egress.

All paths are workspace-relative to the bobsql root. Kit: sqlmcpserver/build/. Run .ps1 scripts synchronously (isBackground:false, timeout:0); check exit codes; one command per call.

Prereqs to check first (do NOT try to install these — stop and tell the presenter if missing)

• SQL Server 2025 local instance reachable on localhost via Windows auth (current login = sysadmin). No container, no sa.
• sqlcmd on PATH (SqlServer command-line tools).
• .NET SDK (for the DAB global tool).
• PowerShell 7+ (scripts are #requires -Version 7.0).

Quick gate:

sqlcmd -S localhost -E -C -Q "SELECT @@VERSION;"     # instance reachable via Windows auth?
Get-Command sqlcmd, dotnet, pwsh -ErrorAction SilentlyContinue

Build procedure — one command

& sqlmcpserver/build/setup.ps1

setup.ps1 is the one-shot build. It:

1. Checks host prereqs (sqlcmd, dotnet, pwsh; SQL reachable via Windows auth) and aborts if missing.
2. Downloads AdventureWorks2022.bak (git-ignored; skipped if already present).
3. Restores it as [AdventureWorks] (Windows auth).
4. Deploys the mcp schema + mcp.vProductComponents view.
5. Ensures the DAB CLI and runs dab validate on build/dab/dab-config.json.

(The individual scripts — download-adventureworks.ps1, restore-adventureworks.ps1, sql/01-mcp-views.sql, build.ps1 -NoStart — still exist if you need to run a single step.)

Verify — one command

& sqlmcpserver/build/verify-preflight.ps1

Read-only. Asserts: DB up, Production.Product = 504, mcp.vProductComponents for Touring-1000 = 14, dab --version ≥ 2.0.9, dab validate passes, ≤ 1 DAB process. Exit 0 = all green — that is the definition of "built."

MCP surface + registration (HTTP)

The MCP server is now HTTP on :5001, not stdio. .vscode/mcp.json (workspace root) registers Adventure Works (SQL MCP) → http://localhost:5001/mcp. The kit is location-independent (scripts resolve paths via $PSScriptRoot; the config uses localhost) — nothing to edit when the repo path changes. REST (/api), GraphQL (/graphql), and MCP (/mcp) all come from the one :5001 process.

Going live is the run skill's job, not this one; for reference the go-live command is:

& sqlmcpserver/build/start-mcp-http.ps1   # starts DAB HTTP :5001, proves the MCP handshake → GREEN

Optional (only if the demo has been built)

• Demo 4 (add-a-tool) proc: if build/sql/ gains the BOM proc (uspGetProductBOM), deploy it with sqlcmd ... -i the same way as the view, then re-run verify-preflight.ps1. As of last edit this is pending a design decision — skip unless present.

Do NOT

• Install SQL Server, .NET, or sqlcmd — setup.ps1 checks and reports; it does not provision the host.
• Go live (start-mcp-http.ps1) or drive the demos here — that's run-sqlmcpserver-demos. This skill leaves a validated, verified environment.
• Author slides.

Done =

setup.ps1 completed and verify-preflight.ps1 is all green (504 products, view = 14 for Touring-1000, dab validate passes, DAB ≥ 2.0.9). Hand off to run-sqlmcpserver-demos (which runs start-mcp-http.ps1 and drives the beats).