The Agent-Ready Database: Security, Backup, and MCP
Part 4 of 4 β The Multi-Model Database Series
This is the final post in a four-part series on multi-model databases in SQL Server 2025 and Azure SQL – exploring how the optimizer, storage engine, and security layer treat each data model as a first-class citizen under one roof.
In Part 1: The Polyglot Tax, we described the trajectory: you spin up a database, point an agent at it, and start building fast. The complexity comes later – JSON, graph, vectors, analytics – and each new requirement tempts you to spin up another database. In Part 2: When JSON Met Graph, we proved that native JSON types and graph MATCH syntax compile into a single execution plan alongside relational joins. In Part 3: Vectors, Analytics, and the End of ETL, we added DiskANN vector search and columnstore analytics – five data models, one stored procedure, one transaction boundary.
But here is where many “multi-model” stories stop. They show you the features and skip the hard parts – the parts that determine whether your architecture survives contact with production. Security across all those data models. Backup and recovery that is actually consistent. An API layer that agents can call without you hand-coding middleware for every endpoint.
Today we close the series with those hard parts. They are not as flashy as DiskANN or columnstore, but they are the reason you can actually trust a single-engine architecture at scale.
1 Unified Security: One Policy Across All Models
In Part 1 we counted five separate auth systems in a polyglot stack. Let us see what “one security model” actually looks like in practice.
1.1 Row-Level Security Across Data Models
Consider a multi-tenant SaaS application. Every table has a TenantID column. We want to guarantee that Tenant A never sees Tenant B’s data – regardless of which data model the query uses.
First, define the filter function:
CREATE FUNCTION dbo.fn_TenantFilter(@TenantID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_result
WHERE @TenantID = CAST(SESSION_CONTEXT(N'TenantID') AS INT);
This function checks whether the row’s TenantID matches the tenant ID stored in the session context. SESSION_CONTEXT is set at connection time by the application (or Data API Builder) – the user never controls it directly.
Now apply it across all tables, regardless of data model:
CREATE SECURITY POLICY TenantIsolation
ADD FILTER PREDICATE dbo.fn_TenantFilter(TenantID)
ON dbo.Customers, -- Relational
ADD FILTER PREDICATE dbo.fn_TenantFilter(TenantID)
ON dbo.Events, -- JSON data
ADD FILTER PREDICATE dbo.fn_TenantFilter(TenantID)
ON dbo.Relationships, -- Graph edges
ADD FILTER PREDICATE dbo.fn_TenantFilter(TenantID)
ON dbo.Embeddings -- Vector data
WITH (STATE = ON);
From this point forward, every query against these tables – relational joins, JSON path queries, graph traversals, vector similarity searches – is automatically filtered by tenant. The calling code does not need to include WHERE TenantID = @id in every query. The engine injects the filter predicate into the execution plan before any data leaves the storage engine.
Here is what this means for security audits. In a polyglot system, the auditor asks: “Prove that Tenant A cannot access Tenant B’s data.” You need to demonstrate this for each database independently – the relational database’s RLS, the document store’s field-level access control, the graph database’s node-level restrictions, and hope that your vector store supports metadata filtering. Each system has different configuration, different failure modes, different edge cases.
With a unified security model, the answer is one policy, one proof, one explanation. The filter predicate is compiled into the execution plan. It is not optional. It cannot be bypassed by switching to a different query syntax or data model.
1.2 Beyond RLS: Layered Permissions
Row-Level Security handles which rows a caller can see. But a unified security model goes further.
Stored procedures as the permission boundary. When stored procedures are the interface – as we recommend in Section 6 – you grant EXECUTE on those procedures and nothing else. The caller has no direct SELECT, INSERT, UPDATE, or DELETE permissions on the underlying tables. If an agent’s credentials are compromised, the attacker can only call the procedures you exposed, with the parameters those procedures accept. No ad-hoc queries. No schema discovery.
-- The agent can only call these procedures - nothing else
GRANT EXECUTE ON dbo.FraudCheck TO agent_fraud_checker;
GRANT EXECUTE ON dbo.GetCustomerContext TO agent_fraud_checker;
-- No SELECT, INSERT, UPDATE, DELETE on any table
Dynamic Data Masking protects sensitive columns – email addresses, phone numbers, account balances – from roles that should not see full values. Unlike RLS, which filters rows, masking returns the row but obscures column values. An analytics agent that needs aggregate patterns but not individual PII sees masked data by default.
Always Encrypted keeps sensitive columns encrypted end-to-end. The database engine never sees the plaintext. Even a DBA with full server access cannot read the data. For compliance-heavy workloads, this is the strongest guarantee the engine offers.
These layers compose. A single query can hit RLS (row filtering), Dynamic Data Masking (column obfuscation), and stored procedure permissions (surface restriction) simultaneously – all enforced by the engine, all configured in one place.
1.3 Testing It
-- Set context to Tenant 100
EXEC sp_set_session_context @key = N'TenantID', @value = 100;
SELECT * FROM Customers; -- Returns only Tenant 100 customers
SELECT * FROM Events; -- Returns only Tenant 100 events
SELECT * FROM Relationships; -- Returns only Tenant 100 graph edges
SELECT * FROM Embeddings; -- Returns only Tenant 100 vectors
-- Switch context to Tenant 200
EXEC sp_set_session_context @key = N'TenantID', @value = 200;
SELECT * FROM Customers; -- Returns only Tenant 200 customers
-- Same tables, same queries, different results. No code changes.
The security policy is invisible to the application. Your queries do not change. Your stored procedures do not change. The engine handles it.
2 Unified Backup and Recovery
-- One backup captures ALL data models to a consistent point
BACKUP DATABASE MultiModelApp
TO URL = 'https://storage.blob.core.windows.net/backups/MultiModelApp.bak'
WITH COMPRESSION, ENCRYPTION (
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupCert
);
-- One restore recovers ALL data models to a consistent point
RESTORE DATABASE MultiModelApp
FROM URL = 'https://storage.blob.core.windows.net/backups/MultiModelApp.bak'
WITH STOPAT = '2026-02-01 10:30:00';
The STOPAT clause is point-in-time recovery. The engine replays the transaction log to the specified moment, incorporating all operations – relational inserts, JSON updates, graph edge creations, vector inserts – into a single consistent state.
In a polyglot system, point-in-time recovery across five databases requires coordinating five separate restore operations and hoping that the timestamps line up. If your graph database restored to 10:30:00 but your relational database restored to 10:29:58, you have two seconds of inconsistency. For financial data, audit trails, and compliance-sensitive systems, this is not acceptable.
With one database, one transaction log, and one backup, the recovery is atomic.
3 Ledger Tables: Cryptographic Audit Trails
For regulated industries – finance, healthcare, government – “who changed what, when” needs to be tamper-evident. SQL Server’s ledger tables provide this:
-- Updatable ledger table: tracks all changes with cryptographic hashes
CREATE TABLE FinancialTransactions (
TransactionID INT PRIMARY KEY,
AccountID INT NOT NULL,
Amount MONEY NOT NULL,
TransactionType NVARCHAR(20),
Description NVARCHAR(500),
TransactionDate DATETIME2 DEFAULT SYSUTCDATETIME()
)
WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);
Every insert, update, and delete is recorded in a history table with a cryptographic hash chain. The chain ensures that if anyone tampers with historical records – even a DBA with full access – the verification check fails.
-- Verify nobody has tampered with the ledger
EXECUTE sp_verify_database_ledger_from_digest_storage;
For append-only audit logs where updates and deletes should be physically impossible:
CREATE TABLE AuditLog (
LogID INT IDENTITY PRIMARY KEY,
EventType NVARCHAR(50) NOT NULL,
EventData JSON,
UserName NVARCHAR(100) DEFAULT SUSER_SNAME(),
EventTime DATETIME2 DEFAULT SYSUTCDATETIME()
)
WITH (LEDGER = ON (APPEND_ONLY = ON));
An APPEND_ONLY ledger table rejects UPDATE and DELETE statements at the engine level. Combined with the JSON column for event data, this gives you a schema-flexible, tamper-evident audit trail – governed by the same RLS policies as everything else.
4 SQL MCP Server: From Database to Agent in 60 Seconds
We have established that the database engine can handle multiple data models with unified governance. The remaining question: how do applications and AI agents access it?
SQL MCP Server is a feature of Data API Builder (DAB) – Microsoft’s open-source engine that generates REST, GraphQL, and MCP endpoints directly from your database schema. No backend code. No ORM. No controller classes. It is the prescriptive approach for exposing enterprise databases to agents – and it solves problems that other database MCP implementations do not attempt.
4.1 Installation and Configuration
# Install DAB CLI (.NET tool)
dotnet tool install -g Microsoft.DataApiBuilder
# Initialize a configuration file
dab init --database-type mssql \
--connection-string "Server=localhost;Database=MultiModelApp;Trusted_Connection=true"
This creates a dab-config.json file. Let us add entities:
# Expose a table as REST + GraphQL + MCP
dab add Customer \
--source dbo.Customers \
--permissions "anonymous:read" \
--rest true \
--graphql true
# Expose a stored procedure (our multi-model query from Part 2)
dab add GetCustomerContext \
--source dbo.GetCustomerContext \
--source.type "stored-procedure" \
--source.params "customerID:,query:" \
--permissions "authenticated:execute" \
--rest.methods "get,post" \
--graphql.operation "query"
# Start the API server
dab start
You now have: – REST at http://localhost:5000/api/Customer – GraphQL at http://localhost:5000/graphql – MCP at http://localhost:5000/mcp for AI agents
No code generation. No deployment pipeline for a separate API layer. If your table schema changes, DAB picks up the changes on restart.
4.2 The Configuration File
Here is what DAB creates under the hood:
{
"data-source": {
"database-type": "mssql",
"connection-string": "@env('SQL_CONNECTION_STRING')"
},
"entities": {
"Customer": {
"source": {
"type": "table",
"object": "dbo.Customers"
},
"rest": { "enabled": true },
"graphql": {
"enabled": true,
"type": { "singular": "Customer", "plural": "Customers" }
},
"permissions": [
{ "role": "anonymous", "actions": ["read"] }
]
},
"GetCustomerContext": {
"source": {
"type": "stored-procedure",
"object": "dbo.GetCustomerContext",
"parameters": {
"customerID": "",
"query": ""
}
},
"rest": { "methods": ["GET", "POST"] },
"graphql": { "operation": "query" },
"permissions": [
{ "role": "authenticated", "actions": ["execute"] }
]
}
}
}
First, the connection string uses @env('SQL_CONNECTION_STRING') – it reads from an environment variable, not a plain-text config file. Configuration values can also reference Azure Key Vault secrets. Credentials never sit in a file that might be committed to source control.
Second, the permissions model distinguishes between anonymous and authenticated roles. The Customer entity allows anonymous reads (public catalog data). The GetCustomerContext procedure requires authentication. DAB supports Azure AD / Entra ID integration, so “authenticated” means the caller presented a valid JWT – which DAB validates before forwarding the request to SQL Server. The database-side RLS policy then filters the results by tenant. Two layers of defense, zero custom middleware.
4.3 How SQL MCP Server Exposes Your Data to Agents
SQL MCP Server takes a fundamentally different approach from the database MCP servers you will find on GitHub. Three design decisions matter.
Fixed, small tool set. Regardless of how many tables or procedures your database has, SQL MCP Server exposes exactly seven DML tools: describe_entities, create_record, read_records, update_record, delete_record, execute_entity, and aggregate_records. The agent’s context window is its thinking space. When dozens of tools compete for that space, reasoning quality drops. A fixed tool set keeps the context focused – the agent thinks first and accesses data second.
"runtime": {
"mcp": {
"enabled": true,
"path": "/mcp",
"dml-tools": {
"describe-entities": true,
"create-record": true,
"read-records": true,
"update-record": true,
"delete-record": true,
"execute-entity": true,
"aggregate-records": true
}
}
}
You can disable individual tools per deployment. A read-only analytics agent gets read_records and aggregate_records. A fraud-checker agent gets execute_entity to call dbo.FraudCheck. The blast-radius scoping from Section 6 extends to the MCP layer.
Entity abstraction – no raw schema exposure. The agent never sees dbo.Customers or dbo.TransactionHistory. It sees the entity names you defined in the configuration: Customer, GetCustomerContext. You can alias column names, hide sensitive fields per role, and add descriptions that guide agent behavior. The internal schema stays internal.
Deterministic query building (NL2DAB, not NL2SQL). SQL MCP Server intentionally does not support NL2SQL. Models are not deterministic, and complex queries are the likeliest to produce subtle errors – exactly the queries users hope AI can generate. Instead, when an agent calls read_records or aggregate_records, DAB’s built-in query builder produces well-formed T-SQL deterministically from the structured tool parameters. Same input, same query, every time. The risk, overhead, and unpredictability of NL2SQL disappear entirely.
4.4 Semantic Descriptions: Teaching Agents What Your Data Means
Without descriptions, an agent sees technical names like ProductID or dbo.Orders. With descriptions, the agent understands that ProductID is “Unique identifier for each product in the catalog” and the Orders entity contains “Customer purchase orders with line items and shipping details.” You can add descriptions at every level – entities, fields, and stored procedure parameters – using the DAB CLI.
This matters for three reasons: agents find the right entities faster (tool discovery), build better queries with proper context (query accuracy), and return only relevant fields (field selection). In our fraud detection system, describing dbo.FraudCheck as “Real-time fraud assessment combining transaction history, device fingerprint, social graph, vector similarity, and statistical baselines” tells the agent exactly when to use it.
4.5 Custom Tools: Stored Procedures as MCP Tools
For enterprises that want a bespoke MCP surface rather than a generic CRUD interface, SQL MCP Server supports promoting stored procedures as custom tools. You turn off the built-in DML tools and expose only your human-reviewed procedures. Our dbo.FraudCheck and dbo.GetCustomerContext become the only tools the agent can call – each with typed parameters, descriptions, and role-based permissions. This is the tightest possible contract between an agent and your data.
4.6 Caching and Monitoring
SQL MCP Server automatically caches results from read_records. Both L1 (in-memory) and L2 (Redis / Azure Managed Redis) caching are supported, configurable per entity. For agent workloads that repeatedly query the same reference data – product catalogs, customer profiles, baseline statistics – caching reduces database load and prevents request stampedes.
For observability, SQL MCP Server emits logs and telemetry to Azure Log Analytics, Application Insights, and OpenTelemetry endpoints. Combined with the observability patterns in Section 6, you get end-to-end visibility: which agent called which MCP tool, which T-SQL was generated, how long it took, and what the execution plan looked like.
5 The Multi-Model Stored Procedure
In Part 2 and Part 3 we built queries that combined individual data models. Here is a complete stored procedure that an AI agent calls via DAB to get full customer context in a single round-trip:
CREATE PROCEDURE dbo.GetCustomerContext
@customerID INT,
@query NVARCHAR(MAX) = NULL
AS
BEGIN
SELECT
-- Relational: Core customer data
c.Name,
c.Email,
c.AccountStatus,
c.LifetimeValue,
-- JSON: Recent interactions (schema-flexible)
(
SELECT TOP 5
JSON_VALUE(i.Data, '$.channel') AS Channel,
JSON_VALUE(i.Data, '$.sentiment') AS Sentiment,
i.Timestamp
FROM Interactions i
WHERE i.CustomerID = @customerID
ORDER BY i.Timestamp DESC
FOR JSON PATH
) AS RecentInteractions,
-- Graph: Connected high-value customers
(
SELECT p2.Name, p2.LifetimeValue
FROM Customers c2, Knows k, Customers p2
WHERE MATCH(c2-(k)->p2)
AND c2.CustomerID = @customerID
AND p2.LifetimeValue > 10000
FOR JSON PATH
) AS HighValueConnections,
-- Vector: Similar past support tickets
(
SELECT TOP 3
t.Resolution,
t.SatisfactionScore
FROM SupportTickets t
WHERE @query IS NOT NULL
ORDER BY VECTOR_DISTANCE('cosine', t.Embedding,
(SELECT embedding FROM dbo.GetEmbedding(@query)))
FOR JSON PATH
) AS SimilarTickets
FROM Customers c
WHERE c.CustomerID = @customerID;
END;
Here is what happens when an agent calls this via SQL MCP Server.
- The agent discovers
GetCustomerContext through the describe_entities tool – the description tells it this procedure returns full customer context
- The agent calls
execute_entity with {"entity": "GetCustomerContext", "parameters": {"customerID": 123, "query": "shipping delay"}}
- SQL MCP Server validates the JWT, extracts the tenant ID, and sets
SESSION_CONTEXT
- DAB generates the
EXEC dbo.GetCustomerContext call deterministically from the typed parameters – no NL2SQL, no prompt-to-query translation
- The engine executes one query plan that touches relational tables, JSON documents, graph edges, and vector indexes
- RLS filters all results by tenant – the agent only sees data its user is authorized to see
- The procedure returns a single JSON response with all four data models represented
- The agent receives complete context in one round-trip: ~10β50ms
Compare this to the polyglot workflow from Part 1: 5 API calls, 5 auth contexts, 5 failure modes, 200β500ms. The multi-model version is not just faster – it reduces the agent’s cognitive burden. Fewer tool calls means less opportunity for the model to hallucinate, lose context, or retry in unexpected ways.
6 When the Caller is an Agent
The stored procedures above work. But traditional database design assumes a deterministic, human-reviewed caller. Agents violate that assumption at every layer. They reason their way to queries, retry on failure, and hold connections while they think. The patterns needed to handle this – statement timeouts, append-only logs, idempotency keys, blast-radius scoping, query tagging – are not new technology. They are existing patterns elevated from “nice to have” to “load-bearing infrastructure.”
Stored procedures as the API surface
The strongest defense against unpredictable agent queries is to never let agents write arbitrary SQL. The dbo.FraudCheck procedure from Part 3 is the agent’s API surface. The agent calls execute_entity with typed parameters, and SQL MCP Server generates the EXEC dbo.FraudCheck @PersonID = 3, @IncomingAmount = 9200 deterministically – not a five-table join the model reasoned into existence. This is the NL2DAB approach in practice: the agent expresses intent through structured tool calls, and DAB’s query builder produces the SQL. No prompt-to-SQL translation. No hallucinated table names. The “non-deterministic caller” problem stops mattering when the callable surface is a set of human-reviewed procedures exposed through a deterministic query builder.
Resource isolation
Agent workloads are unpredictable – a fraud-check burst can spike CPU while customer-facing checkout queries need consistent latency. Resource Governor creates workload groups with CPU, memory, and I/O ceilings:
-- Separate agent traffic from human-facing application queries
CREATE RESOURCE POOL AgentPool WITH (CAP_CPU_PERCENT = 30);
CREATE WORKLOAD GROUP AgentWorkload
USING AgentPool;
-- Classify agent sessions into the workload group
CREATE FUNCTION dbo.AgentClassifier() RETURNS SYSNAME
WITH SCHEMABINDING AS
BEGIN
RETURN CASE
WHEN SUSER_NAME() LIKE 'agent_%' THEN 'AgentWorkload'
ELSE 'default'
END;
END;
An agent running fraud checks that consumes excessive CPU hits the pool ceiling. Customer-facing checkout queries continue in their own pool, unaffected. This is enforced by the engine, not by the caller.
Idempotency and audit
Agents retry by design. Every orchestration framework uses at-least-once delivery. An agent that gets a timeout on a fraud decision might re-issue the same call. Temporal tables provide automatic history tracking for every row change:
-- Fraud decisions table with temporal history and idempotency
CREATE TABLE FraudDecisions (
DecisionID INT IDENTITY PRIMARY KEY,
TxnID INT NOT NULL,
PersonID INT NOT NULL,
Decision NVARCHAR(20) NOT NULL, -- 'APPROVED', 'DENIED', 'REVIEW'
DecidedBy NVARCHAR(100) NOT NULL, -- 'agent:fraud-checker-v2' or 'user:analyst-jane'
IdempotencyKey NVARCHAR(64) NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
CONSTRAINT UQ_FraudDecision_Idempotency UNIQUE (IdempotencyKey)
) WITH (SYSTEM_VERSIONING = ON);
Every change is recorded in a history table automatically. “Show me everything the anti-fraud agent changed in the last hour” is a built-in temporal query. The UNIQUE constraint on IdempotencyKey handles retries: the second write with the same key gets a constraint violation, and the agent treats it as “already processed.”
Blast radius scoping
The question is not “what does this agent need?” but “what is the worst case if this agent’s reasoning goes wrong?” In the fraud system, this translates directly:
CREATE ROLE agent_fraud_checker;
CREATE ROLE agent_analytics;
-- Fraud checker agent: can read everything it needs, can only write decisions
GRANT EXECUTE ON dbo.FraudCheck TO agent_fraud_checker;
GRANT INSERT ON FraudDecisions TO agent_fraud_checker;
-- Cannot modify Person.RiskScore, cannot delete transactions, cannot see PII
-- Analytics agent: read-only against the columnstore
GRANT SELECT ON TransactionHistory TO agent_analytics;
GRANT SELECT ON Person TO agent_analytics;
-- Cannot write anything
Row-Level Security (from Section 1) extends this further. A policy filters which persons or accounts the agent can see. The agent does not know the filter exists. It calls dbo.FraudCheck and gets back only the data it is authorized to see.
Query observability
When an agent issues a slow fraud check, you need to know which agent, which task, and which reasoning step produced it. Two layers work together.
At the application layer, SQL MCP Server emits structured telemetry – including agent identity and request metadata – to Azure Log Analytics, Application Insights, and OpenTelemetry endpoints. Session context tags set at connection time provide correlation IDs that flow through DAB’s logging pipeline:
-- Agent sets context at connection time
EXEC sp_set_session_context @key = N'agent_id', @value = N'fraud-checker-v2';
EXEC sp_set_session_context @key = N'task_id', @value = N'txn-review-9200';
At the database layer, Query Store captures every stored procedure execution with runtime stats and execution plans. When agent workloads funnel through a fixed set of stored procedures (as described above), identifying problematic queries is straightforward – Query Store tells you which executions were slow and why the optimizer chose that plan.
The pattern
None of these are add-ons. Temporal tables replace hand-built audit logs. Resource Governor replaces hand-built connection pool isolation. Row-Level Security replaces hand-built authorization filters. Query Store replaces hand-built query monitoring. The database was designed with these safeguards for human callers. For agentic callers, you activate them rather than invent them.
7 Reference Architecture: E-Commerce Platform
Here is what the full architecture looks like in production.
Azure SQL Database (Multi-Model Core)
βββ Relational: Customers, Orders, Inventory, Payments
βββ JSON: Product specs, event logs, user preferences, feature flags
βββ Graph: Customer networks, fraud rings, product relationships
βββ Vector (DiskANN): Product search, support ticket similarity, recommendations
βββ Columnstore: Sales analytics, inventory forecasting, customer segmentation
Data API Builder
βββ REST: Mobile/web apps, partner integrations
βββ GraphQL: Frontend teams, flexible queries
βββ MCP: AI agents (Claude, GPT, custom)
Governance (applied uniformly)
βββ Row-Level Security: Tenant isolation across all models
βββ Dynamic Data Masking: PII protection in API responses
βββ Always Encrypted: Client-side encryption for sensitive columns
βββ Ledger Tables: Tamper-evident audit trail
βββ TDE: Encryption at rest
βββ Unified Audit: Single log for all data access
Recommendation Engine
Here is a product recommendation procedure that uses all five data models:
CREATE PROCEDURE dbo.GetProductRecommendations
@customerID INT,
@limit INT = 10
AS
BEGIN
WITH CustomerProfile AS (
-- Graph: What do customers in this person's network buy?
SELECT
oi.ProductID,
COUNT(*) * 0.3 AS GraphScore
FROM Customers c1, Similar s, Customers c2
JOIN Orders o ON c2.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
WHERE MATCH(c1-(s)->c2)
AND c1.CustomerID = @customerID
GROUP BY oi.ProductID
),
VectorSimilarity AS (
-- Vector: Products semantically similar to recent purchases
SELECT
p.ProductID,
MIN(VECTOR_DISTANCE('cosine', p.Embedding, recent.Embedding)) * 0.4 AS VectorScore
FROM Products p
CROSS JOIN (
SELECT TOP 3 pr.Embedding
FROM OrderItems oi
JOIN Orders o ON oi.OrderID = o.OrderID
JOIN Products pr ON oi.ProductID = pr.ProductID
WHERE o.CustomerID = @customerID
ORDER BY o.OrderDate DESC
) recent
GROUP BY p.ProductID
),
TrendingProducts AS (
-- Columnstore/Analytics: What is trending this week?
SELECT
ProductID,
PERCENT_RANK() OVER (ORDER BY SUM(Quantity)) * 0.2 AS TrendScore
FROM SalesHistory
WHERE SaleDate > DATEADD(DAY, -7, GETUTCDATE())
GROUP BY ProductID
)
SELECT TOP (@limit)
p.ProductID,
p.Name,
JSON_VALUE(p.Specs, '$.shortDescription') AS Description, -- JSON
p.Price,
COALESCE(cp.GraphScore, 0) +
COALESCE(vs.VectorScore, 0) +
COALESCE(tp.TrendScore, 0) AS TotalScore
FROM Products p
LEFT JOIN CustomerProfile cp ON p.ProductID = cp.ProductID
LEFT JOIN VectorSimilarity vs ON p.ProductID = vs.ProductID
LEFT JOIN TrendingProducts tp ON p.ProductID = tp.ProductID
WHERE p.InStock = 1
ORDER BY TotalScore DESC;
END;
One procedure. Five data models. One execution plan. One transaction. One security boundary. One backup captures the entire state.
8 Getting Started
Everything in this series runs on free-tier infrastructure.
Option 1: SQL Server 2025 (On-Premises, Free)
SQL Server 2025 Express includes all multi-model features: native JSON type, graph tables, vector search, columnstore indexes. Up to 10 GB per database. No license cost.
# Install via winget
winget install Microsoft.SQLServer.2025.Express
Option 2: Azure SQL Database (Cloud, Free Tier)
Azure SQL Database Free Offer – 100,000 vCore-seconds per month. Full cloud-managed experience with automatic backups and high availability.
Data API Builder (Free, Open Source)
dotnet tool install -g Microsoft.DataApiBuilder
dab init --database-type mssql --connection-string "YOUR_CONNECTION_STRING"
dab add Customer --source dbo.Customers --permissions "anonymous:read"
dab start
Sixty seconds from install to a running API with REST, GraphQL, and MCP endpoints.
Series Summary
Across four posts, we traced a single fraud detection scenario from architectural problem to production-ready solution:
- Part 1: The Polyglot Tax – Five databases, five auth systems, five backup schedules, five things that break at 2 AM. We measured the compounding costs of polyglot persistence: network latency, saga patterns for consistency, exploding security surface area, operational overhead, and developer cognitive load. The tax is not any single cost – it is all of them multiplied together.
- Part 2: When JSON Met Graph – Native
JSON type with pre-parsed binary storage and indexed paths replaced NVARCHAR(MAX) string parsing. Graph MATCH syntax compiled to the same join infrastructure the optimizer has refined for three decades. One query combined relational joins, JSON path extraction, and graph traversal in a single execution plan. Two databases eliminated.
- Part 3: Vectors, Analytics, and the End of ETL – DiskANN delivered billion-scale vector search on commodity storage. The pre-filter pattern – where relational predicates narrow 10 million rows to 3,000 before any distance computation – is an optimization that only works when vectors and relational metadata share the same engine. Columnstore achieved 60x analytical speedups through compression, column elimination, batch mode, and segment elimination. Two more databases eliminated.
- Part 4 (this post) – Row-level security applied uniformly across all five data models. Point-in-time backup and recovery as a single atomic operation. SQL MCP Server generating REST, GraphQL, and MCP endpoints without custom code – with entity abstraction, deterministic query building, and a fixed tool set that keeps the agent’s context window focused. Agent-hardening patterns – stored procedures as API surfaces, resource isolation, idempotency, blast-radius scoping – built from primitives the engine already has.
So What?
In Part 1, you were building fast. An agent, a database, a working app by lunch. Then the complexity arrived – JSON schemas that evolve weekly, graph traversals for fraud detection, semantic search for product discovery, real-time dashboards over millions of rows – and the conventional answer was to spin up a new database for each one. Five databases. Five auth systems. Five backup schedules. Five things that can break while you sleep.
Across the next three posts, we dismantled that assumption:
- Part 2: JSON and graph live inside the same engine, compiled into the same execution plans, optimized by the same query planner. Two databases eliminated.
- Part 3: DiskANN provides web-scale vector search with hybrid filtering that external vector databases cannot replicate. Columnstore delivers 60x analytical speedups through physics, not tuning. Two more databases eliminated.
- Part 4 (this post): One row-level security policy across all five data models. One backup to a consistent point in time. SQL MCP Server generating REST, GraphQL, and MCP endpoints with entity abstraction, deterministic query building, and a fixed seven-tool surface that keeps agents focused. Agent-hardening patterns that use the engine’s existing primitives – not new inventions.
Five databases consolidated to one. Five auth systems to one. Five backup schedules to one.
For the agentic world, this is not an incremental improvement – it is a structural one. Your agent makes one call, gets one response, through one auth context, in under 50ms. No orchestrating between services. No hoping that five databases all return consistent data. No writing fallback logic because one of them is slow.
The case is not that SQL Server is the only database you will ever need. There are workloads – petabyte-scale OLAP, real-time streaming with sub-millisecond latency, specialized time-series at extreme ingest rates – where purpose-built systems justify their operational overhead.
The case is this: the database should never be the reason you slow down. Your agent is fast. Your iteration cycle is fast. Your architecture should keep up. For the majority of application workloads, a multi-model engine removes the tax entirely – one database, one optimizer, one security model, one backup, one API.
The math from Part 1 still holds: 1 Γ 1 Γ 1 = 1, not 5 Γ 5 Γ 5 = 125.
Start with the free tier. Run the scripts. Point an agent at it. Build fast – and keep building fast.
The Full Series
- Part 1: The Polyglot Tax – The cost of running five databases for one application
- Part 2: When JSON Met Graph – Native JSON, graph MATCH, and cross-model execution plans
- Part 3: Vectors, Analytics, and the End of ETL – DiskANN vector search, columnstore analytics, and the five-model fraud check
- Part 4: The Agent-Ready Database (this post) – Security, backup, MCP, Data API Builder, and agent-ready architecture
Further Reading: – Azure SQL Database Documentation – JSON in SQL Server – Graph Processing in SQL Server – Vector Search in SQL Server – Columnstore Indexes – Data API Builder – SQL MCP Server – Row-Level Security – Ledger Tables
The post The Polyglot tax β Part 4 appeared first on Azure SQL Dev Corner.
Log in
Bun support for Vite apps
Container changes that actually help
Better debugging and fewer build paper cuts
The bigger JavaScript picture
The bottom line
Learn more: Read the docs on 
Give us feedback: We’d love to hear what you think β file issues or join discussions on the 
