# SQL AI in Action
Demos and scripts for the **SQL and AI for Developers in Action** keynote — showing how AI runs
*where your data lives*, on the **edge and on the ground**, with no cloud dependency
for inference.
The theme: modern SQL engines are now AI engines. Vector search, embeddings,
DiskANN indexing, JSON, ledger, and RAG (retrieval-augmented generation) run **inside
the database**, next to the operational data — with local or on-prem model serving, and
AI agents reasoning over live operational data through MCP.
## Repository layout
```
sqlaiinaction/
├── sqlserver2025/ # Edge / on-the-ground AI demos on SQL Server 2025
│ ├── zavacliniq/ # Outpatient clinic — SQL Server 2025 + Foundry Local (laptop)
│ └── zavahospital/ # Inpatient hospital — SQL Server 2025 + NVIDIA NIM on AKS
├── azuresqlcontainer/ # Azure SQL "From Database to Live Site" — container + AI agent in the loop
│ ├── local/ # run the whole demo on a laptop (Docker: SQL + Ollama + Caddy, .NET Aspire)
│ └── azure/ # guide (+ reference SQL) for moving the demo to Azure — Hyperscale, Azure OpenAI, optional APIM
├── hyperscale/ # Azure SQL Hyperscale — vector search + AI loan scoring at scale
│ ├── build/ # deploy the AI objects: basedeploy (schema+seed) → narratives → embeddings → vector index → procs
│ ├── demo/ # on-stage runner + T-SQL (Narrative Search, DML, AI scoring) and the Foundry agent
│ └── loan-platform-internal/ # internal ops console (Narrative Search + AI Loan Scoring pages)
├── tools/ # Shared utilities / tooling for the demos (placeholder — WIP)
├── .github/ # Custom Copilot agent + skill the azuresqlcontainer demo discovers
│ ├── agents/ # live-site-sql.agent.md
│ └── skills/ # live-site-sql/SKILL.md
└── .vscode/ # mcp.json — registers the zavalivesite-sql MCP server (DAB REST + MCP)
```
## Demos
### `sqlserver2025/zavacliniq` — ZavaClinIQ (outpatient, fully local)
An outpatient clinical assistant that generates patient care plans via RAG, running
**entirely on a laptop** with no cloud dependency.
- **Vector search:** DiskANN index, cosine metric, over top-K clinical evidence chunks
- **Model serving:** [Foundry Local](https://learn.microsoft.com/azure/ai-foundry/foundry-local/) on Windows
- Embeddings: `qwen3-embedding-0.6b` (1024 dims)
- Chat: `phi-4` (auto-selects best GPU variant)
- **Flow:** symptom prompt → vector search → prompt assembly (`JSON_OBJECT`) →
`sp_invoke_external_rest_endpoint` → local model → parse & persist to an append-only ledger
See [sqlserver2025/zavacliniq/readme.md](sqlserver2025/zavacliniq/readme.md) for the full
architecture, prerequisites, and setup scripts.
### `sqlserver2025/zavahospital` — ZavaHospital (inpatient, on-prem GPU)
An inpatient clinical assistant that retrieves similar historical cases and generates
recommendations, using **NVIDIA NIM on AKS** for GPU-accelerated model serving — patient
data stays inside the network.
- **Vector search:** DiskANN index, cosine metric, on the primary (no replica routing)
- **Model serving:** [NVIDIA NIM](https://www.nvidia.com/en-us/ai-data-science/products/nim-microservices/) on AKS (NVIDIA T4 GPUs)
- Embeddings: `nvidia/nv-embedqa-e5-v5-query` (1024 dims)
- Chat: `meta/llama-3.2-3b-instruct`
- **Flow:** NL prompt → `AI_GENERATE_EMBEDDINGS` → vector search → join encounters /
vitals / symptoms / orders / notes → `sp_invoke_external_rest_endpoint` → NIM chat →
structured care plan (JSON)
See [sqlserver2025/zavahospital/readme.md](sqlserver2025/zavahospital/readme.md) for the
full architecture, the NIM-on-AKS deployment, and setup scripts. The
[sqlserver2025/zavahospital/build/nvidianim/](sqlserver2025/zavahospital/build/nvidianim/) folder must be
stood up **before** any SQL is run.
### `azuresqlcontainer` — Azure SQL: From Database to Live Site, with AI Agents in the Loop
An end-to-end demo where Azure SQL (vector + JSON + ledger + AI) grounds an
incident-triage agent. One Docker container hosts SQL + Ollama + a Caddy TLS proxy; a
.NET Aspire AppHost wires up a Blazor WASM UI and a Data API Builder REST/MCP endpoint.
A Blazor page polls DAB REST every 2 s while a custom Copilot agent reaches the same
database over MCP to triage an incident and write a mitigation back — which the page then
renders live. Pulled from the [Microsoft Build 2026 session repo](https://github.com/microsoft/Build26-BRK223-from-rows-to-reasoning-designing-databases-for-ai-apps-and-agents).
- **One `INSERT`, five SQL Server 2025 features:** native `json` type, `REGEXP_MATCHES`,
`FOR JSON PATH`, `AI_GENERATE_EMBEDDINGS ... USE MODEL`, and the `vector(N)` type
- **Hybrid search:** one stored proc, two access paths — DiskANN `VECTOR_SEARCH` over
~150k weakly-tagged incidents + JSON-index seek + exact `vector_distance` over runbooks
- **Model serving:** [Ollama](https://ollama.com/) in-container (`mxbai-embed-large`
embeddings, `phi4` chat) behind a Caddy TLS proxy — or Azure OpenAI in the cloud variant
- **Agent in the loop:** a custom Copilot Chat agent ([.github/agents/live-site-sql.agent.md](.github/agents/live-site-sql.agent.md))
+ skill ([.github/skills/live-site-sql/SKILL.md](.github/skills/live-site-sql/SKILL.md))
reach the DB via the `zavalivesite-sql` MCP server ([.vscode/mcp.json](.vscode/mcp.json))
Two ways to run it: **[azuresqlcontainer/local/](azuresqlcontainer/local/)** (whole demo on a
laptop) or **[azuresqlcontainer/azure/](azuresqlcontainer/azure/)** (lift to Azure via Bicep).
See [azuresqlcontainer/README.md](azuresqlcontainer/README.md) for the T-SQL tour,
[azuresqlcontainer/PRESENTER-RUNBOOK.md](azuresqlcontainer/PRESENTER-RUNBOOK.md) for the on-stage walkthrough, and
[azuresqlcontainer/design.md](azuresqlcontainer/design.md) for the locked design spec.
### `hyperscale` — Azure SQL Hyperscale: vector search + AI scoring at scale
The `ZavaLendingDB` loan database isn't just big and fast — it's **intelligent**. Native
vector search in T-SQL finds similar historical loans across **12M rows**, Azure OpenAI
scores the risk, and every decision is auditable. The architectural point: **vector search
runs on an Analytics named replica**, not the primary — the primary handles writes and AI
scoring, so there's no contention.
- **Workload isolation on Hyperscale:** `VECTOR_DISTANCE`/`VECTOR_SEARCH` (DiskANN, cosine)
over 12M loans on an 80-vCore Analytics named replica; AI scoring + writes on the primary
- **FTS → vector:** full-text search fails on natural-language prompts; vector embeddings
succeed
- **AI scoring in T-SQL:** `sp_invoke_external_rest_endpoint` → Azure OpenAI generates a risk
narrative referencing the similar loans; decision stored with `HASHBYTES` tamper detection
- **Agent option:** a loan-scoring agent (DAB + MCP + Foundry) reasons over the same DB
Deploy the base database with [hyperscale/build/sql/basedeploy/](hyperscale/build/sql/basedeploy/)
(`Provision-Hyperscale.ps1` → `Deploy-Base.ps1`), run the numbered build scripts in
[hyperscale/build/](hyperscale/build/), then drive the demo with
[hyperscale/demo/Run-LiveDemo.ps1](hyperscale/demo/Run-LiveDemo.ps1). See
[hyperscale/README.md](hyperscale/README.md) for the full flow. **Prerequisite:** an Azure SQL
Hyperscale database (created by `basedeploy`); an Analytics named replica is optional — the
demo falls back to the primary.
### `tools` — shared utilities (placeholder)
Reserved for cross-demo tooling and helper utilities. **Empty / work-in-progress** —
content will land here as the keynote build-out progresses. See
[tools/README.md](tools/README.md).
## Common concepts
The demos share the same SQL Server 2025 / Azure SQL building blocks:
| Concept | What it does |
|---|---|
| **Vector data type + DiskANN index** | Native vector storage and approximate nearest-neighbor search inside the engine |
| **`AI_GENERATE_EMBEDDINGS` / `CREATE EXTERNAL MODEL`** | Generate embeddings from T-SQL against a registered model endpoint |
| **`sp_invoke_external_rest_endpoint`** | Call a chat/completions endpoint from T-SQL for RAG |
| **RAG in the database** | Retrieval (vector search) and generation (LLM call) orchestrated in T-SQL, next to the data |
| **Append-only ledger / audit trail** | Every AI output persisted with full context, model version, and raw response |
| **JSON type + JSON indexes / `REGEXP_MATCHES`** | Native JSON storage, indexed `$.path` seeks, and regex extraction in T-SQL (shown in `azuresqlcontainer`) |
| **Data API Builder + MCP** | REST *and* MCP endpoints generated from one config, so an agent can reason over live data (shown in `azuresqlcontainer`) |
What differs is *where the model runs and who calls it*: **Foundry Local** on a laptop
(`zavacliniq`), **NVIDIA NIM on AKS** GPUs on-prem (`zavahospital`), **in-container
Ollama** with a Copilot agent in the loop (`azuresqlcontainer`), and **Azure OpenAI** called
from the primary while vector search runs on a named replica (`hyperscale`).
## Prerequisites (all demos)
- **SQL Server 2025** or **Azure SQL** with `sp_invoke_external_rest_endpoint` enabled
- A model-serving endpoint reachable over HTTPS (Foundry Local + Caddy, NIM on AKS, or
in-container Ollama + Caddy)
- The serving endpoint's TLS certificate imported into the SQL Server trust store, and
the SQL Server service restarted afterward
Per-demo prerequisites and setup steps are in each demo's `readme.md`.
## External tools required
These demos depend on tooling that is **not bundled in this repo**. Have it installed
and on your `PATH` (or otherwise available) before running:
| Tool | Used by | Notes |
|---|---|---|
| **`sqlsim.exe`** | `sqlserver2025` + `hyperscale` demos | Batch runner for the `.sql` scripts. Resolve it from `PATH` (`Get-Command sqlsim.exe`) or set `$env:SQLSIM_PATH`. |
| **Foundry Local** | `zavacliniq` | Windows model host serving `qwen3-embedding-0.6b` and `phi-4`. See the [Foundry Local docs](https://learn.microsoft.com/azure/ai-foundry/foundry-local/). |
| **Caddy** + helper scripts | `zavacliniq` | HTTPS reverse proxy in front of Foundry Local (ports 8444/8445), plus `import_caddy_cert.ps1`, `start_caddy.ps1`, and `bootstrap_foundrylocal.py`. This proxy/cert setup lives outside this repo. |
| **Azure CLI + kubectl** | `zavahospital` | Provision AKS and deploy NIM. Deployment scripts are in [sqlserver2025/zavahospital/build/nvidianim/](sqlserver2025/zavahospital/build/nvidianim/). |
| **NGC API key** | `zavahospital` | Required to pull NVIDIA NIM container images. |
| **Docker Desktop + .NET 10 SDK + PowerShell 7** | `azuresqlcontainer` | Runs the SQL container, the .NET Aspire AppHost, and the build scripts. Cold build ~12–15 min. |
| **VS Code + GitHub Copilot** | `azuresqlcontainer` | Discovers the custom agent/skill in `.github/` and the `zavalivesite-sql` MCP server in `.vscode/mcp.json`. |
| **Azure CLI** + an Azure SQL Hyperscale DB | `hyperscale` | `az login` supplies the access token the scripts pass to `sqlsim -T`. The demo assumes a Hyperscale database with an Analytics named replica already exists. Azure OpenAI (or the APIM gateway) provides scoring. |
> **Secrets:** the `hyperscale` scripts were sanitized on import — API keys, subscription
> keys, and the subscription ID were replaced with placeholders like `<AZURE_OPENAI_API_KEY>`,
> `<APIM_SUBSCRIPTION_KEY>`, `<MASTER_KEY_PASSWORD>`, and `<SUBSCRIPTION_ID>`. Fill these in
> (or generate them via the setup scripts) before running.
## Status
Iterating from here. This README and the folder layout are the starting point for the
keynote build-out.
## License
This project is licensed under the [MIT License](LICENSE).
## Trademarks
This project may contain trademarks or logos for projects, products, or services.
Authorized use of Microsoft trademarks or logos is subject to and must follow
[Microsoft's Trademark & Brand Guidelines](https://www.microsoft.com/legal/intellectualproperty/trademarks/usage/general).
Use of Microsoft trademarks or logos in modified versions of this project must not cause
confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are
subject to those third-parties' policies.