Dperussina Mssql MCP Server

🚀 MS SQL MCP Server English Guide

MS SQL MCP Server is a powerful tool for interacting with MS SQL databases. It provides a wide range of features such as database querying, result navigation, and database discovery. This guide will help you get started with the server and make the most of its capabilities.

🚀 Quick Start

📦 Installation

Run the following command to install the required dependencies:

npm install

⚙️ Configure Environment Variables

Create or edit the .env file in the project root directory and fill in the following content according to your actual situation:

# Database connection settings
DB_USER=Your username           # SQL Server username
DB_PASSWORD=Your password         # SQL Server password
DB_SERVER=Your server name      # Server hostname or IP address (e.g., localhost, 10.0.0.1, myserver.database.windows.net)
DB_DATABASE=Your database name    # Name of the database to connect to

# Service configuration
PORT=3333                    # Port on which the HTTP/SSE server listens
TRANSPORT=stdio              # Connection method: 'stdio' (for Claude Desktop) or 'sse' (for network connection)
SERVER_URL=http://localhost:3333   # Base URL when using SSE transport (must match the PORT setting)

# Advanced settings
DEBUG=false                  # Set to 'true' to enable detailed logging (useful for debugging)
QUERY_RESULTS_PATH=/path/to/query_results  # Directory path where query results will be saved (each query result will be stored as a JSON file here)

🚀 Start the Service

• Start with stdio transport:

  npm start
  

• Start with sse transport:

  npm run start:sse
  

✨ Features

MS SQL MCP Server offers the following core features:

1. Database Tools

   • Query data
   • Browse table structures
   • Execute management commands

2. Result Navigation

   • Browse results page by page
   • Export results
   • Search for content

3. Query Execution

   • Support multiple formats (JSON, CSV)
   • Handle large result sets
   • Support transactions

4. Database Discovery

   • Browse database structures
   • View table information
   • Get stored procedure documentation

💻 Usage Examples

Query Data

Basic Query

SELECT TOP 5 * FROM Customers

Pagination Query

SELECT * FROM Orders 
ORDER BY OrderID OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY

Browse Database Structures

# List all databases
LIST DATABASES

# Show tables in the current database
DESCRIBE TABLE Customers

📚 Documentation

Pagination Browsing of Results

1. Get Pagination Information

   {
     "tool": "pagination",
     "args": {
       "query": "SELECT * FROM Orders",
       "page_size": 20,
       "current_page": 1
     }
   }
   

2. Execute Pagination Query

   {
     "tool": "paged_query"
   }
   

Execute Management Commands

EXECUTE SYSTEM COMMAND 'BACKUP DATABASE MyDatabase TO DISK = \'C:\Backups\MyDatabase.bak\''

⚠️ Important Note

⚠️ Important Note

The database connection string contains sensitive information. Please ensure its security. A leak may lead to unauthorized access to your database.

💡 Usage Tip

1. Security
   • Do not share the database connection string.
   • Use secure credentials.
   • Configure appropriate data access permissions.
2. Performance Optimization
   • Ensure index optimization.
   • Use pagination when handling large result sets.
   • Avoid long-running queries.

🔧 Technical Details

Core Modules

1. Database Module (database.mjs)

   • Responsible for handling all database connections and interactions.
   • Manage transactions and session states.

2. Query Execution Module (query_executor.mjs)

   • Parse and execute SQL statements.
   • Handle result sets.
   • Execute stored procedures.

3. Pagination Module (pagination.mjs)

   • Implement pagination logic.
   • Support multiple database dialects.
   • Provide efficient data navigation.

Explanation of Environment Configuration

Database Connection Settings

• DB_USER: The username for logging in to SQL Server.
• DB_PASSWORD: The corresponding user's password.
• DB_SERVER: The network address of the server.
• DB_DATABASE: The specific name of the database to connect to.

Service Configuration

• PORT: The port on which the service runs, defaulting to 3333.
• TRANSPORT: The connection method, stdio for local development and sse for production environments.
• SERVER_URL: When using sse transport, define the base URL of the server.

Advanced Settings

• DEBUG: Enable debug mode to obtain detailed log information.
• QUERY_RESULTS_PATH: The storage path for query result files.

📄 Conclusion

With the above guide, you can successfully configure and use the MS SQL MCP Server, and expand and optimize it as needed.