Migrate MySQL to Microsoft SQL Server

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.

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

• 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.

• 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:

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.

You must Sign In to use this message board.

FAQ  Noise Tolerance Very HighHighMediumLowVery Low  Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

Msgs 1 to 12 of 12 (Total in Forum: 12) (Refresh) FirstPrevNext

Error Member 2194878 3:24 5 Nov '08   hi i am getting this error : OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL". Sign In·View Thread·PermaLink It worked!! cullio 22:51 21 Oct '08   I had to import only a few tables from MySQL to MsSQL. Thank you Sign In·View Thread·PermaLink Problem adding linked server David Haysom 15:09 10 Oct '08   Hi Niklas First thank you for posting your article. It was a breeze until this point which I can not get around. Major Error 0x80040E14, Minor Error 25501 > 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=test; USER=root; PASSWORD=; OPTION=3' There was an error parsing the query. [ Token line number = 1,Token line offset = 12,Token in error = . ] I am using SQL 2005 and Sql Server Management Studio Express. I successfully added the connection to my MySQL database using the ODBC Data Source Configuration and it tested OK. Hope that you can help David Sign In·View Thread·PermaLink Re: Problem adding linked server Niklas Henricson 23:43 12 Oct '08   Hello David! I tried to make a research regarding the error you've got and I couldn't find any good answer to that. What I suggest is to create the link to your MySQL database manually instead. On the Object Explorer window in the management studio express, browse under "Server Objects"->"Linked Servers". Right click "Linked Servers" and choose the option "New Linked Server...", name it as "MySQL", choose the "Other data source", and inside the drop down list try to find the MySQL ODBC provider. Try to fill all that nessesary information and attempt to connect again. If that doesn't help please inform me. Best regards, Niklas Henricson Sign In·View Thread·PermaLink Re: Problem adding linked server David Haysom 22:22 18 Oct '08   Thank you very much for your reply and your help Niklas. I really appreciate that. I tried what you suggested but in the New Linked Server window there are plenty of Microsoft options but not the MySQL provider. I went back to control panel and the MySQL ODBS 5.1 Driver definitely exists and I can test connect to it. I wonder if it is a permissions problem? I am on Vista which does have its own quirks at times. (Even though SQLEXPRESS is on my PC, when I went to register it in SQL Server Management Studio Express, I found it under Network Servers in the Browse for Servers window.) David Sign In·View Thread·PermaLink Why use SSMS? neilio 8:23 11 Sep '08   Thanks for the article. But I'm wondering, why import into a native MSSQL database using SQL statements, table by table? If you've got an MySQL db with a large number of tables, this could get quite tedious, especially if you have to worry about datatypes. Instead, I simply used MS Access to connect to the MySQL database via ODBC, imported all the data and tables at once, then did an import into MSSQL from Access via SSMS. It took very little time. Sign In·View Thread·PermaLink Re: Why use SSMS? [modified] Niklas Henricson 9:28 11 Sep '08   It is not always certain that you need to import everything in once. In this article I used one of several ways of migrating a database into another. I choosed simply the easiest one. Some tables do not exist between two applications, there fore it wouldn't be nessesary to migrate everything. By migrating through SQL, you could simply have more control on what you are doing (on minor databases), or also build an application of your own handling the migration through these SQL statements. Another way would be to use the "Import and Export Wizard" through the ".NET Framework Data Provider for ODBC". My connection string would look like: Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=myDataBase;User=root;Password=hejsan; Your idea was a good one as well. Thank you for sharing it. And any other ideas are of course warmly welcomed Niklas Henricson modified on Thursday, September 11, 2008 2:50 PM Sign In·View Thread·PermaLink Re: Why use SSMS? neilio 10:46 11 Sep '08   Makes sense, thanks Niklas. Sign In·View Thread·PermaLink Just what I needed Aceisback 8:38 6 Sep '08   Glad to at last find a result for the info I was after. I am currently in the process of trying to move my phpbb forum to YAF, and seeing as the links on YAF where others had made the same move no longer work I am glad to see this. Thank you very much (and if you do publish the sql scripts it'll make my life that much easier too ) Sign In·View Thread·PermaLink Re: Just what I needed [modified] Niklas Henricson 21:20 7 Sep '08   Here is the original location of the database script that help you transfer the data between these two forums: Migrate PHPbb to YAF - Database script thread[^] However, something you should be aware of, is that PHPbb forum got UNIX time format, in difference to MS SQL datetime format. Another issue is that some of the posts are not showing up after conversion. I haven't looked into it very closely yet, to identify the source of this problem. If you find out what causes that (a missing flag maybe?), please tell me. I also had to remove all the passwords after migration from the YAF passwords column, since they are encrypted. You should develop a "password acquisition through e-mail", functionality in YAF, where users get new passwords. YAF and PHPbb do not use the same password-encryption. Niklas Henricson modified on Monday, September 8, 2008 3:13 AM Sign In·View Thread·PermaLink Re: Just what I needed Aceisback 13:45 8 Sep '08   Yeah I had that problem at first, can't say I'm 100% happy with the database layout of YAF (a lot of data redundancy) You need to set the message's flag to 22 (approved post and not deleted, you can't just set IsApproved to equal True) I think I've got most of the forum converted now (i decided to skip the private messages and watched list) I was (originally) trying to find a way to use the old PhpBB hashing function in YAF so that I could get people to use their own passwords still, and just upgrade the hashed field when their next login was successful. Sign In·View Thread·PermaLink Re: Just what I needed Niklas Henricson 20:12 8 Sep '08   I agree with you and thanks for your information regarding the flags. Niklas Henricson Sign In·View Thread·PermaLink 2.00/5 (1 vote)

Last Visit: Friday, November 21, 2008 10:26 AM     Last Update:Friday, November 21, 2008 10:26 AM