Click here to Skip to main content
11,413,676 members (76,913 online)
Click here to Skip to main content

Migrate MySQL to Microsoft SQL Server

, 5 Sep 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Migrate MySQL to Microsoft SQL Server in just a few simple steps

Introduction

This article describes a few simple steps in order to migrate 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 a Microsoft SQL Server environment.

Background

Initially, I started my search for an article on CodeProject regarding MySQL->MS SQL migration without any success. I had an old PHPbb forum running, that needed to be upgraded to a Microsoft environment 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 how to setup a MySQL or Microsoft SQL server, but make sure you have downloaded at least the MySQL ODBC Connector from here.

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 labelled 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 other than 3306, unless during your MySQL server installation, you 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 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=tigerdb; USER=root; PASSWORD=hejsan; 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:

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 "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 Microsoft SQL called testMySQL.

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

That's 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 for these scripts, 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 losing 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)

Share

About the Author

Niklas Henricson
Software Developer (Senior) TakePoint AB
Sweden Sweden
I started my journey with programming 1999 by working for the Swedish Working Life Enviroment authority.

Since then, I've been involved in numerous projects and jobs 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, I worked as consultant for Mandator AB with some of my assignments at Ericsson ST in Stockholm.

In 2009 I moved to south Sweden where I continued working as an IT consultant at Cybergroup Group South AB with assignments at Sony Ericsson, Swedish Institute for Infectious Disease Control and other in-house projects.

Today, I work for CGI with several different assignments in various projects within .NET Development.

One of my most favorite books in regards to programming is "The Pragmatic Programmer". Something that I definetely recommend for everyone to read.

My LinkedIn is: http://www.linkedin.com/pub/niklas-henricson/49/422/a3

Comments and Discussions

 
QuestionMigrate multiples tables Pin
JorgeSanzRi, 15-Dec-14 2:09
memberJorgeSanzRi15-Dec-14 2:09 
Questioni am getting an error while doing the above process. Pin
Member 10851839, 22-Sep-14 0:56
memberMember 1085183922-Sep-14 0:56 
QuestionChecks on New Database created from Mysql Pin
Member 7896136, 3-Sep-14 4:13
memberMember 78961363-Sep-14 4:13 
QuestionCan we convert stored procedure from ms sql server to mysql Pin
Priyant Jain, 17-Mar-14 20:28
memberPriyant Jain17-Mar-14 20:28 
AnswerRe: Can we convert stored procedure from ms sql server to mysql Pin
Member 10966016, 23-Jul-14 4:01
memberMember 1096601623-Jul-14 4:01 
QuestionCompare to SSMA Pin
dsa42, 30-Oct-13 10:49
memberdsa4230-Oct-13 10:49 
AnswerRe: Compare to SSMA Pin
dsa42, 30-Oct-13 11:07
memberdsa4230-Oct-13 11:07 
GeneralMySQL client ran out of memory Pin
Hninn Pwint Phyu, 8-Aug-13 19:16
memberHninn Pwint Phyu8-Aug-13 19:16 
GeneralRe: MySQL client ran out of memory Pin
Niklas Henricson, 9-Aug-13 21:57
memberNiklas Henricson9-Aug-13 21:57 
GeneralRe: MySQL client ran out of memory Pin
Hninn Pwint Phyu, 9-Aug-13 22:05
memberHninn Pwint Phyu9-Aug-13 22:05 
GeneralRe: MySQL client ran out of memory Pin
Niklas Henricson, 9-Aug-13 22:19
professionalNiklas Henricson9-Aug-13 22:19 
GeneralRe: MySQL client ran out of memory Pin
Hninn Pwint Phyu, 14-Aug-13 22:56
memberHninn Pwint Phyu14-Aug-13 22:56 
GeneralData Type Issue Pin
satz mois, 24-Jul-13 6:14
membersatz mois24-Jul-13 6:14 
GeneralRe: Data Type Issue Pin
satz mois, 25-Jul-13 20:24
membersatz mois25-Jul-13 20:24 
GeneralRe: Data Type Issue Pin
Niklas Henricson, 9-Aug-13 21:59
memberNiklas Henricson9-Aug-13 21:59 
GeneralMy vote of 4 Pin
Abhishek Durvasula, 17-Jul-13 21:32
memberAbhishek Durvasula17-Jul-13 21:32 
GeneralRe: My vote of 4 Pin
Niklas Henricson, 9-Aug-13 21:58
memberNiklas Henricson9-Aug-13 21:58 
QuestionError When Testing the Connection from SQL Server Management Studio Pin
Member 7824797, 3-Jun-13 10:12
memberMember 78247973-Jun-13 10:12 
QuestionBro you are a F*&(ng Legend!! Pin
Robert James Battam, 23-May-13 20:36
memberRobert James Battam23-May-13 20:36 
AnswerRe: Bro you are a F*&(ng Legend!! Pin
Niklas Henricson, 9-Aug-13 22:02
memberNiklas Henricson9-Aug-13 22:02 
GeneralThank You Pin
ron_x, 15-Mar-13 3:17
memberron_x15-Mar-13 3:17 
GeneralRe: Thank You Pin
Niklas Henricson, 15-Mar-13 4:21
memberNiklas Henricson15-Mar-13 4:21 
Questionthank you Pin
anhdktk, 18-Dec-12 2:50
memberanhdktk18-Dec-12 2:50 
GeneralMy vote of 5 Pin
Member 8423092, 1-Dec-12 2:14
memberMember 84230921-Dec-12 2:14 
SuggestionA note for 64 bit users... Pin
andymccluggage, 21-Aug-12 1:17
memberandymccluggage21-Aug-12 1:17 
GeneralRe: A note for 64 bit users... Pin
SamsonSargsyan, 20-Apr-13 11:26
memberSamsonSargsyan20-Apr-13 11:26 
QuestionMultiple Tables Pin
Ricardo Casquete, 6-Aug-12 16:03
memberRicardo Casquete6-Aug-12 16:03 
QuestionThere are 3rd party tools for this Pin
Damir Bulic, 1-Aug-12 22:38
memberDamir Bulic1-Aug-12 22:38 
SuggestionRe: There are 3rd party tools for this Pin
Dmitry Narizhnykh, 14-Nov-12 9:16
memberDmitry Narizhnykh14-Nov-12 9:16 
AnswerRe: There are 3rd party tools for this Pin
radjiv1, 17-Jun-13 13:23
memberradjiv117-Jun-13 13:23 
AnswerRe: There are 3rd party tools for this Pin
msuhyd, 12-Mar-14 0:43
membermsuhyd12-Mar-14 0:43 
QuestionProblem with the second query Pin
Ehud Grand, 10-Jun-12 4:41
memberEhud Grand10-Jun-12 4:41 
Questioncopy just data or whole table structure too? [modified] Pin
r7ap7r, 29-May-12 1:41
memberr7ap7r29-May-12 1:41 
BugError occur when compile last query in SQL2005 Pin
Bilal Ahmed Lilla, 17-Apr-12 22:44
memberBilal Ahmed Lilla17-Apr-12 22:44 
GeneralRe: Error occur when compile last query in SQL2005 Pin
Darvin Pappachan, 13-Aug-12 22:36
memberDarvin Pappachan13-Aug-12 22:36 
GeneralMy vote of 5 Pin
manoj kumar choubey, 2-Apr-12 1:28
membermanoj kumar choubey2-Apr-12 1:28 
GeneralMy vote of 5 Pin
SimbarasheM, 24-Feb-12 12:08
memberSimbarasheM24-Feb-12 12:08 
QuestionWhat about varbinary fields? Pin
Kiks, 14-Feb-12 3:18
memberKiks14-Feb-12 3:18 
QuestionProblem to migrate a whole mysql DB into MSSQL 2008 Pin
Kodjak, 25-Nov-11 1:59
memberKodjak25-Nov-11 1:59 
AnswerRe: Problem to migrate a whole mysql DB into MSSQL 2008 Pin
Kodjak, 25-Nov-11 4:32
memberKodjak25-Nov-11 4:32 
Questionproblem Pin
Mihaly Sogorka, 9-Nov-11 6:12
memberMihaly Sogorka9-Nov-11 6:12 
AnswerRe: problem Pin
Niklas Henricson, 9-Nov-11 7:43
memberNiklas Henricson9-Nov-11 7:43 
QuestionError to mygrate data Pin
ratulalam, 7-Oct-11 22:56
memberratulalam7-Oct-11 22:56 
AnswerRe: Error to mygrate data Pin
Niklas Henricson, 9-Nov-11 7:44
memberNiklas Henricson9-Nov-11 7:44 
QuestionDon't see MySQL option when adding new ODBC data source in control panel Pin
halifaxdal, 18-Sep-11 2:59
memberhalifaxdal18-Sep-11 2:59 
AnswerRe: Don't see MySQL option when adding new ODBC data source in control panel Pin
Niklas Henricson, 9-Nov-11 7:48
memberNiklas Henricson9-Nov-11 7:48 
QuestionHow can loop through all tables in my db Pin
aramosvizcarra, 15-Sep-11 5:14
memberaramosvizcarra15-Sep-11 5:14 
GeneralMigration another solution Pin
TheAceSolutions, 14-May-11 4:07
memberTheAceSolutions14-May-11 4:07 
GeneralRe: Migration another solution Pin
Niklas Henricson, 14-May-11 5:22
memberNiklas Henricson14-May-11 5:22 
GeneralError converting data type DBTYPE_DBDATE to datetime. Pin
Nasser019, 23-Apr-11 19:21
memberNasser01923-Apr-11 19:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150427.2 | Last Updated 5 Sep 2008
Article Copyright 2008 by Niklas Henricson
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid