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.
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:
SQL Server MySQL
BIT BOOLEAN or TINYINT(1)
NTEXT, NATIONAL TEXT TEXT CHARACTER SET UTF8
SMALL MONEY DECIMAL(10,4)
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)
DATEADD(year, 1, $expression) $expression + interval 1 year
DATEADD(month, 2, $expression) $expression + interval 2 month
DATEADD(day, 3, $expression) $expression + interval 3 day
‘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.
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