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:
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)
The engine does NOT auto-create databases on connect. You must
CREATE DATABASE appdb on a master connection before connecting with
Database=appdb.
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).
A master connection is for provisioning only. Do real work on appdb.
Standard connection string (use User Id=/Password=/Database=, never
Uid=/Pwd=):
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.
# 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.
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
);"
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.
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.
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.
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: 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):
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.
