Giving AI Agents Visibility Into SQL Server with MCP
Page content
I’ve been thinking a lot lately about what it actually takes to make an AI agent genuinely useful for database work, both for administration and for application access to the data tier. Writing the T-SQL code is the easy part. A coding assistant can do that out of the box. The hard part is giving it visibility into a running SQL Server: which sessions are blocked right now, where the wait stats are pointing, which indexes the optimizer is begging for. Without that, the agent is just guessing. With application access, an agent can propose how things should work, but what if we had tools that added context describing the agent database’s schema and what the entities actually mean to the application when interacting with the database agentically?
Imagine being able to ask your database estate, “What’s the situation with backups in my environment? Or “What products are we going to sell out of by the end of the quarter?” With MCP, you can do just that.
With the sql-mcp-server project, a custom MCP server that gives AI agents real-time eyes into SQL Server DMVs and combines that with Data API Builder for user database interactions. I’m excited to share it today as an open-source project so you can get your hands dirty with this tech right away. Let’s go.
Why I Built This
The Model Context Protocol (MCP) is how you extend an AI agent’s reach beyond what it already knows. It lets you expose Tools (structured function calls) that an agent can invoke during a conversation. The agent decides when to call them, chains them together, and synthesizes the results into a coherent answer. Then you can define how the agent interacts with your environment with a skills file. We’ll cover that part in an upcoming post. Think…you define an SRE persona and define what good looks like in your environment in terms of availability, performance, configuration, backup policy any of the normal SOP operations, then use it as the guidance for your agent, well, now your SRE Agent. You can quickly get your arms around a complex estate with tools like this.
For SQL Server, this means you can ask your agent a question like “are there any blocking sessions right now, and what SQL is causing it?” and instead of generating a query for you to run manually, it goes and gets the answer itself, calling DMVs, correlating the results, and giving you a diagnosis in plain English.
I tried the zero-code path first. Data API Builder (DAB) can automatically stand up an MCP server. Point it at a user database, and it exposes REST, GraphQL, and MCP endpoints with no code. It works great for controlled agentic interactions against user databases. Ask “which products are low on inventory?” and the agent calls the DAB MCP endpoint, filters the Products table, and returns the answer. No SQL written, no schema required. But when I tried to expose system DMVs via DAB, it quickly broke down. Unsupported data types and query patterns in the system views prevented DAB from surfacing what I needed. So I wrote a custom server.
The result is an MCP server with 30 tools that query SQL Server DMVs directly. A single container manages connection pools to multiple SQL Server instances. The agent never touches the database directly. It calls the tool server, the tool server runs the structure SQL tools, and you stay in control.
So what you have here is a container-based environment that allows you to experiment with both DBA tasks via the custom-built MCP server and to use DAB to interact with a user database, all agentically.
How It Works
The demo wires up two complementary MCP servers, both running in Docker Compose:
- Data API Builder (DAB): zero-code MCP server for the ProductsDB application database. Exposes CRUD operations on Products, Categories, Orders, and OrderDetails over REST, GraphQL, and MCP. This is the right tool when you want natural-language data access against a known application schema.
- Custom SQL MCP Server: 30 DBA-focused tools querying SQL Server DMVs directly. Blocking chains, wait stats, missing indexes, query plan cache, memory pressure, CPU history, AG health, backup status, and more. Supports multiple SQL Server instances. Add a new server to a JSON array in
.env, restart the container, and the agent can reach it immediately.
Here’s the architecture:
┌─────────────────────────────────────────────────────────────────────┐
│ Docker Network │
│ │
│ ┌──────────────┐ CRUD ┌─────────────────────────────────────┐ │
│ │ │◄─────────│ DAB MCP (dab-mcp :5001) │ │
│ │ SqlServer1 │ │ REST / GraphQL / MCP │ │
│ │ ProductsDB │ └─────────────────────────────────────┘ │
│ │ Port 1433 │ │
│ │ │ SELECT ┌─────────────────────────────────────┐ │
│ │ │◄────┐ │ SQL MCP Server (:3001) │ │
│ └──────────────┘ └────│ connectionManager.ts │ │
│ ┌───│ 30 DMV tools │ │
│ ┌──────────────┐ │ └─────────────────────────────────────┘ │
│ │ SqlServer2 │◄─────┘ │
│ │ Port 1433 │ SELECT (second instance) │
│ └──────────────┘ │
└─────────────────────────────────────────────────────────────────────┘
The multi-instance support is one of the parts I’m most happy with. Instances are registered via the INSTANCES environment variable at startup. Each gets its own lazy connection pool. The first tool call opens the pool, subsequent calls reuse it, and the pool self-heals if it errors. The fan_out_query tool runs the same T-SQL across all instances in parallel, so if one instance is down, it doesn’t cancel queries on the others.
Getting Started
You’ll need Docker Desktop. That’s it.
# Clone and configure
git clone https://github.com/nocentino/sql-mcp-server.git
cd sql-mcp-server
cp .env.example .env # set your passwords in .env
# Start everything
docker compose up --build -d # first run: ~2-3 min to pull + seed the DB
# Verify
docker compose ps
curl http://localhost:3001/health # SQL MCP server
curl http://localhost:5001/health # DAB
Once the containers are running, register both servers with your AI agent. For GitHub Copilot in VS Code, add them to the MCP configuration file:
{
"servers": {
"sql-dba": { "type": "http", "url": "http://localhost:3001/mcp" },
"products-db": { "type": "http", "url": "http://localhost:5001/mcp" }
}
}
For Claude Desktop, use the same server URLs in the Claude configuration. The demos/ folder has six scripted walkthroughs that show exactly what the agent can do.
What This Looks Like in Practice
This is where it gets interesting. The agent doesn’t just run a single query. It chains tool calls, cross-references the results, and synthesizes a diagnosis from a single natural-language question.
Let’s start with natural-language interactions with application data.
Using DAB to Check Our Product Inventory
Here’s a low-stock check against the ProductsDB application database through DAB:
“Show me all products with fewer than 50 units in stock, sorted by stock level. Which categories are most at risk of running out?”
Agent → products-db: read_records(entity: "Products",
filter: "UnitsInStock lt 50", orderby: "UnitsInStock asc")
← [
{ ProductName: "Standing Desk", Category: "Furniture", UnitsInStock: 15 },
{ ProductName: "Bookshelf", Category: "Furniture", UnitsInStock: 20 },
{ ProductName: "Air Purifier", Category: "Electronics", UnitsInStock: 25 },
{ ProductName: "Office Chair", Category: "Furniture", UnitsInStock: 30 }
]
Agent: "Furniture is the most at-risk category. 3 of the 4 low-stock products
are Furniture. Standing Desk is critically low at 15 units."
Summer sale discount (DAB)
“We’re running a summer sale. Apply a 15% discount to all Furniture products. Show me the before and after prices.”
Agent → products-db: read_records(entity: "Products", filter: "Category eq 'Furniture'")
← [Standing Desk $599.99, Bookshelf $149.99, Office Chair $299.99]
Agent → products-db: update_record(entity: "Products", id: 3, body: { UnitPrice: 509.99 })
Agent → products-db: update_record(entity: "Products", id: 8, body: { UnitPrice: 127.49 })
Agent → products-db: update_record(entity: "Products", id: 12, body: { UnitPrice: 254.99 })
Agent: "15% discount applied to all Furniture products:
- Standing Desk: $599.99 → $509.99
- Bookshelf: $149.99 → $127.49
- Office Chair: $299.99 → $254.99"
Notice that the agent never wrote an UPDATE statement. It read the records, calculated the new prices, and issued individual PATCH calls through the DAB REST layer. That’s controlled agentic access to user data, no direct SQL, no schema knowledge required from the caller.
You might be wondering where the guardrails are. That’s inside the DAB configuration. The dab-config.json file in this demo explicitly defines which tables, views, and other objects are exposed (Products, Categories, Orders, OrderDetails) and what actions are allowed on each. You could lock this down to read-only for certain entities, restrict updates to specific columns, or add authentication and role-based permissions. The agent never sees SQL. It just calls the MCP tool, and DAB enforces the rules.
Each entity includes a description field that tells the agent when and how to use it. That’s critical. The agent reads those descriptions at runtime to decide which tool to invoke. Good descriptions mean the agent picks the right entity for the job. Bad descriptions mean it guesses.
Shifting to DBA Diagnostics
That’s the application side. DAB handles natural-language queries against user databases. But what about operational visibility? That’s where the custom SQL MCP server comes in. It gives the agent direct access to DMVs, so instead of asking you to run a query, it can diagnose blocking, wait stats, missing indexes, and configuration drift on its own. Here’s what that looks like.
Here’s a blocking investigation using the custom SQL MCP server. I have an open transaction holding a lock with a WAITFOR. The agent identifies the head blocker, explains what it’s holding and why, and tells me exactly what to do about it:
“Are there any blocking sessions right now? Who is blocking whom, how long has the block been in place, and what SQL is running?”
Agent → get_blocking_chains(instance_name: "SqlServer1")
← {
blocked_session_id: 54,
blocking_session_id: 52,
wait_type: "LCK_M_S",
wait_seconds: 14.3,
blocker_sql_text: "BEGIN TRANSACTION;
UPDATE dbo.Products SET UnitPrice = UnitPrice * 1.01
WHERE Category = 'Electronics';
WAITFOR DELAY '00:05:00';"
}
Agent: "Session 52 is the head blocker. It opened a transaction, ran an UPDATE
on all Electronics rows, then entered a WAITFOR, holding an exclusive
lock for over 14 seconds. Two sessions are queued behind it. Kill session
52 with KILL 52 to unblock both, or wait for the WAITFOR to expire."
Multi-instance wait stats
Here’s a wait stats comparison across both SQL Servers in our test environment in one shot:
“Check wait stats on all SQL Server instances and tell me if there are any concerns.”
Agent → list_instances()
← ["SqlServer1", "SqlServer2"]
Agent → get_wait_stats(instance_name: "SqlServer1") [parallel]
Agent → get_wait_stats(instance_name: "SqlServer2") [parallel]
Agent: "SqlServer1 shows significant disk read pressure (PAGEIOLATCH_SH 42%)
and CPU contention (SOS_SCHEDULER_YIELD 18%), worth investigating
with get_file_io_stats and get_top_queries.
SqlServer2's dominant wait is ASYNC_NETWORK_IO at 61%, which usually
means a client is not consuming result sets fast enough. Check for
large unbounded SELECTs or a slow application tier."
Full health snapshot
My favorite so far is a full health snapshot, returned as a structured incident report ready to hand to a DBA. The agent calls get_server_info, get_database_info, get_wait_stats, get_top_queries, get_blocking_chains, and get_missing_indexes in sequence, synthesizes all of it, and flags configuration concerns (uncapped max server memory, MAXDOP=0, cost threshold too low) alongside query performance findings. That’s the kind of thing that used to take 20 minutes of manual and rigit DMV queries.
Wrapping Up
I built this to see what a properly instrumented AI agent could actually do with a real SQL Server problem. Not just write queries, but diagnose them. The 30 DMV-backed tools give agents enough visibility to identify blocking chains, spot expensive queries, find missing indexes, and flag configuration concerns, all from a single natural-language question.
Clone the repo, spin up the containers, and see what it does in your environment. The code is at github.com/nocentino/sql-mcp-server. Let me know how it works.