# 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:**
   ```bash
   git clone https://dev.azure.com/goplanet-west/West%20AI/_git/mcp-db-analyzer
   cd mcp-db-analyzer
   ```

2. **Install dependencies:**
   ```bash
   npm install
   ```

3. **Build the project:**
   ```bash
   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`

```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:

```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"
      }
    }
  }
}
```

#### Visual Studio Code (GitHub Copilot)

VS Code supports MCP servers natively through GitHub Copilot's **Agent mode** (VS Code 1.99+).

**Option 1 — Workspace configuration** (checked into source control, applies to everyone opening this project):

Create or edit `.vscode/mcp.json` in your project root:

```json
{
  "servers": {
    "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"
      }
    }
  }
}
```

**Option 2 — User settings** (applies to all your VS Code projects):

Open your user `settings.json` (<kbd>Ctrl/Cmd</kbd>+<kbd>Shift</kbd>+<kbd>P</kbd> → *Preferences: Open User Settings (JSON)*) and add:

```json
{
  "mcp": {
    "servers": {
      "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"
        }
      }
    }
  }
}
```

**Activating the server:**

1. Open the Copilot Chat panel (<kbd>Ctrl/Cmd</kbd>+<kbd>Alt</kbd>+<kbd>I</kbd>)
2. Switch to **Agent mode** using the mode selector at the bottom of the chat input
3. Click the **Tools** button (⚙) to verify `db-analyzer` appears in the tool list
4. Start chatting — Copilot will automatically invoke the MCP tools as needed

> **Note:** If the server doesn't appear, run **MCP: List Servers** from the Command Palette to refresh, and check the output panel for errors.

#### 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