When individuals or companies want to integrate their MySQL data with .NET technology or host their database on the Windows platform, they need to migrate to SQL Server. Migrating from MySQL to SQL Server involves transferring the database schema, data, and relevant application code. This whitepaper explores different approaches to convert MySQL to SQL Server and provides tips for choosing a suitable converter. Here are the generic steps involved in a common database migration project:
- Analyze the source database. Assess the size, complexity, and dependencies of the MySQL database. Identify any MySQL-specific features or functionalities that may require adjustments during the migration to SQL Server.
- Plan the migration. Design a migration plan that includes the timeline, prerequisites, and specific actions required to migrate from MySQL to SQL Server. Consider factors such as data volume, downtime tolerance, and potential risks of data corruption. Schedule a downtime window to switch the production environment from MySQL to SQL Server.
- Set up the target system. Install and configure SQL Server or Azure SQL cloud. Ensure that all necessary permissions and access grants are in place on both DBMS to facilitate the migration.
- Migration of meta-objects. Migrate MySQL tables definitions, indexes, constrains, views, and other meta-objects to SQL Server or Azure SQL. Pay attention to correct type mapping and translation of built-in functions and operators.
- Migration of data. Transfer the MySQL data to SQL Server or Azure SQL cloud using one of the following approaches: utilizing the SQL Server Migration Assistant (SSMA), writing scripts, or employing data conversion tools.
- Conduct thorough testing to validate the performance and accuracy of the resulting database. Test various data retrieval, manipulation, and application workflows on the SQL Server side.
- Application Layer. Modify configuration of the applications, connection strings, and any other relevant settings in comply to the new SQL Server database.
Types Mapping
MySQL and MS SQL have different sets of data types. The table below provides the necessary translations of data types when converting from MySQL to SQL Server:
| MySQL | SQL Server |
| BIT(n) | BINARY(n/8) |
| BLOB(n) | VARBINARY(max) |
| BOOLEAN, BOOL | BIT |
| DOUBLE | FLOAT |
| FIXED(p,s) | DECIMAL(p,s) |
| FLOAT8 | BINARY_DOUBLE |
| LONGBLOB | VARBINARY(max) |
| LONGTEXT | VARCHAR(max) |
| MEDIUMBLOB | VARBINARY(max) |
| MEDIUMINT | INT |
| MEDIUMTEXT | VARCHAR(max) |
| REAL | DOUBLE PRECISION |
| TEXT | VARCHAR(max) |
| TIMESTAMP(p) | DATETIME2(p) |
| TINYBLOB | VARBINARY(255) |
| TINYINT | SMALLINT |
| TINYTEXT | VARCHAR(255) |
| YEAR[(2 | 4)] | NUMERIC(4) |
MySQL columns having AUTO_INCREMENTproperty aremapped into SQL Server IDENTITY.
By following these steps above and ensuring proper type mapping, users can successfully migrate their schemas (table definitions) from MySQL to SQL Server.
Convert SQL Code from MySQL to SQL Server
One of the most challenging part of MySQL to SQL Server database migration is converting SQL code of views, stored procedures, functions, and triggers. It is important to address certain differences between MySQL and SQL Server. Here are some considerations for code conversion:
- Conversion of MySQL limited queries: In MySQL, queries with the syntax ‘SELECT … LIMIT number_of_rows’ are commonly used. To migrate them to SQL Server, the syntax needs to be changed to ‘SELECT TOP number_of_rows …’.
- Handling ‘SELECT … GROUP BY …’ queries: SQL Server and Azure SQL require that all selected columns in ‘SELECT … GROUP BY …’ queries be either included in the ‘GROUP BY’ clause or used within aggregation functions. This differs from MySQL, where non-aggregated columns can be included in the ‘SELECT’ clause without being part of the ‘GROUP BY’ clause.
- Replacement of MySQL built-in functions: MySQL and SQL Server have different sets of built-in functions. When migrating, specific MySQL functions need to be replaced with their SQL Server equivalents. Here is a list of MySQL built-in functions with their SQL Server replacements:
| MySQL | SQL Server |
| CONCAT($arg1, $arg2, …) | $arg1 + $arg2 + … |
| curdate(), CURRENT_DATE | CAST(getdate() AS date) |
| curtime(),CURRENT_TIME | CAST(getdate() AS time) |
| DAY($date) | DATENAME(d, $date) |
| DAYOFYEAR($date) | DATENAME(dy, $date) |
| DAYNAME($date) | DATENAME(dw, $date) |
| HOUR($date) | DATENAME(hh, $date) |
| isnull($expression) | $expression is null |
| now(), CURRENT_TIMESTAMP | getdate() |
| RAND() | newID() |
| $expr like $temmplate | CONTAINS($expr, $template) |
| timediff($date1, $date2) | CAST($date1 – $date2 AS TIME) |
By addressing these differences, SQL code can be converted from MySQL to SQL Server effectively, ensuring the functionality and compatibility of views, stored procedures, functions, and triggers in the target environment.
MySQL to SQL Server Converters
The previous sections of this whitepaper prove thatit is not so easy to convert MySQL to SQL Server. However, there are software tools available that can convert MySQL to SQL Server semi-automatically simplifying many steps of the migration process. For example, the Microsoft SQL Server Migration Assistant (SSMA) for MySQL can partially automate the database migration. Below is a step-by-step guide on using SSMA for MySQL:
- Install and Connect. Download and install the most recent version of SSMA for MySQL from the official site. Launch the tool and create a new project. Connect to MySQL database providing the required information, such as server name, port, username, and password.
- Next step after connecting to MySQL server is to configure the SSMA conversion settings. In the project window, click on “Project Settings” to customize data type mapping, schema and meta-objects conversion, and other database migration preferences.
- Schema Migration.At this step all table definitions must be converted from MySQL to the SQL Server or Azure SQL format. Highlight the MySQL database by right-clicking mouse button and choose “Convert Schema” menu item. This action will launch the conversion of MySQL table definitions to MS SQL format based on the provided settings. It is important to review the conversion report to find and resolve any possible issues.
- Data Migration. The next step is to migrate the MySQL data into SQL Server or Azure SQL tables. Highlight the MySQL database by right-click of mouse button and select “Migrate Data” menu item. Once the data is migrated, it is extremely important to verify the integrity and consistency of the resulting SQL Server data. Perform thorough testing to ensure accuracy and reliability of the migrated data.
If you need toconvert MySQL to SQL Server in a fully automated way, the Microsoft SQL Server Migration Assistant may not be enough. In this case it is recommended to review commercial tools like the MySQL to SQL Server converter developed by Intelligent Converters. Here are the key features of the softwareproduct:
- Supports all modern versions of on-premises MySQL and SQL Server and cloud platforms like Azure SQL, Google Cloud and Amazon RDS.
- Converts table definitions, data, indexes, constraints, foreign keys, and views with all necessary properties and attributes.
- Provides high-performance data migration through efficient bulk reading and writing techniques.
- Allows to customize column attributes, exclude some columns from migration, and convert a MySQL database into a T-SQL script.
- Option to merge or synchronize existing MS SQL databases with MySQL data.
- Provides data filtering capabilities and the ability to combine multiple tables into a single one using SELECT queries.
For more information about the MySQL to SQL Server converter please visit official website of the vendor.
