… using only freeware.
This article show how to transfer data from a MSSQL server in a company behind a firewall to a MySQL database on the internet.
This post is in English because it contains a lot of technical information.
All software that is described here is freeware.
Connection to MySQL server with VPN
To transfer just small amounts of data you can use VPN and an MSSQL linked server. See this post for more information and code examples.
Uploading data with an MSSQL linked server is very slow though. Data is transferred row by row and not in bulk. Therefore there are better ways to transfer large amounts of data.
Export data from MSSQL with BCP
MSSQL comes with a very handy tool that exports data from any SELECT into a CSV file. This tool is called „bcp“.
I use this bcp command line to export data:
bcp "$sql" queryout "$csvPath" -T -C RAW -c -r 0x0A5E0A -t [;]
$sql contains the SQL with the SELECT
$csvPath is the path to the output file
-T uses your windows user to login to the MSSQL database
-C defines the code page, here we use the same as the database. It is the fastest way without any conversion
-c : use character as storage type
-r is the row terminator. I use a string that is never used in any database field. It has two line feeds which makes it easy to check the data in the CSV file
-t is the column separator. Again this is a string that is never used in any database field.
The newly created CSV file can almost be import imported into MySQL, there is just one thing that MySQL does not accept: MSSQL writes \x00 for NULL and MySQL does not recognize this. I used „sed“ to replace \00 to „\N“ which creates NULL in MySQL.
Transfer data to webserver
To transfer the CSV file to the webserver I use SCP. PSFTP from Putty or WinSCP dan upload from command line scripts very well.
Import data into MySQL
To import the CSV file into MySQL very fast I use LOAD DATA INFILE with this syntax:
LOAD DATA INFILE 'mycsv.csv'
INTO TABLE some_temporary_table
CHARACTER SET latin1
COLUMNS
TERMINATED BY '[;]' ESCAPED BY ''
LINES
TERMINATED BY X'0A5E0A'
I always import into a temporary table first. Then I check whether the data seems plausible and if that is OK I update the real table.
To start the import into MySQL I call a website from the MSSQL server. You can do this easily with wget or you can write a PHP script.
Any problems?
You have a problem uploading data from MSSQL to MySQL on a webserver? Please post your question below or send me an email and I will help you.
Sync from MySQL to MSSQL
Read this article when you want to download data from MySQL to MSSQL.
