Reasons to Migrate

SQL Server is a popular database management system having its own strength such as user-friendly and easy to learn interface. However, a lot of organizations are migrating their databases from Microsoft SQL to MySQL due to the following advantages of the target DBMS: free license, wide range of supported OS and easy integration with web.

About Migration

Migration of schema, data and indexes to MySQL is not a challenging task because MySQL supports data types, table designs, and index structures similar to MS SQL. However there are some details that require careful consideration. The table below specifies data types that are not equal in both DBMS:

Advertisement

SQL Server MySQL

BIT BOOLEAN or TINYINT(1)

DATETIMEOFFSET TIMESTAMP

IDENTITY AUTO_INCREMENT

NTEXT, NATIONAL TEXT TEXT CHARACTER SET UTF8

SMALLDATETIME DATETIME

MONEY DECIMAL(19,4)

SMALL MONEY DECIMAL(10,4)

UNIQUEIDENTIFIER CHAR(38)

SYSNAME CHAR(160)

XML TEXT

Also, SQL Server and MySQL support different set of embedded functions, so some of them must be replaced by equivalents in the destination DBMS during the migration. The table below explores such equivalents:


SQL Server MySQL

CONVERT($type, $expression) CAST($expression AS $type)

LEN($expression) LENGTH($expression)

DATEADD(year, 1, $expression) $expression + interval 1 year

DATEADD(month, 2, $expression) $expression + interval 2 month

DATEADD(day, 3, $expression) $expression + interval 3 day

GETDATE() NOW()

‘string1' + ‘string2' CONCAT(‘string1', ‘string2')

CONTAINS($expr, $templ) $expr LIKE $templ

CHARINDEX ($exp1, $exp2) LOCATE($exp1, $exp2)


Approaches to Migration

Basically, there are two options to migrate database from MS SQL to MySQL, below strong and weak sides of each option are listed.

Manual migration provides that database specialist migrates all database objects manually based on standard tools provided by DBMS vendors. For example, MS SQL Server provides Export and Import tool. It uses ODBC Drivers for conversion, so it is necessary to to create ODBC Data Source before running the migration. If Import & Export tool is launched on the same machine where MS SQL database is running, ODBC driver is automatically installed by SQL Server. MySQL ODBC driver is required as well.

Another option is fully automated migration via dedicated software. This approach requires minimal human efforts since the tool handles all aspects of migration properly. However, even this method assume some manual post processing because none of converters can migrate such database entries as stored functions, procedures and triggers due to complicated nature of these objects.

Advertisement

For both options listed above the database specialist must validate the results to make sure all database objects have been transferred properly.

More information about different aspects of database migration from SQL Server to MySQL can be found at: https://www.convert-in.com/docs/mss2sql/intro.htm