SQL Server 2025: AI-Ready Database

By Law Wen Feng, Principal Solution Architect

Every enterprise AI project I've architected in the last three years hit the same wall: the data stack sprawl. Your transactional data lives in SQL Server, your embeddings live in a vector database, your analytics live in a lake, and your developers need three connection strings, three security models, and three backup strategies to build one chatbot.

SQL Server 2025 is Microsoft's answer to that sprawl. It's not a single headline feature — it's three distinct moves that together change what a SQL Server instance can do: native vector search built into the database engine, GitHub Copilot inside SQL Server Management Studio, and Fabric Mirroring that streams on-premises SQL Server data continuously into Microsoft Fabric without ETL pipelines.

I've spent the last few weeks digging into the Microsoft Learn documentation and testing what's real versus what's marketing. Here's the fact-checked picture, with working code.

The Problem: AI Data Stack Sprawl

The standard RAG architecture that every vendor demo shows you looks simple: chunk your documents, embed them, store the vectors, retrieve the top matches, feed them to a language model.

What the demo doesn't show is the operational reality. In practice you end up with:

A relational database for the transactional data your business runs on.
A dedicated vector store for embeddings, with its own access control, monitoring, and DR plan.
A synchronization job that keeps the two consistent — and pages you at 2 AM when it drifts.
An ETL pipeline if the analytics team wants any of it in the lake.

For a 10-person startup, that's manageable. For the enterprises I work with across Malaysia, Singapore, and the broader ASEAN region — most running lean database teams — every additional data platform is a real operational tax. This is the gap SQL Server 2025 attacks directly.

What SQL Server 2025 Actually Changes

Three capabilities matter for AI workloads:

Native vector search. A VECTOR data type, DiskANN-based approximate nearest neighbor indexes, and T-SQL functions for similarity search — all inside the database engine. No extension, no sidecar service.
GitHub Copilot in SSMS. Natural-language T-SQL assistance, code completions, and now an autonomous Agent mode, directly in SQL Server Management Studio 22.
Fabric Mirroring for SQL Server. Continuous, near-real-time replication of your SQL Server databases into Microsoft Fabric's OneLake as open Delta tables — including, for the first time, from on-premises SQL Server 2025 instances.

Let me walk through each one.

Native Vector Search: Semantic Queries in Plain T-SQL

The headline feature. SQL Server 2025 stores embeddings in a native VECTOR column and searches them with familiar T-SQL. If your team can write a JOIN, they can write a vector search.

The Core Workflow

First, register your embedding endpoint as an external model:

CREATE EXTERNAL MODEL EmbeddingModel
WITH (
    LOCATION = 'https://aoai-wenfeng-poc.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2023-05-15',
    API_FORMAT = 'Azure OpenAI',
    MODEL_TYPE = EMBEDDINGS,
    MODEL = 'text-embedding-3-small',
    CREDENTIAL = AzureOpenAICredential
);

The API_FORMAT accepts Azure OpenAI, OpenAI, Ollama, or ONNX Runtime — so local, air-gapped embedding models are also supported via Ollama or ONNX. That matters for regulated industries in this region that can't send text to a cloud endpoint.

Next, a table with a vector column. Note that the dimension must match your model's output — 1536 for text-embedding-3-small and text-embedding-ada-002:

CREATE TABLE KnowledgeBase (
    ArticleID   INT IDENTITY PRIMARY KEY,
    Title       NVARCHAR(500) NOT NULL,
    Content     NVARCHAR(MAX) NOT NULL,
    Category    NVARCHAR(100),
    Embedding   VECTOR(1536)
);

Then the DiskANN index:

CREATE VECTOR INDEX idx_KB_Embedding
ON KnowledgeBase (Embedding)
WITH (METRIC = 'cosine', TYPE = 'DiskANN', MAXDOP = 4);

Microsoft chose DiskANN deliberately: it's an SSD-friendly approximate nearest neighbor algorithm, so you get billion-scale-class indexing behavior without keeping the entire index in RAM. The supported distance metrics are cosine, euclidean, and dot.

Finally, the semantic query itself:

DECLARE @qv VECTOR(1536) =
    AI_GENERATE_EMBEDDINGS(N'I forgot my login credentials'
                           USE MODEL = EmbeddingModel);

SELECT TOP (5)
    k.ArticleID, k.Title, k.Category, v.distance
FROM VECTOR_SEARCH(
    TABLE = KnowledgeBase AS k,
    COLUMN = Embedding,
    SIMILAR_TO = @qv,
    METRIC = 'cosine',
    TOP_N = 5
) AS v
ORDER BY v.distance;

One syntax note: TOP_N is the parameter for the earlier-version vector indexes that SQL Server 2025 ships. The newer SELECT TOP (N) WITH APPROXIMATE form applies to the latest vector index version, which at the time of writing is available only in Azure SQL Database and SQL database in Microsoft Fabric. On SQL Server 2025, use VECTOR_SEARCH with TOP_N.

Exact Search Is Sometimes the Right Answer

You don't always need an ANN index. For exact k-nearest-neighbor search, VECTOR_DISTANCE() scans without an index:

SELECT TOP (10) ArticleID, Title,
       VECTOR_DISTANCE('cosine', @qv, Embedding) AS distance
FROM KnowledgeBase
ORDER BY distance;

Perfect recall, simpler operations. For catalogs and knowledge bases under roughly 50,000 vectors, exact search is often fast enough — don't over-engineer.

Generating Embeddings Without Leaving the Database

The AI_GENERATE_EMBEDDINGS() function above deserves its own section, because it's quietly one of the most useful additions. SQL Server calls your embedding endpoint directly from T-SQL — on insert, in a query, or in a batch job. No application round-trip.

Three prerequisites that the documentation buries and every team misses:

-- 1. Vector features are preview in SQL Server 2025; enable them per database
ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;

-- 2. Allow the engine to call external REST endpoints (server level)
EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;

-- 3. Grant users the right to execute the model
GRANT EXECUTE ON EXTERNAL MODEL::EmbeddingModel TO [AppRole];

The external model authenticates through a DATABASE SCOPED CREDENTIAL holding your Azure OpenAI API key. On Azure SQL Database and SQL database in Fabric the REST endpoint option is on by default; on SQL Server and SQL Managed Instance you must enable it explicitly.

The alternative pattern — and the one most production applications use — is generating embeddings in the application layer and inserting them as a JSON cast:

import json
import os

import pyodbc
from openai import AzureOpenAI

client = AzureOpenAI(
    azure_endpoint="https://aoai-wenfeng-poc.openai.azure.com",
    api_version="2023-05-15",
    api_key=os.environ["AZURE_OPENAI_KEY"],
)

resp = client.embeddings.create(
    model="text-embedding-3-small",
    input="How do I reset my password?",
)
vec = json.dumps(resp.data[0].embedding)

conn = pyodbc.connect(conn_str)
cur = conn.cursor()
cur.execute(
    "INSERT INTO KnowledgeBase (Title, Content, Embedding) "
    "VALUES (?, ?, CAST(? AS VECTOR(1536)))",
    ("Reset password", "Go to Settings > Security...", vec),
)
conn.commit()

Use in-database embedding for bulk backfills and pure-SQL pipelines; use application-side embedding when you need control, batching, and retry logic. Both land in the same VECTOR column.

GitHub Copilot in SSMS: Natural Language Meets Your Schema

The Copilot story has evolved, so let me state the current situation precisely. The AI assistant in SSMS is now GitHub Copilot in SQL Server Management Studio, and it requires SSMS 22 or later. You sign in with a GitHub account that has Copilot access — or sign up for Copilot Free directly from the SSMS badge in the top-right corner.

What it actually does:

Chat and inline chat — ask questions about your database in plain English, get T-SQL back. "Show me all orders from the last 30 days for customers in Kuala Lumpur" becomes a working query against your real schema.
Code completions — inline suggestions in the query editor, available from SSMS 22.2.
Slash commands — /doc, /explain, /fix, /optimize for documenting and repairing T-SQL.
Database instructions — you can store business rules and context in the database itself so Copilot generates more accurate queries.
Agent mode (preview) — from SSMS 22.7, give Copilot a high-level goal and it works through it autonomously: executing queries, reading execution plans, and modifying schema with your approval, extensible via MCP servers.

The security posture is the part that matters for my enterprise clients: Copilot executes queries under your login's permissions — if you can't SELECT from Sales.Orders, neither can Copilot. And per Microsoft's documentation, prompts, responses, and system metadata are not retained, and your data is not used to train models.

Fabric Mirroring: Your On-Prem Data in OneLake, Without ETL

This is the feature in the title, and arguably the most strategically important of the three. Mirroring in Fabric continuously replicates SQL Server databases into OneLake as open Delta/Parquet tables, with near-real-time latency. Inside Fabric, each mirrored database gets an auto-generated SQL analytics endpoint — queryable with T-SQL from SSMS, VS Code, or Power BI.

Why this matters: Synapse Link for SQL Server is discontinued in SQL Server 2025. Mirroring in Fabric is the replacement path, and it's a genuinely better one — no ETL pipeline, no staging database, and the data lands in an open format.

The SQL Server 2025 specifics you need to know, straight from the documentation:

SQL Server 2025 mirroring is supported for on-premises instances. It is currently not supported for SQL Server 2025 running in Azure Virtual Machines, and not on Linux.
It requires Azure Arc onboarding with the Azure Extension for SQL Server.
SQL Server 2025 uses the Fabric mirroring change feed feature — not classic Change Data Capture. Older versions (SQL Server 2016–2022, Standard/Enterprise/Developer editions) mirror via CDC.
SQL Server 2025 adds resource governor workload groups to control the CPU and I/O that mirroring consumes, plus automatic reseed configuration to prevent unbounded growth of the change feed.

The architecture this unlocks is the one I now recommend for clients with a large on-premises SQL estate:

┌────────────────────────────────────────────┐
│   Apps & APIs (OLTP, vectors, JSON)        │
├────────────────────────────────────────────┤
│   SQL Server 2025 (on-premises, Arc-joined)│
│   VECTOR columns + DiskANN + Copilot       │
├────────────────────────────────────────────┤
│   Fabric Mirroring (change feed, Arc)      │
├────────────────────────────────────────────┤
│   Microsoft Fabric OneLake (Delta tables)  │
│   SQL analytics endpoint + Power BI        │
└────────────────────────────────────────────┘

Operational data stays in SQL Server 2025 where your AI application serves it; analytics and BI read continuously refreshed Delta copies in Fabric. No nightly ETL window, no "the dashboard is stale" conversations.

Pitfalls I See Teams Hit

From testing and early client engagements, these are the traps:

Forgetting `PREVIEW_FEATURES`. Vector search in SQL Server 2025 is a preview feature. Without ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON on the target database, vector operations fail — and Microsoft explicitly cautions that preview features aren't recommended for production. Plan your GA timing accordingly.
Dimension mismatch. VECTOR(1536) must match your embedding model's output. Switching from text-embedding-ada-002 to text-embedding-3-large (3072 dimensions) means a new column and a full re-embedding of existing data. Embeddings are model-specific: you cannot mix them.
Metric mismatch between index and query. An ANN index is only used if the query's distance metric matches the index's metric. If you build the index with cosine and query with euclidean, SQL Server silently falls back to exact kNN and just warns you.
Assuming mirroring works from Azure VMs. For SQL Server 2025, Fabric Mirroring currently supports on-premises instances only. If your SQL Server runs in an Azure VM, mirror via the Azure SQL Database or Managed Instance paths instead — or wait.
Treating Copilot as a security boundary. Copilot respects your login's permissions, which is exactly as safe as your permission model. If your developers connect with db_owner or sysadmin accounts — as too many do — Copilot inherits that reach. Use least-privilege logins before handing anyone an AI assistant.
Re-embedding costs. Every content update invalidates its embedding. Budget the Azure OpenAI token cost of re-embedding into your change workflow, especially for large knowledge bases that update frequently.

When You Still Need a Dedicated Vector Database

Honest guidance, because SQL Server vector search is not always the answer:

Choose SQL Server 2025	Choose a dedicated vector store
Vectors must JOIN with relational data (inventory, pricing, permissions)	Billion-vector scale or thousands of similarity queries per second
Dataset under a few million vectors	Multimodal workloads (image, audio, video embeddings)
You want one backup, one security model, one connection string	Vectors are fully independent of transactional data
ACID transactions around vector writes	You need specialized hybrid-search tuning

For the use cases I see most in this region — internal knowledge bases, customer support RAG, product catalog search — SQL Server 2025 is simpler, cheaper, and operationally leaner than bolting on a second database.

Key Takeaways

Vector search is native T-SQL. The VECTOR type, DiskANN indexes, VECTOR_SEARCH(), and VECTOR_DISTANCE() bring semantic search into the database engine — but it's a preview feature gated behind PREVIEW_FEATURES = ON, so treat production timing carefully.
Embeddings can be generated inside the engine. AI_GENERATE_EMBEDDINGS() plus an external model (Azure OpenAI, OpenAI, Ollama, or ONNX) keeps the whole RAG pipeline in one database — or generate embeddings in your app and CAST the JSON into a vector column.
Copilot in SSMS is GitHub Copilot. It requires SSMS 22+, signs in with a GitHub account, executes queries under your permissions, and now includes completions, database instructions, and a preview Agent mode.
Fabric Mirroring replaces Synapse Link — and for the first time it reaches on-premises SQL Server 2025 via Azure Arc and the change feed, streaming your operational data into OneLake as open Delta tables without ETL.
The strategic play is consolidation. One engine for transactional data, vectors, and JSON; one continuous mirror into Fabric for analytics. Before you buy a second database for AI, prove you actually need it.