Migrate MariaDB to SQL Server: 6 Easy Methods

Companies migrate from MariaDB to SQL Server to consolidate onto Microsoft technology, integrate more tightly with Azure and other Microsoft services, and take advantage of SQL Server's enterprise features and security controls. Whatever the reason, the migration comes down to one question: how do you move your data across reliably?

There are six ways to move data from MariaDB to SQL Server, ranging from quick manual exports to a fully automated pipeline:

• Method 1: Using a CSV File
• Method 2: Using a Dump (Structure)
• Method 3: Using a Dump (Data)
• Method 4: Using CONNECT Tables
• Method 5: Using a Linked Server
• Method 6: Using a No-Code Tool like Estuary

The first five are native or manual approaches. They work for a one-time move but take hands-on effort and do not keep the two databases in sync afterward. The sixth is an automated, real-time option. Before we walk through each, here is a quick look at both platforms.

What Is MariaDB? An Overview

Image Source

MariaDB is a relational database management system (RDBMS) designed as a highly compatible drop-in replacement for MySQL. While it's compatible with MySQL's commands and APIs, it also includes additional features such as window functions, JSON functions, and system-versioned tables.

Apart from inheriting the storage engines available in MySQL, MariaDB includes the Aria storage engine, which is a crash-safe alternative to MyISAM. This was the default storage engine from MySQL 3.23 until it was replaced by InnoDB in MySQL 5.5 and MariaDB. 

MariaDB also supports the TokuDB storage engine, which is used in high-performance and write-intensive environments. Apart from this, the MariaDB ColumnStore storage engine, which uses a massively parallel architecture, provides columnar storage capabilities for real-time analytics.

In terms of security, MariaDB offers multiple features designed to protect data and maintain database integrity. It allows encryption of data at rest in entire tables and tablespaces to protect data stored on disks. MariaDB also supports the encryption of data in transit between the server and clients with the use of TLS/SSL, providing an additional layer of protection.

What Is SQL Server? An Overview

Image Source

SQL Server is an RDBMS first released by Microsoft in 1989. It’s a user-friendly, dependable, and effective database system with lots of staying power. Key features of SQL Server include columnstore indexes, resulting in improved analytics query performance, and database mirroring capabilities that keep a standby copy of a database readily available.

At the core of the SQL Server architecture is the database engine, which consists of a query engine and a storage engine. The query engine is responsible for evaluating user requests and executing them. Also known as the query processor, it contains the components that determine the best way to execute a query.

Based on the input query, the query engine requests data from the storage engine and processes the results. The storage engine is responsible for storing and retrieving data from storage and buffer managers (memory).

Method 1: Using No-Code Tools like Estuary to Connect MariaDB to SQL Server

No-code tools are an efficient option for integrating platforms without significant manual intervention. Such tools have an intuitive interface, lower error rate, and scalable architecture that makes it easy to set up data migration pipelines between different sources and destinations.

Estuary, an advanced DataOps platform, is an effective no-code tool that simplifies the process of migrating data from MariaDB to SQL Server. Estuary supports real-time data transfers between the source and destination, with end-to-end latency in the millisecond range.

To set up a data migration pipeline between MariaDB to SQL Server using Estuary, you first must register for a free Estuary account. If you already have one, sign in to your account and follow these simple steps.

Step 1: Configure MariaDB as the Data Source

Before you use Estuary’s MariaDB source connector, ensure you’ve met the prerequisites.

After signing in, select Sources from the left-hand side pane of the Estuary dashboard to begin setting up the source end of the ETL pipeline. Click + NEW CAPTURE and search for the MariaDB connector using the Search connectors box. When the connector appears in the search results, click the Capture button.

You’ll be redirected to the MariaDB connector page. There, you need to fill in the required fields, including a Name for the capture, Server Address, and Login Password. Once that’s done, click NEXT followed by the SAVE AND PUBLISH button.

The MariaDB connector is a change data capture (CDC) connector that captures change events from a MariaDB database via the Binary Log.

Step 2: Configure SQL Server as the Data Destination

Ensure you meet the prerequisites before using Estuary’s SQL Server connector.

Following a successful capture, a pop-up appears with the associated details. To begin setting up the destination end of the integration pipeline, click the MATERIALIZE COLLECTIONS button.

This will redirect you to the Create Materialization page. Use the Search connectors box to look for the SQL Server connector. When you see the connector in the search results, click the Materialization button.

On the SQL Server connector page, specify the required details, such as a Name for the materialization, Address, User, Password, and Database.

While the Estuary collections will be automatically selected for you, consider using the Source Collections section to manually add the collections you want to materialize into SQL Server. Then, click NEXT > SAVE AND PUBLISH.

The connector will materialize Estuary collections into tables in a Microsoft SQL Server database.

Method 2: Using CSV Files to Migrate from MariaDB to SQL Server

You can also use CSV files to export data from MariaDB to SQL Server. 

There are a few different ways to create CSV files from MariaDB, including:

• The CSV storage engine (it doesn’t support NULL and indexes).
  
• The CONNECT storage engine, with the CSV table type.
  
• The SELECT INTO OUTFILE statement.

Method 3: Using a Dump (Structure)

With mariadb-dump, you can generate a dump of a database, all databases, or a set of tables, and you can limit it to specific rows with a WHERE clause. To dump table structures without data, use the --no-data option:

plaintext
mariadb-dump --no-data your_database > structure.sql

One thing to know before you load it: mariadb-dump produces MySQL-flavored SQL, not SQL Server T-SQL. Even with --compatible=mysql, the output is a starting point that needs manual conversion for SQL Server, including data types, AUTO_INCREMENT columns, and identifier quoting. Plan for that editing step rather than expecting the dump to load as-is.

Method 4: Using a Dump (Data)

By default, mariadb-dump outputs both structure and data. A few options help when you want data only or are working with large tables:

plaintext
mariadb-dump --no-create-info --single-transaction --quick your_database > data.sql

• -no-create-info skips the CREATE TABLE statements so you export data alone.
• -single-transaction reads the source in one transaction for a consistent dump.
• -quick speeds up dumps of large tables.

As with the structure dump, the result is MySQL-flavored SQL. You will still need to adapt it to SQL Server's syntax before loading.

Method 5: Using CONNECT Tables

You can also use the CONNECT storage engine to access external data in different forms, including:

• Data files in multiple formats (e.g., CSV, XML, JSON, HTML, and more)
  
• Remote databases, using the JDBC or ODBC standards, or MariaDB/MySQL native protocol
  
• Some special data sources

CONNECT can access remote SQL Server tables, read data from them, and even write to them. For CONNECT to work with SQL Server, you need to meet these prerequisites:

• Install the ODBC driver
  
• Install unixODBC
  
• Install CONNECT

Here’s an example of a CONNECT table that points to a SQL Server table:

plaintext
CREATE TABLE organization (
    org_id INT PRIMARY KEY,
    org_name VARCHAR(255),
    org_address VARCHAR(500),
    founded_date DATE
)
    ENGINE=CONNECT,
    TABLE_TYPE=ODBC,
    TABNAME=’organization'
    CONNECTION='Driver=SQL Server Native Client 13.0;Server=sql-server-hostname;Database=business_directory;UID=mariadb_connect;PWD=secret';

• ENGINE=CONNECT tells MariaDB that you want to create a CONNECT table.
  
• TABLE_TYPE must be ODBC for CONNECT to know what type of data source it needs to use.
  
• CONNECTION is the connection string to use which includes the server address, username, and password.
  
• TABNAME tells CONNECT what the remote table is called.

Method 6: Using a Linked Server

Not interested in any of those methods? You can also use the SQL Server Linked Server (SSLS) functionality to convert MariaDB to SQL Server. This reads data from a remote MariaDB database and copies it into SQL Server tables on your local machine. However, CONNECT gives you more control over type and character set mapping.

Benefits of Using Estuary to Connect MariaDB to SQL Server

While it’s certainly possible to migrate data from MariaDB to SQL Server manually, doing so comes with several drawbacks:

• Custom scripting to transform the schema of MariaDB data to SQL Server-specific schema is complex and requires manual effort.
  
• Large databases generate huge dump files, which slows the migration process.
  
• Extracting data from MariaDB, manually transforming it for schema compatibility, and loading it to SQL Server is a resource-intensive process. Due to its time-consuming nature, real-time data integration is impossible.

Good news: Estuary, an efficient no-code tool, can help overcome these challenges. There are several benefits of using Estuary to integrate MariaDB and SQL Server, including:

• Automation. Estuary automates the entire migration process, from data extraction and transformation to loading into the destination. Since manual efforts aren’t required, you can redirect time and resources to other critical tasks.
  
• Fully managed. As a SaaS solution, updates and security patches are handled by the Estuary team, eliminating the need for in-house maintenance.
  
• Scalability. The enterprise-grade system of Estuary can seamlessly scale, supporting flows of 7GB/s for 10TB tables.
  
• Change data capture. Estuary uses advanced log-based CDC techniques which help capture granular data changes at the source. This reduces associated latencies and maintains data integrity.

Ready to streamline your data migration? Register for free at Estuary and start moving data from MariaDB to SQL Server in minutes. Need assistance? Contact us today!

Conclusion

Migrating MariaDB to SQL Server helps with scalability and integration across Microsoft enterprise services, which in turn supports smoother operations and better data-driven decisions.

The manual methods (CSV files, a structure dump, a data dump, CONNECT tables, and a linked server) all work, but they tend to be time-consuming, resource-intensive, and error-prone, and none of them keep your data in sync in real time. A no-code tool like Estuary removes that overhead: with ready-made connectors, an intuitive interface, and change data capture, it moves your data and keeps SQL Server current in minutes. You can register for free and start moving data from MariaDB to SQL Server, or contact the team if you want a hand setting it up.