Agentic RAG in SQL Server: What Vectors, Chat Completion and Foundry Change for DBAs

Most AI work today still sits outside the database: a Python service, a separate vector store, an orchestration framework, and SQL Server reduced to a source of rows. The latest Data Exposed episode from Bob Ward and Anna Hoffman, published on 27 September, shows the opposite pattern becoming real: agentic RAG in SQL Server, Azure SQL and SQL database in Fabric, with native vectors, REST calls to models, in-engine chat completion, an immutable log on ledger tables, and Microsoft Foundry gateway integration for moderation and prompt-injection protection. Less data movement, more engine, and a different set of responsibilities for the people who run databases.

The capability stack in one place

The individual pieces are documented. What the episode shows is them working together as one pattern, inside the engine:

• The vector data type, VECTOR_DISTANCE and vector indexes, available in SQL Server 2025 (17.x), Azure SQL Database, Managed Instance on the Always-up-to-date or SQL Server 2025 update policy, and SQL database in Fabric.
• CREATE EXTERNAL MODEL, a database-scoped object that points at an inference endpoint and keeps the API format and credential reference in one place. Accepted formats include Azure OpenAI, OpenAI, Ollama and ONNX Runtime.
• AI_GENERATE_EMBEDDINGS and AI_GENERATE_CHUNKS, which generate embeddings and split text into chunks inside ordinary T-SQL statements, no external pipeline required.
• sp_invoke_external_rest_endpoint for everything those functions do not cover, including chat completion calls whose responses you parse with OPENJSON.
• Ledger tables as an immutable record of prompts and responses, and the Foundry gateway handling content moderation and prompt-injection prevention, both demonstrated in the episode.

Enabling the REST path is one setting and one object:

EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE;

CREATE EXTERNAL MODEL dbo.MyEmbeddingModel
WITH (
    LOCATION = 'https://my-openai-endpoint.cognitiveservices.azure.com/openai/deployments/text-embedding-ada-002/embeddings?api-version=2023-05-15',
    API_FORMAT = 'Azure OpenAI',
    MODEL_TYPE = EMBEDDINGS,
    MODEL = 'text-embedding-ada-002',
    CREDENTIAL = [https://my-openai-endpoint.cognitiveservices.azure.com/]
);

In the box product, sp_invoke_external_rest_endpoint starts with SQL Server 2025 (17.x). On Azure SQL Managed Instance it depends on the Always-up-to-date update policy. If your estate still runs SQL Server 2022 or earlier, read this as a direction of travel rather than a task for Monday. It is still worth briefing your data platform owner on now, because the governance questions below apply to whatever build you eventually adopt.

Less data movement is the real prize

RAG is a loop: retrieve relevant context, build the prompt, call the model, return an answer. The common deployment splits that loop across a Python service, a vector database and a model endpoint, then pays for it in sync jobs, a second copy of your text, and another credential surface to defend. When the embeddings sit next to the relational rows, retrieval becomes a join and the grounding is live data rather than last night's export.

INSERT INTO dbo.doc_embeddings (notice_id, chunk_text, embedding)
SELECT n.notice_id,
       c.chunk,
       AI_GENERATE_EMBEDDINGS(c.chunk USE MODEL dbo.MyEmbeddingModel)
FROM dbo.contract_notices AS n
CROSS APPLY AI_GENERATE_CHUNKS (
    SOURCE = n.body,
    CHUNK_TYPE = FIXED,
    CHUNK_SIZE = 100
) AS c;

DECLARE @question VECTOR(1536);
SET @question = AI_GENERATE_EMBEDDINGS(
    'which contracts have notice periods ending this quarter'
    USE MODEL dbo.MyEmbeddingModel
);

SELECT TOP (5) n.notice_id, n.contract_ref, d.chunk_text
FROM dbo.doc_embeddings AS d
JOIN dbo.contract_notices AS n
    ON n.notice_id = d.notice_id
ORDER BY VECTOR_DISTANCE('cosine', d.embedding, @question);

The search returns chunks that join straight back to their source rows, so an answer can cite a contract number rather than paraphrase a paragraph. That distinction matters more than it sounds. A governance committee can accept a system that shows exactly which rows informed an answer. The chunking, embedding and similarity search all run where the data already lives. Nothing leaves the instance until the final prompt, which carries only the retrieved context, never a bulk copy of your documents. The episode's closing point, "more secure, less data movement", is the right summary, and it matches what we saw building RAG the traditional way on Azure Cognitive Search in 2024: the sync layer was where most of the complexity lived.

Two cautions belong next to the enthusiasm. A vector index is not a substitute for testing, so use representative prompts and realistic row counts before you trust a top-5 result. And every embedding call is a network round trip initiated from your database engine, which puts the model endpoint's latency and capacity on the database's critical path.

Turning it on without surprises

Sequence the work and most of the risk disappears:

1. Enable the external REST endpoint setting only where model calls are intended. In SQL Server 2025 (17.x) it is a server-level sp_configure change, so treat it as a change-managed event, not a toggle during a quiet Friday.
2. Create the external model objects deliberately, and set a timeout on every REST call. Assume the model endpoint will have a bad day, and decide in advance what your queries do when it does.
3. Turn on the diagnostics before the first production call. The AI_GENERATE_EMBEDDINGS documentation lists the ai_generate_embeddings_summary and external_rest_endpoint_summary extended events, which capture status codes, errors and model usage.
4. Batch embedding backfills off peak, and keep REST-based chat calls away from hot OLTP paths until your own load tests give you latency numbers.

Prototype on a development copy or a Fabric SQL database rather than the OLTP primary. Neither option costs you an outage while you learn where the sharp edges are, and the Fabric route condenses chunking and embedding into a single statement.

What this adds to the DBA job description

The honest reading of agentic RAG in SQL Server is that it removes systems to operate, not responsibilities to carry. Three of them land with the database team.

Credential custody comes first. The credential objects that back external models, their key rotation, and the grants that decide which principals may call which model all sit in your instance. The CREATE EXTERNAL MODEL documentation notes that users may need explicit permission to use specific external models. That is a feature if you make it deliberate, and an accident if you do not.

Performance stewardship comes second. Every chat completion is a network round trip taken inside a query, holding database resources while it waits. Timeouts and the extended events give you the measurement side. Capacity planning for the model endpoint becomes part of database capacity planning, sitting in the same conversation as storage and tempdb rather than in someone else's sprint.

Audit evidence comes third. Prompts, retrieved context and responses become records the moment they touch your instance. Ledger tables give you a tamper-evident trail, and the extended events give the operational half, so the question of who asked what and what came back has an answer. On the estates we assess, an ungoverned AI surface is simply the newest example of the hidden risks of an unmanaged SQL estate, and the remedy is the same discipline: one inventory, explicit ownership, reviewed access.

Three questions before you switch it on

• Who approves a production database calling an external model endpoint, and where is that decision recorded?
• Where does the prompt log live, who can read it, and how long is it retained?
• What does your workload do when the endpoint is slow, throttled or unreachable for an hour?

If nobody can answer those in a sentence each, settle the governance before the first production chat completion runs. That work fits naturally into the reviews and health checks we run for SQL Server and Azure SQL estates, and it is the same muscle as the one behind From Reactive to Proactive: Building a SQL Server Strategy: make the decision before the platform makes it for you. The engine now has the capability. Whether the control around it is real is a question worth answering this quarter, not after the first incident.