Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/A-Point-Systems-ltd/ms-sql-mcp/llms.txt

Use this file to discover all available pages before exploring further.

The AI Insights layer is an optional caching system built into the MSSQL MCP Server that stores LLM-authored and server-generated summaries of database objects — tables, views, stored procedures, and functions — directly inside your target database. Instead of re-investigating the same objects from scratch on every agent session, the server attaches a cached insight to introspection responses and flags when that cache needs upgrading. The layer is enabled by default (controlled by the USE_INSIGHTS_LAYER environment variable), but the database objects it needs are not auto-installed — you must call InstallInsightsLayer once per target database.

​
How it works

The Insights layer operates across three distinct phases that together keep the cache accurate with minimal manual effort.

​
1. Read path

Every time an agent calls DescribeTable, DescribeView, or GetObject, the server looks up the cached insight for that object and attaches it to the response alongside several freshness signals:
  • insight — the full cached SchemaInsights row (description, business purpose, data patterns, usage guidelines, related objects, confidence, model, etc.), or null if absent.
  • insightFreshness — one of Fresh, Absent, StaleArchived, LayerDisabled, AccessDenied, or DefinitionUnavailable. See Freshness for the full table.
  • enrichmentSuggested — true when the cached insight is an auto-mechanical baseline (confidence 0.30) that an LLM should upgrade.
  • insightEnrichment — present when enrichmentSuggested is true; contains a pre-filled UpsertInsight payload and the full MCP-Insight-Enrichment-v1 protocol directive.
This read enrichment runs best-effort and never fails the parent tool — if the insights layer throws an error, the introspection result is still returned without insight data.

​
2. Write path

After every successful write operation (CreateTable, DropTable, InsertData, UpdateData, ExecuteSQL), the server signals the InsightDdlProcessingQueue background service. This service drains new rows from dbo.DDL_AuditLog (using a watermark stored in AIInsights.DdlChangeWatermark) and archives any insight rows whose objects were affected by those DDL events. If the DDL audit tables are not present, it falls back to a fingerprint scan instead. When INSIGHTS_AUTOPOPULATE is enabled, the background service also rebuilds auto-mechanical baselines for any objects that were just archived.

​
3. Auto-population

When INSIGHTS_AUTOPOPULATE is enabled (the default), the server automatically creates a mechanical baseline insight for any object that has no cached row (freshness Absent) or whose row was just archived (freshness StaleArchived). The baseline is written with LlmModel = "auto-mechanical" and Confidence = 0.30, and is built entirely from sys.* metadata — column counts, approximate row counts, object IDs, and foreign-key relationships. This gives agents something useful to work with immediately, while the enrichmentSuggested flag and insightEnrichment block signal that a real LLM-authored description should replace it.

​
Installed objects

InstallInsightsLayer creates the following objects in the target database (the install is idempotent — safe to re-run):
  • AIInsights schema — container for all cache tables
  • AIInsights.SchemaInsights — current cached insights, one row per (objectType, schema, objectName, columnName)
  • AIInsights.InsightHistory — archive table; rows moved here when an insight is stale or its object is dropped
  • AIInsights.DdlChangeWatermark — singleton row tracking the highest processed DDL_AuditLog.ID
  • dbo.DDL_AuditLog — captures DDL events (table name, schema, event type, command text, XML)
  • DDL_Audit — database-level trigger that fires on DDL events and inserts into DDL_AuditLog
Follow this sequence the first time you connect an agent to a new database:
1

Get server info

Call GetServerInfo to confirm the server version, edition, and that the connection is working.
2

Check insights layer state

Call InsightsCheck to see whether the AIInsights schema and DDL_Audit trigger are installed, the current insight row count, and the DDL watermark.
3

Install the layer (if needed)

If InsightsCheck reports the schema or DDL trigger is missing, call InstallInsightsLayer. The operation is idempotent. See Installation for permission requirements.
4

List tables

Call ListObjects(objectType=Table) to get an inventory of tables in the database.
5

Describe a table

Call DescribeTable(name=…) for one or more tables. Inspect the insight and enrichmentSuggested fields in the response.
6

Enrich baseline insights

When enrichmentSuggested is true, call UpsertInsight using the pre-filled args from insightEnrichment.nextAction.args — replacing every <fill in: ...> placeholder with real observations from the introspection response. See Enrichment Protocol for the full obligation.

​
Disabling the layer

Set USE_INSIGHTS_LAYER=false (or 0, no, off, disabled) in the MCP server environment to opt out entirely. With the layer disabled:
  • All insight-specific tools (InsightsCheck, InstallInsightsLayer, GetInsight, UpsertInsight, ListInsights, GetInsightHistory, RefreshInsights, RebuildBaselineInsights) return a “layer is disabled” error.
  • Introspection tools (DescribeTable, DescribeView, GetObject) skip the insight enrichment step entirely — no insight, insightFreshness, or enrichmentSuggested fields are attached to responses.
  • The InsightDdlProcessingQueue background service exits immediately without querying the database.
Restart the MCP server after changing this variable.

Installation

Run InstallInsightsLayer once per database to create the schema, audit tables, and DDL trigger.

Enrichment Protocol

When enrichmentSuggested is true, agents must call UpsertInsight before answering — this page explains the MCP-Insight-Enrichment-v1 obligation.

Freshness

How fingerprints and DDL audit keep the cache accurate, and what each insightFreshness value means.

InsightsCheck tool

Inspect the current layer install state, DDL audit presence, and watermark without running any SQL manually.

Build docs developers (and LLMs) love

Get started for freeTalk to us