---
title: "RAG on SQL Server 2025 in .NET: architecture"
description: "Wiring RAG into a .NET and SQL Server application without a separate vector store: the VECTOR type, chunking that follows the documents, permissions enforced in the query, an Agent Framework agent. Complete, tested code on GitHub."
url: "https://gilabs.fr/en/guides/rag-sql-server-2025/"
lang: "en"
author: "Yann Gilliot"
published: "2026-09-30"
tags: ["RAG", "SQL Server", ".NET", "Agent Framework", "Architecture"]
---
# RAG on SQL Server 2025: a reference architecture for an existing .NET application

By [Yann Gilliot](https://gilabs.fr/en/about/) · published on 2026-09-30

Wiring RAG into a .NET and SQL Server application without a separate vector store: the VECTOR type, chunking that follows the documents, permissions enforced in the query, an Agent Framework agent. Complete, tested code on GitHub.

## The short version

- SQL Server 2025 stores embeddings in a VECTOR type and computes distances with VECTOR_DISTANCE, both generally available. The application database can be the vector store: no second system to secure.
- The DiskANN vector index is still in preview on SQL Server 2025 (up to CU9, 15 September 2026): the table becomes read-only once indexed and filters apply after the search. Exact search is the right default, recommended by Microsoft up to about 50,000 vectors after filtering.
- Permissions are enforced in the SQL query, on the permission table the application already uses, with roles taken from the server-side identity. A chunk the user may not read never reaches the model.
- Chunking follows the document's structure (sections, paragraphs, whole tables), and every chunk carries its heading path into its embedding.

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](https://gilabs.fr/en/guides/semantic-kernel-agent-framework/)). The code in this guide comes from a [public GitHub repository](https://github.com/ygilliot/rag-sql-server-dotnet), 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:

| Feature | SQL Server 2025 (up to CU9, 15/09/2026) | Azure SQL Database, SQL database in Fabric |
|---|---|---|
| `VECTOR` type, up to 1,998 float32 dimensions | Generally available | Generally available |
| `VECTOR_DISTANCE` (cosine, Euclidean, dot product) | Generally available | Generally available |
| Binary transport from .NET (`SqlVector<float>`) | Microsoft.Data.SqlClient 6.1 or later | Same |
| `CREATE VECTOR INDEX` (DiskANN) and `VECTOR_SEARCH` | Preview, `PREVIEW_FEATURES` option | Generally available |
| Writes to an indexed table, filters applied during the search | No: earlier index format, read-only table, filter afterwards | Yes (latest index format) |

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

| Layer | What already exists | What RAG adds |
|---|---|---|
| Data | The application's documents table | One chunks table with a `VECTOR(1536)` column |
| Permissions | The existing permission table or mechanism | Nothing: the search reuses it |
| .NET service | User identity, logging, audit | A chunker, an ingestor, a retriever |
| Interface | The application | An Agent Framework agent that answers from authorised chunks |

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:

```sql
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:

```csharp
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:

```sql
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:

```csharp
// 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.

| Criterion | Exact search (`VECTOR_DISTANCE`) | DiskANN index on SQL Server 2025 | DiskANN index on Azure SQL |
|---|---|---|---|
| Status | Generally available | Preview | Generally available |
| Results | Exact | Approximate | Approximate |
| Permission filter | Applied first, complete results | Applied afterwards, results may be missing | Applied during the search |
| Writes to the table | Normal | Read-only table (unless `ALLOW_STALE_VECTOR_INDEX` is on) | Normal |
| Suitable volume | Up to about 50,000 vectors after filtering | Large volumes, stable data | Large volumes |

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:

```csharp
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](https://github.com/ygilliot/rag-sql-server-dotnet) 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](https://gilabs.fr/en/guides/semantic-kernel-agent-framework/) still compiles. For the gap between a demo that works and a system that holds up, see [what holds up in production](https://gilabs.fr/en/blog/ce-qui-tient-en-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](https://gilabs.fr/en/services/dotnet-ai-integration/).

## Sources and references

- [The guide's GitHub repository: rag-sql-server-dotnet (complete code and tests)](https://github.com/ygilliot/rag-sql-server-dotnet), GitHub
- [SQL Server 2025 Embraces Vectors (SQL Server 2025 release, 18 November 2025)](https://devblogs.microsoft.com/azure-sql/sql-server-2025-embraces-vectors-setting-the-foundation-for-empowering-your-data-with-ai/), Microsoft
- [Vector data type](https://learn.microsoft.com/en-us/sql/t-sql/data-types/vector-data-type), Microsoft Learn
- [Vector search and vector indexes in the SQL Database Engine](https://learn.microsoft.com/en-us/sql/sql-server/ai/vectors), Microsoft Learn
- [CREATE VECTOR INDEX (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-vector-index-transact-sql), Microsoft Learn
- [VECTOR_SEARCH (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/vector-search-transact-sql), Microsoft Learn
- [SQL Server 2025 build versions (CU9, 15 September 2026)](https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2025/build-versions), Microsoft Learn
- [RAG with Agent Framework (TextSearchProvider)](https://learn.microsoft.com/en-us/agent-framework/agents/rag), Microsoft Learn

Topics: RAG, SQL Server, .NET, Agent Framework, Architecture

## Take it further

Can your applications answer from your own documents without opening a second permission system?

For mid-caps and software vendors whose IT lives in .NET and SQL Server: AI developed inside your application (document extraction, agents, classification) with your business rules, your architecture, your code. Not another tool next to the IT system: a new capability inside it. I work in your repo, alongside your developers, to your team's standards.

[.NET AI integration](https://gilabs.fr/en/services/dotnet-ai-integration/)

**Book a 30-minute call**: [Calendly](https://calendly.com/yann-gilabs/30min)