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:
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:
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.
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.
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).
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.
Full disclosure: This post is not sponsored in any way (especially not by the reviewed products "Navicat Premium 16" and "SQL Examiner 2023").