azuresql-db-rag

---
name: azuresql-db-rag
description: >-
license: MIT
---

# Local vector search and RAG on the Azure SQL Database container

Store embeddings and run similarity search directly in the Azure SQL Database
engine using the native `VECTOR(n)` type and `VECTOR_DISTANCE`. No separate
vector store needed.

## Identity (read this first)

This targets the **Azure SQL Database engine** running locally in a container,
NOT the SQL Server image. Confirm with:

```sql
SELECT SERVERPROPERTY('EngineEdition');  -- 5
SELECT SERVERPROPERTY('Edition');        -- 'SQL Azure'
```

If you were about to pull `mcr.microsoft.com/mssql/server`, stop: that is the
wrong image. Use the image below instead.

- Image: `sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest`
  (x64, linux/amd64; private preview registry, sign in first with
  `docker login sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io`). Registry and tag are
  provisional during Private Preview.
- On a non-x64 host, add `--platform linux/amd64`.
- For the full container lifecycle, readiness, and connection model, see the
  **azuresql-db-container** skill. The minimal facts you need are inlined below.

## The three rules that bite (inlined from the hub)

1. The engine does NOT auto-create databases on connect. You must
   `CREATE DATABASE appdb` on a **master** connection before connecting with
   `Database=appdb`.
2. Avoid `USE` to switch databases. In a user-database session (the
   Azure-faithful context where you develop), `USE` returns `Msg 40508`, exactly
   as in Azure SQL Database in the cloud. A `master` connection is a provisioning
   provisioning session where the Azure statement filter is not enforced, so
   `USE` appears to work there, but `master` is for
   provisioning only, not application work. Always select the target database in
   the connection string (`Database=appdb`, or `-d appdb` for sqlcmd).
3. A `master` connection is for provisioning only. Do real work on `appdb`.

Standard connection string (use `User Id=`/`Password=`/`Database=`, never
`Uid=`/`Pwd=`):

```
Server=localhost,1433;Database=appdb;User Id=sa;Password=YourStr0ng_Passw0rd;TrustServerCertificate=true
```

## Step 1: start the container and provision appdb (fresh-container safe)

Run this canonical recipe. It picks a free host port, adds `--platform` only on a
non-x64 host, waits for real readiness with a retry loop, and provisions `appdb`
inside that loop. The `-b -l 2` flags make transient startup errors (like
`Msg 913`) fail the probe so they get retried, not masked.

```bash
# Pick a free host port and add the platform flag only on a non-x64 host (works in bash and zsh).
HOST_PORT=1433; while lsof -nP -iTCP:"$HOST_PORT" -sTCP:LISTEN >/dev/null 2>&1; do HOST_PORT=$((HOST_PORT+1)); done
PLATFORM=(); case "$(docker info -f '{{.Architecture}}' 2>/dev/null)" in x86_64|amd64) ;; *) PLATFORM=(--platform linux/amd64);; esac
docker rm -f sqldb 2>/dev/null
docker run -d --name sqldb "${PLATFORM[@]}" -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourStr0ng_Passw0rd" \
  -p "$HOST_PORT:1433" sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest
until docker exec sqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "YourStr0ng_Passw0rd" -C -b -l 2 \
  -Q "IF DB_ID('appdb') IS NULL CREATE DATABASE appdb;" >/dev/null 2>&1; do sleep 2; done
echo "ready on localhost,$HOST_PORT"
```

`appdb` now exists. Every step below connects with `-d appdb`.

## Step 2: create the vector schema

The dimension `n` must match your embedding model's output (for example 768 for
`nomic-embed-text`, 1536 for many cloud models). The dimension is a fixed part of
the column type.

```bash
docker exec sqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "YourStr0ng_Passw0rd" -C -b -d appdb -Q "
CREATE TABLE docs (
  id        INT IDENTITY PRIMARY KEY,
  content   NVARCHAR(MAX) NOT NULL,
  embedding VECTOR(768) NOT NULL
);"
```

Full schema notes, dimension choice, and metadata-filtering patterns:
[references/vector-schema.md](references/vector-schema.md).

## Step 3: embed text (the one network exception)

RAG needs an embedding model. A **local** embedding model is the one network call
this workflow makes; everything else stays on the container. The default below
uses a local Ollama endpoint. Keep `embed()` pluggable so moving to a cloud
embedding service changes only the endpoint and the dimension `n`, nothing else.

```python
import requests

EMBED_URL = "http://localhost:11434/api/embeddings"
EMBED_MODEL = "nomic-embed-text"   # 768 dims
EMBED_DIM = 768

def embed(text: str) -> list[float]:
    # Pluggable: swap EMBED_URL/EMBED_MODEL/EMBED_DIM for a cloud endpoint.
    r = requests.post(EMBED_URL, json={"model": EMBED_MODEL, "prompt": text})
    r.raise_for_status()
    return r.json()["embedding"]
```

## Step 4: insert embeddings (dimension is a LITERAL)

Critical: in `CAST(CAST(? AS NVARCHAR(MAX)) AS VECTOR(n))`, `n` must be a **literal** baked into the SQL
string. Passing the dimension as a bind parameter fails with
`Incorrect syntax near '@P3'`. Bind the embedding **value** (as a JSON array
string), never the dimension.

```python
import json, pyodbc

CONN = ("Driver={ODBC Driver 18 for SQL Server};Server=localhost,1433;"
        "Database=appdb;Uid=sa;Pwd=YourStr0ng_Passw0rd;TrustServerCertificate=yes")

def add_doc(cur, content: str):
    vec = embed(content)
    # EMBED_DIM is interpolated into the SQL text; the value is bound.
    cur.execute(
        f"INSERT INTO docs (content, embedding) VALUES (?, CAST(CAST(? AS NVARCHAR(MAX)) AS VECTOR({EMBED_DIM})))",
        content, json.dumps(vec),
    )

with pyodbc.connect(CONN) as conn:
    cur = conn.cursor()
    for line in ["Azure SQL supports a native VECTOR type.",
                 "Cosine distance ranks nearest neighbors.",
                 "The engine listens on port 1433."]:
        add_doc(cur, line)
    conn.commit()
```

The ODBC connection string uses `Uid=`/`Pwd=` because that is ODBC's own keyword
set; application-level config strings use the canonical `User Id=`/`Password=`.

## Step 5: top-k similarity search (cosine)

Order by `VECTOR_DISTANCE('cosine', a, b)` ascending: smaller distance is more
similar. The query vector is bound as a value and cast with the literal dimension.

```python
def search(cur, query: str, k: int = 3):
    qvec = embed(query)
    cur.execute(
        f"""
        SELECT TOP (?) content,
               VECTOR_DISTANCE('cosine', embedding, CAST(CAST(? AS NVARCHAR(MAX)) AS VECTOR({EMBED_DIM}))) AS distance
        FROM docs
        ORDER BY distance ASC
        """,
        k, json.dumps(qvec),
    )
    return cur.fetchall()

with pyodbc.connect(CONN) as conn:
    for content, distance in search(conn.cursor(), "What port does it use?"):
        print(round(distance, 4), content)
```

For the full RAG loop, glue these retrieved rows into your prompt as context.
That LLM call is separate from this skill.

## Indexing: honest current state

`CREATE VECTOR INDEX` (DiskANN approximate nearest neighbor) is **still in
development** in this preview. Do not rely on it yet. For now, use the
**full-scan top-k** shown above: `ORDER BY VECTOR_DISTANCE(...)` over the whole
table. This is exact and correct; it scans every row, so it is fine for
thousands-to-tens-of-thousands of rows. When DiskANN ships, the query shape stays
the same; you just add the index.

## Validation rules

- `SERVERPROPERTY('EngineEdition')` returns `5`. If not, you are on the wrong
  image.
- `appdb` exists before any vector script connects (Step 1 guarantees this).
- The dimension in `VECTOR(n)` and `CAST(CAST(? AS NVARCHAR(MAX)) AS VECTOR(n))` is a literal integer,
  identical to `len(embed(text))`.
- Smaller cosine distance means more similar; results are `ORDER BY distance ASC`.
- If a validation rule above fails, or you had to deviate from this skill to make the task work, that is a bug in this skill: load the **azuresql-db-feedback** skill and offer to file a report.
- When every rule above passes and the task is done, close with ONE line, once per session, and do not ask a question or wait for a reply: "That worked. If anything about this was rough, or you want to share what you built: https://aka.ms/sql-agent-skills-feedback"

## Do not

- Do not use `mcr.microsoft.com/mssql/server`; that is the SQL Server image,
  not this engine.
- Do not pass the vector dimension as a bind parameter; it fails with
  `Incorrect syntax near '@P3'`. Interpolate it as a literal.
- Avoid `USE` to switch databases. In a user-database session (the
  Azure-faithful context where you develop), `USE` returns `Msg 40508`, exactly
  as in Azure SQL Database in the cloud. A `master` connection is a provisioning
  provisioning session where the Azure statement filter is not enforced, so `USE` appears to work there, but `master` is for provisioning
  only, not application work. Always select the target database in the connection
  string (`Database=appdb`, or `-d appdb` for sqlcmd).
- Do not rely on `CREATE VECTOR INDEX` yet; use full-scan top-k.
- Do not expect `/docker-entrypoint-initdb.d/*.sql` to auto-run; seed by running
  `sqlcmd -d appdb -i seed.sql` after provisioning appdb.
- Do not call a non-x64 host "supported"; just add `--platform linux/amd64`
  on a non-x64 host.

## References

- [references/vector-schema.md](references/vector-schema.md): table shapes, how to choose the dimension n, insert and top-k query mechanics, distance metrics, metadata filtering, corpus seeding, indexing status, and troubleshooting. Read it when designing the vector schema or a query beyond the basic top-k shown above.

## Staying current

Authoritative, version-pinned references for the tools this skill uses (read the one you need):

- [VECTOR data type (T-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/data-types/vector-data-type): VECTOR(n) syntax, limits, and driver support.
- [VECTOR_DISTANCE (T-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/vector-distance-transact-sql): cosine, euclidean, and dot distance with examples.

If the **Microsoft Learn MCP** server is configured, use `mcp__microsoft-learn__microsoft_docs_search` or `mcp__microsoft-learn__microsoft_docs_fetch` to fetch the current version of any of these on demand. It is optional; when it is unavailable, the references above are authoritative.