Mssql Synth Data Agent

An AI-powered agent that generates synthetic test data for Microsoft SQL Server with autonomous schema awareness.

MSSQL Synthetic Data Gen Agent is an agentic AI tool that autonomously generates synthetic data for Microsoft SQL Server databases. It helps developers and data engineers quickly populate tables with realistic, privacy-safe data for testing, development, and analytics.

📑 Table of Contents

• ✨ Key Features
• 🖥️ Environment Setup
• 🐍 Python Virtual Environment
• 🛢️ Step 1: SQL Server in Docker
• ⚙️ Step 2: MCP Server for SQL Server
• 🗂️ Step 3: Fetch Table Names from Database

✨ Key Features

• AI-driven autonomous data generation
• Supports MS SQL Server tables and schemas
• Generates synthetic, realistic, and safe data

🖥️ Environment Setup

Development is done on macOS using the tools below. Install them with Homebrew.

• Cursor 1.5.5
• Git 2.39.5
• Python 3.11.13
• Docker Desktop
• Azure Data Studio
• unixODBC
• msodbcsql18
• mssql-tools

Install Microsoft’s ODBC driver and tools by tapping Microsoft’s Homebrew repo:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install unixodbc msodbcsql18 mssql-tools
odbcinst -q -d -n "ODBC Driver 18 for SQL Server" 
brew install --cask docker
brew install --cask azure-data-studio

If a Python virtual environment already exists, reinstall pyodbc inside it:

pip uninstall pyodbc -y
pip install pyodbc --no-binary :all:

🐍 Create a virtual environment and install packages

Review requirements.txt and run the commands below:

python -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install -r requirements.txt

To deactivate, run deactivate.

🛢️ Step 1: Run SQL Server in Docker on macOS and set up the source DB

Follow these steps to set up a lightweight SQL Server environment on macOS:

1. Pull the lightweight SQL Edge image

   docker pull mcr.microsoft.com/azure-sql-edge
   

2. Run the SQL container

   docker run -e "ACCEPT_EULA=1" \
           -e "MSSQL_SA_PASSWORD=XXXXXX" \
           -e "MSSQL_PID=Developer" \
           -p 1433:1433 \
           -d --name sql \
           mcr.microsoft.com/azure-sql-edge
   

   • ACCEPT_EULA=1 → Accepts the SQL Server license.
   • MSSQL_SA_PASSWORD → Sets the SA (system administrator) password (must meet complexity rules).
   • MSSQL_PID=Developer → Runs Developer Edition (full feature set for dev/test).
   • -p 1433:1433 → Exposes SQL Server on port 1433.

Note: For this PoC, we use a sample MovieReviews database to test the overall flow. This database is created by the script in the next step.

3. Run the SQL generator Python script

   python 01-source-db-setup/source_data_generator.py
   

--

⚙️ Step 2: Setup a MCP Server for SQL Server

Download Microsoft SQL MCP Server (Dotnet version) from Azure SQL AI Samples into 02-mcp-server folder.

NOTE:

• Updated 02-mcp-server/MssqlMcp/McsqlMcp.csproj file with version to net9.0
• Removed few unwanted files (like .gitignore, .editorconfig etc. ) from the source Git repo.

Install Dotnet SDK 9

brew install --cask dotnet-sdk
dotnet --version

Build the MCP Server.

cd 02-mcp-server/MssqlMcp
dotnet restore
dotnet build

Verify MssqlMcp.dll should be created under 02-mcp-server/MssqlMcp/bin/Debug/net9.0/.

🗂️ Step 3: Fetch Table Names from Database

Run the schema analyzer script:

python 03-sql-table-analyzer-agent/main.py

The agent will:

• Connect to the running MCP Server.
• Use the Database Analyst Agent to extract table names and schemas.
• Use the Validator Agent to verify the correctness of the extracted schema.
• Save the results to tables.json.

Example output:

[
   { "schema": "dbo", "table": "Genres" },
   { "schema": "dbo", "table": "Movies" },
   { "schema": "dbo", "table": "Reviews" }
]

Validation Output:

{
  "validation_passed": true,
  "issues": [],
  "message": "All expected tables and schema definitions exist with proper primary and foreign key constraints."
}

If validation fails, the agent automatically retries (up to n times) using feedback from the Validator to improve the analysis.

🗂️ Step 4: Fetch Schema from Database

Run the schema analyzer script:

python 04-sql-schema-analyzer-agent/main.py

The agent will:

• Connect to the running MCP Server.
• Use the Database Analyst Agent to extract column information.
• Use the Validator Agent to verify the correctness of the extracted column information.
• Save the results to tablename_schema.json.

Example output:

[
  {
    "table_name": "Genres",
    "column_name": "GenreID",
    "data_type": "int",
    "length": "4",
    "is_primary_key": "true",
    "is_nullable": "false"
  },
  {
    "table_name": "Genres",
    "column_name": "GenreName",
    "data_type": "nvarchar",
    "length": "200",
    "is_primary_key": "false",
    "is_nullable": "false"
  }
]

Validation Output:

{
  "validation_passed": true,
  "issues": [],
  "message": "All expected column information exist and are valid for Genres table of MovieReviews."
}

If validation fails, the agent automatically retries (up to n times) using feedback from the Validator to improve the analysis.

🗂️ Step 5: Fetch Relationships from Database

Run the schema analyzer script:

python 05-sql-relationship-analyzer-agent/main.py

The agent will:

• Connect to the running MCP Server.
• Use the Database Analyst Agent to extract relationships.
• Use the Validator Agent to verify the correctness of the extracted relationsships.
• Save the results to tablename_relationships.json.

Example output:

[
  {
    "name": "FK__Movies__GenreID__3B75D760",
    "table": "Movies",
    "column": "GenreID",
    "ref_table": "Genres",
    "ref_column": "GenreID"
  },
  {
    "name": "FK__Reviews__MovieID__3F466844",
    "table": "Reviews",
    "column": "MovieID",
    "ref_table": "Movies",
    "ref_column": "MovieID"
  }
]

Validation Output:

{
  "validation_passed": true,
  "issues": [],
  "message": "All expected foreign key relationships exist and are valid for MovieReviews."
}

If validation fails, the agent automatically retries (up to n times) using feedback from the Validator to improve the analysis.

🗂️ Step 6: Consolidate the Schema

Run the schema consolidator script:

python 06-schema-consolidator/main.py

The agent will:

• Fetch tables, schema and relationship json files.
• Consolidate all files into a single file

Example output:

{
  "foreign_keys": [
    {
      "name": "FK__Movies__GenreID__3B75D760",
      "table": "Movies",
      "column": "GenreID",
      "ref_table": "Genres",
      "ref_column": "GenreID"
    },
    {
      "name": "FK__Reviews__MovieID__3F466844",
      "table": "Reviews",
      "column": "MovieID",
      "ref_table": "Movies",
      "ref_column": "MovieID"
    }
  ],
  "columns": [
    {
      "table_name": "Genres",
      "column_name": "GenreID",
      "data_type": "int",
      "length": "4",
      "is_primary_key": "true",
      "is_nullable": "false"
    },
    {
      "table_name": "Genres",
      "column_name": "GenreName",
      "data_type": "nvarchar",
      "length": "200",
      "is_primary_key": "false",
      "is_nullable": "false"
    },
    {
      "table_name": "Movies",
      "column_name": "MovieID",
      "data_type": "int",
      "length": "4",
      "is_primary_key": "true",
      "is_nullable": "false"
    },
    {
      "table_name": "Movies",
      "column_name": "Title",
      "data_type": "nvarchar",
      "length": "510",
      "is_primary_key": "false",
      "is_nullable": "false"
    },
    {
      "table_name": "Movies",
      "column_name": "ReleaseYear",
      "data_type": "int",
      "length": "4",
      "is_primary_key": "false",
      "is_nullable": "true"
    }
    ....
  ],
  "tables": [
    {
      "schema": "dbo",
      "table": "Genres"
    },
    {
      "schema": "dbo",
      "table": "Movies"
    },
    {
      "schema": "dbo",
      "table": "Reviews"
    }
  ]
}

Output messages: ✅ Merged Genres_schema.json into columns ✅ Merged Movies_schema.json into columns ✅ Merged tables.json into tables ✅ Merged Reviews_schema.json into columns ✅ Merged Genres_relationships.json into foreign_keys ✅ Merged Movies_relationships.json into foreign_keys ✅ Merged Reviews_relationships.json into foreign_keys ⚠️ Unknown file type for tasks.json, skipping. ⚠️ Unknown file type for consolidated.json, skipping.

💾 Consolidated JSON saved to output/consolidated.json

🗂️ Step 7: Plan tasks for data analysis

Run the task planner agent script:

python 07-data-analyzer-task-planner-agent/main.py

The agent will:

• Connect to the LLM.
• Use the consolidated schema json file.
• Itentify tasks based on schema.
• Use the Validator Agent to verify the correctness of the tasks.
• Save the results to tasks.json.

Example output:

[
  "Calculate the number of movies for each GenreName",
  "Compute average and maximum DurationMinutes for each GenreName",
  "Count the number of movies released per ReleaseYear",
  "Retrieve the top 5 movies by DurationMinutes with their Title and ReleaseYear",
  "Calculate the average Rating and total number of reviews for each MovieID",
  "Determine the minimum and maximum length of ReviewText across all reviews"
]

Validation Output:

{
  "validation_passed": true,
  "issues": [],
  "message": "All tasks reference valid tables and columns and can be executed against the provided schema."
}

If validation fails, the agent automatically retries (up to n times) using feedback from the Validator to improve the analysis.