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.

How to Convert SQL Server to MySQL?

Method 1: Using Hevo Data to Automate the Process

Hevo Data streamlines your SQL Server to MySQL migration process and offers a real-time extract, transform, load (ETL) solution. Hevo is user-friendly and does not require manual coding to create a data integration pipeline and, therefore, works efficiently even if you do not have any prior technical experience.

Here’s a breakdown of the process:

Step 1: Configuring SQL Server as the Source

• Create New Pipeline: Click PIPELINES in the Navigation Bar, then click + CREATE PIPELINE and select SQL Server as your source type.
• Test and Continue: Click TEST CONNECTION to validate your settings, then click TEST & CONTINUE to proceed with destination setup once all mandatory fields are completed.
• Enter Connection Details: Provide your Pipeline Name, Database Host (IP address or DNS like ms-sql-server-1.xxxxx.rds.amazonaws.com), Database Port (default: 1433), Database User credentials, and Database Password for the read-only user.
• Configure Database Settings: Specify your Database Name, Schema Name (default: dbo), select your preferred Ingestion Mode (Change Tracking, Table, or Custom SQL), and enable SSH connection if additional security is required.

For more details on connecting MS SQL Server, refer Hevo Docs.

Step 2: Configuring MySQL as the Destination

• Create New Destination: Click DESTINATIONS in the Navigation Bar, then click + CREATE DESTINATION and select MySQL from the destination options.
• Enter Connection Details: Provide your Destination Name, Database Host (IP address or DNS), Database Port (default: 3306), Database User credentials, Database Password, and target Database Name where data will be loaded.
• Configure Security Settings: Enable SSH connection for additional security if needed, set up SSL encryption with CA certificates and client keys if required, and enable table/column name sanitization to replace non-alphanumeric characters with underscores.
• Test and Save: Click TEST CONNECTION to validate your configuration settings, then click SAVE & CONTINUE to complete the MySQL destination setup once all mandatory fields are specified.

For more details on connecting MySQL Destination, refer Hevo Docs.

Method 2: Using ODBC Driver

Pre-Migration Considerations

• Ensure MySQL Connector/ODBC is installed on the migration machine.
• Verify MSSQL and MySQL connections using ODBC.
• Create schema, tables, and define necessary data types.
• Map MSSQL data types to MySQL (e.g., DATETIME2 → DATETIME).

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.

Data Type Mapping Between SQL Server and MySQL

When migrating data from SQL Server to MySQL, one of the critical technical considerations for data engineers is the accurate mapping of data types. While MySQL supports most data types in MS SQL Server, direct one-to-one equivalents don’t always exist for every specialized type. Correct mapping is vital for maintaining data integrity and precision and preventing data loss or truncation during migration.

Below is a table highlighting some common SQL Server data types and their typical corresponding types or representations in MySQL.

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)

Managing these conversions manually, especially across numerous tables and columns, can be tedious and error-prone, adding significant overhead to the migration project.

Hevo Data intelligently and automatically handles the complexities of data type mapping between SQL Server and MySQL. During the pipeline setup, Hevo infers the source schema and maps the SQL Server data types to the most appropriate MySQL data types.

Limitations of Manual Method

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

• Lack of Automation: The manual migration process necessitates continuous supervision and user interaction, which reduces efficiency and adds to the effort. 
• 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.

These challenges reveal the inefficiencies and risks of manual SQL Server to MySQL migrations. An automated data pipeline solution like Hevo Data should be used to combat these challenges. 

Hevo Data can automate the data extraction, schema mapping, data type conversion, and loading process for better efficiency, reduced manual effort, reduced errors, and a successful and reliable process. 

This allows data engineering teams to get data transferred quicker and with more confidence while enabling them to spend time on more essential data objectives.

Understanding migration from MS SQL Server to MySQL

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.

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.

Check out our blogs on some other migrations you may find handy:

• WooCommerce to MySQL
• Magento to MySQL
• Stripe to MySQL
• CleverTap 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 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.

Frequently Asked Questions