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?

Developed in 2009, MariaDB is an open-source relational database management system that is a drop-in replacement of the MySQL database. The MariaDB Server can be used as a drop-in replacement for the standard MySQL Server. MariaDB was created by MySQL developers to improve performance, reliability, and usability.

MariaDB can support different SQL statements, structures, rules, functions, procedures, server variables, SQL modes, partitioning of tables, database backup, logs, and more.

Many well-known companies, including Google, RedHat, Ubuntu, AWS, Wikipedia, and others, use MariaDB because it is open-source and provides better storage engines.

It is based on the principles of performance, stability, and openness, and the MariaDB Foundation ensures that contributions are accepted based on technical merit. Advanced clustering with Galera Cluster 4, Oracle Database compatibility, and Temporal Data Tables, which allow you to query data as it was at any point in the past, are just a few of the recent new features.

MariaDB is a commercially supported fork of the MySQL relational database management system (RDBMS), which is under the GNU General Public License. Some of MySQL’s original developers are leading the project, which was forked in 2009 due to concerns about Oracle Corporation’s acquisition of the database.

MariaDB is designed to be a drop-in replacement for MySQL, with library binary parity and an exact match with MySQL APIs and commands.

MariaDB is a database that adapts to the modern world in a fundamentally different way. Workloads that used to require a variety of specialized databases can now be supported by our pluggable, purpose-built storage engines.

Organizations can now rely on a single comprehensive database to meet all of their requirements, whether on commodity hardware or in their preferred cloud. MariaDB provides unmatched operational agility without sacrificing key enterprise features like real ACID compliance and full SQL and can be deployed in minutes for transactional, analytical, or hybrid use cases.

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 in 1989, SQL is a relational database management system (RDBMS) developed by Microsoft. SQL Server is a relational database management system based on SQL, a popular programming language. SQL Server has been available on Linux since 2016, but it has previously only been available on Windows operating systems.

Microsoft SQL Server is a Relational Database Management System (RDBMS) that is used in corporate IT environments to support a wide range of Transaction Processing, Business Intelligence, and Analytics applications.

Microsoft SQL Server is available in at least a dozen different editions, each aimed at a different audience and suited to different workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

SQL Server also comes with a number of data management, BI, and analytics tools from Microsoft. SQL Server Analysis Services, an analytical engine that processes data for use in BI and data visualization applications, and SQL Server Reporting Services, which supports the creation and delivery of BI reports, are among the data analysis offerings, in addition to the R Services and now Machine Learning Services technology, which debuted in SQL Server 2016.

The SQL Server Operating System, or SQLOS, is located beneath the Database Engine. Lower-level functions like memory and I/O management, job scheduling, and data locking to prevent conflicting updates are handled by SQLOS. The Database Engine is surrounded by a network interface layer that uses Microsoft’s Tabular Data Stream protocol to facilitate request and response interactions with database servers.

SQL Server DBAs and developers, on the other hand, write T-SQL statements to build and modify database structures, manipulate data, implement security protections, and back up databases, among other things.

The SQL Server consists of a core component called a Database engine. This Database engine consists of a relational engine to process queries and a storage engine to manage databases, files, pages, indexes, and more. Database objects such as stored procedures, views, and triggers are also executed by the Database engine.

Key Features of Microsoft SQL Server

• Analytics Support: Organizations seek insights into stored and managed data to make better decisions. You can use Microsoft SQL to analyze your stored data by integrating it with Microsoft SQL Server Analysis (SSAS), a web-based analytical processing tool.
• Multiple integrations: SQL enables users to perform various tasks like Data Extraction, Transformation, Data Migration, Data Loading, and more on their stored data using Microsoft SQL Server Integration Services (SSIS). In short, SSIS is a platform for enterprise-level data integration and transformation solutions.
• High Performance: SQL Server is a database programming language for creating highly transactional databases. With SQL queries, you can describe your data analytically. 
• High Availability: SQL Server is compatible with a variety of databases, including MySQL, Oracle, Microsoft Access, IBM DB2, and others.
• High Security: Tables, views, stored procedures, and other SQL Server objects can all have permissions assigned to them. Therefore, SQL Server provides security to your database.
• Accelerated Data Recovery: Microsoft SQL Server has an exceptional recovery and rollback mechanism that automatically switches on during a server crash. The process of database recovery leverages Transaction Logs which drastically decreases the delay time.
• Advanced Encryption: Considering that businesses store classified information in databases, ensuring privacy is essential for Database Providers. Since 2016, Microsoft SQL Server relies on a new Encryption Technology known as AlwaysEncrypted that offers transparent Column Encryption without providing Database Administrators any access to Decryption Keys.
• Advanced Analytics: You can easily leverage the data stored in Microsoft SQL Server to implement a comprehensive analysis as per your requirements. Moreover, you can make use of Microsoft SQL Server Analysis Service (SSAS) which to excel at Data Mining tasks.

Connecting SQL Server to MariaDB

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

SQL Server to MariaDB: 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';

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.

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.

visit our website to explore hevo

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!