Getting Microsoft SQL server data into MariaDB with the CONNECT storage engine

Posted on April 28, 2016 by

MariaDB works with many clients to migrate Microsoft SQL and Oracle to MariaDB. With the CONNECT storage engine we can access any ODBC data source in MariaDB. Here’s a small HOWTO for those who want to give it a quick try. In this example we use MSSQL, though the same principle should be possible with Oracle ODBC servers.

Getting Microsoft SQL server data into MariaDB with the CONNECT storage engine

We start with a clean MariaDB installation, no ODBC drivers installed yet. In this example we used CentOS7. It’s important to start at a point where unixODBC is not yet installed, because the Microsoft installation package wants to install its own unixODBC.

Step 1

Microsoft is kind enough to supply us with an ODBC driver for Linux. We download it and unpack it.

Step 2

We now install unixODBC using the build_dm.sh script provided by the package. It needs bash to run:

bash build_dm.sh
....
The script is provided as a convenience to you as-is, without any express
or implied warranties of any kind.  Microsoft is not liable for any issues
arising out of your use of the script.

Enter 'YES' to have this script continue: YES

Verifying processor and operating system ................................... OK
Verifying wget is installed ................................................ OK
Verifying tar is installed ................................................. OK
Verifying make is installed ................................................ OK
Downloading unixODBC 2.3.0 DriverManager ................................... OK
Unpacking unixODBC 2.3.0 DriverManager ..................................... OK
Configuring unixODBC 2.3.0 DriverManager ................................... OK
Building unixODBC 2.3.0 DriverManager ...................................... OK
Build of the unixODBC 2.3.0 DriverManager complete.

Run the command 'cd /tmp/unixODBC.32235.28222.16428/unixODBC-2.3.0; make install' to install the driver manager.

Afterwards we install unixODBC with cd /tmp/unixODBC.32235.28222.16428/unixODBC-2.3.0; make install, we go back with cd $OLDPWD. uu As you can see it checks for some dependencies, if these fail you can install them easily with yum install yum-utils && yum-builddep unixODBC.

Warning: Installing the MSSQL driver will fail if we install the newest package from the yum repository.

Step 3

We now install the MSSQL package, the installation on CentOS/RHEL can be executed with checks:

bash install.sh install

We accept the license agreement by typing capitalized YES and see it installed:

Enter YES to accept the license or anything else to terminate the installation: YES

Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ................................. NOT FOUND
unixODBC utilities (odbc_config and odbcinst) installed ............ NOT CHECKED
unixODBC Driver Manager version 2.3.0 installed .................... NOT CHECKED
unixODBC Driver Manager configuration correct ...................... NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .. NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux files copied ................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft SQL Server ODBC Driver V1.0 for Linux registered ........... INSTALLED

We can now also install/upgrade the ODBC packages from our own repository so it will automatically update with our other packages: yum install unixODBC. In /etc/odbcinst.ini we verify it MSSQL still exists.

Step 4

We install the CONNECT engine and load it into the MariaDB server: yum install MariaDB-connect-engine. In the MySQL console: INSTALL SONAME 'ha_connect.so';

Step 5

We create our table using the same ODBC identifier as we read in /etc/odbcinst.ini:

CREATE TABLE mssql_table ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='Driver=SQL Server Native Client 11.0;Server=ms-sql01;Database=old_version_migration_data;UID=mariadb;PWD=password' `TABLE_TYPE`='ODBC'

For more information

See the Knowledge Base for more information about the CONNECT storage engine, or if you are considering a migration to MariaDB, you might want to consider asking about MariaDB Enterprise or our migration consulting services.