How to Convert/Migrate/Move/Copy MSSQL Database to MySQL Database?

[sajugovind]

Scenario

Your Company/Organization/Institute maintains certain data in MSSQL 2000 but now on suggestion of System Analyst or database administrator you want to move the data to MySQL 5.x.

Questions?
1) How to migrate the data?
2) Do we need to recreate the complete database schema?
3) Can we use any third Party Application to convert it and how feasible it will be?
etc.

Solution

Using following steps you can easily move/convert the data from MSSQL to MySql (Only data will be moved).

Downloads Required Tools

1) MySql ODBC connector: MySQL ODBC Connector will help Windows OS to configure MySQL as Database Source Name (DSN). It can be downloaded from http://dev.mysql.com/downloads/connector/odbc/5.1.htm


Install MySql ODBC connector on the Server.

Installation of MySql ODBC connector is pretty easy. You will just have to run setup file and follow the instructions.


Configure MySQL Database Source Name (DSN)

Database Source Name or DSN specifies a data structure that contains the information about a specific data source or database that an Open Database Connectivity (ODBC) driver needs in order to connect to that data source or database. To add new DSN follow the instructions given below

1) Go to “Control Panel -> Administrative Tools -> Data Source”  and Click on “Add…” to add new DSN

2) Select “MySQL ODBC 5.1 Driver” from the list and press “Finish”

3) Once you press Finish “MySQL Connector/ODBC Data Source Configuration” window will open.

Here, provide the information about DSN, database, password etc. I have used Localhost as TCP/IP Server as I have the MySQL server installed locally and migrating it locally. Once the data is filled you can test the DSN connectivity with database. Then Press Ok to configure the DSN.

4) Now ODBC Data Source Administrator will show the Datasource which you have created.

Export/Migrate Data from MSSQL to MySQL using DSN

1) Go to “MSSQL Enterprise Manager >> Databases >> Right Click the database you want to migrate or export >> All Tasks >> Export Data …“

2) You will get “Data Transformation Services Import/Export Wizard” Window. Press next.

3) Choose Source: You have to select the Data Source from where the data will be migrated. As we are going to migrated data from MSSQL to MySQL select “Microsoft OLE DB Provider for SQL Server” and the name of database which you want to migrate.

4) Select Destination: Select “MySQL ODBC 5.1 Driver” from dropdown list which will show the list of DSN in drop-down under “User/System DSN“. Select the DSN you have created and enter the login details.

Press Next to Continue.

5) On “Specify Table Copy or Query” select “Copy table(s) and view(s) from the source database” and press next.

6) Select Source tables(s) and view(s): Select the tables which you want to migrate. I have pressed “Select All” for complete database migration.

Press “Next“

7) Select “Run immediately“; press Next and on next screen press Finish to start Migration.


Depending upon size of database it will take 20-30 minutes to complete.