MySQL to SQL Server Open-Source Transfer Tool

SQLpipe is a free, open-source tool that can transfer the result of a query, or an entire table, from MySQL to SQL Server.

If SQLpipe helps you, please consider starring our GitHub repository! It really helps us.

Step 1 – Install / run SQLpipe

The easiest way to run SQLpipe is with Docker:

docker run --publish 9000:9000 --name sqlpipe sqlpipe/sqlpipe

There are multiple ways to acquire the program, however. You can view all of those ways on the SQLpipe downloads page.

Step 2 – Gather connection information

Gather the following information for MySQL:

• Hostname
• Port
• Database name
• Username
• Password
• Table name (for full table transfer) or write a query

Gather the following information for SQL Server:

• Hostname
• Port
• Database name
• Username
• Password
• Schema name

Step 3 – Submit a transfer command

Transferring data is accomplished by sending a POST request to SQLpipe's /transfer/create route. You can send this kind of command any way you want - from an Airflow DAG, a Cron command, or just Curl on your command line. Here is an outline of the kind of payload you must submit:

{
 "source-name": "<any name you like>",
 "source-type": "mysql",
 "source-connection-string": "<username>:<password@tcp(<hostname>:<port>)/<db name>?parseTime=true&loc=US%2FPacific",
 "target-name": "<any name you like>",
 "target-type": "mssql",
 "target-connection-string": “Server=<hostname>,<port number>;Database=<db name>;User Id=<username>;Password=<password>;”,
 "source-table": "<source table name>",
 "target-table": "<target table name>",
 "drop-target-table-if-exists": true,
 "create-target-table-if-not-exists": true,
 "create-target-schema-if-not-exists": true,
 "target-schema": "<target schema name>",
 "target-database": "<target db name>",
 "target-password": "<password>",
 "target-hostname": "<hostname>;TrustServerCertificate=yes",
 "target-username": "<username>"
}

Note on MySQL sources: you must supply a "parseTime" and url-encoded "loc" query parameter in the source connection string.

Note: to supply additional arguments to a mssql target, you must put them in the target-hostname field, e.g. TrustServerCertificate

Example Curl commands

To move the result of a query, you can use the following command as a starting command:

curl -d '{"source-name": "<any name you like>", "source-type": "mysql", "source-connection-string": "<username>:<password@tcp(<hostname>:<port>)/<db name>?parseTime=true&loc=US%2FPacific", "target-name": "<any name you like>", "target-type": "mssql", "target-connection-string": “Server=<hostname>,<port number>;Database=<db name>;User Id=<username>;Password=<password>;”, "query": "<query>", "target-table": "<target table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true, "create-target-schema-if-not-exists": true, "target-schema": "<target schema name>", "target-database": "<target db name>", "target-password": "<password>", "target-hostname": "<hostname>;TrustServerCertificate=yes", "target-username": "<username>"}' localhost:9000/transfers/create

To move an entire table (and gain slightly better automatic type inference), you can use the following command as a starting template:

curl -d '{"source-name": "<any name you like>", "source-type": "mysql", "source-connection-string": "<username>:<password@tcp(<hostname>:<port>)/<db name>?parseTime=true&loc=US%2FPacific", "target-name": "<any name you like>", "target-type": "mssql", "target-connection-string": “Server=<hostname>,<port number>;Database=<db name>;User Id=<username>;Password=<password>;”, "source-table":"<source table name>", "target-table": "<target table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true, "create-target-schema-if-not-exists": true, "target-schema": "<target schema name>", "target-database": "<target db name>", "target-password": "<password>", "target-hostname": "<hostname>;TrustServerCertificate=yes", "target-username": "<username>"}' localhost:9000/transfers/create

If the command is successful, the program will tell you that a transfer was queued:

{
       "query-transfer": {
               "id": "4cbfb3fa-6dc6-4308-92d5-57b542900193",
               "created-at": "2023-11-21T10:08:10.705804735Z",
               "status": "queued",
               "keep-files": false,
               "tmp-dir": "/tmp/sqlpipe/4cbfb3fa-6dc6-4308-92d5-57b542900193",
               "pipe-file-dir": "/tmp/sqlpipe/4cbfb3fa-6dc6-4308-92d5-57b542900193/pipe-files",
               "final-csv-dir": "/tmp/sqlpipe/4cbfb3fa-6dc6-4308-92d5-57b542900193/final-csv",
               "source-connection-info": {
                       "instance-name": "my-source",
                       "type": "mysql",
                       "hostname": "",
                       "database": "",
                       "username": ""
               },
               "target-connection-info": {
                       "instance-name": "my-target",
                       "type": "mssql",
                       "hostname": "mssql;TrustServerCertificate=yes",
                       "database": "mydb",
                       "username": "sa"
               },
               "drop-target-table-if-exists": true,
               "create-target-schema-if-not-exists": true,
               "create-target-table-if-not-exists": true,
               "source-table": "my_table",
               "target-schema": "dbo",
               "target-name": "mysql_my_table",
               "delimiter": "{dlm}",
               "newline": "{nwln}",
               "null": "{nll}"
       }
}

If you make a mistake submitting the data, SQLpipe will do its best to tell you what's wrong. For example, if you forgot to specify a target table, it would return:

{
       "error": {
               "target-table": "must be provided"
       }
}

Conclusion

Yes, it really is that easy!

If you’d like more in-depth documentation, or want to let us know about a bug, we please make an issue on SQLpipe’s Github page.