SSRS reporting

How to Migrate from MySQL to Microsoft SQL Server

How to migrate from MySQL to Microsoft SQL Server 2005 and later using the MySQL ODBC Connector 5.1. After following these steps you should have a MySQL database fully migrated in to your Microsoft SQL Server environment.

ODBC Connection Setup to MySQL Database

This article will assume that user have already downloaded and installed the MySQL ODBC Connector.

These instructions use MySQL ODBC Connector 5.1.

The setup of the MySQL ODBC Connector is quite simple:

  1. Go to Control Panel -> Administrative Tools-> Data Sources (ODBC). Under the tab labeled as System DSN, press the Add button.odbc-data-source1 How to Migrate from MySQL to Microsoft SQL Server
  2. From the dialog box named Create New Data Source, select MySQL ODBC 5.1 Driver and then click on the Finish How to Migrate from MySQL to Microsoft SQL Server
  3. In the MySQL connector/ODBC Data Source Configuration dialog box, add your connection parameters for the MySQL database account that you are migrating including a user name which has full access to your databases in MySQL preferably the “root” account. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else. This is default port number for MySQL database. In the server option you can also type remote server host name in case your MySQL server is remotely located.mysql-connector1 How to Migrate from MySQL to Microsoft SQL Server
  4. You can check that you have the right connection settings by pressing the Test button and then the OK button.

Query to link Microsoft SQL Server to MySQL Database (Linked Server)

After completing above steps open a query window in MS SQL and run the following SQL statement:

EXEC master.dbo.sp_addlinkedserver
@server = N’MYSQL’,
@provstr=N’DRIVER= {MySQL ODBC 5.1 Driver}; SERVER=mysql-server-name; DATABASE=db-name; USER=username; PASSWORD=password; OPTION=3′

Be sure to replace mysql-server-name, db-name, username and password in the above query.

This script will produce a link to your MySQL database through the ODBC connection. You can check this linking in the Microsoft SQL Server Management Studio Object Explorer under Server Objects -> Linked Servers -> Providers.

sql-server-management How to Migrate from MySQL to Microsoft SQL Server


Data Migration between the Databases

Create a new database in Microsoft SQL Server, for instance “testMySQL”. Now run the following SQL statement to migrate/import table “users” from the MySQL database db-name, into the newly created
database in Microsoft SQL “testMySQL”.

SELECT * INTO testMySQL.dbo.users
FROM openquery(MYSQL, ‘SELECT * FROM db-name.users’)

As with the previous query, be sure to replace db-name and testMySQL as appropriate.

That’s it. Your data is now in your Microsoft SQL Server.

Please post any queries or questions in the comments section below.

The post How to Migrate from MySQL to Microsoft SQL Server appeared first on OptimusBI.