Migrating from SQL Server to MariaDB

17 Jun 2023 - by 'Maurits van der Schee'

I have a .net application that runs on SQL Server that I want to migrate to MariaDB for licensing reasons. The .net code to query MariaDB looks a lot like the code to query SQL Server, so that is not much work (mainly search and replace). Also the SQL queries need adjusting, but I'm so much more well-versed in MariaDB that rewriting the SQL is a joy.

I use a Debian LAMP server to host the ClickOnce .net application. I use a SSH tunnel with a private/public key-pair instead of a password to encrypt the database connection. This allows me to connect remote to the database even though the database server is not accessible over the Internet (only listens on localhost).

Relevant Nuget packages:

Code conversion

In order to migrate from SQL Server to MariaDB we need to covert the code and the data. First you need to change the code:

Change "Microsoft.Data.SqlClient" to "MySql.Data.MySqlClient" and prefix corresponding classes with "My" ("SqlConnection" to "MySqlConnection" and "SqlCommand" to "MySqlCommand").
Change the SQL queries from the SQL Server dialect to the MySQL dialect. Note that table and columns names in Linux installs of MariaDB are case sensitive.

This is a lot of work (depending on the number of queries), but I currently have not found a way to automate this (except for search and replace). Next up is the data conversion.

Data conversion

Typically I try not to use commercial tools, but for this job I had to convert 50 databases each with 10 to 100 tables with 3 to 30 fields having a combined size of 50 gigabyte. Doing this work manually would have costed weeks (if not months), while with these tools I have completed the task in a few hours. The tools I used are very easy to install on Windows and have a free demo period.

With the "Data Transfer" option of "Navicat Premium 16" (commercial product) convert the database from SQL server to MariaDB.
With the "Synchronize" option of "SQL Examiner 2023" (another commercial product) fine-tune the MariaDB schema to match the SQL server schema.

Note that both "Navicat Premium 16" and "SQL Examiner 2023" have options to keep the data (and structure) of your converted database in sync in the period you are busy converting the code of your application (and testing it).

Conclusion

If you need to migrate from SQL Server to MariaDB then you need to convert the database structure and automate data synchronization. Commercial tools like "Navicat Premium 16" and "SQL Examiner 2023" can do these two things for you and since they have a free (fully functional) demo period there is absolutely no risk in trying them out.

Links

Full disclosure: This post is not sponsored in any way (especially not by the reviewed products "Navicat Premium 16" and "SQL Examiner 2023").

PS: Liked this article? Please share it on Facebook, Twitter or LinkedIn.

TQ
dev.com