Migrate MySQL to Microsoft SQL Server

Setup ODBC Connection to MySQL Database

For ODBC connection there should be MySQL ODBC Connector on windows server. You can download it from link mentioned below.

http://dev.mysql.com/downloads/connector/odbc/

  • Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools -> Data Sources (odbc). Under the tab labelled as “System DSN”, press the “Add” button.

 

img3

  • On the “Create New Data Source” dialog that appeared, choose MySQL ODBC 5.2 ANSI Driver and then press the “Finish” button.

 

img2

  • 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 “techsuccor“. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else.

img1

  • Press the “Test” button to ensure your connection settings are set properly and then the “OK” button when you’re done.

Create a Microsoft SQL Link to your MySQL Database

Now 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.2 ANSI Driver}; SERVER=localhost; _
	DATABASE=techsuccor; USER=root; PASSWORD=xyz4sgd; OPTION=3'
  • 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 like this:
img

Import Data between the Databases

  • Create a new database in Microsoft SQL Server. I used “testtechsuccor“. In the query window, run the following SQL statement to import table techtable from the MySQL database techsuccor, into the newly created database in Microsoft SQL called testtechsuccor.
SELECT * INTO testtechsuccor.dbo.techtable
FROM openquery(MYSQL, 'SELECT * FROM techsuccor.techtable')

Bingo!!!!
Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *