How to Migrate MS SQL to MySQL in 3 Methods?

Unlike SQL Server, MySQL is a much cheaper option, even if you opt for an enterprise edition. With MySQL, you can also enjoy a wide range of operating systems, including several Linux, Solaris, and Mac distributions. You also get a scalable database infrastructure with several additional features that have been tested over many years by a vast open-source community.

You can easily convert SQL Server to MySQL by setting up ODBC drivers and the MySQL Workbench or automating the process using third-party tools. In this article, you will learn how to effectively carry out SQL Server to MySQL migration using 2 different methods.

How to Convert SQL Server to MySQL?

Method 1: Using Hevo Data to Automate the Process to Convert SQL Server to MySQL 

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs.

With integration with 150+ Data Sources (60+ free sources), we help you not only export data from sources & load data to the destination such as MySQL but also transform & enrich your data, & make it analysis-ready. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Without the need for manually setting up any connection drivers or the limited-feature MySQL Workbench, you can effortlessly carry out mssql to mysql migration using Hevo by following the simple steps given below:

Step 1: Configuring Source

Step 2: Configuring Destination

Automatic Data Backup for Recovery Mechanism to Avoid Data Loss

The automated data pipeline is capable of helping you when there is a pipeline failure due to a functional bug or any other technical issues like a power outage. When the sources are SaaS, you can re-ingest the data by re-running the Pipeline.

For webhook and log-based Sources, there is a chance of data loss. To avoid this, an encrypted backup of your last three days’ data is kept for webhook and log-based Sources.

Hevo’s mechanism avoids any data loss as offset can be reset to three days prior, and it allows to re-ingest data. The primary keys will be retained during this process.

Salient Features of Hevo

• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
• Data Transformation: Hevo provides a simple interface to perfect, modify, and enrich the data you want to transfer.
• Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.

Method 2: Convert MSSQL Server to MySQL Using ODBC Driver

Before Migration:

• Have user privileges on both databases for the task.
• MySQL Workbench has access to both databases.

MySQL Workbench provides a Migration Wizard that allows you to migrate mssql to MySQL Server to MySQL. However, you need to set up the MySQL ODBC (Open Database Connectivity) Driver to up the connection between these databases. You can achieve this by following the steps given below:

• Step 1: Configure the ODBC Driver to Convert SQL Server to MySQL
• Step 2: Use the MySQL Migration Wizard to Convert SQL Server to MySQL

Step 1: Configure the ODBC Driver to Convert SQL Server to MySQL

Before using the MySQL Workbench database, you have to set up the ODBC Driver to convert SQL Server to MySQL. An ODBC driver allows applications and programming languages to interact with and access Databases or a cloud application. You can easily set up your driver by going through the following steps:

• Step 1:  Click on your Windows icon. Search for “Windows Administrative Tools” in the search bar & select it.
• Step 2: From the list of tools, there will be 2 ODBC drivers namely for 32-bit and 64-bit. You can select the one according to your system. For this example, the 64-bit ODBC driver is selected.
• Step 3: An ODBC Data Source Administrator(64-bit) window will pop on your screen. Click on the System DSN tab and then click on the Add button.

• Step 4: From the list of Data Sources, scroll down, select the SQL Server, and click on the Finish button.
• Step 5: A new “Connect a New Data Source to SQL Server” window will appear on your screen. As a new ODBC data source is being created to act as a mediator between MySQL and SQL Server, you can provide the name & description you would like to refer to.
• Step 6: For the SQL Server name, open your Microsoft SQL Server Management Studio. You can directly copy the SQL Server name from the landing window i.e. the “Connect to Server” window.

You can also write a simple SQL command to retrieve the SQL Server name.  

• Step 7: Copy and paste the Server Name in the “Connect a New Data Source to SQL Server” window. Click on the Next button to move forward. Leaving the default settings for the SQL Server Authentication method as Windows NT, click on the Next button.
• Step 8: Now, check the “Change the default Database to” box. You can now select your desired database that you want to migrate to MySQL and click on the Next button. In this example, the “Work” database is selected. 

• Step 9: Click on the Finish button and then click on the Test Data Source button. Upon a successful test, you will get a confirmation message. This completes the ODBC Driver step in the process to convert SQL Server to MySQL.

Step 2: Use the MySQL Migration Wizard to Convert SQL Server to MySQL

After you have set up your ODBC Data Source, you can now use the MySQL Workbench to convert SQL Server to MySQL. To do that, you can follow the simple steps given below:

• Step 1: Open your MySQL Workbench and navigate to Database > Migration Wizard.
• Step 2: In the Migration, you can view the Migration Task List on the left side of your screen that you to follow. To get started, click on the Start Migration button.
• Step 3: On the Source Selection page, you can now enter your MS SQL Server Connection parameters as shown below. Click on the Test Connection button once you are done. A confirmation message will be displayed on your screen afterward.

• Step 4: Click on the Next Button to move to the Target Selection page. Now, enter your MySQL destination details. Once you are done, you can click on the Test Connection button and a successful test confirmation message will pop up on your screen.

• Step 5: Click on the Next button. The MySQL Migration Wizard will now fetch the Schema list from your SQL Server. Click on the Next button after the list is successfully extracted.
• Step 6: In the list of databases, scroll down and select your desired database schema. In this example, the Work database is selected. 

• Step 7: Click on the Next button and now the MySQL Migration wizard will fetch the selected schema metadata. Click the Next button once that is complete.
• Step 8: Click on the View Selection button to see or edit the objects that will be migrated when you convert SQL Server to MySQL. Click on the Next button after selecting your desired objects.
• Step 9: The MySQL Migration Wizard will convert the SQL Server Objects to MySQL compatible objects. Click on the Next button after MySQL Workbench finishes the task.
• Step 10: To let MySQL Workbench create the required schema in the target RDMS i.e. MySQL, leave the default settings as it is and click on the Next button. MySQL will generate SQL scripts and execute them to create the desired schema. Click on the Next button once that is done.

• Step 11: The MySQL Wizard will now display a report representing the status of the schema elements in the Target database. Review the list and click on the Next button.
• Step 12: The MySQL Migration Wizard will present you with a few ways to convert SQL Server to MySQL. Keeping the default option of copying the online table to Target RDMS, you can click on the Next button. 

• Step 13: Click on the Next button after the process to convert sql to mysql online. Final Data Migration will be displayed on your screen.  This completes the manual method to convert SQL Server to MySQL.

Method 3: Migrate SQL Server to MySQL with MySQLyog

1.     Install MySQL Client tool SQLyog to your system.

2.     Right-click the database you are targetting> Import> Import External Data…

3.     Click on Start a new job and select Next.

4.     If there isn’t DSN that should be exported to SQL Server in your list, click on Create a New DSN. Select File Data Source (Irrelevant to the Machine) (F), and select Next. Then click on SQL Server as the driver. Select Next.

5.     Type in a name for the data source file or browse for a location, and select Next.

6.     Give a name and description for the source, and click on a SQL Server from the drop-down. Select Finish.

7.     Open the Create new data source to SQL Server window. Select Use user to enter login ID and password for SQL Server authentication (S) when you connect the remote server, and provide the ID and password. Click on Next.

8.     Select Change the default database to box and click on one from the drop-down, then select both Use ANSI reference identifier (U) and Use the null value, provide details, and warning of ANSI (A). After that, Test the Data Source.

9.     Choose Any ODBC Source as the Type of Data Source, and select the newly made File DSN. Provide the SQL Server account and password to log in to it.

10.  Select the target MySQL server, provide the IP address, and other account details. Do a test of the connection.

11.  Choose Copy table(s) from the data source and select Next.

12.  Click on a portion or all the tables to move your data.

Tip: 

• Make sure that the bit type and the date (datetime) type is properly mapped. Otherwise, the migration won’t be successful. You can carry this out under the Map.
• You need to change the boolean in MySQL which will be tinyint.

13.  Go to the Advanced options, click on Import into existing table, Update with source details. Import foreign keys under the DDL/DML Options, and select OK.

14. Either select or leave the Error handling options and select Run immediately for the task. Find a location for error logs during the task execution.

15.  After this, you can check the execution results and do a comparison of the data.

Limitations of Manually Converting SQL Server to MySQL (Method 2 &3)

Although there is more control when transferring data manually from SQL Server to MySQL, there are a few drawbacks:

• Time-consuming and Error-Prone

Writing conversion scripts, handling data transfer, and manually analysing schemas may be time-consuming and error-prone, particularly when dealing with huge datasets or intricate architectures.

• Risk of Data Loss

The use of manual procedures raises the risk of data loss or inconsistent results from human mistake during import and transformation.

• Restricted Scalability

Large dataset migrations can be laborious and resource-intensive, resulting in prolonged downtime and possible performance problems.

• Lack of Automation

The manual migration process necessitates continuous supervision and user interaction, which reduces efficiency and adds to the effort. 

Reasons to Move MS SQL Server to MySQL

Strong relational database management systems like MySQL and MS SQL Server are frequently used in online and enterprise-level applications. Here are some advantages of MySQL over MS SQL Server:

• Cost

MS SQL Server is a commercial product that needs a license, which may be costly; in contrast, MySQL is an open-source database system that is free to use. Even though MS SQL Server Express is offered online for free, it has some restrictions. For instance, it has a maximum database size of 10 GB and can only address up to 1 GB of RAM. Therefore, for production installations, you will have to pay for SQL Server’s licensed edition.

• Portability

Because MySQL is compatible with many operating systems, including Windows, Linux, and macOS, it may be easily installed on a variety of platforms. Conversely, MS SQL Server has limited support on other platforms and is primarily geared for Windows operating systems.

• Performance

MySQL is well-known for its scalability and quick speed, which make it appropriate for demanding applications. Although MS SQL Server performs well, bigger databases could need more hardware resources.  MySQL typically downloads and installs in 15 minutes or less.

Oracle provides MySQL Installer, which makes the installation and configuration procedure simpler for Windows customers. It simply takes three minutes to install and set up MySQL products on Windows using MySQL Installer.

• Flexibility

Because of MySQL’s great adaptability and customization, developers may add new features and alter it to suit their requirements. The design of MS SQL Server is less versatile; therefore, customization may take more work.

• Usability 

Compared to SQL Server, MySQL installs more quickly and has fewer tuning configuration knobs and footprints for massive database administration.

• Storage support

While MySQL provides a variety of storage engines and plug-in storage solutions, SQL Server only supports one storage engine.

• Graphical tool

While MySQL comes with an integrated MySQL Workbench for creating, designing, and building databases, SQL Server is dependent on the T-SQL prompt and does not support any connectors.

• High availability

A few tried-and-true techniques that MySQL offers that, depending on the circumstance, can equal or even surpass SQL Server performance include replication, SANs, and MySQL Cluster.

Data Type Mapping Between SQL SERVER and MySQL

MySQL supports all the important MS SQL data types. However, there are some SQL server data types that do not match with MySQL data types. Some of the major data types you’ll need to map MySQL with are as follows:

SQL Server	MySQL
VARCHAR(max	LONGTEXT
SQL_VARIANT	BLOB
IDENTITY	AUTO_INCREMENT
NTEXT, NATIONAL TEXT	TEXT CHARACTER SET UTF8
SMALLDATETIME	DATETIME
DATETIMEOFFSET	TIMESTAMP
MONEY	DECIMAL(19,4)
UNIQUEIDENTIFIER	BINARY(16)
SYSNAME	CHAR(256)

Additional Resources on SQL server to MySQL

• Stream Data From MySQL to SQL Server
• Connect MariaDB to MySQL

Conclusion

• In this article, you have learned how to effectively convert SQL Server to MySQL using 2 different methods.
• If it is one-time data migration or you rarely need to convert sql server to mysql online, you can configure your ODBC driver and use MySQL Workbench as shown in the second method.
• However, if you need to frequently replicate data that require complex transformations, you can eliminate all this hassle and automate your process by opting for a No-Code Automated ETL Tool like Hevo Data!
• Hevo Data is a No-code Data Pipeline solution that can help you import data from a vast sea of sources like SQL Server to your desired destination such as MySQL, Data Warehouses, or BI tools.
• Hevo also supports MySQL as a Source and MS SQL Server as a destination. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

FAQ on Migrate MS SQL to MySQL