MSSQL DBA MCP Stdio Server

The server provides context to an LLM, empowering models to complete various Database Administration tasks, such as improving table health, optimizing existing indexes, and identifying missing ones. The server aims to find the best way to provide context so the LLMs can maximize their potential in completing DBA tasks. For feedback, questions, or support, please join our Discord!

Table of Contents

• Prerequisites
• Configuration
  • Usage with MCP Client
    • npx
• Prompts
  • Optimize Query
  • Optimize Indexes
• Tools
  • Table-Level Tools
    • Get Tables Info
    • Get Tables Index Health
    • Get Tables Missing indexes
  • Server-Level Tools
    • Get Server Version
    • Get Server Timezone
  • Database-Level Tools
    • Get Database Collation
    • Get Collation Mismatches
• License
• Contact

Prerequisites

Create a user with these permissions:

USE [master];
GO

CREATE LOGIN [dbai] WITH PASSWORD = 'YourStrongPasswordHere';
GO

GRANT VIEW SERVER STATE TO [dbai];
GO

USE [YourDatabase];
GO

CREATE USER [dbai] FOR LOGIN [dbai];
GO

GRANT VIEW DEFINITION TO [dbai];
GO

Configuration

Usage with MCP Client

There is a partial list of existing clients at modelcontextprotocol.io. Consult their documentation to install the MCP server.

Currently we support only SQL Server Authenitcation method.

Example setup for Claude Code: Add this to your claude_desktop_config.json:

npx

{
  "mcpServers": {
    "mssql-dba": {
      "command": "npx",
      "args": ["@dba-i/mssql-dba"],
      "env": {
        // Required Parameters
        "DB_HOST": "host",
        "DB_PORT": "1433",
        "DB_USER": "user",
        "DB_PASSWORD": "password",
        "DB_NAME": "database name",
        // Optional Parameters
        "TRUST_SERVER_CERTIFICATE": "true",
        "ENCRYPT": "false",
        "MAX_POOL": "10",
        "MIN_POOL": "0",
        "IDLE": "30000"
      }
    }
  }
}

Prompts

Optimize Query

• Name: optimize-query
• Description: Optimize SQL queries for better performance.
• Input:
  • query: The SQL query to optimize.
• Behavior:
  • Identifies tables involved in the query and fetches comprehensive schema information.
  • Analyzes existing indexes and identifies potential redundancies or missing indexes.
  • Suggests schema-level optimizations and highlights query inefficiencies.
  • Generates optimized query files and schema optimization scripts with detailed documentation.

Optimize Indexes

• Name: optimize-indexes
• Description: Optimize indexes on specified tables.
• Input:
  • tableNames: A string containing the names of tables to optimize.
• Behavior:
  • Fetches information about existing and missing indexes for the specified tables.
  • Identifies inefficient indexes and suggests removing them.
  • Provides options for index consolidation where possible.
  • Analyzes missing indexes and includes them if necessary, while avoiding redundant indexes.
  • Generates index optimization scripts with detailed documentation.
  • Creates rollback scripts and index maintenance stored procedures.

Tools

The MCP server exposes the following tools organized by scope:

Table-Level Tools

Get Tables Info

• Name: get-tables-info
• Description: Get the metadata about specified tables.
• Input:
  • tableNames (array of strings): Names of the tables to retrieve metadata for.
• Output:
  • JSON metadata about the specified tables.

Get Tables Index Health

• Name: get-tables-index-health
• Description: Assess index health for specified tables.
• Input:
  • tableNames (array of strings): Names of the tables to assess.
• Output:
  • JSON with index health information for the specified tables.

Get Tables Missing Indexes

• Name: get-tables-missing-indexes
• Description: Identify missing indexes for specified tables.
• Input:
  • tableNames (array of strings): Names of the tables to check for missing indexes.
• Output:
  • JSON with missing indexes.

Server-Level Tools

Get Server Version

• Name: get-server-version
• Description: Retrieve information about the SQL Server instance such as version, current update level, edition, and licensing details.
• Input:
  • No input parameters required.
• Output:
  • JSON with comprehensive SQL Server instance version and product level.

Get Server Timezone

• Name: get-server-timezone
• Description: Retrieve the current timezone settings of the SQL Server instance.
• Input:
  • No input parameters required.
• Output:
  • JSON with timezone information including server date/time values, UTC values, timezone offsets, and OS timezone configuration.

Database-Level Tools

Get Database Collation

• Name: get-db-collation
• Description: Retrieve the collation setting for the current database.
• Input:
  • No input parameters required.
• Output:
  • JSON with the database's collation information.

Get Collation Mismatches

• Name: get-collation-mismatches
• Description: Retrieve the columns with collation settings that differ from the database default.
• Input:
  • No input parameters required.
• Output:
  • JSON with information about columns that have collation mismatches with the database default.

License

This project is licensed under the Elastic License 2.0 (ELv2).

Contact

For feedback, questions or support, please join our Discord.