zavahospital

name	zavahospital
description	ZavaHospital – AI-powered inpatient clinical assistant using SQL Server 2025 vector search + NVIDIA NIM on AKS (GPU). Use when: deploying zavahospital build scripts, building encounter/doctor-note embeddings, running vector search or clinical recommendation procs, presenting the on-stage demo, or working in sqlserver2025/zavahospital/.

ZavaHospital – SQL Server 2025 + NVIDIA NIM on AKS

Single skill covering build (deploy the database) and demo (present on stage). Everything lives under sqlserver2025/zavahospital/:

• build/sql/ — numbered T-SQL deploy scripts (00_prereqs.sql … 14_verify.sql)
• build/nvidianim/ — NIM-on-AKS deployment (must stand up before any SQL) — see its README.md
• demo/ — teaching-ordered walkthrough scripts + Run-LiveDemo.ps1
• resources/ — architecture slides + UI demo HTML/JS/CSS

Architecture

• SQL Server 2025 on localhost, Windows auth, database zavahospital
• NVIDIA NIM embeddings: nvidia/nv-embedqa-e5-v5-query (1024 dims, T4 GPU on AKS)
• NVIDIA NIM chat: meta/llama-3.2-3b-instruct (T4 GPU on AKS)
• AKS cluster: aks-nvidianim in rg-nvidianim-westus2, 2x NC4as_T4_v3 GPU nodes + 2x DS2_v2 system nodes (two system nodes are required so the NGINX ingress controller fits alongside the platform add-ons — one 2-vCPU node leaves the controller Pending)
• TLS: Self-signed cert (CN=nim-aks.local) via AKS Web App Routing (managed NGINX ingress)
• Hostname: nim-aks.local → <aks-ingress-ip> via Windows hosts file
• Content-Type fix: NGINX annotation proxy_set_header Content-Type "application/json"

Flow

SQL Server 2025 → HTTPS → AKS Ingress (nim-aks.local) → NIM pods

• Embeddings: AI_GENERATE_EMBEDDINGS(@prompt USE MODEL NIMEmbeddingModel) → 1024-dim vector
• Chat: sp_invoke_external_rest_endpoint → https://nim-aks.local/v1/chat/completions
• Vector search: VECTOR_SEARCH() with DiskANN index (cosine metric)
• Recommendation: vector search → gather vitals/symptoms/allergies/orders → NIM chat → structured JSON

Critical Facts

• PREVIEW_FEATURES = ON is REQUIRED — 01_database.sql sets ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;, mandatory for CREATE VECTOR INDEX and VECTOR_SEARCH() in SQL Server 2025
• Embedding model name MUST use -query suffix: nvidia/nv-embedqa-e5-v5-query. The -query suffix bakes in input_type=query server-side — AI_GENERATE_EMBEDDINGS cannot send input_type.
• Vector dimensions are 1024 (not 1536)
• sp_invoke_external_rest_endpoint @timeout max is 230 seconds (product limit; default 30) — this demo calls NIM with a shorter @timeout, so keep prompts concise
• NIM rejects application/json;charset=utf-8 — the NGINX annotation strips the charset suffix
• Chat model (llama-3.2-3b) is small — system prompt MUST include grounding facts for quality answers
• 00_prereqs.sql (enables external rest endpoint) and 01_database.sql (creates the database) run against master. All other scripts run against zavahospital.
• Scripts 07_genembeddings.sql and 11_embeddingtable.sql call NIM per row — slower than the rest
• Post-filter compensation: VECTOR_SEARCH() applies JOINs/WHERE after the ANN scan, so procs over-fetch 3x

Database Design

• Schemas: ref (reference), core (patients/encounters), clinical (vitals/notes/orders/alerts), sec (RLS)
• Ledger: clinical.DoctorNotes uses LEDGER = ON (APPEND_ONLY = ON) for audit immutability
• RLS: Row-Level Security predicates scope data by building/ward via SESSION_CONTEXT
• ADR: ACCELERATED_DATABASE_RECOVERY = ON
• Optimized Locking: OPTIMIZED_LOCKING = ON
• Data compression: clinical.VitalsSnapshots uses DATA_COMPRESSION = ROW

Stored Procedures

Proc	Purpose
clinical.usp_GetCurrentPatientVitals	Latest vitals per patient (open encounters)
clinical.usp_GetPatientSymptoms	Symptoms for patient/encounter
clinical.usp_GetDoctorNotes	Doctor notes with filtering
clinical.usp_CreateOrder	Create clinical order
clinical.usp_findsimilarcases	Vector search for similar encounters (3x over-fetch)
clinical.usp_clinical_recommendation	RAG: vector search + NIM chat → structured JSON recommendation
clinical.usp_search_doctor_notes	Vector search on individual doctor notes

Embedding Tables

Table	Granularity	Source
clinical.EncounterVectors	One vector per encounter	Composite narrative (reason + symptoms + orders + vitals + notes + ward)
clinical.DoctorNotesEmbeddings	One vector per doctor note	Individual NoteText from clinical.DoctorNotes

NIM on AKS (stand up FIRST, before any SQL)

Full deployment scripts + detail live in build/nvidianim/ (see that folder's README.md). One-shot: Setup-NimOnAks.ps1 -Subscription <guid> ($env:NGC_API_KEY set) chains steps 1-4, waiting on kubectl rollout status for both pods before switching hostname; -Test verifies endpoints, -SkipClusterCreate reuses a cluster. It runs no SQL. Individual steps:

1. create-aks-cluster.ps1 — AKS + GPU node pool (2x NC4as_T4_v3), NVIDIA device plugin, kubectl
2. deploy-nim.ps1 — NIM chat + embedding containers + ingress (requires $env:NGC_API_KEY)
3. switch-hostname.ps1 -NewHostname nim-aks.local — TLS cert, K8s secrets, ingress, hosts file, Windows Trusted Root (infra only — runs no SQL; the build scripts own the model URL)
4. test-embedding.ps1, test-chat.ps1 — verify both endpoints respond

• Additional demo machine (AKS already exists): do NOT re-run Setup-NimOnAks.ps1/switch-hostname.ps1 (they regenerate the TLS cert and break the first machine). Attach with Join-NimClient.ps1 -Subscription <guid> -RestartSql — auto-detects the ingress IP and live-pulls the served cert from the endpoint (no saved state / cert file); add -IngressIP <ip> if this machine has no kubectl/az context. Only sets hosts entry + Trusted Root cert + SQL restart, no cluster changes. Add -Test to verify both endpoints over https://nim-aks.local (no kubectl needed). Undo on that machine with Remove-NimClient.ps1.
• Permissions to run Join (colleague): local Administrator on their machine is mandatory (hosts/cert/SQL-service via UAC); no SQL permission for Join itself. Azure RBAC needed only for -Subscription ingress-IP auto-detect: cluster uses local K8s accounts (no Entra/AAD), so the single role is Azure Kubernetes Service Cluster User Role at the cluster scope — grant via Grant-NimClientAccess.ps1 -PrincipalId <upn-or-oid> -Subscription <guid>. Skip all Azure access by passing -IngressIP <ip> (then only local admin + outbound TCP 443).
• NGC API key required ($env:NGC_API_KEY); images from nvcr.io/nim/
• Llama 3.1 8B OOMs on T4 (16GB); Llama 3.2 3B fits but needs grounded system prompt
• Same containers + K8s manifests deploy on Azure Local for the edge story (demo uses AKS in Azure to avoid standing up an Azure Local machine)
• New clusters self-patch host-kernel CVEs via --node-os-upgrade-channel NodeImage (set in create-aks-cluster.ps1)
• Teardown: cleanup.ps1 deletes the cluster/resource group; Remove-NimClient.ps1 reverses the client join per machine (both needed for a from-scratch test)
• From-scratch client test (same machine, no cluster teardown): run from build/nvidianim, each script self-elevates (UAC). If kubectl isn't on PATH first run $env:PATH += ";$HOME\.azure-kubectl;$HOME\.azure-kubelogin" (or pass -IngressIP <ip>). Then: .\Remove-NimClient.ps1 -RestartSql → .\Join-NimClient.ps1 -RestartSql -Test. A passing (embeddings 1024-dim + chat over ) proves hosts mapping + freshly imported cert are trusted.

Script Execution Order (build)

All deploy scripts are in build/sql/. Run in order, stopping on the first error:

$scriptDir = 'c:\sqlaiinaction\sqlserver2025\zavahospital\build\sql'

# -I sets QUOTED_IDENTIFIER ON (sqlcmd defaults OFF) — required for CREATE VECTOR INDEX.
# Against master (server config + create database + db-scoped config):
sqlcmd -S localhost -d master       -E -I -i "$scriptDir\00_prereqs.sql"
sqlcmd -S localhost -d master       -E -I -i "$scriptDir\01_database.sql"

# Against zavahospital (all remaining):
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\02_schema.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\03_seeding.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\04_procs.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\05_ai_model.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\06_embedding_table.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\07_genembeddings.sql"          # calls NIM
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\08_create_vector_index.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\09_find_similar_cases.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\10_clinical_recommendation.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\11_embeddingtable.sql"         # calls NIM
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\12_create_notes_vector_index.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\13_search_doctor_notes.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\14_verify.sql"                  # read-only gate

14_verify.sql is the final read-only gate — it asserts every object/seed set and RAISERRORs (sev 16) so -b stops on failure. The live "call the proc" demos live in demo/03 and demo/05.

Prerequisites (before running SQL)

1. NIM on AKS deployed and pods running (see build/nvidianim/README.md)
2. Hosts file: <aks-ingress-ip> nim-aks.local (get the IP from kubectl get ingress -n nim)
3. NIM TLS cert imported into Local Machine Trusted Root CAs
4. SQL Server restarted after cert import
5. sp_invoke_external_rest_endpoint enabled (sp_configure)
6. Verify NIM endpoints respond: .\test-embedding.ps1, .\test-chat.ps1 in build/nvidianim/

Demo Walkthrough (on-stage)

The demo/ folder is a teaching-ordered view of what build/ already deployed — not a second deployment. Presentation assets are in resources/.

• SSMS: set the database dropdown to zavahospital before running any demo script — the demo scripts carry no USE zavahospital; (context comes from the dropdown, or -d zavahospital via Run-LiveDemo.ps1/sqlcmd).

1. resources/architecture-slide.html/.png — opening slide (AMD / HPE sponsor variants alongside)
2. demo/01_clinical_recommendation_building_blocks.sql — narrate building blocks — do not execute
3. demo/02_clinical_recommendation_proc.sql — walk RAG proc body — do not execute (CREATE OR ALTER copy of build/sql/10)
4. demo/03_call_clinical_recommendation.sql — EXECUTE LIVE — RAG recommendation via NIM chat
5. demo/04_doctor_notes_vector_search.sql — narrate note embedding/search — do not execute (copy of build/sql/11-13)
6. demo/05_call_search_doctor_notes.sql — EXECUTE LIVE — vector search over doctor notes
7. demo/ssms_copilot_prompt.md — closer via SSMS GitHub Copilot

• All procs/indexes are created by build/, not demo/. demo/01/04 are narration; demo/02 is a CREATE OR ALTER teaching copy of build/sql/10_clinical_recommendation.sql that would OVERWRITE the deployed proc. Only demo/03 and demo/05 execute — both pure EXEC.
• Auto-run the live scripts: demo/Run-LiveDemo.ps1 runs only demo/03 + demo/05 in order (-Pause for stage pacing).
• Regenerate architecture-slide.png with the html-slide-to-png skill (Edge classic headless).

UI Demos (optional visual closers)

Self-contained static HTML in resources/ — open in a browser, no server/DB. They fake live data client-side:

• resources/digital-chart.html — bedside digital patient chart
• resources/clinical-search-app.html — clinical similarity search UI

Troubleshooting

• AI_GENERATE_EMBEDDINGS TLS error → import build\nvidianim\k8s\tls.cer to Trusted Root CAs, restart SQL
• Vector search returns fewer rows than expected → increase @SearchTopN (procs already over-fetch 3x)
• nim-aks.local unreachable → check hosts file, AKS ingress IP, pods running; build\nvidianim\switch-hostname.ps1
• EXTERNAL MODEL fails → use API_FORMAT = 'OpenAI', URL must end in /v1/embeddings
• NIM pod OOM → use llama-3.2-3b (not 8B) for chat on T4
• Chat hallucinations → add grounding facts to system prompt (3B model needs explicit context)
• 03_seeding.sql "already seeded" → script has guard clause; drop+recreate via 02_schema.sql first
• Scripts 08/12 slow → these call NIM per row; normal for initial population

Demo Narrative

Edge AI pattern: hospital runs SQL Server 2025 + NIM on Azure Local in their data center. Patient data never leaves the building — zero cloud API calls. Same code, same containers, same T-SQL works in cloud AKS or on-premises Azure Local.