The industry is seeing a rise in the migration of proprietary engines to open-source ones. While SQL Server and MySQL are both firm favorites with database users, people tend to move SQL Server to MySQL for cost saving, or business requirements. But database migration should tread lightly because of various accidents in the process, say, data loss or corruption, or unexpected downtime.
Before we get to that, we need to know why exactly we migrate SQL Server to MySQL.
Why Move SQL Server to MySQL?
Save cost: SQL Server is a proprietary database with $14,526 per core licensing for the Enterprise version from Microsoft. Despite the fact that Developer and Express versions are free of charge, they have some limitations of database, CPU, RAM, and more. While with MySQL, the community edition is available to you without charge or restrictions.
More OS support: SQL Server runs on Windows, Linux, and Docker containers, but MySQL works with many operating systems, including Linux, Mac OS, Windows, OpenBSD, OpenServer, NetBSD, and many more.
Ease of use: MySQL installs faster and has fewer tuning configuration knobs and footprints for large database management than SQL Server.
Storage support: SQL Server supports only a single storage engine, whereas MySQL offers various storage engines and plug-in storage options.
Graphical tool: SQL Server doesn’t support any connector and counts on the T-SQL prompt to work, and MySQL has a built-in MySQL Workbench to create, design, and build databases.
High availability: replication, SANs, and MySQL Cluster are a few tested methods offered by MySQL that, depending on the situation, can match or even outperform SQL Server.
Plenty of tools can perform the migration from SQL Server to MySQL, such as Amazon DMS, Data Integration (Kettle), and MySQL Workbench, in this case, we’ll use MySQL Workbench and MySQLyog.
Method 1: Migrate SQL Server to MySQL with MySQL Workbench
Before Migration:
Configure the ODBC Driver for Conversion
1. Click the Windows icon, look for and click Windows Administrative Tools. Choose the 32-bit or 64-bit ODBC drivers according to your system.
2. A window pops up, click System DSN tab> Add.
3. Choose SQL Server from the Data Sources drop-down and click Finish.
4. Provide the details in the Connect a New Data Source to SQL Server window. Find the name of the SQL Server from the Connect to Server window in your Microsoft SQL Server Management Studio or run select @@SERVERNAME in the SQLQuery. Click Next.
5. Leave the SQL Server authentication method as Windows NT, and click Next.
6. Select Change the default Database to MySQL (name) and click Next.
7. Click Finish and then Test Data Source.
Convert SQL Server to MySQL
1. Launch the MySQL Workbench, go to Database> Migration Wizard.
2. View the Migration Task List and click Start Migration.
3. Enter SQL Server connection details on the Source Selection page, then click Test Connection. Click Next.
4. On the Target Selection page, enter MySQL destination details, then click Test Connection. Click Next.
5. Click Next after the schema list is extracted, locate the desired database schema in the list, then click Next. Then click it again once the process is completed.
6. Click View Selection to see and choose the target objects, then click Next. The conversion will begin, click Next after this.
7. Check the Create schema in target RDBMS box, leave the default settings, and click Next. Click the button again once the creation is done.
8. Review the report of schema status in the MySQL database, and click Next.
9. Select Online copy of table data to target RDVMS (by default) in the Data Transfer Setup window and click Next.
10. Click Next after the conversion is finished, and a Final Data Migration will be presented.
Method 2: Migrate SQL Server to MySQL with MySQLyog
1. Install MySQL Client tool SQLyog.
2. Right-click the target database> Import> Import External Data…
3. Select Start a new job and click Next.
4. Select Create a New DSN… if there isn’t DSN that needs to be exported to SQL Server in your list. Choose File Data Source (Irrelevant to the Machine) (F), and click Next. Then select SQL Server as the driver. Click Next.
5. Enter a name for the data source file or browse for a location, and click Next.
6. Enter a name and description for the data source, and select a SQL Server from the drop-down. Click Finish.
7. In the Create new data source to SQL Server window, choose Use user to enter login ID and password for SQL Server authentication (S) if you are connecting the remote server, and input the ID and password. Click Next.
8. Check Change the default database to box and select one from the drop-down, then choose both Use ANSI reference identifier (U) and Use the null value, fill-in, and warning of ANSI (A). Then Test the Data Source.
9. Select Any ODBC Source as the Type of Data Source, and choose the just created File DSN from the … Fill in the SQL Server account and password to log in.
10. Choose the target MySQL server, fill in the IP address, and account details, then test the connection.
11. Select Copy table(s) from the data source and click Next.
12. Choose a part or all the tables to migrate.
Note: the bit type and the date (datetime) type must be mapped, or the migration will fail. Click and edit the corresponding … under the Map, and the tool will change it to the compatible type.
Note that the boolean in MySQL is shown as tinyint, so you need to modify it.
13. Under the Advanced options, select Import into existing table, Update with source details, and Import foreign keys under the DDL/DML Options, and click OK.
14. Choose or leave the Error handling options and select Run immediately for the task. Find a location for error logs during the task execution.
15. The wizard was completed successfully, we can check the execution results and compare the data.
Backup SQL Server and MySQL for Risky Operations
While in the process of database migration, dangers like data loss, data corruption, and unstable applications and systems may threaten data security. So, it’s suggested that you test and back up these objects before the migration.
Vinchin Backup & Recovery backs up and restores multiple databases (such as Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB) in both physical and virtual machines with smart strategies.
You can automate the backup under settable schedules and email notifications via SAN/LAN and multithread lines for speed.
You can also reduce at least 50% of data size after deduplication and compression to save storage and cost for it.
Database recovery to the original or a new destination is, of course, easy with one click of Vinchin Backup & Recovery.
Apart from that, you can rest assured that all backups and the backup server itself are perfectly safe under the protection of real-time IO monitoring that denies any suspicious access from the OS layer.
Best of all, you can get all these incredible features at an extremely reasonable price, and here is a 60-day full-featured free trial of the Enterprise version available for testing.
1. Choose a backup source from the licensed database.
2. Choose the destination for the backup task.
3. Configure the desired backup strategies.
4. Check settings and submit.
Verdict
Some SQL Server database users may want to migrate to MySQL for reduced cost, OS compatibility, or fewer restrictions. This article introduces two methods to convert SQL Server to MySQL using MySQL Workbench and MySQLyog (see how to convert MySQL to SQL Server), choose one based on your needs.
Remember to backup SQL Server before the migration and MySQL after the process as a safety net in case of data loss disasters. Use Vinchin Backup & Recovery for complete database protection.
