By: Hassan Shareef,
Database Administrator
Since 17 years
Converting from MSSQL
to MySQL often becomes imperative for organizations seeking to
migrate their database management systems. This shift can stem from a
multitude of factors including cost-effectiveness, scalability enhancements,
and compatibility requirements.
For instance, consider a scenario
where a company operates a website backend supported by MySQL while
employing Microsoft SQL Server for their internal applications. In such
cases, bridging the gap between the two systems becomes crucial. This
necessitates the expertise of database administrators (DBAs) who are tasked
with the frequent migration of data from MSSQL to MySQL.
A reliable SQL Server to MySQL migration should preserve schema, data types, indexes, and relationships while minimizing downtime.
Fortunately,
there are numerous tools available to facilitate this conversion process,
each with its own set of features and capabilities. Selecting the right tool
tailored to your organization's specific needs is pivotal. Delving into the
intricacies of these tools can streamline the migration process and ensure a
seamless transition.
In this context, the following article serves as
a valuable resource, offering insights and guidance on selecting the most
suitable tool for converting MS SQL Server to MySQL. By leveraging the
information provided, organizations can make informed decisions to
effectively manage their database migration endeavors.
In this article, we review the top 3 tools to convert MSSQL to MySQL, comparing their features, limitations, and suitability for different migration scenarios.
I have chosen the sample Northwind database
which is given by Microsoft MSSQL as a demo database and tried to convert it
to MySQL using the following tools.
Here are the tools which I have
tested and ranked in the order of their efficiency and performance
It stands apart so far from all other tools. Faced the least amount of
issues while converting from SQL Server to MySQL. There were only 2 issues
related to default values which needed manual attention otherwise the
conversion ran smoothly. The best thing which I liked was the ability to
load into existing tables also. None of the other tools lets you load data
into existing tables, they only create fresh tables whereas Data Loader can
load data into fresh tables as well as lets you load into existing tables
and even it allows column mappings. The other good thing is it lets you
Synchronize source and target tables by defining your comparison columns or
you can take the already chosen default Primary key columns. It also comes
with it's own scheduler.
The other best feature I liked is it also comes with a separate
command line tool to execute saved sessions either from other applications
or you can also use Windows Task Scheduler to execute saved Sessions at
particular intervals.
Pros:
Convert and Load data into new tables or existing tables
Column Mapping
Uses latest Providers and Drivers which provides fast data migration and not just the old ODBC drivers.
Synchronization between any database to any other database
Can create SQL dumps where direct connection to target database is not available.
Comes with its own Scheduler to execute conversion at specified time intervals
Email notifications
Can convert Views from one database to another database as tables or Views.
Bulk Loading feature for ultimate fast loading.
Cons:
Does not convert Procedures and Triggers
Runs only on Windows
It's the free tool from MySQL stable. It allows you to convert from MS SQL to MySQL using ODBC drivers. You should have SQL Server ODBC driver installed in order to use this tool. Once you installed the ODBC driver you will need to create ODBC DSN by clicking on ODBC Administrator in Windows Control panel. Once DSN is created you can call it from Workbench to do the conversion.
MySQL Workbench is a GUI tool for managing and working with MySQL databases. The Migration Wizard is just a part of this GUI tool.
To get to the Migration Wizard you will need to :
Start MySQL Workbench
Click on Database menu
Click on Migration Wizard as shown in the picture below
Although it migrated the tables, it failed to convert the
Views. Another thing which was lacking is it doesn't let you load the data
into existing tables leave alone column mapping. It also doesn't let you
save the migration jobs. No synchronization support.
Pros:
Cons:
The MS SQL Server comes with it's own Export and Import tool. It also comes free with Express editions but in Express editions it doesn't let you save migration jobs.
It utilizes ODBC Drivers for conversion. We have to create ODBC Data Source by going into Control Panel first. If you are running the Import & Export tool in the same machine where MSSQL database is running then there is no need to download and install the ODBC driver as this driver is automatically installed when you install MS SQL Server.
You also need to install MySQL ODBC Driver. If you don't have it installed in your system, then you can download it from MySQL website.
After installation of ODBC drivers start ODBC administrator and create Data sources. To start ODBC Administrator
1 Click on Start Menu in Windows
2. Type Data Sources in Run command text box as shown below
3 Click on Data Sources (ODBC) and you will get a window as shown below
4 Click on User DSN or System DSN tab and click Add button. Then it will prompt you to choose DSN (Data Source Name), ODBC driver details and etc.
Pros:
Cons:
| Tool | Synch. Source & Target DB |
Data Migration | Large DB Support | Ease of Use |
|---|---|---|---|---|
| Data Loader | Yes | Yes | Yes | High |
| MySQL Workbench | No | Yes | No | Medium |
| Import/Export Wizard | No | Yes | No | Low |
For Step by Step instructions on converting MSSQL to MySQL using MSSQL Import and Export Tool please click :
How to export data from MSSQL using SQL Server Management Studio (SSMS):
Migrating from MSSQL to MySQL requires careful planning and the right tools. While free tools may work for small databases, professional tools like Data Loader offer reliability, speed, and complete schema conversion for production environments.
If you are looking for a dependable solution to convert MSSQL to MySQL, Data Loader is a proven choice used by database professionals worldwide.
Step by Step Guide to Migrating MSSQL to MySQL