RAG on SQL Server 2025: a reference architecture for an existing .NET application

In July I published three decisions for wiring RAG into an existing .NET estate: keep the vectors next to the data, chunk along the structure of the documents, keep orchestration in the service layer. This guide replaces that article. The three decisions still hold, but the text stayed at the level of principles. Here is the complete architecture, with the SQL and the C#.

Two things have changed since. SQL Server 2025, released on 18 November 2025, stores embeddings in a proper column type. And Microsoft Agent Framework 1.0, released in April 2026, has replaced Semantic Kernel for the agent layer (see the migration guide). The code in this guide comes from a public GitHub repository, built and tested against SQL Server 2025 on every change.

Where SQL Server 2025 stands on vectors

The documentation mixes generally available features with preview ones, and the difference between SQL Server on your own servers and Azure SQL matters. Status on 30 September 2026:

Remember the last row. On SQL Server 2025 running on your own servers, today’s vector index makes the table read-only once created, and WHERE conditions apply after the approximate search. For RAG filtered by permissions, that means a query can return nothing even though authorised documents exist. Exact search does not have this flaw, and it is enough in most cases (see below).

The architecture in one table

One new table, no new system. That is the whole point of doing RAG in SQL Server when the application already lives there.

Decision 1: the vectors stay next to the data

The chunks table points to the existing documents table. It copies neither the title nor the permissions:

CREATE TABLE dbo.DocumentChunks
(
    ChunkId        INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_DocumentChunks PRIMARY KEY CLUSTERED,
    DocumentId     INT                NOT NULL CONSTRAINT FK_DocumentChunks_Documents
                                               REFERENCES dbo.Documents (DocumentId) ON DELETE CASCADE,
    Ordinal        INT                NOT NULL,
    HeadingPath    NVARCHAR(400)      NOT NULL,
    Content        NVARCHAR(MAX)      NOT NULL,
    Embedding      VECTOR(1536)       NOT NULL,
    EmbeddingModel NVARCHAR(100)      NOT NULL
);

Three details that save trouble later. The primary key is a clustered INT, which a vector index requires if you ever add one. The cascading delete removes the chunks when the document goes: no orphan chunk still answering questions. And the EmbeddingModel column records which model produced each vector. Vectors from two embedding models cannot be compared: the day you change models, this column tells you what is left to recompute, and the search filters on it so the two never mix.

Decision 2: chunking follows the structure of the document

Cutting every 500 tokens is simple, and it cuts a clause, a table or a procedure step in two. The model gets half a rule and answers from that half. Business documents already carry their structure; the chunker in the repository respects it:

• one chunk per section, delimited by headings;
• a section that is too long is split between two paragraphs, never inside one;
• a table or a code block stays whole, even beyond the target size;
• each chunk keeps its heading path, for example “Expense policy > Travel > Hotels”.

That path is prepended to the text sent to the embedding model. A chunk that only says “ceiling: 120 euros per night” does not say what it is about; with its path, the question “how much for a hotel?” finds it:

public sealed record DocumentChunk(int Ordinal, string HeadingPath, string Content)
{
    // The heading path travels with the text into the embedding.
    public string EmbeddingText => $"{HeadingPath}\n\n{Content}";
}

The repository’s chunker works on Markdown. For PDF or Word files, the conversion upstream matters as much as the chunking: that is the job of your existing extraction pipeline, or of Microsoft’s Microsoft.Extensions.DataIngestion library, still in preview, which also offers heading-based chunking.

Decision 3: permissions are enforced in the query

This is the decision that matters most, and the one tutorials skip. The search filters on the application’s permission table, in the same query as the distance calculation:

SELECT TOP (@top)
    c.ChunkId, c.DocumentId, d.Title, d.SourceUri, c.HeadingPath, c.Content,
    VECTOR_DISTANCE('cosine', c.Embedding, @query) AS Distance
FROM dbo.DocumentChunks AS c
INNER JOIN dbo.Documents AS d ON d.DocumentId = c.DocumentId
WHERE c.EmbeddingModel = @model
  AND EXISTS (
      SELECT 1
      FROM dbo.DocumentAccess AS a
      INNER JOIN OPENJSON(@roles) WITH (RoleName NVARCHAR(100) '$') AS r
          ON r.RoleName = a.RoleName
      WHERE a.DocumentId = c.DocumentId)
ORDER BY Distance;

On the .NET side, the question vector travels in binary, with no JSON round trip:

// Fail closed: a caller without any role sees nothing, rather than everything.
if (caller.Roles.Count == 0)
{
    return [];
}

var queryVector = await embeddingGenerator.GenerateVectorAsync(question, cancellationToken: cancellationToken);

command.Parameters.Add("@roles", SqlDbType.NVarChar, -1).Value = JsonSerializer.Serialize(caller.Roles);
command.Parameters.Add("@query", SqlDbTypeExtensions.Vector).Value = new SqlVector<float>(queryVector);

Two rules sit behind this code. Roles come from the server-side identity (token, session, claims), never from a parameter the model or the client could supply. And a user without a role sees nothing: when in doubt, the search fails closed. In your application, the DocumentAccess table is replaced by your real permission model, whether that is roles, departments, row-level security or a view combining them. The principle does not change: the filter lives where the permissions already live.

Exact search or a vector index?

Exact search computes the distance to every candidate chunk. Microsoft recommends it as long as the search covers fewer than about 50,000 vectors, counted after the WHERE conditions. With a permission filter, that threshold is rarely reached: a user may read a fraction of the documents, not all of them.

Start with exact search, measure response times on your real volumes, and move to the index only if the measurements call for it. The repository keeps the index in a separate script, with these caveats at the top.

Wiring the agent

The agent layer uses Microsoft Agent Framework. The search plugs in as a context provider: TextSearchProvider runs it before each model call, with the user captured on the server side:

public static AIAgent Create(IChatClient chatClient, ChunkRetriever retriever, ICallerContext caller) =>
    chatClient.AsAIAgent(new ChatClientAgentOptions
    {
        ChatOptions = new() { Instructions = Instructions },
        AIContextProviders =
        [
            new TextSearchProvider(
                (query, cancellationToken) => SearchAsync(retriever, caller, query, cancellationToken),
                new TextSearchProviderOptions
                {
                    SearchTime = TextSearchProviderOptions.TextSearchBehavior.BeforeAIInvoke,
                }),
        ],
    });

The agent is created per request, for the user of that request: it is a light object around the shared chat client. If you prefer the mode where the model calls a search tool itself, keep the roles out of the tool’s parameters. Otherwise the model decides what the user may read.

What the tests check

The repository’s tests run against a real SQL Server 2025 on every change, with a deterministic embedding generator that needs no API key. They check:

• that an employee finds the hotel ceiling in the expense policy, with its source;
• that an employee never retrieves a chunk of the HR salary document, even with a question that uses its words;
• that a user with several roles sees the union of their permissions, and a user with none sees nothing;
• that ingesting a document again replaces its chunks instead of duplicating them;
• and above all, by running the full agent against a fake model that records everything it receives, that the text of a forbidden document never reaches the prompt.

That last test is the one I recommend copying into your own code base. Checking what the query returns is not enough: what matters is what reaches the model.

Checklist before production

1. Find the source of permissions. Where does the rule “who may read which document” live today? That is what the search must query, not a copy.
2. Choose the embedding model and its dimension. The VECTOR(n) column depends on it. For a regulated profession, a model hosted in the European Union, or on premises.
3. Plan for a model change. An EmbeddingModel column, a re-embedding procedure, and a filter on the model in the search.
4. Read twenty chunks. On your real documents, before any tuning: bad chunking shows at a glance.
5. Build a reference question set. Around thirty real questions, with the expected documents and, for each profile, the ones that must never come out.
6. Fail closed. No role, no result. A permission error, no result.
7. Treat embeddings like the documents. A vector derives from the text: same confidentiality, same retention period, same deletion.
8. Measure before indexing. Response time and number of candidate chunks per profile; a vector index only if those measurements justify it.

Going further

The GitHub repository contains the schema, the chunker, the ingestion, the retrieval, the agent, the tests and a sample that runs without an API key. On every change it also checks that the code published in the guide Semantic Kernel or Microsoft Agent Framework still compiles. For the gap between a demo that works and a system that holds up, see what holds up in production. And if you want your business documents to be answerable from your existing applications, that is the core of AI integration for .NET applications.