Click here to Skip to main content
6,594,932 members and growing! (14,520 online)
Email Password   helpLost your password?
Database » Database » SQL Server     Beginner License: The Code Project Open License (CPOL)

Migrate MySQL to Microsoft SQL Server

By Niklas Henricson

Migrate MySQL to Microsoft SQL Server in just a few simple steps
SQL, Windows, SQL Server (SQL 2005)
Posted:5 Sep 2008
Views:27,329
Bookmarked:24 times
Unedited contribution
Announcements
Loading...
 
Search    
Advanced Search
Add to IE Search
printPrint   add Share
      Discuss Discuss   Broken Article?Report  
9 votes for this article.
Popularity: 4.26 Rating: 4.47 out of 5

1
1 vote, 11.1%
2

3
2 votes, 22.2%
4
6 votes, 66.7%
5

Introduction

This article is describing few simple steps in order to migrate a MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to an MS SQL Server enviroment.

Background

Initially I started my search for an article in Codeproject regarding MySQL->MS SQL migration without any success. I had an old PHPbb forum running, that needed to be upgraded to a Microsoft enviroment entirely. I could have just kept MySQL and Apache server, but instead I decided to migrate the entire concept of PHPbb to a YAF-forum.

Setup ODBC connection to MySQL database

This article will not go through on how to setup a MySQL or MS SQL server, but make sure you have downloaded at least the MySQL ODBC Connector from here: http://dev.mysql.com/downloads/connector/

For this article I downloaded the MySQL ODBC Connector 5.1.

The setup of this connector is pretty simple:

  • Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. Under the tab labeled as "System DSN" press the "Add" button.

Setup_ODBC1.jpg

  • On the "Create New Data Source" dialog that appeared, choose MySQL ODBC 5.1 Driver and then press the "Finish" button.

Setup_ODBC2.jpg

  • 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 "tigerdb". Do not change the port to anything else then 3306, unless during your MySQL server installation have defined something else.

Setup_ODBC3.jpg

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

Create a MS 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=tigerdb; USER=root; PASSWORD=hejsan; OPTION=3'
This script will produce a link to your MySQL database through the ODBC connection you just created on the previous stage of this article. The link will appear in the MS SQL Server Management studio like this:

Create_link.jpg

If it doesn't show up in the treeview, press the refresh button.

Import data between the databases

Create a new database in Microsoft SQL Server. I called mine for "testMySQL". In the query window run the following SQL statement to import table shoutbox from the MySQL database tigerdb, into the newly created database in MS SQL called testMySQL.

SELECT * INTO testMySQL.dbo.shoutbox
FROM openquery(MYSQL, 'SELECT * FROM tigerdb.shoutbox')

Thats it!

Points of Interest

During this migration I had to import lately my newly migrated database into the structure of "Yet Another Forum" tables. For that I used a series of SQL-scripts. However I am not going to post them here. If folks leave comments here about the need to these script, just tell me and I will gladly change this article and start adding them. You're welcome to post your comments.

Another issue you will most likely encounter are the differences between these two databases based on datatypes. I would suggest to proceed with a reverse engineering of your MySQL database (for example, Visio is one application that provides reverse engineering functionality) and start mapping all the differences and potential risks of loosing parts of data for instance, within varchar columns.

Microsoft SQL datatypes: http://msdn.microsoft.com/en-us/library/aa258271.aspx

MySQL datatypes: http://dev.mysql.com/tech-resources/articles/visual-basic-datatypes.html

History

2008-09-05: First version of this article.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Niklas Henricson


Member
Niklas Henricson began with programming in 1999 by working for the Swedish Working Life Enviroment authority.

Since then, he has been involved in numerous projects involving MTS/COM+, VB 6.0, ISE Eiffel 4.5, ASP.NET/C#/VB.NET as well as common ASP, and finally database enviroments based on Oracle, MySQL and MS SQL.

Between April 2007 and December 2008, Niklas worked as consultant for Mandator AB with some of his assignments spended in Ericsson.

Today he works as a senior consultat at Cybercom Group in Sweden assigned in a project for the Swedish Institute for Infectious Disease Control.

One of his most favorite books regarding programming is "The Pragmatic Programmer". Something he is recommending everyone to read.
Occupation: Web Developer
Company: Cybercom Group South AB
Location: Sweden Sweden

Other popular Database articles:

Article Top
You must Sign In to use this message board.
FAQ FAQ 
 
Noise Tolerance  Layout  Per page   
 Msgs 1 to 25 of 38 (Total in Forum: 38) (Refresh)FirstPrevNext
GeneralGood Job PinmemberMike Hankey22:03 6 Sep '09  
NewsPerfecto Pinmembercmschick20:00 8 Jul '09  
AnswerRe: Perfecto PinmemberNiklas Henricson22:51 8 Jul '09  
GeneralProblem with initializing the data source object of OLE DB PinmemberAhmed Barradah9:26 2 Jul '09  
GeneralRe: Problem with initializing the data source object of OLE DB PinmemberAhmed Barradah23:14 2 Jul '09  
GeneralRe: Problem with initializing the data source object of OLE DB PinmemberNiklas Henricson23:26 2 Jul '09  
QuestionProblem creating the Linked Server Pinmembersoumenbanerjee286:47 23 Jun '09  
AnswerRe: Problem creating the Linked Server PinmemberNiklas Henricson22:41 23 Jun '09  
GeneralRe: Problem creating the Linked Server Pinmembersoumenbanerjee287:37 24 Jun '09  
GeneralRe: Problem creating the Linked Server Pinmembersoumenbanerjee289:05 24 Jun '09  
AnswerRe: Problem creating the Linked Server PinmemberNiklas Henricson22:43 24 Jun '09  
GeneralRe: Problem creating the Linked Server Pinmembersoumenbanerjee285:20 25 Jun '09  
GeneralI have a problem ?? Pinmemberruba72:06 23 Jun '09  
GeneralRe: I have a problem ?? PinmemberNiklas Henricson2:27 23 Jun '09  
GeneralMigrate data from MySQL to SQL Server Pinmemberryanjoseph15:56 15 Jun '09  
NewsRe: Migrate data from MySQL to SQL Server PinmemberNiklas Henricson23:50 15 Jun '09  
GeneralBetter to use SSIS for this Pinmembersamardeep2:33 3 Apr '09  
GeneralSQL Server to MySQL??? Pinmembermjanulaitis12348:11 2 Feb '09  
GeneralRe: SQL Server to MySQL??? PinmemberNiklas Henricson2:33 10 Feb '09  
GeneralRe: SQL Server to MySQL??? Pinmemberryanjoseph15:49 15 Jun '09  
GeneralRe: SQL Server to MySQL??? PinmemberNiklas Henricson23:54 15 Jun '09  
GeneralRe: SQL Server to MySQL??? PinmemberJ vincent r6:15 18 Aug '09  
GeneralRe: SQL Server to MySQL??? PinmemberNiklas Henricson8:06 18 Aug '09  
GeneralRe: SQL Server to MySQL??? Pinmemberjeans31671:21 28 Aug '09  
GeneralClient Ran out of memory ... resolved! Pinmembernatejohnson@cox.nett10:51 24 Nov '08  

General General    News News    Question Question    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

PermaLink | Privacy | Terms of Use
Last Updated: 5 Sep 2008
Editor:
Copyright 2008 by Niklas Henricson
Everything else Copyright © CodeProject, 1999-2009
Web20 | Advertise on the Code Project