| name | zavahospital |
| description | ZavaHospital – AI-powered inpatient clinical assistant using SQL Server 2025 vector search + NVIDIA NIM on AKS. Use when: deploying zavahospital scripts, building encounter embeddings, running vector search procs, generating clinical recommendations via NIM chat, creating doctor notes search, working in zavahealth/sqlserver2025/zavahospital/. |
ZavaHospital – SQL Server 2025 + NVIDIA NIM on AKS
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
- TLS: Self-signed cert (CN=nim-aks.local) via AKS Web App Routing (managed NGINX ingress)
- Hostname:
nim-aks.local → <aks-ingress-ip> via 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)
Critical Facts
- PREVIEW_FEATURES = ON is REQUIRED —
01_schema.sql sets ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON; which is 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 the input_type=query parameter server-side — AI_GENERATE_EMBEDDINGS cannot send input_type.
- Vector dimensions are 1024 (not 1536)
sp_invoke_external_rest_endpoint max timeout is 120 seconds- 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
01_schema.sql runs against master (creates the database). All other scripts run against zavahospital.- Script 08 and 13 call NIM to generate embeddings — they take longer than other scripts
- 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 |
Script Execution Order
# Against master (creates database):
sqlcmd -S localhost -d master -E -i 01_schema.sql
# Against zavahospital (all remaining):
sqlcmd -S localhost -d zavahospital -E -i 02_seeding.sql
sqlcmd -S localhost -d zavahospital -E -i 03_procs.sql
sqlcmd -S localhost -d zavahospital -E -i 05_dbcreds.sql -v MasterKeyPassword="<your-strong-password>"
sqlcmd -S localhost -d zavahospital -E -i 06_ai_model.sql
sqlcmd -S localhost -d zavahospital -E -i 07_embedding_table.sql
sqlcmd -S localhost -d zavahospital -E -i 08_genembeddings.sql # calls NIM
sqlcmd -S localhost -d zavahospital -E -i 09_create_vector_index.sql
sqlcmd -S localhost -d zavahospital -E -i 10_find_similar_cases.sql
sqlcmd -S localhost -d zavahospital -E -i 11_clinical_recommendation.sql
sqlcmd -S localhost -d zavahospital -E -i 13_embeddingtable.sql # calls NIM
sqlcmd -S localhost -d zavahospital -E -i 14_create_notes_vector_index.sql
sqlcmd -S localhost -d zavahospital -E -i 15_search_doctor_notes.sql
Demo Scripts (run after deployment)
sqlcmd -S localhost -d zavahospital -E -i 04_call_procs.sql
sqlcmd -S localhost -d zavahospital -E -i 12_call_recommendation.sql
sqlcmd -S localhost -d zavahospital -E -i 12_vector_search_exmple.sql
sqlcmd -S localhost -d zavahospital -E -i 16_call_search_doctor_notes.sql
Prerequisites (before running scripts)
- NIM on AKS deployed and pods running (see nvidianim/SKILL.md)
- Hosts file:
<aks-ingress-ip> nim-aks.local (get the IP from kubectl get ingress -n nim)
- NIM TLS cert imported into Local Machine Trusted Root CAs
- SQL Server restarted after cert import
sp_invoke_external_rest_endpoint enabled (sp_configure)- Verify NIM endpoints respond:
.\test-embedding.ps1, .\test-chat.ps1 in nvidianim/
Troubleshooting
AI_GENERATE_EMBEDDINGS TLS error → import 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- 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)
02_seeding.sql "already seeded" → script has guard clause; drop+recreate via 01_schema.sql first- Script 08/13 slow → these call NIM for each 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.
