… using only freeware.
This article show how to transfer data from a MySQL database on the internet to a MSSQL server in a company behind a firewall.
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
The best way to connect a server in a company network to a MySQL server on the internet is a VPN connection. For this you need a good provider and a network technician to install the VPN.
Connection to MySQL server via SSH (2nd best)
If VPN is not available there is a 2nd best solution with a SSH tunnel.
The SSH client from Bitvise can be used as an SSH tunnel to connect to a MySQL server.
The command line tool “stnlc” can be used to start an SSH connection that will re-establish the connection when it gets interrupted. This program can be installed as a Windows service with the freeware NSSM (Non-Sucking Service Manager).
Weblinks:
www.bitvise.com
www.bitvise.com/ssh-client-unattended
www.bitvise.com/ssh-client-as-windows-service
www.nssm.cc
ODBC driver
To connect to MySQL from any Windows application you need to install the MySQL ODBC driver. I used ODBC 5.3 Unicode Driver which worked well for me.
https://dev.mysql.com/downloads/connector/odbc/
Configure the ODBC driver as shown below in the screenshots.
Do NOT use a password with a ; in it.
Check these checkboxes:
Pad CHAR to full length with space
Return SQL_NULL_CHARACTER for zero date
All other checkboxes NOT checked
Linked server from MSSQL to MySQL
To connect our MSSQL server to the MySQL server we use a “Linked Server”.
Configure the server as in the screenshots below.
Provider string:
DRIVER=(MySQL ODBC 5.3 Unicode Driver);SERVER=localhost;PORT=13306;DATABASE=dbname;USER=xxx;PASSWORD=yyy;OPTION=3;
Note: meaning of OPTION=3 in the MySQL connection string:
Option=1 FLAG_FIELD_LENGHT: Do not Optimize Column Width
Option=2 FLAG_FOUND_ROWS: Return matching rows
Option=3 option 1 and 2 together
Alternative:
Leave providerstring empty and fill user+pw on the next tab.
RPC and RPC Out set to TRUE
Select with Openquery
Use Openquery to Select data from a linked server:
SELECT * FROM openquery(NameOfLinkedServer, 'select* from mytable')
To use a string in the SQL for MySQL you need 2 single quotes:
SELECT * FROM openquery(NameOfLinkedServer, 'select * from mytable where mydate>''2016-09-29''')
When you want to know the table definition of a remote table:
SELECT * FROM openquery(NameOfLinkedServer, 'show columns from mytable')
And then there is the special datetime value in MySQL that does not exist in MSSQL: 0000-00-00 00:00:00. To get a NULL value for this special date:
SELECT * FROM openquery(NameOfLinkedServer, 'select if(mydate>''1900-01-01'',mydate,NULL) as mydate from mytable')
It is possible to select data from MySQL without Openquery but this is not preferable because in this case data is read in full from the remote MySQL before any joins are applied. Rather first import the remote data you need into a local table and then perform the join.
More about performance with linked servers:
https://blogs.msdn.microsoft.com/…
http://www.sqlusa.com/articles2005/linkedserver/
SELECT with EXEC … AT
An example with the EXEC command and with parameters:
declare @myId as int
SET @myId = 10
EXEC('SELECT * FROM mydb.mytable WHERE id=?', @myId) AT [NameOfLinkedServer];
SELECT with a distributed query
You can refer to a table on a linked server in a SQL with 3 dots. As mentioned above this is not the preferred way.
SELECT * from NameOfLinkedServer...mytable where id=1
UPDATE with Openquery
Always remember to transfer only as many recores as necessary via the Openquery SQL.
UPDATE a
SET a.mystring='abc'
FROM OPENQUERY(NameOfLinkedServer,
'select * from mydb.mytable where id=1') a
INSERT into remote from local table
INSERT INTO openquery(NameOfLinkedServer, 'select * from mytable')
SELECT * from [myLocalDb].[dbo].[myLocalTable]
Known problems
The linked server with ODBC has a problem with CHAR fields in the MySQL database. Before any SELECT which selects a CHAR column use this command: “DBCC TRACEON(8765);”
This error exists since 2009 and is still not corrected: https://bugs.mysql.com/bug.php?id=46857&noprocess
Any more problems?
If you have a problem creating a connection between a MSSQL and a MySQL server? Please post your question below or send me an email and I will help you.
Sync from MSSQL to MySQL
The connection via linked server is fast and reliable in downloading data. When you want to upload large amounts of data a linked server is very slow. For a sync from MSSQL to MySQL there is a better and faster way, read about it here.
