SQL Server to MariaDB: An Easy Guide 101 for Replication

Organizations use SQL Server for various transaction processing and data manipulation, as it supports complex database operations on large amounts of data within less time. It is compatible with many databases for the migration of information. One such database that can connect SQL Server to MariaDB is CONNECT engine.

MariaDB can connect to external local or remote data using the CONNECT storage engine. This is accomplished by creating tables based on various data types, including files in various formats, data extracted from other DBMS or products using ODBC or JDBC, and data retrieved from the environment.

In this article, you will learn to connect SQL Server to MariaDB using the CONNECT engine.

Prerequisites

To understand how to connect SQL Server to MariaDB you should have a basic understanding of databases.

What is MariaDB?

MariaDB is an open-source relational database management system. Developed in 2009 as a drop-in replacement for MySQL, it strives to improve performance, reliability, and usability compared to MySQL. It has first-class support for a huge variety of SQL statements and features. Advanced clustering with Galera Cluster 4, Oracle Database compatibility, and Temporal Data Tables are some of the key features. 

MariaDB is known for high performance, high reliability, and high openness; it supports both MySQL binary and API. It is designed to be as generic as possible and support pluggable storage engines, allowing it to replace many special-purpose databases while simultaneously supporting comprehensive features.

Key Features of MariaDB

• Storage Engines in MariaDB: MariaDB consists of InnoDB, a storage engine for balancing high reliability and performance with ACID-compliant (Atomicity, Consistency, Isolation, and Durability) transaction features and supports foreign keys. MariaDB consists of another popular storage engine called XtraDB. This storage engine was used in versions before 10.1. After 10.2, InnoDB became the default storage engine of MariaDB.
• Compatibility: MariaDB uses a very popular SQL language for querying data. It supports a variety of programming languages and can run on a variety of operating systems. It can operate on Windows and Linux operating systems and support languages like Python, PHP, C++, Java, JavaScript, Ruby, and more.
• JSON Support: JSON is a standard format for storing data. All the versions of MariaDB 10.2 and later include JSON supporting functions.

What is Microsoft SQL Server?

Developed by Microsoft back in 1989, the SQL Server is a relational database management system designed to assist in running a wide array of applications, which include transaction processing, business intelligence, and analytics. First available on Windows, SQL Server supported Linux in 2016. It has different editions to meet requirements that range from small applications to highly scaled and trafficked large environments, and a package of tools can aid in management and analysis, such as SQL Server Analysis Services, SQL Server Reporting Services, and Machine Learning Services.

Key Features of Microsoft SQL Server

• Advanced Encryption: It leverages AlwaysEncrypted, a technology of Microsoft SQL Server for transparent column encryption, to provide high data privacy without exposing the decryption keys to Database Administrators.
• High Availability: It does support a variety of databases, like MySQL and Oracle, with a strong recovery mechanism and transaction logs to reduce server downtimes in case of a crash.
• Advanced Analytics: It has tools that can execute the deepest data analysis and mining, allowing organizations to glean valuable insights from data. These are included within SQL Server Analysis Services.

Connecting SQL Server to MariaDB

• Method 1: Migrating SQL Server to MariaDB with the CONNECT Storage Engine and CSV Files
• Method 2: Moving Data from MariaDB to SQL Server using CSV Files and CONNECT Storage Engine

Method 1: Migrating SQL Server to MariaDB with the CONNECT Storage Engine and CSV Files

Connect Storage Engine

This is very important to connect SQL Server to MariaDB. The CONNECT engine allows access to any ODBC data source in MariaDB. To use any ODBC data source, you must first install ODBC drivers. ODBC driver uses the Open Database Connectivity that allows applications to access data in the database management system using SQL.

To connect SQL Server to MariaDB using CONNECT Storage Engine follow the steps:

• Step 1: Unpack the ODBC driver for Linux that you downloaded.
• Step 2: Install the ODBC using the build_dm.sh script as follows.

bash build_dm.sh

This is the output that will appear:

• Step 3: Navigate to cd $OLDPWD to verify dependencies after installing the ODBC driver. If these dependencies fail, run yum install yum-utils && yum-builddep unixODBC to install them.
• Step 4: You can install the SQL package on CentOS/REST using the below command.

bash install.sh install

• Step 5: Accept the license agreement by typing capital YES as follows.

Type YES to accept the license, or any other character to cancel the installation: YES

• Step 6: The command below can be used to update the ODBC packages.

yum install unixODBC

• Step 7: Install the CONNECT engine and store it in the MariaDB server using:

yum install MariaDB-connect-engine

• Step 8: The CONNECT storage engine’s shared library is included in the MariaDB packages as ha_connect.so. Therefore, install this library on the MariaDB console. In the MariaDB console, type the following. 

INSTALL SONAME 'ha_connect.so';

Error: If the plugin is already installed or if there is an issue (e.g., the file is not found), the output might indicate an error or provide information about the current status of the plugin

ERROR 1126 (HY000): Can't open shared library 'ha_connect.so' (errno: 0 /usr/lib64/mysql/plugin/ha_connect.so: cannot open shared object file: No such file or directory)

Make sure the ha_connect.so file is in the correct directory and accessible to MariaDB.

The INSTALL SONAME is used to install all the plugins in the ‘ha_connect.so’ library.

• Step 9: Create the table below using the ODBC identifier in /etc/odbcinst.ini – the text configuration file for ODBC drivers. The CONNECT storage engine migrates the SQL ODBC data source to the MariaDB database using the following command.

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'

CSV Files

You can also use CSV Files to connect SQL Server to MariaDB. You can export the CSV files from SQL Server and import them into the MariaDB database. SQL Server Management Studio and other Microsoft tools allow you to export CSV files from SQL Server.

You can import CSV files to MariaDB to connect SQL Server to MariaDB using the LOAD DATA INFILE statement. This is the MariaDB equivalent of BULK INSERT.

• LOAD DATA INFILE: The LOAD DATA INFILE is used to read rows from a text file to a particular table on the database. MariaDB Server will try to read the input file from its file system when you run the LOAD DATA INFILE command. Executing LOAD DATA INFILE activates Triggers. You need to have the FILE privilege to execute the LOAD DATA INFILE statement.

Syntax :

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

Sometimes, it might happen that you do not want to import the entire data from SQL Server to MariaDB but some filtered version of it. In such cases, you must use the CONNECT storage engine to access CSV files to migrate SQL Server to MariaDB and then query them. INSERT SELECT can be used to insert the results of a query like this into tables.

SQL Server to MariaDB: Moving Data from MariaDB to SQL Server using CSV Files and CONNECT Storage engine

   You can move data from MariaDB to SQL Server in the following ways:

• If the tables are in SQL Server, you can use CSV files to migrate data from the SQL server to MariaDB.
• If the tables are in SQL Server, insert tables into CONNECT storage engine that point to remote SQL Server tables. This will migrate the database from the SQL server to MariaDB.

CSV Files

CSV files are used to send data from the MariaDB database to SQL Server. You can produce CSV files from MariaDB in the following ways.

• SELECT INTO OUTFILE Statement: The SELECT INTO OUTFILE statement writes the resulting rows to a file. It allows columns and row terminators to specify a particular output format. Users need the FILE privilege to run this statement. MariaDB also needs file permissions to write files at a specific location. The CHARACTER SET CLAUSE in the SELECT INTO OUTFILE statement specifies the character sets in which the results are to be written.

Syntax :

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

CONNECT Storage Engine

As mentioned earlier, CONNECT engine is used to read a CSV file and query it in SQL. It is also used to filter and transform the data you want to move into MariaDB. However, CONNECT engine can also be used to access remote SQL Server tables.

Using CONNECT Engine to connect SQL Server to MariaDB, you need to fulfill the below requirements.

• Download and install the ODBC driver.
• Install uinxODBC.
• Install CONNECT.

The below example is of the CONNECT engine that will point to the SQL Server table.

CREATE TABLE city (
    id INT PRIMARY KEY,
    city_name VARCHAR(100),
    province_id INT NOT NULL
)
    ENGINE=CONNECT,
    TABLE_TYPE=ODBC,
    TABNAME='city'
    CONNECTION='Driver=SQL Server Native Client 13.0;Server=sql-server-hostname;Database=world;UID=mariadb_connect;PWD=secret';

From above,

• ENGINE=CONNECT: It informs MariaDB that a CONNECT table should be created.
• TABLE_TYPE: It must be ‘ODBC’ so that the CONNECT knows what type of data source it has to use.
• CONNECTION: It is the connection string that consists of the User Name, Password, and Server address.
• TABNAME: It tells the CONNECT what the remote table is called. 

The CONNECT engine can query SQL Server to find out the remote table structure. Besides CONNECT engine, you can also use Linked Server to read data from a remote MariaDB database and then copy it into local SQL Server tables.

Learn about the MariaDB JDBC driver and how it facilitates seamless integration with MariaDB databases through our detailed guide.

Conclusion

In this tutorial, you learned to connect the SQL Server to MariaDB database using the MariaDB storage engine called CONNECT. SQL Server can also be integrated with other databases like Oracle, MySQL, MongoDB, and more. Organizations use SQL Server due to its easy installation process and high performance. SQL Server also provides users with several editions like Enterprise, Standard, Express, and Developer such that every small, medium, or large-scale organization can use SQL Server.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo Data with its strong integration with 150+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Also, let us know your thoughts on this blog on SQL Server to MariaDB migration in the comments!

FAQs to integrate SQL Server with MariaDB