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:
ScalabilityMySQL 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 PerformanceBased 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.
SecurityCompared 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:
-
Open SQL Server Management Studio (SSMS), right-click Databases, and then click New Database.
-
In New Database window, specify a name for the database.
-
Now, open SQL Server Migration Assistant (SSMA) for MySQL and select the New Project icon.
-
Click Connect to MySQL.
-
Connect to MySQL window is displayed. Specify all parameters to connect to the source (MySQL) database, and then click Connect.
-
Once connection to the source database is established, you will have access to MySQL databases.
-
Click Connect to SQL Server to establish connection with the sample database you created above.
-
A list of all MS SQL databases is displayed.
-
In MySQL Metadata Explorer, select the tables you want to convert, and then click Convert Schema.
-
To apply source database schema to the target database, right-click the newly created database, and then select Synchronize with database.
-
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:
- 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.
7 min read
