Liberating data from MS SQL server is surprisingly easy thanks to fantastic wizard for MSSQL to MySQL migration provided by MySQL Workbench 5.2.47 and above.

However setting-up unixodbc is a little bit tricky due to lack of good step-by-step documentation so here is our quick start with unixodbc:

Install prerequisites:

##                            "tdsodbc" is a MSSQL driver.
sudo aptitude install unixodbc tdsodbc

Note: in Debian "unixodbc" is in much better shape than "iODBC".

Test MSSQL connectivity (Optional)

Before registering MSSQL driver we can check database connectivity using tsql utility provided by "freetds-bin" package:

sudo apt-get install freetds-bin
tsql -S hostname\\DBNAME -U uid -P pwd

    select db_name()
    go

unixodbc configuration:

Register MSSQL driver using template /usr/share/tdsodbc/odbcinst.ini ("FreeTDS" config section will be created in /etc/odbcinst.ini):

sudo odbcinst -i -d -f /usr/share/tdsodbc/odbcinst.ini

MSSQL database driver configuration:

Create the following configuration in ${HOME}/.odbc.ini or in /etc/odbc.ini:

~~~~ [mymssqldb] Driver = FreeTDS Server = hostname\DBNAME Database = databasename TDS_Version = 0 ClientCharset = ISO-8859-1 ~~~~

"TDS_Version" specifies protocol to use to communicate with SQL Server.

Try out the auto-protocol feature. FreeTDS has experimental support for iteratively trying protocol connections until it finds one the server accepts. This is suitable when query responses are non-trivial (because the tiny delay in connecting is thus insignificant). Try setting your TDS version to 0 and report your results.

Read more:

Check ODBC connectivity:

isql -v mymssqldb uid pwd

Database migration

Now everything is ready to resque data from MSSQL using MySQL Workbench Database Migration Wizard.

If migration wizard is unable to extract schemas make sure you're connecting to MS SQL as user "sa" (non-admin users may lack the required permissions).

Test ODBC access using Perl (Optional)

sudo aptitude install libdbd-odbc-perl libdbi-perl
perl -MDBI -E 'my $dbh = DBI->connect(qw(dbi:ODBC:mymssqldb uid pwd)) or die $DBI::errstr; $dbh->disconnect();'