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.<\/em><\/p>\n In Part 1: The Polyglot Tax<\/a>, 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<\/a>, we proved that native JSON types and graph 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.<\/p>\n 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.<\/p>\n In Part 1<\/a> we counted five separate auth systems in a polyglot stack. Let us see what “one security model” actually looks like in practice.<\/p>\n Consider a multi-tenant SaaS application. Every table has a First, define the filter function:<\/p>\n This function checks whether the row’s Now apply it across all tables, regardless of data model:<\/p>\n 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 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.<\/p>\n 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.<\/p>\n Row-Level Security handles which rows<\/em> a caller can see. But a unified security model goes further.<\/p>\n Stored procedures as the permission boundary.<\/strong> When stored procedures are the interface – as we recommend in Section 6 – you grant Dynamic Data Masking<\/strong> 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.<\/p>\n Always Encrypted<\/strong> 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.<\/p>\n 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.<\/p>\n The security policy is invisible to the application. Your queries do not change. Your stored procedures do not change. The engine handles it.<\/p>\n The 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.<\/p>\n With one database, one transaction log, and one backup, the recovery is atomic.<\/p>\n For regulated industries – finance, healthcare, government – “who changed what, when” needs to be tamper-evident. SQL Server’s ledger tables provide this:<\/p>\n 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.<\/p>\n For append-only audit logs where updates and deletes should be physically impossible:<\/p>\n An 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?<\/p>\n SQL MCP Server<\/strong><\/a> is a feature of Data API Builder (DAB)<\/strong> – 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.<\/p>\n This creates a You now have: – REST at No code generation. No deployment pipeline for a separate API layer. If your table schema changes, DAB picks up the changes on restart.<\/p>\n Here is what DAB creates under the hood:<\/p>\n First<\/strong>, the connection string uses Second<\/strong>, the permissions model distinguishes between SQL MCP Server takes a fundamentally different approach from the database MCP servers you will find on GitHub. Three design decisions matter.<\/p>\n Fixed, small tool set.<\/strong> Regardless of how many tables or procedures your database has, SQL MCP Server exposes exactly seven DML tools: You can disable individual tools per deployment. A read-only analytics agent gets Entity abstraction – no raw schema exposure.<\/strong> The agent never sees Deterministic query building (NL2DAB, not NL2SQL).<\/strong> 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 Without descriptions, an agent sees technical names like 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 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 SQL MCP Server automatically caches results from 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.<\/p>\n In Part 2<\/a> and Part 3<\/a> 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:<\/p>\n Here is what happens when an agent calls this via SQL MCP Server.<\/p>\n Compare this to the polyglot workflow from Part 1<\/a>: 5 API calls, 5 auth contexts, 5 failure modes, 200\u2013500ms. 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.<\/p>\n 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.”<\/p>\n The strongest defense against unpredictable agent queries is to never let agents write arbitrary SQL. The 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:<\/p>\n 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.<\/p>\n 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:<\/p>\n 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 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:<\/p>\n 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 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.<\/p>\n 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:<\/p>\n 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.<\/p>\n 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.<\/p>\n Here is what the full architecture looks like in production.<\/p>\n Here is a product recommendation procedure that uses all five data models:<\/p>\n One procedure. Five data models. One execution plan. One transaction. One security boundary. One backup captures the entire state.<\/p>\n Everything in this series runs on free-tier infrastructure.<\/p>\n SQL Server 2025 Express<\/a> includes all multi-model features: native JSON type, graph tables, vector search, columnstore indexes. Up to 10 GB per database. No license cost.<\/p>\n Azure SQL Database Free Offer<\/a> – 100,000 vCore-seconds per month. Full cloud-managed experience with automatic backups and high availability.<\/p>\n Sixty seconds from install to a running API with REST, GraphQL, and MCP endpoints.<\/p>\nMATCH<\/code> syntax compile into a single execution plan alongside relational joins. In Part 3: Vectors, Analytics, and the End of ETL<\/a>, we added DiskANN vector search and columnstore analytics – five data models, one stored procedure, one transaction boundary.<\/p>\n1 Unified Security: One Policy Across All Models<\/h2>\n
1.1 Row-Level Security Across Data Models<\/h3>\n
TenantID<\/code> column. We want to guarantee that Tenant A never sees Tenant B’s data – regardless of which data model the query uses.<\/p>\nCREATE FUNCTION dbo.fn_TenantFilter(@TenantID INT)\nRETURNS TABLE\nWITH SCHEMABINDING\nAS\nRETURN SELECT 1 AS fn_result\nWHERE @TenantID = CAST(SESSION_CONTEXT(N'TenantID') AS INT);\n<\/code><\/pre>\nTenantID<\/code> matches the tenant ID stored in the session context. SESSION_CONTEXT<\/code> is set at connection time by the application (or Data API Builder) – the user never controls it directly.<\/p>\nCREATE SECURITY POLICY TenantIsolation\nADD FILTER PREDICATE dbo.fn_TenantFilter(TenantID)\n ON dbo.Customers, -- Relational\nADD FILTER PREDICATE dbo.fn_TenantFilter(TenantID)\n ON dbo.Events, -- JSON data\nADD FILTER PREDICATE dbo.fn_TenantFilter(TenantID)\n ON dbo.Relationships, -- Graph edges\nADD FILTER PREDICATE dbo.fn_TenantFilter(TenantID)\n ON dbo.Embeddings -- Vector data\nWITH (STATE = ON);\n<\/code><\/pre>\nWHERE TenantID = @id<\/code> in every query. The engine injects the filter predicate into the execution plan before any data leaves the storage engine.<\/p>\n1.2 Beyond RLS: Layered Permissions<\/h3>\n
EXECUTE<\/code> on those procedures and nothing else. The caller has no direct SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, or DELETE<\/code> 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.<\/p>\n-- The agent can only call these procedures - nothing else\nGRANT EXECUTE ON dbo.FraudCheck TO agent_fraud_checker;\nGRANT EXECUTE ON dbo.GetCustomerContext TO agent_fraud_checker;\n-- No SELECT, INSERT, UPDATE, DELETE on any table\n<\/code><\/pre>\n1.3 Testing It<\/h3>\n
-- Set context to Tenant 100\nEXEC sp_set_session_context @key = N'TenantID', @value = 100;\n\nSELECT * FROM Customers; -- Returns only Tenant 100 customers\nSELECT * FROM Events; -- Returns only Tenant 100 events\nSELECT * FROM Relationships; -- Returns only Tenant 100 graph edges\nSELECT * FROM Embeddings; -- Returns only Tenant 100 vectors\n\n-- Switch context to Tenant 200\nEXEC sp_set_session_context @key = N'TenantID', @value = 200;\n\nSELECT * FROM Customers; -- Returns only Tenant 200 customers\n-- Same tables, same queries, different results. No code changes.\n<\/code><\/pre>\n2 Unified Backup and Recovery<\/h2>\n
-- One backup captures ALL data models to a consistent point\nBACKUP DATABASE MultiModelApp\nTO URL = 'https:\/\/storage.blob.core.windows.net\/backups\/MultiModelApp.bak'\nWITH COMPRESSION, ENCRYPTION (\n ALGORITHM = AES_256,\n SERVER CERTIFICATE = BackupCert\n);\n\n-- One restore recovers ALL data models to a consistent point\nRESTORE DATABASE MultiModelApp\nFROM URL = 'https:\/\/storage.blob.core.windows.net\/backups\/MultiModelApp.bak'\nWITH STOPAT = '2026-02-01 10:30:00';\n<\/code><\/pre>\nSTOPAT<\/code> 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.<\/p>\n3 Ledger Tables: Cryptographic Audit Trails<\/h2>\n
-- Updatable ledger table: tracks all changes with cryptographic hashes\nCREATE TABLE FinancialTransactions (\n TransactionID INT PRIMARY KEY,\n AccountID INT NOT NULL,\n Amount MONEY NOT NULL,\n TransactionType NVARCHAR(20),\n Description NVARCHAR(500),\n TransactionDate DATETIME2 DEFAULT SYSUTCDATETIME()\n)\nWITH (SYSTEM_VERSIONING = ON, LEDGER = ON);\n<\/code><\/pre>\n-- Verify nobody has tampered with the ledger\nEXECUTE sp_verify_database_ledger_from_digest_storage;\n<\/code><\/pre>\nCREATE TABLE AuditLog (\n LogID INT IDENTITY PRIMARY KEY,\n EventType NVARCHAR(50) NOT NULL,\n EventData JSON,\n UserName NVARCHAR(100) DEFAULT SUSER_SNAME(),\n EventTime DATETIME2 DEFAULT SYSUTCDATETIME()\n)\nWITH (LEDGER = ON (APPEND_ONLY = ON));\n<\/code><\/pre>\nAPPEND_ONLY<\/code> ledger table rejects UPDATE<\/code> and DELETE<\/code> 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.<\/p>\n4 SQL MCP Server: From Database to Agent in 60 Seconds<\/h2>\n
4.1 Installation and Configuration<\/h3>\n
# Install DAB CLI (.NET tool)\ndotnet tool install -g Microsoft.DataApiBuilder\n\n# Initialize a configuration file\ndab init --database-type mssql \\\n --connection-string \"Server=localhost;Database=MultiModelApp;Trusted_Connection=true\"\n<\/code><\/pre>\ndab-config.json<\/code> file. Let us add entities:<\/p>\n# Expose a table as REST + GraphQL + MCP\ndab add Customer \\\n --source dbo.Customers \\\n --permissions \"anonymous:read\" \\\n --rest true \\\n --graphql true\n\n# Expose a stored procedure (our multi-model query from Part 2)\ndab add GetCustomerContext \\\n --source dbo.GetCustomerContext \\\n --source.type \"stored-procedure\" \\\n --source.params \"customerID:,query:\" \\\n --permissions \"authenticated:execute\" \\\n --rest.methods \"get,post\" \\\n --graphql.operation \"query\"\n<\/code><\/pre>\n# Start the API server\ndab start\n<\/code><\/pre>\nhttp:\/\/localhost:5000\/api\/Customer<\/code> – GraphQL at http:\/\/localhost:5000\/graphql<\/code> – MCP at http:\/\/localhost:5000\/mcp<\/code> for AI agents<\/p>\n4.2 The Configuration File<\/h3>\n
{\n \"data-source\": {\n \"database-type\": \"mssql\",\n \"connection-string\": \"@env('SQL_CONNECTION_STRING')\"\n },\n \"entities\": {\n \"Customer\": {\n \"source\": {\n \"type\": \"table\",\n \"object\": \"dbo.Customers\"\n },\n \"rest\": { \"enabled\": true },\n \"graphql\": {\n \"enabled\": true,\n \"type\": { \"singular\": \"Customer\", \"plural\": \"Customers\" }\n },\n \"permissions\": [\n { \"role\": \"anonymous\", \"actions\": [\"read\"] }\n ]\n },\n \"GetCustomerContext\": {\n \"source\": {\n \"type\": \"stored-procedure\",\n \"object\": \"dbo.GetCustomerContext\",\n \"parameters\": {\n \"customerID\": \"\",\n \"query\": \"\"\n }\n },\n \"rest\": { \"methods\": [\"GET\", \"POST\"] },\n \"graphql\": { \"operation\": \"query\" },\n \"permissions\": [\n { \"role\": \"authenticated\", \"actions\": [\"execute\"] }\n ]\n }\n }\n}\n<\/code><\/pre>\n@env('SQL_CONNECTION_STRING')<\/code> – 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.<\/p>\nanonymous<\/code> and authenticated<\/code> roles. The Customer<\/code> entity allows anonymous reads (public catalog data). The GetCustomerContext<\/code> 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.<\/p>\n4.3 How SQL MCP Server Exposes Your Data to Agents<\/h3>\n
describe_entities<\/code>, create_record<\/code>, read_records<\/code>, update_record<\/code>, delete_record<\/code>, execute_entity<\/code>, and aggregate_records<\/code>. 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.<\/p>\n\"runtime\": {\n \"mcp\": {\n \"enabled\": true,\n \"path\": \"\/mcp\",\n \"dml-tools\": {\n \"describe-entities\": true,\n \"create-record\": true,\n \"read-records\": true,\n \"update-record\": true,\n \"delete-record\": true,\n \"execute-entity\": true,\n \"aggregate-records\": true\n }\n }\n}\n<\/code><\/pre>\nread_records<\/code> and aggregate_records<\/code>. A fraud-checker agent gets execute_entity<\/code> to call dbo.FraudCheck<\/code>. The blast-radius scoping from Section 6 extends to the MCP layer.<\/p>\ndbo.Customers<\/code> or dbo.TransactionHistory<\/code>. It sees the entity names you defined in the configuration: Customer<\/code>, GetCustomerContext<\/code>. You can alias column names, hide sensitive fields per role, and add descriptions that guide agent behavior. The internal schema stays internal.<\/p>\nread_records<\/code> or aggregate_records<\/code>, 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.<\/p>\n4.4 Semantic Descriptions: Teaching Agents What Your Data Means<\/h3>\n
ProductID<\/code> or dbo.Orders<\/code>. With descriptions, the agent understands that ProductID<\/code> is “Unique identifier for each product in the catalog” and the Orders<\/code> 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.<\/p>\ndbo.FraudCheck<\/code> 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.<\/p>\n4.5 Custom Tools: Stored Procedures as MCP Tools<\/h3>\n
dbo.FraudCheck<\/code> and dbo.GetCustomerContext<\/code> become the only<\/em> 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.<\/p>\n4.6 Caching and Monitoring<\/h3>\n
read_records<\/code>. 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.<\/p>\n5 The Multi-Model Stored Procedure<\/h2>\n
CREATE PROCEDURE dbo.GetCustomerContext\n @customerID INT,\n @query NVARCHAR(MAX) = NULL\nAS\nBEGIN\n SELECT \n -- Relational: Core customer data\n c.Name,\n c.Email,\n c.AccountStatus,\n c.LifetimeValue,\n\n -- JSON: Recent interactions (schema-flexible)\n (\n SELECT TOP 5 \n JSON_VALUE(i.Data, '$.channel') AS Channel,\n JSON_VALUE(i.Data, '$.sentiment') AS Sentiment,\n i.Timestamp\n FROM Interactions i\n WHERE i.CustomerID = @customerID\n ORDER BY i.Timestamp DESC\n FOR JSON PATH\n ) AS RecentInteractions,\n\n -- Graph: Connected high-value customers\n (\n SELECT p2.Name, p2.LifetimeValue\n FROM Customers c2, Knows k, Customers p2\n WHERE MATCH(c2-(k)->p2)\n AND c2.CustomerID = @customerID\n AND p2.LifetimeValue > 10000\n FOR JSON PATH\n ) AS HighValueConnections,\n\n -- Vector: Similar past support tickets\n (\n SELECT TOP 3\n t.Resolution,\n t.SatisfactionScore\n FROM SupportTickets t\n WHERE @query IS NOT NULL\n ORDER BY VECTOR_DISTANCE('cosine', t.Embedding, \n (SELECT embedding FROM dbo.GetEmbedding(@query)))\n FOR JSON PATH\n ) AS SimilarTickets\n\n FROM Customers c\n WHERE c.CustomerID = @customerID;\nEND;\n<\/code><\/pre>\n\n
GetCustomerContext<\/code> through the describe_entities<\/code> tool – the description tells it this procedure returns full customer context<\/li>\nexecute_entity<\/code> with {\"entity\": \"GetCustomerContext\", \"parameters\": {\"customerID\": 123, \"query\": \"shipping delay\"}}<\/code><\/li>\nSESSION_CONTEXT<\/code><\/li>\nEXEC dbo.GetCustomerContext<\/code> call deterministically from the typed parameters – no NL2SQL, no prompt-to-query translation<\/li>\n6 When the Caller is an Agent<\/h2>\n
Stored procedures as the API surface<\/h3>\n
dbo.FraudCheck<\/code> procedure from Part 3<\/a> is<\/em> the agent’s API surface. The agent calls execute_entity<\/code> with typed parameters, and SQL MCP Server generates the EXEC dbo.FraudCheck @PersonID = 3, @IncomingAmount = 9200<\/code> 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.<\/p>\nResource isolation<\/h3>\n
-- Separate agent traffic from human-facing application queries\nCREATE RESOURCE POOL AgentPool WITH (CAP_CPU_PERCENT = 30);\nCREATE WORKLOAD GROUP AgentWorkload\n USING AgentPool;\n\n-- Classify agent sessions into the workload group\nCREATE FUNCTION dbo.AgentClassifier() RETURNS SYSNAME\nWITH SCHEMABINDING AS\nBEGIN\n RETURN CASE \n WHEN SUSER_NAME() LIKE 'agent_%' THEN 'AgentWorkload' \n ELSE 'default' \n END;\nEND;\n<\/code><\/pre>\nIdempotency and audit<\/h3>\n
-- Fraud decisions table with temporal history and idempotency\nCREATE TABLE FraudDecisions (\n DecisionID INT IDENTITY PRIMARY KEY,\n TxnID INT NOT NULL,\n PersonID INT NOT NULL,\n Decision NVARCHAR(20) NOT NULL, -- 'APPROVED', 'DENIED', 'REVIEW'\n DecidedBy NVARCHAR(100) NOT NULL, -- 'agent:fraud-checker-v2' or 'user:analyst-jane'\n IdempotencyKey NVARCHAR(64) NULL,\n ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,\n ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,\n PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),\n CONSTRAINT UQ_FraudDecision_Idempotency UNIQUE (IdempotencyKey)\n) WITH (SYSTEM_VERSIONING = ON);\n<\/code><\/pre>\nUNIQUE<\/code> constraint on IdempotencyKey<\/code> handles retries: the second write with the same key gets a constraint violation, and the agent treats it as “already processed.”<\/p>\nBlast radius scoping<\/h3>\n
CREATE ROLE agent_fraud_checker;\nCREATE ROLE agent_analytics;\n\n-- Fraud checker agent: can read everything it needs, can only write decisions\nGRANT EXECUTE ON dbo.FraudCheck TO agent_fraud_checker;\nGRANT INSERT ON FraudDecisions TO agent_fraud_checker;\n-- Cannot modify Person.RiskScore, cannot delete transactions, cannot see PII\n\n-- Analytics agent: read-only against the columnstore\nGRANT SELECT ON TransactionHistory TO agent_analytics;\nGRANT SELECT ON Person TO agent_analytics;\n-- Cannot write anything\n<\/code><\/pre>\ndbo.FraudCheck<\/code> and gets back only the data it is authorized to see.<\/p>\nQuery observability<\/h3>\n
-- Agent sets context at connection time\nEXEC sp_set_session_context @key = N'agent_id', @value = N'fraud-checker-v2';\nEXEC sp_set_session_context @key = N'task_id', @value = N'txn-review-9200';\n<\/code><\/pre>\nThe pattern<\/h3>\n
7 Reference Architecture: E-Commerce Platform<\/h2>\n
Azure SQL Database (Multi-Model Core)\n\u251c\u2500\u2500 Relational: Customers, Orders, Inventory, Payments\n\u251c\u2500\u2500 JSON: Product specs, event logs, user preferences, feature flags\n\u251c\u2500\u2500 Graph: Customer networks, fraud rings, product relationships\n\u251c\u2500\u2500 Vector (DiskANN): Product search, support ticket similarity, recommendations\n\u2514\u2500\u2500 Columnstore: Sales analytics, inventory forecasting, customer segmentation\n\nData API Builder\n\u251c\u2500\u2500 REST: Mobile\/web apps, partner integrations\n\u251c\u2500\u2500 GraphQL: Frontend teams, flexible queries\n\u2514\u2500\u2500 MCP: AI agents (Claude, GPT, custom)\n\nGovernance (applied uniformly)\n\u251c\u2500\u2500 Row-Level Security: Tenant isolation across all models\n\u251c\u2500\u2500 Dynamic Data Masking: PII protection in API responses\n\u251c\u2500\u2500 Always Encrypted: Client-side encryption for sensitive columns\n\u251c\u2500\u2500 Ledger Tables: Tamper-evident audit trail\n\u251c\u2500\u2500 TDE: Encryption at rest\n\u2514\u2500\u2500 Unified Audit: Single log for all data access\n<\/code><\/pre>\nRecommendation Engine<\/h3>\n
CREATE PROCEDURE dbo.GetProductRecommendations\n @customerID INT,\n @limit INT = 10\nAS\nBEGIN\n WITH CustomerProfile AS (\n -- Graph: What do customers in this person's network buy?\n SELECT \n oi.ProductID,\n COUNT(*) * 0.3 AS GraphScore\n FROM Customers c1, Similar s, Customers c2\n JOIN Orders o ON c2.CustomerID = o.CustomerID\n JOIN OrderItems oi ON o.OrderID = oi.OrderID\n WHERE MATCH(c1-(s)->c2)\n AND c1.CustomerID = @customerID\n GROUP BY oi.ProductID\n ),\n VectorSimilarity AS (\n -- Vector: Products semantically similar to recent purchases\n SELECT \n p.ProductID,\n MIN(VECTOR_DISTANCE('cosine', p.Embedding, recent.Embedding)) * 0.4 AS VectorScore\n FROM Products p\n CROSS JOIN (\n SELECT TOP 3 pr.Embedding \n FROM OrderItems oi \n JOIN Orders o ON oi.OrderID = o.OrderID\n JOIN Products pr ON oi.ProductID = pr.ProductID\n WHERE o.CustomerID = @customerID\n ORDER BY o.OrderDate DESC\n ) recent\n GROUP BY p.ProductID\n ),\n TrendingProducts AS (\n -- Columnstore\/Analytics: What is trending this week?\n SELECT \n ProductID,\n PERCENT_RANK() OVER (ORDER BY SUM(Quantity)) * 0.2 AS TrendScore\n FROM SalesHistory\n WHERE SaleDate > DATEADD(DAY, -7, GETUTCDATE())\n GROUP BY ProductID\n )\n SELECT TOP (@limit)\n p.ProductID,\n p.Name,\n JSON_VALUE(p.Specs, '$.shortDescription') AS Description, -- JSON\n p.Price,\n COALESCE(cp.GraphScore, 0) +\n COALESCE(vs.VectorScore, 0) +\n COALESCE(tp.TrendScore, 0) AS TotalScore\n FROM Products p\n LEFT JOIN CustomerProfile cp ON p.ProductID = cp.ProductID\n LEFT JOIN VectorSimilarity vs ON p.ProductID = vs.ProductID\n LEFT JOIN TrendingProducts tp ON p.ProductID = tp.ProductID\n WHERE p.InStock = 1\n ORDER BY TotalScore DESC;\nEND;\n<\/code><\/pre>\n8 Getting Started<\/h2>\n
Option 1: SQL Server 2025 (On-Premises, Free)<\/h3>\n
# Install via winget\nwinget install Microsoft.SQLServer.2025.Express\n<\/code><\/pre>\nOption 2: Azure SQL Database (Cloud, Free Tier)<\/h3>\n
Data API Builder (Free, Open Source)<\/h3>\n
dotnet tool install -g Microsoft.DataApiBuilder\ndab init --database-type mssql --connection-string \"YOUR_CONNECTION_STRING\"\ndab add Customer --source dbo.Customers --permissions \"anonymous:read\"\ndab start\n<\/code><\/pre>\n