How to Migrate MS SQL to MySQL in 2 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 the migration using 2 different methods.

What is MS SQL Server?

Microsoft SQL Server is a widely used relational database management system (RDBMS) designed to provide users with data storage and retrieval facilities per their requirements. Microsoft SQL Server facilitates many functionalities, from which transactional processing and data analytics are popular among businesses today. Moreover, Microsoft SQL Server is one of the top three leading database technologies, along with Oracle Database and IBM DB2.

What is MySQL?

MySQL is considered to be one of the most popular open-source relational database management systems (RDBMS). MySQL implements a simple client-server model that helps its users manage relational databases, i.e., data stored in the form of rows and columns across tables. It uses the well-known query language, structured query language (SQL), which allows users to perform all required CRUD (Create, Read, Update, Delete) operations.

How to Convert SQL Server to MySQL?

Method 1: Using Hevo Data to Automate the Process

Step 1: Configuring Source

Step 2: Configuring Destination

Method 2: Using ODBC Driver

Step 1: Launch MySQL Workbench Migration Wizard

1. Open MySQL Workbench.
2. Navigate to Database -> Migrate in the main menu.
3. A new tab will appear, displaying the Migration Wizard Overview page.

Step 2: Check Prerequisites

Before proceeding, ensure the following:

• You have installed an ODBC driver for SQL Server.
• For Windows, check for installed drivers via ODBC Data Source Administrator.
  • Open MySQL Workbench and navigate to Plugins -> Start ODBC Administrator.
  • Alternatively, open a command prompt and type odbcad32.exe.
• If missing, install the SQL Server Native Client for compatibility.

Step 3: Configure Source Database Connection

1. Click Start Migration in the Migration Wizard Overview.
2. In the Source Selection page:
   • Choose Microsoft SQL Server as the database system.
   • Select ODBC (native) as the connection method.
   • Enter the SQL Server instance name (e.g., localhost\SQLEXPRESS) or IP address.
   • Provide authentication credentials.
   • Specify the database name (optional).
   • Click Test Connection to ensure connectivity.

Step 4: Configure Target Database Connection

1. Click Next to proceed to the Target Selection page.
2. Provide details for your MySQL Server instance:
   • Choose MySQL as the target database system.
   • Enter MySQL Server hostname, port, username, and password.
   • Click “Test Connection” to verify access.

Step 5: Select Schemas for Migration

1. Click “Next” to retrieve available schemas from MSSQL.
2. If no database was specified earlier, select the desired schema(s) from the list.
3. Choose how to handle schema names:
   • Merge schemas into a single MySQL database.
   • Retain individual schemas as separate MySQL databases.
4. Click “Next” to continue.

Step 6: Select Objects to Migrate

1. The Migration Wizard will reverse-engineer the MSSQL schema.
2. A list of objects (tables, views, stored procedures) will be displayed.
3. By default, only tables are selected. You can manually add views, functions, or triggers if needed.
4. Click Next.

Step 7: Review and Convert Schema

1. The wizard will generate MySQL-compatible schema definitions.
2. Review the Object Editing section:
   • Modify database names if required.
   • Adjust column mappings to align with MySQL data types.
   • Click Next.

Step 8: Create Database Objects in MySQL

1. In the “Target Creation” options page:
   • Choose to run the generated MySQL script automatically.
   • Optionally, save the script for manual execution.
2. Click Next to create the database objects in MySQL.

Step 9: Data Migration

1. MySQL Workbench will now transfer data from MSSQL to MySQL.
2. Monitor progress in the “Data Transfer” page.
3. Click “Next” once the process completes successfully.

Step 10: Verify Data Integrity

1. Use MySQL Workbench or a MySQL client to inspect migrated tables.
2. Validate row counts and data consistency between MSSQL and MySQL.
3. Test application queries to ensure correct functionality.

Limitations of Manual Method

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 MSSQL to MySQL

1. 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, you will have to pay for SQL Server’s licensed edition for production installations.

2. Portability

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

3. 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.

4. 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.

5. Usability 

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

6. Storage support

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

7. 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.

Also, check out the 10 Best Microsoft SQL Server ETL Tools.

8. High availability

 MySQL offers several tried-and-true techniques—such as replication, SANs, and MySQL Cluster—that can match or even surpass SQL Server’s performance depending on the use case.

You can also take a look at the advantages of moving data from SQL Server to a data warehouse like Google BigQuery to identify more use cases of migrating data from SQL Server.

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)

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 MSSQL to MySQL, 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, or Data Warehouses.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your data integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

FAQ