Microsoft SQL Server and MySQL are two capable database management systems that offer a range of features and functionalities for building efficient databases. However, organizations have been increasingly opting to migrate from MS SQL to MySQL due to its advantages such as being open-source, offering a lower total cost of ownership, and being cross-platform.
Migrating databases from SQL Server or Azure SQL to MySQL can be done through various approaches, such as manual mode, semi-automated solutions using free tools like Microsoft Data Transformation Services (DTS) or MySQL Workbench Migration Wizard, and fully automated migration using appropriate commercial tools. Regardless of the approach chosen, it is essential to be aware of potential bottlenecks and validate the results.
Table structures (DDL) and data migration require special attention during the migration process. Additionally, some SQL Server or Azure SQL types have to be converted as they do not have a direct equivalent in MySQL, including date time offset, hierarchyid, money, small money, unique identifier, and xml. Similar types may be supported by MySQL, but they have different names and may require additional processing during migration.
Manual Migration from MS SQL to MySQL
To migrate data from MS SQL to MySQL, there are several methods available, including using Microsoft Data Transformation Services or Integration Services, or a combination of SQL Server bulk copy program and MySQL LOAD DATA INFILE. However, these methods require skills in database administration and cannot be used for other types of database entries.
While schemas, indexes, constraints, and foreign keys can be extracted in the form of create statements, they need to be converted into MySQL format before loading them into the destination database. The syntax of CREATE VIEW statements used in SQL Server or Azure SQL is similar to MySQL, MariaDB, and Percona. Therefore, converting views just requires translating particular operators and embedded functions into MySQL equivalents.
Migrating stored procedures, functions, and triggers is the most complicated part of SQL Server to MySQL database migration, as it requires in-depth knowledge of the SQL language of both DBMS. This process needs to be done carefully and with attention to detail to ensure that the code is properly translated and functions as expected in the new environment.
MS SQL to MySQL Migration Tools
Despite of the fact it is not possible to completely automate the migration of all database entries including views, stored procedures, functions and triggers from MS SQL or Azure to MySQL and its forks, there are software tools that can partially do the job.
Intelligent Converters’ MS SQL to MySQL converter is one such tool that can migrate schemas, data, indexes, constraints, and views, making it ideal for even the largest and most complex MS SQL databases. The converter can efficiently transform data and convert object definitions, as well as merge or synchronize data between an existing database and a new MySQL database. Command line support is extra advantage of MS SQL to MySQL converter that allows scripting and scheduling the database migration.
The product exposes intuitive wizard-style user interface leading step-by-step through the entire migration process.
Step 1. Select to migrate directly to MySQL server or export into local SQL script file that contains statements to create database objects and transfer the data. The second option may help when MySQL is hosted on a remote server and it does not allow remote connections.
Step 2. Provide all necessary details to connect to Microsoft SQL Server or Azure SQL. Enter host name or IP address of the source database server, user name and password (for SQL Server authentication only).
Step 3a. If you select to migrate directly to MySQL server on the 1st wizard screen, now you have to enter all necessary MySQL connection settings: host name or IP address (if it is a remote server) or select ‘local’, user name and password. Post is required only if it differs from the default 3306.
Step 3b. If you select to migrate SQL Server or Azure SQL database into a local MySQL script file, now you have to customize the related formatting settings.
Step 4. Enter names of MS SQL and MySQL databases, specify a log file to enable logging (or leave it blank to disable logging). You can view all available MS SQL databases buy expanding the drop box, but you must have the sufficient permissions for this.
Step 5. Provide migration settings: MySQL code page and engine type, migrate table structures only and others.
Step 6. Select MS SQL tables to migrate. Push ‘Add Query’ button to compose SELECT-query for filtering or preprocessing data before migration. Double click on an item in ‘Selected tables’ list to modify it. For tables, you can change attributes of any column and exclude it from migration. For queries, you can edit the SELECT-statement.
Step 7. Select MS SQL views to translate into MySQL dialect of SQL. Use up and down arrows to control migration order of dependencies.
Then database migration will be started.
Conclusion
Microsoft SQL Server and MySQL are two database management systems, with organizations increasingly migrating from MS SQL to MySQL due to its advantages, including being open-source, having a lower total cost of ownership, and being cross-platform. There are various approaches to migrate databases, including manual, semi-automated, and fully automated migration. During the migration process, table structures and data migration require special attention, and some SQL Server or Azure SQL types have to be converted, as they do not have a direct equivalent in MySQL.
Intelligent Converters’ MS SQL to MySQL converter is a software tool that can partially automate the migration process, including schemas, data, indexes, constraints, and views. The tool allows for direct migration to a MySQL server or export to a local SQL script file. The product has a user-friendly interface and can efficiently transform data and convert object definitions, as well as merge or synchronize data between an existing database and a new MySQL database. The tool also has command line support, allowing for scripting and scheduling of the database migration.
