There are several companies that prefer migrating their database from MS SQL to MySQL. It becomes essential for enterprises to migrate to MySQL when there are few MS SQL server databases around. There might some other reasons as below that can be considered for migrating from MS SQL to My SQL –
- MySQL supports numerous operating systems including Solaris, Linux distros, and Mac which MS SQL doesn’t support.
- MS SQL server requires huge license and support fees while MySQL is less expensive.
- Implementation of highly scalable database infrastructure.
- MySQL database comes with several advanced features that have been tested intensively over the years by a huge open-source community.
You can migrate data from MS SQL to MySQL, by using the “MySQL Workbench” utility. Installing MySQL Workbench becomes easy with the installation of “Oracle MySQL installer for Windows”. Which includes the installation of several MySQL tools along with Workbench.
Migrating from MS SQL to My SQL by using My SQL Workbench –
- Start the Migration Wizard from the main MySQL Workbench screen by clicking on the Database Migration launcher in the Workbench Central panel or through Database -> Migrate in the main menu. A new tab displaying the Overview page of Migration Wizard displayed.
- Go through the Prerequisites section which displays that you require an ODBC driver for your source RDBMS Recent version of Windows updated with some ODBC drivers. But the earlier versions require installation of these drivers which done with Microsoft Data Access Components (MDAC).
- To check for an ODBC driver for the SQL server – Go to Plugins -> Start ODBC Administrator or simply open a Windows terminal and type exe. Then go to the Drivers tab. Once you learn how to use the driver, note down its name as displayed in ODBC Data Source Administrator. Since it needed to connect to your SQL Server instance from Migration Wizard.
- To create a DSN for your database file. Then go to the User DSN tab and click on Add.
- Select the Source and Target Database –
Now, you will need to define the source Microsoft SQL Server database connection parameter. Select Microsoft SQL Server tab from the database system dropdown list. Next, in the parameters tab, select the DSN and specify the username to the source database.
- Define the destination MySQL database connection parameter –
Select the Local Instance MySQL or Remote Instance MySQL as per your requirement. You will need to mention the IP address or hostname where the MySQL database is running – the MySQL port username. Once you specify the source and destination. All the available databases and schemas listed from where you can choose the specific schema for migration. Stating custom schema mapping to the destination MySQL database is also possible.
- Migrating the Objects –
In this step the table objects, Microsoft SQL Server schema object, default values, data types, primary keys and indexes get converted. Ensure that function objects, view objects and stored procedures simply copied and a comment about it appears on the screen as those need converted manually.
- Data Migration –
In this step, the data copied automatically from the source to the destination database for migrated tables.
Remember that using the migration wizard only tables converted and data copied but the triggers, views and stored procedures can’t be converted. Those need done manually.