By: Hassan Shareef,
Database Administrator
Since 15 years
Converting from Microsoft SQL Server to MySQL can be a necessary step for organizations looking to switch database management systems for various reasons, such as cost, scalability, or compatibility.
You may need to carry out the conversion due to several reasons. Like for example, your company has website which uses MySQL in the backend and they have MSSQL for their in-house applications. Moving data from MSSQL to MySQL is a frequent necessity that needs to be performed by DBA's.
The following article can help you in choosing right tool for converting MS SQL Server to MySQL.
Microsoft SQL Server, commonly known as MS SQL
Server, is a relational database management system (RDBMS) developed by
Microsoft. It is a software product that provides powerful tools for
storing, managing, and retrieving data from databases. MS SQL Server is
widely used in enterprise-level applications, web applications, and various
other software systems.
MySQL is an open-source relational database
management system (RDBMS) developed by MySQL AB and now owned by Oracle
Corporation. It is one of the
most popular and widely used database systems in the world, powering many
web applications, content management systems, and other software systems.
MySQL is available under the GNU General Public License (GPL) and can be downloaded and used for free. Additionally, there are several commercial versions of MySQL available with additional features and support.
Cross
database migration is a complex process and it needs to be performed with
proper planning and schedule. 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
Data Loader
MySQL Workbench (Data Migration)
MS SQL Import and Export Wizard
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.
Cons:
Does not convert Procedures and Triggers
Runs only on Windows
Some complex default values were not converted and needed manual intervention
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.
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.
MySQL and MS SQL Server are both powerful relational database management systems that are widely used in enterprise-level applications and web applications. Here are some advantages of MySQL over MS SQL Server:
Cost: MySQL is an open-source database system and is available for free, whereas MS SQL Server is a commercial product and requires a license, which can be expensive.
Portability: MySQL is available on multiple platforms, including Windows, Linux, and macOS, making it easy to deploy on different operating systems. MS SQL Server, on the other hand, is primarily designed for Windows operating systems and has limited support on other platforms.
Performance: MySQL is known for its fast performance and scalability, making it suitable for large-scale applications. MS SQL Server has good performance but may require more hardware resources for larger databases.
Community support: MySQL has a large community of developers who contribute to its development, offer support, and provide plugins and tools. MS SQL Server also has a community, but it is not as large as MySQL's.
Flexibility: MySQL is highly flexible and customizable, allowing developers to modify and extend its functionality to meet their needs. MS SQL Server has a less flexible architecture and may require more effort to customize.
It's worth noting that both MySQL and MS SQL Server have their strengths and weaknesses, and the choice between them ultimately depends on the specific needs and requirements of the project
Using any of the above tools, you can successfully convert from MS SQL Server to MySQL. However, it's important to thoroughly test the converted database before putting it into production to ensure that everything is working as expected.