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.

InstallInsightsLayer is an MCP tool that installs every required database object in a single idempotent operation. You call it once per target database; re-running it at any time is safe — all CREATE statements are guarded by existence checks, and any objects that are already present are left unchanged. No SQL scripts need to be run manually.

​
Prerequisites

Before calling InstallInsightsLayer, confirm the following:
  • USE_INSIGHTS_LAYER must be enabled. This is the default. If you have set it to false, 0, no, off, or disabled, the tool returns a “layer is disabled” error without touching the database. Remove the variable or set it to true and restart the server.
  • The connecting user needs ALTER ANY DATABASE DDL TRIGGER permission (or membership in the ddl_admin or sysadmin fixed server role) to install the database-level DDL_Audit trigger. The AIInsights schema and tables can be created without this permission, but the DDL trigger installation will fail.
  • Azure SQL Database is fully supported. The connecting user needs adequate permissions on the target database. Ensure the account has at minimum db_owner or the ability to create schemas, tables, and DDL triggers.

​
What gets installed

InstallInsightsLayer applies the embedded CreateInsightsSchema.sql and CreateDdlAuditTrigger.sql scripts in sequence, creating the following objects if they do not already exist:
  • AIInsights schema — the namespace for all cache objects
  • AIInsights.SchemaInsights — stores the current cached insight for each (objectType, schemaName, objectName, columnName) tuple; includes description, business purpose, data patterns, usage guidelines, related objects, LLM model, confidence score, schema fingerprint, and the object’s modify_date at analysis time
  • AIInsights.InsightHistory — archive table where rows from SchemaInsights are moved when an object changes or is dropped; preserves the full insight along with archive reason, event name, and source DDL audit ID
  • AIInsights.DdlChangeWatermark — a singleton row that tracks the highest DDL_AuditLog.ID processed by the background service, preventing duplicate processing on restart
  • dbo.DDL_AuditLog — captures every DDL event that fires the trigger; records the host name, login, schema, object name, object type, event type, command text, and XML event data
  • DDL_Audit (database-level trigger) — fires on DDL events at the database scope and inserts one row into dbo.DDL_AuditLog per event; requires ALTER ANY DATABASE DDL TRIGGER to install

​
Running the install

InstallInsightsLayer is a regular MCP tool call — no manual SQL execution is needed. Instruct your agent: 
Call InstallInsightsLayer.
The tool applies both scripts, then returns a DbOperationResult reporting success or the specific error that occurred: 
{
  "success": true,
  "data": {
    "installed": true,
    "message": "AIInsights schema, tables, DDL_AuditLog, and DDL_Audit trigger applied (idempotent)."
  }
}
If the trigger script fails due to a permissions issue, the schema and tables installed by the first script are still committed — you can grant the DDL trigger permission and re-run InstallInsightsLayer to install only the missing trigger.
If InstallInsightsLayer returns a permission error mentioning DDL, grant ALTER ANY DATABASE DDL TRIGGER to the connecting user, or re-run as a member of the ddl_admin or sysadmin role. The schema and tables are already installed; only the trigger installation requires elevated permission.

​
Verifying the install

After installation, call InsightsCheck to confirm every component is in place. InsightsCheck queries the live database and returns the following fields from the LayerStatus model:
FieldTypeMeaning
layerEnabledViaEnvironmentbooltrue unless USE_INSIGHTS_LAYER is set to a falsey value
aiInsightsSchemaExistsbooltrue if the AIInsights schema was found in sys.schemas
ddlAuditTableExistsbooltrue if dbo.DDL_AuditLog exists
ddlAuditTriggerEnabledbooltrue if a database-level trigger named DDL_Audit exists and is not disabled
schemaInsightsCountintcurrent row count in AIInsights.SchemaInsights
lastProcessedAuditIdinthighest DDL_AuditLog.ID consumed by the background service (0 if none yet)
lastProcessedAtdatetime?timestamp of the last watermark update, or null if no DDL events have been processed
A successful install should show aiInsightsSchemaExists, ddlAuditTableExists, and ddlAuditTriggerEnabled all true. schemaInsightsCount starts at 0 and grows as agents call DescribeTable, DescribeView, and GetObject (auto-population fills rows on first introspection).
If USE_INSIGHTS_LAYER=false, InsightsCheck immediately returns all boolean flags as false and all numeric fields as 0 without opening a database connection or running any queries. Enable the layer and restart the server before calling InsightsCheck to get real status.

Build docs developers (and LLMs) love

Get started for freeTalk to us