MS-SQL to MariaDB Migration Tool

A Java-based CLI application for 1:1 migration of MS-SQL databases to MariaDB.

Deutsche Version (README-DE.md)

Features

• Database Analysis: Automatic extraction of tables, columns, indexes, constraints
• Schema Generation: Automatic conversion of MS-SQL DDL to MariaDB DDL
• Data Type Mapping: Intelligent mapping of MS-SQL to MariaDB data types
• Data Migration: Efficient batch migration with configurable parallelism
• Resume Capability: Resume interrupted migrations
• Progress Tracking: H2-based migration progress tracking
• Feature Detection: Detection of views, stored procedures, triggers

Requirements

• Java 17 or higher
• Maven 3.6+
• Access to MS-SQL source database
• Access to MariaDB target database

Installation

Build from Source

# Clone the project
git clone https://github.com/bertolucci66/MSSQL-to-MySQL.git
cd MSSQL-to-MySQL

# Build with Maven
mvn clean package

# The JAR will be located at:
# target/mssql-to-mariadb-migrator.jar

Configuration

1. Copy the example configuration:

cp config/migration.properties.example config/migration.properties

2. Adjust the configuration to your environment:

# MS-SQL Source
mssql.host=localhost
mssql.port=1433
mssql.database=SourceDB
mssql.username=sa
mssql.password=YourPassword123
mssql.schema=dbo

# MariaDB Target
mariadb.host=localhost
mariadb.port=3306
mariadb.database=TargetDB
mariadb.username=root
mariadb.password=YourPassword123
mariadb.charset=utf8mb4

# Migration Settings
migration.batch.size=1000
migration.parallel.threads=4
migration.fetch.size=5000

# Output Directories
output.schema.directory=output/schema
output.logs.directory=output/logs
output.analysis.directory=output/analysis

Usage

1. Full Migration (Recommended)

Runs analysis, schema generation and data migration in a single pass:

java -jar target/mssql-to-mariadb-migrator.jar full-migration \
    -c config/migration.properties

2. Step-by-Step Migration

Step 1: Database Analysis

Analyzes the MS-SQL database and exports metadata as JSON:

java -jar target/mssql-to-mariadb-migrator.jar analyze \
    -c config/migration.properties \
    -o output/analysis/db_analysis.json \
    --check-features

Step 2: Generate Schema

Generates MariaDB DDL from the analysis:

java -jar target/mssql-to-mariadb-migrator.jar generate-schema \
    -i output/analysis/db_analysis.json \
    -o output/schema/mariadb_schema.sql

Step 3: Apply Schema

Apply the generated schema to MariaDB:

mysql -u root -p TargetDB < output/schema/mariadb_schema.sql

Step 4: Migrate Data

Migrates data from MS-SQL to MariaDB:

java -jar target/mssql-to-mariadb-migrator.jar migrate-data \
    -c config/migration.properties \
    --batch-size 2000 \
    --parallel 4

3. Migrate Individual Tables

java -jar target/mssql-to-mariadb-migrator.jar migrate-data \
    -c config/migration.properties \
    -t "Customers,Orders,OrderDetails"

4. Resume Interrupted Migration

java -jar target/mssql-to-mariadb-migrator.jar resume \
    -c config/migration.properties

Commands

analyze

Analyzes the MS-SQL database structure.

Options:

• -c, --config <path> - Path to configuration file (required)
• -o, --output <path> - Output JSON file (default: output/analysis/db_analysis.json)
• --check-features - Also analyze views, stored procedures and triggers

generate-schema

Generates MariaDB DDL from analysis JSON.

Options:

• -i, --input <path> - Input JSON file (required)
• -o, --output <path> - Output SQL file (default: output/schema/mariadb_schema.sql)
• -t, --tables <list> - Comma-separated list of tables

migrate-data

Migrates data from MS-SQL to MariaDB.

Options:

• -c, --config <path> - Path to configuration file (required)
• -t, --tables <list> - Comma-separated list of tables
• --batch-size <size> - Batch size for inserts
• --parallel <threads> - Number of parallel threads

full-migration

Runs the complete migration (Analysis → Schema → Data).

Options:

• -c, --config <path> - Path to configuration file (required)
• --check-features - Also analyze views, stored procedures and triggers

resume

Resumes an interrupted migration.

Options:

• -c, --config <path> - Path to configuration file (required)

Data Type Mapping

MS-SQL	MariaDB	Notes
INT	INT
BIGINT	BIGINT
DECIMAL(p,s)	DECIMAL(p,s)
MONEY	DECIMAL(19,4)
DATETIME	DATETIME
DATETIME2	DATETIME(6)	Higher precision
VARCHAR(n)	VARCHAR(n)
VARCHAR(MAX)	LONGTEXT
NVARCHAR(n)	VARCHAR(n) utf8mb4	Unicode
NVARCHAR(MAX)	LONGTEXT utf8mb4
VARBINARY(n)	VARBINARY(n)
VARBINARY(MAX)	LONGBLOB
UNIQUEIDENTIFIER	VARCHAR(36)	GUIDs
BIT	BOOLEAN
IMAGE	LONGBLOB

Performance Tuning

• Batch Size: Adjust migration.batch.size (default: 1000)

  • Smaller batches: Better for large BLOBs
  • Larger batches: Better for small records

• Fetch Size: Adjust migration.fetch.size (default: 5000)

  • Higher values reduce database round trips

• Parallelism: Use migration.parallel.threads (default: 1)

  • Useful for many small tables
  • Watch connection pool limits

Limitations

• Views: Detected only, not automatically converted
• Stored Procedures: Detected only, not automatically converted
• Triggers: Detected only, not automatically converted
• Complex Constraints: Must be verified manually
• Computed Columns: Not supported

Logging

Logs are written to the following files:

• output/logs/migration.log - All logs (DEBUG level)
• output/logs/migration-error.log - Errors only (ERROR level)

Troubleshooting

"Connection timeout"

• Check firewall settings
• Increase connection timeout in the configuration

"Out of memory"

• Reduce migration.fetch.size
• Reduce migration.batch.size
• Increase Java heap: java -Xmx2G -jar ...

"Table already exists"

• The generated schema includes DROP TABLE IF EXISTS
• Verify you have write permissions on the target database

Migration hangs

• Check logs in output/logs/
• Use Ctrl+C to abort
• Use resume to continue

Development

Project Structure

src/main/java/com/migration/tool/
├── analyzer/          # Database analysis
├── cli/              # CLI commands
├── config/           # Configuration management
├── connection/       # Connection pooling
├── exception/        # Exception hierarchy
├── mapping/          # Data type mapping
├── migration/        # Data migration
├── progress/         # Progress tracking
├── schema/           # Schema generation
└── util/             # Utility functions

Run Tests

mvn test

Build

mvn clean package

The resulting JAR is located at target/mssql-to-mariadb-migrator.jar.

Contributing

Contributions are welcome! Please open an Issue or submit a Pull Request.

License

This project is licensed under the MIT License.