DB Analyzer MCP Server

A Model Context Protocol (MCP) server that provides comprehensive SQL Server database analysis and query capabilities for AI assistants like Claude.

Features

This MCP server exposes 10 powerful tools for database analysis and management:

Connection & Testing

• ping_db - Test database connectivity and retrieve server information

Query Execution

• execute_query - Execute SELECT queries with optional row limits
• execute_non_query - Execute DDL/DML statements (CREATE, UPDATE, DELETE, etc.)
• execute_stored_procedure - Call stored procedures with typed parameters

Schema Discovery

• list_databases - List all databases with optional name filtering
• list_schemas - List schemas in a specific database
• list_tables - List tables with optional schema/name filtering
• get_table_columns - Get detailed column information including types, constraints, and primary keys
• get_indexes - Retrieve index information for specific tables

Health Analysis

• analyze_schema_health - Comprehensive database health check including:
  • Tables without clustered indexes (heaps)
  • Tables missing primary keys
  • Usage of deprecated data types (text, ntext, image, sql_variant)
  • Wide tables (configurable column threshold)
  • Unused indexes based on usage statistics
  • Missing index suggestions from SQL Server DMVs
  • Fragmented indexes (>30% fragmentation, >1000 pages)

Installation

Prerequisites

• Node.js (v18 or higher recommended)
• Access to a SQL Server instance
• npm or yarn package manager

Setup

1. Clone the repository:

   git clone https://dev.azure.com/goplanet-west/West%20AI/_git/mcp-db-analyzer
   cd mcp-db-analyzer

2. Install dependencies:

   npm install

3. Build the project:

   npm run build

Configuration

Environment Variables

The server supports the following environment variables for database connection:

Variable	Description	Default
DB_HOST	SQL Server hostname	<database-hostname>
DB_USER	Database username	<database-user>
DB_PASSWORD	Database password	<database-password>
DB_DATABASE	Default database (optional)	None
DB_PORT	SQL Server port	1433
DB_ENCRYPT	Enable connection encryption	true
DB_TRUST_SERVER_CERT	Trust server certificate	true
DB_POOL_MAX	Maximum connection pool size	10

MCP Client Configuration

Claude Desktop

Add the following to your Claude Desktop configuration file:

Windows: %APPDATA%\Claude\claude_desktop_config.json macOS: ~/Library/Application Support/Claude/claude_desktop_config.json

{
  "mcpServers": {
    "db-analyzer": {
      "command": "node",
      "args": ["C:\\MCP-Servers\\db-analyzer\\build\\index.js"],
      "env": {
        "DB_HOST": "your-server.database.windows.net",
        "DB_USER": "your-username",
        "DB_PASSWORD": "your-password",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "true",
        "DB_TRUST_SERVER_CERT": "false"
      }
    }
  }
}

Cline (VS Code Extension)

Add to your Cline MCP settings:

{
  "mcpServers": {
    "db-analyzer": {
      "command": "node",
      "args": ["C:\\MCP-Servers\\db-analyzer\\build\\index.js"],
      "env": {
        "DB_HOST": "your-server.database.windows.net",
        "DB_USER": "your-username",
        "DB_PASSWORD": "your-password"
      }
    }
  }
}

Other MCP Clients

For other MCP-compatible clients (Cursor, Continue, etc.), refer to their documentation for adding custom MCP servers. The general pattern is:

• Command: node (or full path to Node.js)
• Arguments: Path to build/index.js
• Environment: Database connection variables

Usage Examples

Once connected to an MCP client, you can use natural language to interact with your database:

Example Prompts

Connection Testing:

"Test the database connection and show me the server version"

Schema Discovery:

"Show me all databases on this server"
"List all tables in the dbo schema"
"What are the columns in the Users table?"

Data Querying:

"Get the first 10 rows from the Orders table"
"Show me all customers from California"

Health Analysis:

"Analyze the database for potential issues"
"Find all tables without primary keys"
"Show me any fragmented indexes"

Development

Project Structure

db-analyzer/
├── src/
│   └── index.ts           # Main server implementation
├── build/                 # Compiled JavaScript (generated)
├── node_modules/          # Dependencies (generated)
├── package.json           # Project metadata and dependencies
├── tsconfig.json          # TypeScript configuration
├── .gitignore            # Git ignore rules
└── README.md             # This file

Scripts

• npm run build - Compile TypeScript to JavaScript
• npm start - Run the compiled server

Making Changes

1. Edit src/index.ts
2. Run npm run build to compile
3. Restart your MCP client to load changes

Security Considerations

⚠️ Important Security Notes:

1. Credentials: Never commit database credentials to version control. Use environment variables or secure credential storage.
2. Connection Encryption: For production databases, set DB_ENCRYPT=true and DB_TRUST_SERVER_CERT=false.
3. Permissions: Use a database account with minimal required permissions. Consider read-only access if write operations aren't needed.
4. Network Security: Ensure your SQL Server is not exposed to the public internet. Use VPNs or private networks when possible.
5. SQL Injection: The server uses parameterized queries where possible, but be cautious with dynamic SQL in custom queries.

Connection Pool Management

The server maintains connection pools per database to optimize performance. Pools are automatically created on first use and cleaned up on server shutdown. Connection parameters:

• Max connections: 10 (configurable via DB_POOL_MAX)
• Idle timeout: 30 seconds
• Automatic reconnection on connection loss

Troubleshooting

Common Issues

Connection Refused:

• Verify SQL Server is running and accessible
• Check firewall rules allow connections on port 1433
• Ensure SQL Server authentication is enabled if using username/password

Login Failed:

• Verify credentials are correct
• Check user has appropriate database permissions
• For Windows Authentication, use appropriate connection string format

Certificate Errors:

• For self-signed certificates, set DB_TRUST_SERVER_CERT=true
• For production, obtain proper SSL certificates

MCP Server Not Appearing:

• Verify the path to build/index.js is correct
• Check Node.js is installed and accessible
• Review MCP client logs for error messages

License

[Specify your license here]

Contributing

[Specify contribution guidelines here]

Support

For issues and questions:

• Create an issue in the Azure DevOps repository
• Contact your development team