Inroduce an Mcp Server and a AI Agent to be used with Github CoPilot or CoPilot CLI to query SQL Nexus db (#540)

518 Build initial prototype
518 update docs
518 improve test file to show meaningful test results and also improve/change the top cpu queries
518 SqlNexus MCP Server: consolidate docs, add CLI args, improve test script


Consolidated BUILD_SUMMARY.md, GETTING_STARTED.md, HOW_TO_USE.md, and
SETUP.md into a single README.md with setup, testing, architecture,
MCP protocol flow diagram, and troubleshooting sections

Added –server, –database, and –trusted-connection command-line args
to Program.cs so connection details can be passed directly without
relying on appsettings.json or environment variables

Fixed stdin EOF handling in ProcessRequests() to break on null ReadLine
(pipe close) instead of looping forever, enabling clean exit after
piped test input

Updated Test-McpServer.ps1:

Replaced hardcoded paths with script-relative paths
Added interactive prompts for server/database with defaults
Passes –server and –database args to the exe on each call
Implemented Invoke-McpTool helper with Format-Table output
Fixed while loop exit (replaced switch break with $exit flag)




518 corrections to the Readme/Start_here files
518 SqlNexus MCP Server: fix Copilot CLI compatibility (JSON-RPC spec, MCP lifecycle)

McpTypes.cs

Added NullValueHandling.Ignore to JsonRpcResponse.Result and Error
so responses include only result OR error, never both — fixing a
strict JSON-RPC 2.0 spec violation that caused Copilot CLI to reject
all responses (VS Code was tolerant; CLI was not)

Program.cs

Notification handling: requests with null id are now treated as
notifications and never produce a response; notifications/initialized
is explicitly recognized as a no-op per MCP lifecycle spec
Protocol version negotiation: initialize now echoes back the client’s
requested protocolVersion instead of hardcoding “2024-11-05”
JSON-RPC batch support: lines starting with ‘[‘ are parsed as JArray
and each element processed individually
Lazy SQL initialization: DiagnosticAnalyzer is created on first tool
call via GetAnalyzer() — server now always completes the MCP handshake
even if SQL Server is temporarily unavailable at startup

README.md

Updated note to include Copilot CLI alongside VS Code as a supported client
MCP Protocol Flow diagram updated to show notifications/initialized step
and protocol version negotiation
Added troubleshooting entry for sqlnexus_MCP showing X in Copilot CLI

START_HERE.md

CLI config example updated to include “type”:”stdio” (required by CLI)


518 SqlNexus MCP: Perfmon CPU analysis, per-query CPU capacity %, and delta query stats

DiagnosticAnalyzer.cs
  AnalyzeCpuUsage() — complete rewrite:

Replaced wait-stats-only implementation with a single Perfmon query that
returns per-sample rows (sample_time, sql_cpu_pct, nonsql_cpu_pct,
system_idle_pct) from CounterData joined by RecordIndex, matching the
Bottleneck Analysis RDL DataSet query exactly
SET ANSI_NULLS OFF added to match RDL behaviour: prevents the
det.InstanceIndex = @inst_index join from silently returning zero rows
when @inst_index is NULL
Falls back to tbl_SQL_CPU_HEALTH (ring buffer) when CounterData is absent
Aggregate summary (max, avg, count above 70%) computed in C# from the
already-fetched rows — no second SQL round-trip
Consecutive high-CPU run detector: flags any run of 3+ samples above 70%
with run_start, run_end, consecutive_samples, peak_sql_cpu_pct
sustained_high_cpu_detected boolean gives Copilot an immediate signal

  GetTopCpuQueries():

ReadTrace path: added pct_of_cpu_capacity column — % of total CPU
capacity each query consumed during the collection window, computed as
SUM(CPU) / (cpu_count * collection_duration_ms)
dm_exec_query_stats path (tbl_Hist_Top10_CPU_Queries_ByQueryHash):

Delta between first/last snapshot isolates CPU used only during the
collection window (not cumulative since SQL Server startup)
pct_of_cpu_capacity added using tbl_RUNTIMES for the time window
OBJECT_ID guard on tbl_RUNTIMES prevents parse-time error when absent
Added avg_cpu_ms, delta_executions columns; fixed ORDER BY to DESC


Added ExecuteQueryToDataTable() private helper for methods that compose
multi-section JSON from multiple queries

Program.cs

Updated analyze_cpu_usage tool description to reflect new Perfmon summary
output: max/avg SQL CPU %, samples above 70%, sustained high CPU detection


518 feat(mcp): add list_nexus_tables tool with curated SQL Nexus table catalog

DiagnosticAnalyzer.cs:

Add ListNexusTables() method exposing a curated catalog of ~70
analytically significant SQL Nexus tables (dbo + ReadTrace schema)
Each entry carries a plain-English description of the table’s source
DMV/query, key columns, and how to use it for analysis
At call time, queries sys.tables to annotate each entry with
present_in_database so the AI knows which tables were actually
captured in this collection
Result includes a discovery_hint pointing to INFORMATION_SCHEMA.TABLES
so agents know the catalog is a subset and how to enumerate the rest
Correct tbl_System_Requests description: these are internal system
thread rows (session_id < 0) covering checkpoint, lazy writer, log
writer and ghost cleanup — not user requests
Correct tbl_SQL_CPU_HEALTH description: confirmed populated from the
SQL Server ring buffer health records; used as CPU fallback when
Perfmon CounterData is absent
Add tbl_sqlagent_jobs entry (Agent job definitions for incident
timeline correlation)
Add tbl_database_options entry (per-database options snapshot)
Add ReadTrace.tblInterestingEvents entry (deadlock graphs, attention
events, errors)
Fix tblErrorlog key → tbl_ERRORLOG to match actual table name
Improve tbl_running_drivers description to call out filter drivers
(antivirus, backup agents) explicitly

Program.cs:

Register list_nexus_tables MCP tool in HandleListTools()
Wire list_nexus_tables case in HandleToolCall() switch
Tool description explicitly states it is a curated subset and embeds
the INFORMATION_SCHEMA discovery query for AI agent awareness


518 fix(mcp): correct tool descriptions to match actual query implementations


analyze_cpu_usage: remove incorrect reference to tbl_OS_WAIT_STATS /
wait type section (SOS_SCHEDULER_YIELD, CXPACKET, CXCONSUMER); describe
actual CounterData → tbl_SQL_CPU_HEALTH fallback, per-sample output, and
sustained high-CPU run detection (3+ consecutive samples above 70%)
get_top_cpu_queries: replace wrong tbl_NOTABLEACTIVEQUERIES source with
accurate dual-path description: ReadTrace.tblBatches (primary) and
tbl_Hist_Top10_CPU_Queries_ByQueryHash delta-snapshot (fallback);
mention pct_of_cpu_capacity
analyze_io_waits: add LOGBUFFER and ASYNC_IO_COMPLETION to listed wait
types; clarify first/last snapshot delta approach
get_collection_time_range: add caveat that it depends on
ReadTrace.tblBatches and returns no data for non-ReadTrace collections


518 spinlock high-CPU flag + collection time range fallback


AnalyzeSpinlocks: add is_high_cpu_driver column (1 if
spins_per_millisecond_per_CPU > 20000, else 0)

GetCollectionTimeRange: check tbl_RUNTIMES first; fall back to
ReadTrace.tblBatches if not present



local Local dev config - mcp.json, appsettings, mcp-config files, setup docs, query scripts
AgentMode Add SQL Nexus diagnostic skill files for Copilot agent workflows
AgentMode Add sql-nexus-diagnostic.agent.md - VS Code Copilot agent with MCP tool workflows and skill file references
AgentMode Add 16 new MCP tools covering all skill file queries

New tools in DiagnosticAnalyzer.cs + Program.cs:

get_query_execution_details    (per-execution drill-down by HashID)
get_wait_type_distribution     (request-level wait type frequency)
get_wait_resource_hotspots     (hot pages/rows/keys causing contention)
get_wait_heavy_queries         (queries with CPU < 80% of duration)
get_statements_in_batch        (statement breakdown inside a batch)
get_blocking_chain_tree        (recursive CTE blocking hierarchy)
get_lock_summary_by_object     (lock contention by table/resource)
get_queries_by_application     (queries filtered by ApplicationName)
get_performance_by_application (aggregate perf per ApplicationName)
get_cpu_by_database            (CPU breakdown by database)
get_top_queries_by_reads       (I/O heavy queries, read pressure)
get_top_queries_by_writes      (write heavy / WRITELOG pressure)
get_sql_file_io_stats          (per-file avg read/write latency)
get_compilation_stats          (SQL Compilations/sec from Perfmon)
get_plan_cache_analysis        (cache composition, single-use plans)
get_table_statistics_health    (stale stats detection)

agent.md tools list updated to include all 34 MCP tools.

AgentMode Fix: move agent.md to .github/agents/ (correct VS Code discovery path)
AgentMode Fix agent.md frontmatter: restore valid YAML tools list, add read+search aliases for skill file access
AgentMode Mandate skill file loading: add intent-gathering step, make skill files load-first before any MCP tool call
AgentMode skills-as-fallback: agent investigates freely first, consults skill files only when stuck or cross-checking
AgentMode Remove rigid routing table and step workflows — agent reasons freely from tool descriptions + skill files as fallback
AgentMode Add explicit MCP tool annotations to every query in all 10 scenario skill files - agent now knows exact tool to call per query number
AgentMode Cover all 34 MCP tools in skill files - annotate 6 previously missed query sections + add 5 new query sections for analyze_cpu_usage, get_cpu_by_database, analyze_io_waits, analyze_io_performance, get_performance_summary
518 improved GetTopCpuQueries to spot Parameter seensitive plan issues (PSP issues). Also fixed divide-by-zero bugs
518 added MCP tool for analyzing xevent and sql tracing overhead
518 Move Skills folder into AI folder and update references

Relocate the diagnostic skill files under a dedicated AI folder and update all associated path references.

Move Skills/ to AI/Skills/ (16 markdown files, renamed to preserve history)
Update 22 skill-file path references in  .github/agents/sql-nexus-diagnostic.agent.md from Skills/ to AI/Skills/
Remove SqlNexus.McpServer mcp-config-copilot-cli.json and  mcp-config-vscode.json (local MCP config)
Add both mcp-config files to .gitignore

Internal cross-links between skill files use relative paths and remain valid after the move.

518 Add PII scrubber layer - regex (GUID/IP/computer) + Presidio NLP + URL allowlist applied to all MCP tool outputs
518   #533 Update FileIOStats skills, mcp tool and rowset identifier
add gitignore for mcp and vscode settings.json

518 Update query 28 in scenario-io to filter properly for tempdb in file stats
Remove account details from setup-complete.md

Build a MCP server prototype for SQL Nexus
Fixes #518 reformat path

Build a MCP server prototype for SQL Nexus
Fixes #518 remove .vscode/mcp.json from gitignore to remove account

Build a MCP server prototype for SQL Nexus
Fixes #518 Clean up mcp.json

Build a MCP server prototype for SQL Nexus
Fixes #518 Re-add .vscode/mcp.json to gitignore

Build a MCP server prototype for SQL Nexus
Fixes #518 Update query 29 for filestats for scenario-io in skills

518 PiiScrubber: remove Presidio, expand regex to cover emails, UNC paths, Windows user paths, NT domain tokens, SQL login JSON fields, phone numbers
agent.md: remove multi-model comparison line, clarify read-only offline scope, model selection is engineer decision

518 add an application logger for the MCP Server
518 added a new MCP tool analyze_hadr_health that queries the SQL Nexus HADR tables.

• Added AnalyzeHadrHealth() — queries all seven HADR tables you listed, each guarded with IF OBJECT_ID(…) IS NOT NULL so missing tables are safely skipped (reported under tables_not_present). It returns per-section data for:
• AG states, AG database replica states, listeners
• AlwaysOn health-session events (lease expired, failovers, replica state changes)
• Server diagnostics log configuration
• Added a private helper AppendHadrIssues(…) that defensively (case-insensitive, version-tolerant column lookups) surfaces problems under issues_found: unhealthy AG sync, non-synchronized/suspended database replicas, recorded failovers, lease expirations, and a disabled diagnostics log — each with a severity rating.
• Registered the seven HADR tables in the ListNexusTables() catalog so they’re discoverable.

Add Transparency Note (Application Card) for SQL Nexus Diagnostic Agent - RAI/OneRAI documentation

agent.md: add groundedness, content safety, and security rules for OneRAI compliance

518 Added AnalyzeSetupHealth() MCP tool to analyze setup/install issues:

• tbl_installed_programs (filtered to name LIKE ‘sql%’) — returns the installed SQL Server programs and adds a known_component_status list flagging well-known components (Database Engine, SSAS, SSRS, SSIS, Full-Text, SSMS, Native Client, Browser, Machine Learning, PolyBase, etc.) as installed: true/false so gaps are easy to spot.
• tbl_setup_missing_msi_msp_packages — returns all rows and, per your requirement, raises a High severity issue under issues_found whenever the table contains any rows, since that indicates a missing installer cache that can block patching/repair/uninstall.
• Both tables are guarded with IF OBJECT_ID(…) IS NOT NULL; missing tables are reported under tables_not_present, matching the AnalyzeHadrHealth pattern.
• Registered tbl_installed_programs (updated description) and tbl_setup_missing_msi_msp_packages in the ListNexusTables() catalog (avoiding a duplicate-key error for the already-present tbl_installed_programs).
• Added the analyze_setup_health tool definition in HandleListTools() and the dispatch case in HandleToolCall().

518 Updated: .github/agents/sql-nexus-diagnostic.agent.md, Created: AI/Skills/scenario-hadr.md and AI/Skills/scenario-setup.md


Updated: .github/agents/sql-nexus-diagnostic.agent.md


tools: frontmatter — added sqlnexus_mcp/analyze_hadr_health and sqlnexus_mcp/analyze_setup_health (alphabetically placed).
Phase 2 skill-file mapping table — added rows for the HADR and Setup scenarios.
MCP Tool Catalog table — added analyze_hadr_health and analyze_setup_health with descriptions.
Skill Files reference table — added scenario-hadr.md and scenario-setup.md entries.


Created: AI/Skills/scenario-hadr.md
Full HADR scenario guide covering purpose, when-to-use keywords, the analyze_hadr_health tool, a table mapping each of the 7 HADR tables to what it reveals, an issue-interpretation table with severities, an investigation flow (correlating failovers/lease expirations with CPU/I/O), cross-check query_nexus_database snippets, thresholds/rules, and data-gap guidance (AlwaysOn LogScout scenario).

Created: AI/Skills/scenario-setup.md
Full Setup/Installation scenario guide covering purpose, keywords, the analyze_setup_health tool, the two setup tables, issue interpretation (with the “ANY row = problem” rule for missing MSI/MSP), the investigation flow directing users to the _MissingMsiMsp_Detailed.txt file, cross-check queries, related-data context (tbl_windows_hotfixes_installed, tbl_ServerProperties), and data-gap guidance.
Both skill files follow the existing structure and tone used by the other scenario-.md files in AI/Skills.



518 Add HADR/Setup MCP tools and Responsible AI validation guidance

Introduce two new diagnostic MCP tools and reinforce Responsible AI
transparency across the SqlNexus MCP server and diagnostic agent.
SqlNexus.McpServer/Program.cs:

Add analyze_hadr_health tool (Always On / availability group states,
replica & DB sync, listeners, failovers, lease expirations, diagnostics
log configuration).
Add analyze_setup_health tool (installed SQL Server components and
missing MSI/MSP package detection).
Add ToolSourceTables map linking each tool to its underlying SQL Nexus
source table(s).
Add AppendValidationGuidance: injects a structured Responsible AI
validation notice (ai_generated_notice, validate_against_tables,
validation_steps) into every JSON tool result so the “AI-generated,
may be inaccurate” disclaimer travels with the data; falls back to a
trailing-text notice for non-JSON payloads.
Populate initialize response Instructions with an AI-generated content
notice.

SqlNexus.McpServer/McpTypes.cs:

Add optional Instructions property to InitializeResult for the MCP
connection-time disclaimer.

.github/agents/sql-nexus-diagnostic.agent.md:

Register analyze_hadr_health and analyze_setup_health tools.
Add HADR and Setup skill-file/scenario references and tool→table
validation mappings.
Add Phase 4 (Encourage Validation) and Rule 11 requiring an
AI-generated inaccuracy notice and validation prompt on every response;
renumber subsequent rules.

.vscode/mcp.json:

Remove local MCP server configuration (17 lines). This isn’t needed as each user may decide to configure their MCP differently.


518 Add AI file integrity check to MCP server

Prevent the SqlNexus MCP server from running if its AI guidance files
(skill files and the diagnostic agent definition) have been tampered
with, ensuring the Responsible AI behavior cannot be silently altered.
SqlNexus.McpServer/FileIntegrity.cs:

Add FileIntegrityChecker with a ProtectedFileHashes dictionary keyed by
repository-relative path (agent definition + all AI/Skills/*.md files),
each with an expected SHA-256 hash.
VerifyAll() locates the repo root, hashes every protected file, and
returns an explicit, user-facing error naming any file that is missing,
unreadable, or modified.

SqlNexus.McpServer/Program.cs:

Gate startup on FileIntegrityChecker.VerifyAll; on failure, log and
write the error to stderr and exit (code 2) before serving requests.

SqlNexus.McpServer/GenerateFileHashes.ps1:

Add helper script that recomputes SHA-256 hashes for the protected
files and emits a ready-to-paste ProtectedFileHashes dictionary.

SqlNexus.McpServer/SqlNexus.McpServer.csproj:

Add PreBuildEvent to run GenerateFileHashes.ps1 so current hashes are
printed in the build output (visible in Build > Events UI).


518 fixing some pre-build event issues which prevented successful MCP server build - added exception handling and made sure prebuild runs successfully
518 Add two-database comparison tool to SQL Nexus MCP server

Add a new compare_nexus_databases MCP tool that compares a primary SQL
Nexus database against an optional second database, plus supporting
configuration, guidance, and robustness fixes.
Features:

New optional –database2 startup parameter (aliases:
–database-for-comparison, –database_for_comparison, and
SqlNexus:Database2 config). Optional/null by default, so existing
single-database configurations keep working unchanged.
New compare_nexus_databases tool producing side-by-side sections:
server_properties, database_options, database_scoped_configurations,
sys_configurations (value_in_use), and query_performance (ReadTrace).
Validate both databases exist (parameterized sys.databases check) and
bracket-quote identifiers to guard cross-database queries.
initialize instructions now announce COMPARISON MODE when a second
database is configured, steering the agent to the comparison tool.

Output size reductions for the client:

Config/options/scoped/sys_configurations sections return
differences-only rows; query_performance reduced from TOP 100 to
TOP 30 per database.
Replace non-sargable ISNULL(CONVERT(…)) comparisons with NULL-safe,
function-free NOT EXISTS (… INTERSECT …) predicates.

Diagnostics and safety:

Log the advertised tool catalog sorted, numbered, one per line, plus
comparison-tool presence and comparison-mode status.
Harden query_nexus_database keyword guard: strip string literals and
use whole-word matching to stop false positives (e.g. ‘CreateDate’).

Guidance and integrity:

Add AI/Skills/scenario-database-comparison.md and register it in the
agent skill list; update integrity hashes in FileIntegrity.cs.
Stop hash-protecting the agent definition file, which VS Code rewrites
when tool checkboxes are toggled; enable all tools by default in its
frontmatter.
Add .gitattributes pinning guidance files to CRLF for stable hashes.
Update README.md and appsettings.json for the new parameter.


518 added Unit tests for MCP Server, PII scrubber (extensive) , etc.
518 Make file hash generation independent of PSModulePath

Explicitly import the built-in Microsoft.PowerShell.Utility module and
use the module-qualified Get-FileHash command.
Add a regression test that verifies hash generation with a restricted
PowerShell module path.

version bump

518 Harden MCP PII scrubbing and add database audit


handle JSON-escaped paths, domain users, IPv6, MAC addresses, and additional sensitive fields
validate IP addresses before replacement to avoid scrubbing invalid numeric values
enforce URL allowlists using exact hosts and path boundaries
add regression coverage for new scrubber behavior and allowlist bypass attempts
add a parameterized SQL Nexus database-wide PII audit script
support table exclusions and maximum row limits for large database audits
produce privacy-preserving JSON findings without raw source values
document integration audit prerequisites, options, and usage


518 Stage MCP server and AI assets during builds


Make sqlnexus depend on SqlNexus.McpServer for build ordering.
Copy the complete MCP runtime into the SQL Nexus output directory.
Copy AI skill files and the diagnostic agent definition.
Apply the same output layout to Debug and Release builds.
Fail the build when required MCP or AI source files are missing.


518 Register SQL Nexus integration with GitHub Copilot


Add registration and unregistration scripts for VS Code and Copilot CLI.
Preserve unrelated MCP configuration entries during registration changes.
Install the SQL Nexus diagnostic agent in the shared user agent directory.
Add conflict detection, idempotent updates, and malformed JSON handling.
Stage Copilot integration files in Debug and Release build outputs.
Guarantee MCP server build ordering for direct and solution builds.
Add tests for registration, unregistration, and configuration preservation.
Document setup, removal, configuration locations, and security behavior.
Correct the diagnostic agent YAML frontmatter.


518 Improve Copilot integration registration scripts


add MCP-only registration and unregistration modes
preserve matching settings instead of rewriting files
report timestamped status and concise actionable errors
display retained MCP connection and configuration settings
document VS Code and Copilot CLI setup and usage
add tests for idempotency, MCP-only mode, and error handling


Co-authored-by: pijocoder 26050114+PiJoCoder@users.noreply.github.com
Co-authored-by: Nisha Mohan nishamohan@microsoft.com
Co-authored-by: James Ferebee james.ferebee@microsoft.com