Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
As every migration between relational database management systems, transferring from MySQL to SQL Server involves processing such entries as schemas, data, indexes, constraints, views, stored procedures, functions and triggers. This whitepaper explores the migration process and contains some hints on choosing the proper tool to convert MySQL to SQL Server.
Here are the general steps required for the database migration:
Types mapping is important part of database schemas migration. MySQL and MS SQL have similar sets of supported data types, however some of them must be mapped properly as it is specified in the table below:
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) |
AUTO_INCREMENT field property used by MySQL to automatically generate sequential integer numbers must be converted into IDENTITY in the target SQL Server database.
One of the top challenges when running MySQL to SQL Server database migration is conversion of SQL code in views, stores procedures, functions and triggers.
MySQL limited queries ‘SELECT … LIMIT number_of_rows’ must be converted into ‘SELECT TOP number_of_rows …’ MS SQL.
Unlike MySQL, SQL Server and Azure SQL requires all selected columns in ‘SELECT … GROUP BY …’ queries to be either under ‘GROUP BY’ clause or in aggregation functions.
There are specific MySQL functions that are not supported by SQL Server and so they must be replaced by equivalents according to this table:
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) |
As you can see from the guide above, database migration from MySQL to SQL Server is not a trivial task. Some software vendors offer special tools to automate major steps of the migration process.
The most common choice for partial automation of the database migration is Microsoft SQL Server Migration Assistant (SSMA) for MySQL. This is a standard tool coming as a part of SQL Server installation, it can convert MySQL to SQL Server through the following steps:
Install and Connect. The latest version of SSMA for MySQL is available to download from the official Microsoft site. Launch SSMA and create a new project by clicking on “File” > “New Project”. In the project window, right-click on “Connect to MySQL” and enter the connection details (server name, port, username, and password) for MySQL server. Click “Connect”.
Configure. Next step is to configure SSMA. In the project window, click on “Project Settings”. Here, you can customize type mapping, schema and object conversion, and other migration settings.
Migrate Schemas. Right-click on the MySQL database and choose “Convert Schema”. This will convert table definitions from MySQL to SQL Server format based on the conversion settings specified on the previous step. Carefully review the conversion report to identify any issues that may require manual modifying the schema or adjusting the conversion settings accordingly.
Migrate Data. Right-click on the MySQL database and select “Migrate Data”. SSMA can either migrate all MySQL data or specify a filtering criterion. Once the data migration is complete, run comprehensive testing to ensure the integrity and consistency of SQL Server database.
Although SSMA can essentially help to convert MySQL to SQL Server, this method requires a lot of steps to set up the process. For migration projects requiring completely automated solution, it is recommended to use dedicated tools that are specially designed to fulfill the migration with just a few button-clicks. One of such software products is MySQL to SQL Server converter developed by Intelligent Converters vendor specializing in database migration and synchronization for years.
Key features of MySQL to SQL Server converter:
Visit official site of Intelligent Converters for learn more about how to convert MySQL to SQL Server.