Many companies are following a trend of switching commercial software to freeware or open source equivalents. So, most of them are migrating their databases from SQL server to MySQL server. Software developers are offering dedicated tools known as database converter, for this purpose. These database converters automate migrating of complete database to individual table while carefully processing data, indexes, schemas, and other objects.
And if you are using open source in your enterprise, you might consider migrating server database of MS SQL to MySQL database. Here are a few reasons why you might consider migration of Microsoft SQL Server to MySQL database –
- MySQL is less expensive as compared to MS SQL. You can to pay a huge license and support fees if you are using MS SQL.
- Unlike MS SQL Server, MySQL supports a range of operating systems including Linux, distros, Mac and Solaris.
- MySQL has a highly scalable database infrastructure.
- There are a number of advanced features of MySQL database that have been tested intensively over the years by a big community.
If you are a small scale or mid-sized business looking to save a huge cost on infrastructure, you must migrate from SQL server to MySQL. MySQL Workbench module can be used to convert the databases and carry out migration comfortably.
One of the easiest ways to install MySQL Workbench is by installing Oracle MySQL installer for Windows using the Workbench wizard. Download and install the MySQL installer that includes Workbench and other necessary connectors and drivers required for migration.
What should you know?
Moving data and index structures over to MySQL is not at all a challenging task because it supports all important data types, index structures and table designs. You might face challenges with code related objects such as stored procedures. So, pay special attention to following items:
- Types
- Assemblies
- DDL and statement-based triggers
- Proprietary SQL Server function calls
- Certain cases of dynamic T-SQL
Here are a few datatypes that requires conversion:
How to migrate with Workbench?
To carry out migration, you must have:
- SQL server installed with your database to migrate.
- MySQL Server installed.
- Users on both database servers with privileges to perform the task.
- MySQL Workbench installed.
To start the migration, go to Database Migration Wizard, from the Workbench main screen.
You should check the prerequisites to confirm if you can continue the task. Click on ‘Start Migration’.
Now, provide the information about source database (SQL Server).
Run MySQL Workbench on SQL Server node and use IP Address / Hostname of your database server. Use ‘Test Connection’ button to check the connection.
Add new target parameters (MySQL Server).
Click on ‘Test Connection’ to confirm the added information.
Workbench will connect to SQL Server to fetch a list of catalogs and schemas.
Choose the database from the list. You can choose how reverse engineered schemas and object should be mapped.
Use Catalog.Schema.Table Catalog.Table option to have a database in MySQL Server and current tables in SQL Server database.
If everything goes smoothly, there will be list of objects to migrate.
In the example shown, there are Table Objects, View Objects and Routine Objects. You can select on Table Objects as rest of the objects can be checked corresponding to MySQL equivalent code manually.
Now, the objects are converted from source to MySQL compatible objects.
If everything went well, continue by selecting how you want to create the migrated schema in target, Use the default “Create schema in target RDBMS” option.
Now, check the creation of schema process.
Check the result of each script execution and the new database created in MySQL server.
In the example we have –
You have a data structure, but data is not present. Select how you want to copy the data in MySQL server. Use “Online copy of table data to target RDBMS” option.
The copy process will begin on screen.
The information gets migrated to your MySQL Server.
Check the migration report and finish the task.
The Bottom Line
As you have reviewed in this post, there are a few reasons that can make business decide a database migration, going from proprietary engine to an open source one. So, this is one such guide that will help you easily migrate from MS SQL Server to MySQL server using Workbench.
