How to Migrate the Database from MS SQL toMySQL?

kb.bodhost.com /how-to-migrate-the-database-from-ms-sql-to-mysql/

Emma Taylor

There are severalcompanies that prefermigrating their databasefrom MS SQL to MySQL. Itbecomes essential forenterprises to migrate toMySQL when there arefew MS SQL serverdatabase around. Theremight be some otherreasons as below that canbe considered for migratingfrom MS SQL to My SQL –

MySQL supports numerous operating systems including Solaris, Linux distros and Mac whichthe 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 overyears by a huge open source community.

You can migrate data from MS SQL to MySQL, by using “MySQL Workbench” utility. InstallingMySQL Workbenchbecomes easy with the installation of “Oracle MySQL installer for windows ” thatincludes 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 theDatabase Migration launcher in the Workbench Central panel or through Database -> Migratein the main menu. A new tab displaying Overview page of Migration Wizard will be displayed.

Go through the Prerequisites section which displays that you require an ODBC driver for yoursource RDBMS Recent version of Windows is updated with some ODBC drivers but the earlierversions require installation of these drivers which can be done with Microsoft Data AccessComponents (MDAC).

To check for an ODBC driver for SQL server – Go to Plugins -> Start ODBC Administrator orsimply open a Windows terminal and type exe. Then go to Drivers tab. Once you learn how touse the driver, note down its name as displayed in ODBC Data Source Administrator, since itwill be 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, selectthe 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 needto mention the IP-address or hostname where the MySQL database is running – the MySQL portusername. Once you specify the source and destination, all the available databases and schemas willbe listed from where you can choose the specific schema for migration. Stating custom schemamapping 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 storedprocedures are simply copied and a comment about it appears on the screen as those need to beconverted manually.

Data Migration –

In this step, the data is copied automatically from source to destination database for migrated tables.

Remember that using the migration wizard only tables can be converted and data can be copied but thetriggers, views and stored procedures can’t be converted. Those need to be done manually.