The Microsoft Data and AI Stack Explained: How SQL Server, Fabric, Power BI, Foundry, and MCP Fit Together

If you work with SQL Server in 2026 you are being asked about Microsoft Fabric, Power BI, Foundry, MCP, and AI agents in the same conversation and expected to know how they relate. They are not competing products. They are layers in a single connected platform and SQL Server sits at the foundation of all of it.

This article maps the full stack in plain English, explains what each layer does, where they connect to each other, and specifically what the DBA’s role is at each layer. Every item in the stack has a dedicated SQLYARD deep-dive article linked at the end of each section.

1 Layer 1: SQL Server — The Foundation Everything Builds On Beginner

SQL Server is the data foundation. Every layer above it reads from, writes to, or depends on the quality and reliability of what lives here. Power BI reports surface SQL Server data. Foundry AI agents query SQL Server knowledge bases. MCP connections govern access to SQL Server. None of the upper layers work correctly if the foundation is poorly designed, undocumented, or performing badly.

SQL Server 2025 added capabilities specifically for the AI era: the native VECTOR data type and DiskANN approximate nearest neighbor index for storing and searching vector embeddings directly in SQL Server. This means SQL Server is now both the transactional data store and the AI knowledge base in a single engine. The schema documentation quality, column naming conventions, and extended property descriptions that DBAs maintain are now directly determining the quality of AI-generated SQL and AI agent responses.

This is not a metaphor. When an AI agent queries your database through MCP it reads your table names, your column names, and your extended property descriptions to understand what the data means. A column named c4 produces bad AI output. A column named OrderStatusID with a documented description of each valid value produces correct AI output on the first attempt.

2 Layer 2: Microsoft Fabric — The Unified Platform Beginner

Microsoft Fabric is the platform that connects all the other layers. It is a unified analytics service that combines data engineering, data warehousing, real-time intelligence, and AI under one management surface and one data store called OneLake.

For SQL Server professionals the most relevant Fabric capabilities are SQL database in Fabric (a managed SQL Server-compatible database), data mirroring (which replicates SQL Server data into OneLake in near real time without ETL), and the Fabric SQL analytics endpoint that allows Power BI and AI tools to query OneLake data using T-SQL. If your organization is on Microsoft Fabric, SQL Server data can flow into it automatically and be consumed by Power BI, Foundry AI agents, and other Fabric workloads without manual data movement.

Fabric is not required to use Power BI against SQL Server or to use Foundry. But it is the direction Microsoft is moving the entire data platform and understanding where it fits helps make sense of why the other layers are designed the way they are.

3 Layer 3: Power BI — The Reporting and Decision Layer Beginner

Power BI is how business users and analysts interact with SQL Server data visually. It connects to SQL Server in one of two fundamental modes: Import (takes a snapshot of the data on a schedule) or DirectQuery (queries SQL Server live every time a user interacts with a report).

The choice between these modes is not a Power BI developer decision. It is a decision that directly affects SQL Server performance, concurrency, blocking patterns, and row-level security enforcement. DirectQuery with 50 concurrent users can generate thousands of queries per minute against your production SQL Server. Understanding which mode a report uses, and what that means for the DBA, is the essential knowledge that bridges the SQL Server world and the Power BI world.

4 Layer 4: Microsoft Foundry — The AI Agent Platform Beginner

Microsoft Foundry is where enterprise AI applications and agents are built, deployed, and governed. It sits on top of Fabric infrastructure and provides a unified hub and project model for managing AI resources: model deployments, connections to external services, private networking, RBAC, and monitoring.

The hub and project model in Foundry maps directly to concepts SQL Server DBAs already know. A hub is like a SQL Server instance: shared governance, shared infrastructure, shared connections available to all projects under it. A project is like a database: an isolated workspace that inherits hub-level settings but has its own resources and its own access controls.

Connections in Foundry are how AI agents access external services including SQL Server. A Foundry project can have a connection to an MCP server that sits in front of SQL Server, enabling AI agents to query your databases through a governed, audited, least-privilege access layer.

5 Layer 5: MCP — The Secure Connection Between AI and Data Beginner

The Model Context Protocol is the standard that governs how AI agents connect to external systems including SQL Server. An MCP server sits between a Foundry AI agent and your SQL Server instance. It defines exactly which operations the AI is allowed to perform, blocks dangerous operations, caps row counts, and logs every action for audit purposes.

MCP is the layer that makes AI-to-SQL-Server connections enterprise-safe. Without it an AI agent with database access is an open connection that can run any query the connected account permits. With a properly configured MCP server the AI can only call named, defined tools, every call is logged, row limits prevent runaway queries, and the connection account has least-privilege SQL permissions defined by the DBA.

This is DBA work. Not developer work. Not AI team work. Creating the least-privilege SQL account, defining which schemas are accessible, writing the audit logging, setting row caps, blocking DROP and DELETE at the protocol level. These are the same security and governance principles DBAs have always applied. MCP is just the new access layer they apply them to.

6 How a Real Request Flows Through the Full Stack Beginner

Here is how a single business question flows through every layer of the stack, end to end.

A regional sales manager opens a Power BI dashboard on Monday morning to review the weekend numbers. They also have access to an AI assistant powered by Foundry agents. The same underlying data serves both.

SCENARIO: "What were our top 5 products by revenue this weekend
           and are there any orders still pending from Friday?"

POWER BI PATH (Layer 3 → Layer 1):
Manager clicks the weekend filter on the sales dashboard
  ↓
Power BI generates DirectQuery SQL to SQL Server
  ↓
SQL Server executes the query, returns results
  ↓
Power BI renders the top products visual
  (DBA owns: indexes that make this query fast, RCSI to prevent blocking)

AI AGENT PATH (Layer 4 → Layer 5 → Layer 1):
Manager types the question to the Foundry AI assistant
  ↓
Foundry agent receives the question
  ↓
Agent calls MCP tool: query_database
  ↓
MCP server validates the request (allowed query, within row cap)
  ↓
MCP executes read-only query against SQL Server
  ↓
SQL Server returns results (RLS enforced: manager sees only their region)
  ↓
MCP returns structured results to Foundry agent
  ↓
Agent combines results, generates natural language answer
  ↓
Manager receives: "Your top 5 products were [list]. There are 3 orders
still in Pending status from Friday for customers in your region."
  (DBA owns: MCP security config, SQL account permissions, schema documentation
   that lets the agent understand what OrderStatusID = 2 means)

Both paths use the same SQL Server data. Both paths depend on the DBA’s work: the indexes, the RCSI configuration, the row-level security, the MCP account permissions, and the schema documentation that makes AI-generated SQL correct. The DBA is not a bystander in the AI era. Every layer in this stack rests on work the DBA team does.

7 The DBA’s Role Has Not Shrunk. It Has Expanded. Beginner

Every layer added to the Microsoft data platform adds a new surface where the DBA’s skills apply. The traditional DBA role covered SQL Server performance, availability, backups, and security. In 2026 those responsibilities are unchanged and five new ones have been added:

• Vector store maintenance. The VECTOR tables in SQL Server 2025 that store RAG embeddings need index maintenance, freshness monitoring, and query performance tuning just like any other tables.
• Schema documentation for AI accuracy. Extended property descriptions on tables and columns directly determine the quality of AI-generated SQL. This is now a production responsibility, not a nice-to-have.
• Power BI connection governance. DirectQuery connection accounts, index design for Power BI query patterns, RCSI decisions that affect both OLTP and reporting workloads.
• Foundry connection configuration. Hub and project-level connection governance, Entra ID authentication setup, private VNet configuration for SQL Server accessibility from Foundry agents.
• MCP security layer. The DBA-built security boundary between AI agents and SQL Server. Least-privilege accounts, query restrictions, audit logging.

The Complete SQLYARD AI and Data Platform Series

Each article in this series covers one layer of the stack in depth. Read them in order for the complete picture or jump to the layer most relevant to what you are working on right now.

References

• Microsoft Docs: Microsoft Fabric Overview
• Microsoft Docs: What is Microsoft Foundry?
• Microsoft Docs: About DirectQuery in Power BI
• Model Context Protocol: Official Specification
• Microsoft Docs: Vector Support in SQL Server 2025