Easy Steps to Convert MySQL to MS SQL

February 11, 2020

This article outlines the major reasons why organizations might want to convert MySQL database to Microsoft (MS) SQL database. The article further discusses about things you should keep in mind before attempting the conversion and the procedure involved. To quickly convert MySQL database records to MS SQL Server, you can use a Specialized Database Converter Tool.

Before we proceed, let’s have an overview of MySQL and MS SQL Server.

Overview of MySQL and MS SQL

MySQL is mainly used for developing web-based applications. It is one of the most popular databases used by Internet giants like Facebook, Google, Wikipedia, etc. to power their websites. MySQL is popular due to its lower costs and flexibility. But despite its popularity and widespread use, a number of organizations are moving away from MySQL due to various limitations related to its scalability, performance & security.

MS SQL Server is a proprietary RDBMS designed and developed by Microsoft. It is used by enterprises for developing mission critical applications, which ensure fast and seamless database development and migration.

Why move from MySQL to MS SQL?

Let’s look at some reasons why you might want to convert MySQL database to MS SQL Server database file:

Scalability

MySQL was not designed with scalability in mind. So, as MySQL database size grows, errors may occur from time to time. On the contrary, MS SQL Server provides high scalability and can handle database of any size.

Better Performance

Based on the comparative performance analysis done by IJARCCE, MS SQL server has a better response time than MySQL. The analysis included execution of “SELECT, INSERT, DELETE and UPDATE” queries on both the RDBMS and their execution time was recorded. It was concluded that–except for INSERT query–MS SQL Server took less time in executing queries.

Security

Compared to MySQL, Microsoft SQL server provides better security. Unlike MySQL which allows DBAs to manipulate database files through binaries at run time, MS SQL doesn’t allow data file manipulation when running. Also, MS SQL offers a Vulnerability Assessment tool (VA) security feature to improve database security, by helping developers identify and fix security vulnerabilities.

What You Need

Before starting with MySQL to MS SQL conversion, make sure your PC meets the following requirements:

  • MySQL 4.1 or higher version
  • For connectivity with MySQL server, install MySQL ODBC Connector.
  • SQL Server 2012 or higher edition
  • SQL Server Management Studio (SSMS)
  • Download and install SQL Server Migration Assistant (SSMA) for MySQL client.
  • The Microsoft .NET framework version 4.7.2 or a later version.
  • 4 GB RAM

Data Type Mapping

Data Type Mapping is a challenging aspect of database conversion. The following data types need to be mapped between MySQL and MS SQL to get correct data after conversion:

Numeric Data Types
MySQL MS SQL Server Mapping notes
TINYINT TINYINT TINYINT is unsigned
SMALLINT SMALLINT  
MEDIUMINT, INT INT INT takes up 4 bytes
DECIMAL(M,D) DECIMAL(P,S) Default precision is 18, while the maximum precision is 38.
FLOAT(N) FLOAT(N)  
DOUBLE(,D); REAL(M,D) FLOAT(53)  
DOUBLE(M,D) SMALLMONEY, MONEY  
String Types
MySQL MS SQL Server Mapping notes
CHAR NCHAR(N), UNIQUEIDENTIFIER NCHAR allows up to 4000 characters
VARCHAR, TINYTEXT, TEXT(M), MEDIUMTEXT, LONGTEXT NVARCHAR(N|MAX) NCHAR allows maximum length of 4000 characters but max indicates maximum storage size of 2^31-1 bytes.
YEAR SMALLINT  
Date and Time Types
MySQL MS SQL Server Mapping notes
DATETIME DATETIME2 DATE can range from 0001-01-01 through 9999-01-01
DATE DATE DATE can range from 0001-01-01 to 9999-12-31
TIME TIME TIME range is 00:00:00.0000000 through 23:59:59.9999999
YEAR SMALLINT  

Steps to Convert MySQL to MS SQL

Converting MySQL database files to MS SQL database files format involves the following steps:

Step 1: Database Schema Conversion

For database conversion, you’ll need to convert target database (MS SQL) schema to equivalent schema of your source database (MySQL) instance. To do so, follow these steps:

1. Open SQL Server Management Studio (SSMS), right-click Databases, and then click New Database.

2. In New Database window, specify a name for the database.

3. Now, open SQL Server Migration Assistant (SSMA) for MySQL and select the New Project icon.

4. Click Connect to MySQL.

5. Connect to MySQL window is displayed. Specify all parameters to connect to the source (MySQL) database, and then click Connect.

6. Once connection to the source database is established, you will have access to MySQL databases.

7. Click Connect to SQL Server to establish connection with the sample database you created above.

8. A list of all MS SQL databases is displayed.

9. In MySQL Metadata Explorer, select the tables you want to convert, and then click Convert Schema.

10. To apply source database schema to the target database, right-click the newly created database, and then select Synchronize with database.

11. When prompted, click OK.

Step 2: Move Data from MySQL to MS SQL

For converting data from MySQL database to MS SQL database format, select the tables in Mysql Metadata Explorer, and then click Migrate Data.

The selected tables and its data will be converted to MS SQL.

While the above discussed procedure does convert MySQL database records to MS SQL, it has certain limitations:

  • Your system must be connected to MySQL and MS SQL Server databases.
  • MS SQL and MySQL must be installed on your PC.
  • Incompatible data types result in unhandled exceptions, preventing you from converting MySQL database tables to MS SQL database format.
  • Novices may require expert support to perform the conversion.

How We Can Overcome These Limitations?

You can overcome these limitations by using the Stellar Converter for Database software. The software allows to convert MySQL database files to MS SQL database file format, without Internet connectivity. It requires only the target database (MS SQL) type server to be installed on the system.

The software is purpose-built to help Database Administrators (DBAs), and developers save time spent in performing complex queries and data types mapping for database conversion.

Also, the Stellar Database Converter tool can be used by novices to perform database conversion, without any technical assistance.

Steps to perform MySQL to MS SQL conversion using Stellar Converter for Database are as follows:

NOTE: Before initiating the conversion process, make sure your system has the following prerequisites:
  • The target database MS SQL server must be installed on your machine.
  • Free space equivalent to the size of the selected database.

Step 1: Download, install and launch Stellar Converter for Database software.

Step 2: In Select Database window, choose MySQL as your source database type.

Step 3: Click Browse under Database Path to select the database file you wish to convert, and then click Scan.

NOTE: If you are not aware of the file path, click ‘Search’ to find database files in your system’s hard drive or a folder.

Step 4: When ‘Scan Complete’ window appears, click OK.

Step 5: The software will display preview of MySQL database objects that can be converted to MS SQL.

Step 6: Select the objects you want to convert from the preview window, and then click Convert on File menu.

Step 7: In Convert and Save Database window, select MS SQL under Select Database Type.

Step 8: Specify details under Connect to Server to establish connection to the target database. Next, select Default SQL or New location to save the converted database files.

Step 9: Click Save.

The selected MySQL database files will be converted to MS SQL database files format.

Conclusion

Scalability, better performance, and security are a few reasons that have prompted organizations to migrate from MySQL to MS SQL.

When it comes to performing conversion, you can convert database file from one format to another by using SQL Server Management Studio (SSMS) and SQL Server Migration Assistant (SSMA). However, the procedure can be time-consuming and challenging for beginners. Most importantly, Data Types Mapping between source and target databases can be quite overwhelming for novices.

By using Stellar Converter for Database software, you can eliminate the manual efforts and time required in conversion process. The software’s simple-to-use User Interface (UI) provides detailed instructions to carry out seamless database conversion.

Frequently Asked Questions

Q. Can we run MySQL query in SQL server client application?

Answer. Yes, we can run MySQL query in SQL server SSMA for MySQL application.

Q. Can I perform MySQL to MS SQL database conversion without MySQL installation?

Answer. Yes, Stellar Converter for Database allows to convert MySQL database to MS SQL database file format without MySQL installed on your PC.

Q. How can I preview the database records that I need to convert from MySQL database file to MS SQL database file?

Answer. Use the demo version of Stellar Converter for Database software to scan and preview the database records that can be converted from MySQL database file to MS SQL database file.