hyperscaleai

HyperscaleAI — Azure SQL Hyperscale: vector search + AI loan scoring

ZavaFin's lending database (ZavaLendingDB, aka zavalending) runs on Azure SQL Hyperscale. This demo adds two AI capabilities natively inside the database — no data leaves the boundary:

1. Narrative Search — semantic vector search over loan narratives (DiskANN, cosine).
2. AI Loan Scoring — score a pending application against similar historical loans, with a Phi-4 risk narrative, written to an auditable ledger.

Everything lives under hyperscale/:

• build/sql/basedeploy/ — provision the Hyperscale DB + load base schema/seed (this makes the demo self-contained; originally the DB came from an "Act 1 migration")
• build/sql/ — numbered AI deploy scripts (00–04)
• build/loan-scoring-agent/, build/APIM/ — optional Part 2 (Foundry agent) + optional AI gateway
• demo/ — on-stage runner (Run-LiveDemo.ps1) + teaching-ordered T-SQL (sql/)
• loan-platform-internal/ — internal ops console (open index.html)

Two common requests

• "build demo" / provision → stand up the DB and deploy all objects: basedeploy → build SQL 00–04. See Build the demo below.
• "run demo live" → execute the 4 live T-SQL steps in order via demo/Run-LiveDemo.ps1. See Run the demo live below.

Architecture

• Azure SQL Hyperscale, server zavafinsqlserver.database.windows.net, database zavalending
• Analytics named replica = a database zavalending_NamedReplica on the same server (optional; used for read isolation in demo step 2 — falls back to the primary)
• Embeddings: text-embedding-3-large (3072-dim, float16) via CREATE EXTERNAL MODEL + AI_GENERATE_EMBEDDINGS
• Chat/scoring: Phi-4 via sp_invoke_external_rest_endpoint (direct to Azure AI by default; @UseGateway=1 routes through the optional APIM gateway)
• Index: DiskANN CREATE VECTOR INDEX (cosine) — V3 updateable (DML-friendly)
• Audit: LoanDecisionAudit ledger table (LEDGER = ON (APPEND_ONLY = ON)) + HASHBYTES

Flow

NL prompt → AI_GENERATE_EMBEDDINGS → VECTOR_SEARCH (DiskANN, replica) → JSON of similar loans
          → sp_invoke_external_rest_endpoint (Phi-4) → risk narrative → INSERT decision + audit hash

Critical facts (verified live 2026-07-18)

• Tooling: go-sqlcmd is REQUIRED. The classic ODBC sqlcmd does NOT support --authentication-method. Install go-sqlcmd: winget install sqlcmd (Microsoft.Sqlcmd → C:\Program Files\sqlcmd\sqlcmd.exe). The PowerShell runners auto-detect it (Resolve-GoSqlcmd).
• Entra auth (default): use --authentication-method=ActiveDirectoryDefault and DO NOT also pass -G — they are mutually exclusive in go-sqlcmd. ActiveDirectoryDefault reuses your az login token silently (you must be the server's Entra admin; Provision-Hyperscale.ps1 sets that automatically).
• No -f 65001 — that's ODBC-only. go-sqlcmd reads UTF-8 by default. Valid flags: -N -I -b -l 30 -i -S -d -U -P.
• sp_invoke_external_rest_endpoint @timeout max is 230s (product limit; default 30). The demo calls Phi-4 with a lower @timeout, so a Msg 31629 … exceeded the timeout value on step 4 most often means the Phi-4 deployment capacity is too low (capacity 1 ≈ 1K TPM throttles the multi-thousand-token scoring payload → 429 → retries → timeout). Deploy Phi-4 at --sku-capacity 50+ (GlobalStandard). A killed terminal or cold start can also cause it — re-run.
• Placeholder secrets must be filled before build (build/sql/01-embeddings-setup.sql): master-key password, credential api-key, and the Azure AI endpoint URL (also in 04-loan-scoring.sql). They ship as <your-...> placeholders.
• Requires an Azure AI resource with two deployments: text-embedding-3-large + Phi-4. Nothing provisions this — it's a manual Azure prerequisite.
• DiskANN needs ≥ 100 vectors — the seed narrates exactly LoanId 1–100 (zero headroom).
• Pending demo application is ApplicationId 1 (Art Vandelay, SmallBusiness $150k) — seeded by basedeploy, reset by demo/sql/00-reset.sql.
• Legacy VECTOR_SEARCH(... TOP_N=…) is gone — the V3 index rejects it (Msg 42274); the demo uses TOP (N) WITH APPROXIMATE only.

Build the demo (from nothing to demo-ready)

Base deploy scripts are in build/sql/basedeploy/. Run az login first.

$bd = 'c:\sqlaiinaction\hyperscale\build\sql\basedeploy'
$srv = 'zavafinsqlserver.database.windows.net'
$adAuth = '--authentication-method=ActiveDirectoryDefault'

# 1. Provision the Hyperscale server + DB (idempotent; sets you as Entra admin; -CreateNamedReplica optional)
& "$bd\Provision-Hyperscale.ps1" -ResourceGroup zavafinrg -Server zavafinsqlserver -CreateNamedReplica

# 2. Base schema + seed (4 tables + ledger; 1,000 applicants/loans; Vandelay = ApplicationId 1)
& "$bd\Deploy-Base.ps1" -Server $srv -Database zavalending

# 3. Deploy the AI objects in order (go-sqlcmd; Entra by default). FILL the placeholder
#    secrets in 01-embeddings-setup.sql first.
$sql = 'c:\sqlaiinaction\hyperscale\build\sql'
sqlcmd -S $srv -d zavalending $adAuth -N -I -b -i "$sql\00-add-loan-narratives.sql"
sqlcmd -S $srv -d zavalending $adAuth -N -I -b -i "$sql\01-embeddings-setup.sql"
sqlcmd -S $srv -d zavalending $adAuth -N -I -b -i "$sql\02-vector-index.sql"
sqlcmd -S $srv -d zavalending $adAuth -N -I -b -i "$sql\03-hybrid-search-procedure.sql"
sqlcmd -S $srv -d zavalending $adAuth -N -I -b -i "$sql\04-loan-scoring.sql"

Build order: 00 narratives + full-text → 01 external model + embeddings → 02 DiskANN index → 03 usp_HybridLoanSearch (Narrative Search) → 04 usp_ScoreLoanApplication + LoanDecisionAudit (AI Loan Scoring).

Part 2 (optional) — expose usp_ScoreLoanApplication as an MCP tool via DAB + a Foundry agent: build/loan-scoring-agent/, driven per demo/loan-scoring-agent/foundry-agent-setup.md.

Run the demo live

The demo/ folder executes already-deployed objects (it does NOT build anything). Drive all four steps with the runner:

$demo = 'c:\sqlaiinaction\hyperscale\demo'

# Entra ID via your az login; step 2 on the named replica
& "$demo\Run-LiveDemo.ps1" -ReplicaDatabase zavalending_NamedReplica

# On-stage pacing (Enter before each step)
& "$demo\Run-LiveDemo.ps1" -ReplicaDatabase zavalending_NamedReplica -Pause

The 4 live steps (demo/sql/), in order:

#	Script	Target	Shows
1	00-reset.sql	primary	reset demo state (rows >100, ApplicationId 1 → Pending)
2	03-vector-search-replica.sql	replica (or primary)	Narrative Search (ranked similar loans)
3	04-dml-insert-search.sql	primary	insert 3 loans, instantly searchable (no rebuild)
4	07-execute-scoring.sql	primary	AI Loan Scoring → Phi-4 → auditable decision

Runner params: -Server (default zavafinsqlserver.database.windows.net), -Database (default zavalending), -ReplicaDatabase (step 2 target; else primary), -ReplicaServer (only if the replica is on a different server), -AuthMethod {ActiveDirectoryDefault(default) | ActiveDirectoryInteractive | ActiveDirectoryDeviceCode | SqlPassword}, -SqlUser/-SqlPassword (SqlPassword only), -Pause.

Expected step-4 result: ApplicationId 1 (Art Vandelay, SmallBusiness $150k) → Approved, RiskScore ~15 (Low), 10 similar loans, model Phi-4, a few seconds.

Teardown

build/sql/basedeploy/Teardown-Hyperscale.ps1 — confirmation-gated. Default deletes the DB + named replica; -RemoveServer / -RemoveResourceGroup widen scope; -Force skips the prompt.

& 'c:\sqlaiinaction\hyperscale\build\sql\basedeploy\Teardown-Hyperscale.ps1' -ResourceGroup zavafinrg -Server zavafinsqlserver

Troubleshooting

• Sqlcmd: '…': Unknown Option on -f/--authentication-method → you hit ODBC sqlcmd. Install go-sqlcmd (winget install sqlcmd); the runners auto-detect it.
• The -G and the --authentication-method options are mutually exclusive → drop -G; keep only --authentication-method=….
• Msg 31629 … exceeded the timeout value (step 4) → Phi-4 deployment capacity too low (capacity 1 ≈ 1K TPM throttles). Raise it: az cognitiveservices account deployment create -g <rg> -n <account> --deployment-name Phi-4 --model-name Phi-4 --model-version 7 --model-format Microsoft --sku-name GlobalStandard --sku-capacity 50. A killed terminal / cold start can also cause it — re-run.
• Login/auth failures → confirm az login and that you're the server's Entra admin (Provision-Hyperscale.ps1 sets it; or az sql server ad-admin create).
• Msg 42274 … does not support explicit TOP_N → old legacy syntax on a V3 index; use TOP (N) WITH APPROXIMATE (the current procs already do).

Related

• Full narrative + folder map: hyperscale/README.md
• Build detail + prerequisites: hyperscale/build/README.md
• Base deploy + provisioning: hyperscale/build/sql/basedeploy/README.md
• Live demo steps: hyperscale/demo/README.md