Migrate MySQL to MSSQL

I had a project written in Ruby on Rails that needed to be converted to Asp.net because the requirements of the job had changed from simple to more complex and I feel Asp.net is the way to go when you have business logic and a short amount of time to get the project up and going. My first task was to change the database from MySQL to MSSQL.

ODBC Connection to MySQL Database

First you have to download the MySQL ODBC connector from here.
Then you must setup the MYSQL ODBC connector by opening it up by navigating over to Control Panel/Admin Tools/.
Under the System DSN tab click on the Add Button.
On the “Create New Data Source” dialog that appeared, choose MySQL ODBC 5.1 Driver and then press the “Finish” button.
After that, a MySQL connection configuration dialog will appear. Add your MySQL database account information in it, preferably the “root” account which has full access to your databases in MySQL. In this case, my database is called “casemanager”. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else.
Press the “Test” button to ensure your connection settings are set properly and then the “OK” button when you’re done.

Microsoft SQL Link to your MySQL Database

In this state, you are ready to establish a link towards MySQL database from your Microsoft SQL Server Management Studio. Open a query window and run the following SQL statement:

EXEC master.dbo.sp_addlinkedserver
@server = N’MYSQL’,
@srvproduct=N’MySQL’,
@provider=N’MSDASQL’,
@provstr=N’DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; _
DATABASE=casemanager; USER=root; PASSWORD=password; OPTION=3′

–Make sure you include your database credentials where User= & password=

This script will produce a link to your MySQL database through the ODBC connection you just created in the previous stage of this article. The link will appear in the Microsoft SQL Server Management Studio:

Click on refresh if you don’t see the link.

Create a new database in Microsoft SQL Server. I called mine “casemanager” like I did for the MySQL database. In the query window, run the following SQL statement to import table firstname from the MySQL database casemanager, into the newly created database in Microsoft SQL called casemanager.

SELECT * INTO casemanager.dbo.firstname
FROM openquery(MYSQL, ‘SELECT * FROM casemanager.firstname’)

To import multiple tables within the database I changed the table name for each query and re ran it until all of my tables were imported.

Tags

asp.net mssql mysql ruby on rails