Converting MySQL Database to MSSQL

This blog show how to Convert MySQL Database to MSSQL.

If you are familiar with SQL then it is easy to convert the MySQL db to MSSQL db. The first thing which we have to do is we have to create a link between MySQL and SQL Server by using ODBC (Open Database Connectivity).

Steps to follow for creation of ODBC object:

1. Open Control Panel.
2. Click on Administrative Tools.
   
   
3. ODBC Datasources(if your OS is 32-bit then select ODBC datasources (32 bit) else select ODBC datasources (64 bit)).
   
   
4. Select System DSN tab.
   
   
5. Click on Add button.
   
   
6. Now a window with data sources will be displayed select MySQL ODBC from the list and click on Finish.
   
   
7. Give the Datasource name as you wish and enter the TCP:IP of your MySql workbench. and select the Mysql db which you want to convert to MSSQL and click on Ok.

Here ends the initial Setup. Now we have to construct a bridge between the MySQL and MSSQL Server by using link providers in MSSQL Server.

Steps to follow for construction of Bridge(Link) between the MySQL and MSSQL Server.

1. Open Sqlserver Management Studio and connect to the server.
   
   
2. Open New Query window and paste the following code.
   

   USE [master]
   GO
   EXEC master.dbo.sp_addlinkedserver @server = N'ODBC ConnectionName', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 5.3 Unicode Driver}; SERVER=server_name; _
   DATABASE=db_Convert_to_MSSQL; USER=root; PASSWORD=your MySql pwd; OPTION=3'

3. For confirmation whether the link between MYSQL and MSSQL Server is created just expand Server objects -> Linked Servers in Sql server management Studio.

Here ends the creation of bridge between MySQL and MSSQL Server. Now you have to create a database in SQL server and execute the query as follows.

insert into sql database name
select * from openquery (ODBC connection Name,''SELECT columns which you want
from Mysqldatabase")

After successful execution of about query you can check in your SqlDB you'll find the database which you need.