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   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

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

Good Job Mike Hankey 22:03 6 Sep '09   Niklas, Thanks for the great article, I had to tweek it a bit as I'm accessing an MySQL database on an Ubuntu server but this gave me a solid base to work from. Thanks, Mike "It doesn't matter how big a ranch ya' own, or how many cows ya' brand, the size of your funeral is still gonna depend on the weather." -Harry Truman. Semper Fi http://www.hq4thmarinescomm.com[^] My Site Sign In·View Thread·PermaLink Perfecto cmschick 20:00 8 Jul '09   Thanks for the great 'how-to' this works perfectly as long as you follow the steps exactly as written. Saved me a lot of time and headaches. Sign In·View Thread·PermaLink Re: Perfecto Niklas Henricson 22:51 8 Jul '09   Glad to be of assistance Niklas Henricson Sign In·View Thread·PermaLink Problem with initializing the data source object of OLE DB Ahmed Barradah 9:26 2 Jul '09   Hi, I have followed your code but when I tried to get the data from mysql to SQL Server using this query SELECT * INTO gbdbMS.dbo.shoutbox FROM openquery(MYSQL, 'SELECT * FROM gbdb.shoutbox') I got the following error message Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL". What could be the problem? Thank you for your time and effort. Sign In·View Thread·PermaLink Re: Problem with initializing the data source object of OLE DB Ahmed Barradah 23:14 2 Jul '09   Hi, I have managed to solve the problem by using more updated ODBC driver 5.1 instead of the 3.1 that I had before. However, I am facing another problem, the data that I am importing from mysql is in Arabic character encoded in UTF8. When I used your query and imported this data into Sql Server, the data is displayed in a funny way that doesn't make any sense. I have changed the collation on the table and the column to Arabic but still no luck. Any suggestion from your part about how to solve this problem? This is a business data and I cannot afford re-enter it. Thank you for your time and effort Sign In·View Thread·PermaLink Re: Problem with initializing the data source object of OLE DB Niklas Henricson 23:26 2 Jul '09   Your problem is generic for the Arabic characters. UTF8 encoding should support the migration. I have no clue but suggest you upgrade your mySQL database to higher version and then try with the migration from the beginning. Tell me if that works. Best regards, Niklas Henricson Sign In·View Thread·PermaLink Problem creating the Linked Server soumenbanerjee28 6:47 23 Jun '09   Hi, I am trying to create a linked server between MySQL and MS SQL Server so that the same database can be replicated at both the servers. I am initially testing the linked server with both the servers being on the localhost. Later i would migrate to different server locations. I performed the instructions as mentioned. The creation of the linked server was easy. When i tried to copy one of the tables from MySQL to MS SQL Server, i am getting the following error. OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.1 Driver]Lost connection to MySQL server at 'reading initial communication packet', system error: 0". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL". I am running this on an x64 machine with windows XP SP 2. I have rechecked all the properties. Enabled OLE Automation in SQL Server 2005 Surface Area Configuration. Restarted the MySQL Server. Nothing has helped yet. Kindly suggest what can be the issue and possible solution. Thank You, Soumen Sign In·View Thread·PermaLink Re: Problem creating the Linked Server Niklas Henricson 22:41 23 Jun '09   Sounds like a mySQL configuration issue. Try to see if this description resolves your issues: http://forums.mysql.com/read.php?52,152265,241105#msg-241105[^] Please tell me if that made any difference. Best regards, Niklas Henricson Sign In·View Thread·PermaLink Re: Problem creating the Linked Server soumenbanerjee28 7:37 24 Jun '09   Hi Niklas Henricson, Thank you for getting back to my question. I tried what was mentioned in that bug report. Firstly i didn't find a bind-address value at C:\Program Files\MySQL\MySQL Server 5.1\my.ini. I used these 2 values for the bind-address under the section [mysqld] bind-address=127.0.0.1 bind-address=localhost I still am getting the following errors while trying to run a simple openquery... SELECT * INTO dbName.dbo.tableName FROM openquery(MYSQL, 'SELECT * FROM dbNameMySQL.tableNameMySQL') ------ERROR------ OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.1 Driver]Lost connection to MySQL server at 'reading initial communication packet', system error: 0". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL". ------ERROR------ Also when i try to check the properties of the Linked server in MS SQL Server. Was basically trying to check if there are any issues while mapping the user login between the servers. Here are the following errors found... ------ERROR------ "The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?" ------ERROR------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo) ------ERROR------ Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL". OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.1 Driver]Lost connection to MySQL server at 'reading initial communication packet', system error: 0". (.Net SqlClient Data Provider) ------ERROR------ Would re-installing or changing certain passed in values in MySQL help? Kindly suggest what can be a work around. Thank You Soumen Sign In·View Thread·PermaLink Re: Problem creating the Linked Server soumenbanerjee28 9:05 24 Jun '09   Hi Niklas Henricson, I wanted to update the status of creating the linked server. I was able to successfully configure a MS SQL Server and MySQL server linked server connection on a different 32 bit machine. Both the servers are local and i tried the same steps that you had mentioned. Its strange though that the linked server was giving me problems on my x64 machine on which i was trying till now. Any idea why that would happen? Thank You, Soumen Sign In·View Thread·PermaLink Re: Problem creating the Linked Server Niklas Henricson 22:43 24 Jun '09   Hello Soumen, The only thing I can think of is "myODBC connector" version you are using is the 32 bit one. Are you sure you did a download on the 64 bit one? The download webpage is located at: http://dev.mysql.com/downloads/connector/odbc/5.1.html#winx64[^] If that is not your issue and have verified the version of your "myODBC connector", I have nothing else to think of. For that I am very sorry! My suggestion then is to complete the migration at your 32-bit enviroment and then finally restore the database at your x64 platform. Best regards, Niklas Henricson Sign In·View Thread·PermaLink Re: Problem creating the Linked Server soumenbanerjee28 5:20 25 Jun '09   Hi Niklas, I have the 64-bit version of the myODBC connector. I was myself surprised with this kind of behavior. Anyways i would do as you suggested. Use migration on the 32-bit environment and then restore on 64-bit. Thank You, Soumen Sign In·View Thread·PermaLink I have a problem ?? ruba7 2:06 23 Jun '09   I follow your steps ,but I receive this message: The test connection to the linked server failed. ------------------------------ ADDITIONAL INFORMATION: " An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL". OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Microsoft SQL Server, Error: 7303) so what I can do to solve this error .. Sign In·View Thread·PermaLink Re: I have a problem ?? Niklas Henricson 2:27 23 Jun '09   Hello, Please try reading this bug report attached on a link bellow and look if the suggested fix resolves your issue. http://bugs.mysql.com/bug.php?id=28221[^] Apparently there is an error with the myODBC linking. Try a different version, either newer or older. Best regards, Niklas Henricson Sign In·View Thread·PermaLink Migrate data from MySQL to SQL Server ryanjoseph 15:56 15 Jun '09   Can i also use this to migrate data from sql server to mysql? Please help me... Thanks in advance.. Ryan Sign In·View Thread·PermaLink Re: Migrate data from MySQL to SQL Server Niklas Henricson 23:50 15 Jun '09   Yes, please check this article for more information http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html[^] Niklas Henricson Sign In·View Thread·PermaLink Better to use SSIS for this samardeep 2:33 3 Apr '09   I tried this approach of creating linked server and then import the data but could'nt get success, then i tried using SSIS to import data from MySql to SQL Server and bingo it worked. See this for further help programmign techniques Sign In·View Thread·PermaLink SQL Server to MySQL??? mjanulaitis1234 8:11 2 Feb '09   Well we are migrating to a 64-bit server and we are unable to get PHPBB3 to work with fusion-x lan 64-bit php using an ODBC connection to SQL Server 2005 so we are looking at using MySQL as the MySQL install works flawlessly. I saw this work and was hoping for a solution going in the other direction. Any ideas? Sign In·View Thread·PermaLink Re: SQL Server to MySQL??? Niklas Henricson 2:33 10 Feb '09   Hello! Have you checked this article? http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html[^] It suggests somewhere in the middle which tools you can use to do that job. Hope that helps! Best regards, Niklas Henricson Niklas Henricson Sign In·View Thread·PermaLink Re: SQL Server to MySQL??? ryanjoseph 15:49 15 Jun '09   Can i migrate data from SQL Server to MySQL using some queries? please help me on this... I already google this one, but most of the forum tells on using some tools to do that...I dont want to use the tools,i want it to migrate using sql queries... Please help me... Thanks in advance Ryan Sign In·View Thread·PermaLink Re: SQL Server to MySQL??? Niklas Henricson 23:54 15 Jun '09   Using tools for this type of migration is inevitable as far as I know. The tools mentioned in this article provide good information on where to start from. http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html[^] Good luck! Niklas Henricson Sign In·View Thread·PermaLink Re: SQL Server to MySQL??? J vincent r 6:15 18 Aug '09   I use data loader for migrating almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. In my view this is a best Data Migration Tool. Download Free : http://www.dbload.com Sign In·View Thread·PermaLink 1.00/5 (1 vote) Re: SQL Server to MySQL??? Niklas Henricson 8:06 18 Aug '09   Please do not dispespect this community and commersialize in here. If we where interested in commersial products I wouldn't think people would even search in code project to find a solution. Your solutions are unfortunately not for free! Niklas Henricson Sign In·View Thread·PermaLink Re: SQL Server to MySQL??? jeans3167 1:21 28 Aug '09   Here is a tool to convert mssql to MYSQL database. That i found on google search it says it can convert almost any database try and tel me is that worth or not. Sign In·View Thread·PermaLink Client Ran out of memory ... resolved! natejohnson@cox.nett 10:51 24 Nov '08   I encountered a lot of the following errors with my implementation of this concept: from MySQL: [MySQL][ODBC 5.1 Driver][mysqld-5.0.51a-community]MySQL client ran out of memory and from SQL 2005 Express: An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown. ...so I ended up doing the following T-SQL workaround that I created with some SQL DBA's help. First, I added the linked server using: EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 3.51 Driver}; SERVER=[your IP/name here]; DATABASE=[db name here]; USER=[user here]; PASSWORD=[password here]; OPTION=3' Then, the meat of what I was doing, basically just grabbing 1000 rows at a time (which was small enough that the MySQL connection could handle it) and inserting them into my SQL 2005 Express setup at the new host: begin declare @maxID nvarchar(10); declare @maxID2 nvarchar(10); declare @mySqlSQL nvarchar(2000); select @maxID = max(id) + 1, @maxID2 = max(id)+1000 from localDB.dbo.contacts set @mySqlSQL = 'select * from contacts where id between ' + @maxID + ' and ' + @maxID2; set @mySqlSQL = 'select * from openquery(MySQL, ''' + @mySqlSQL + ''')'; insert into localDB.dbo.contacts EXEC master.dbo.sp_executesql @mySqlSQL; select max(id) AS idMax, count(id) AS idCount from localDB.dbo.contacts -- double check that I didn't grab any duplicates end (lather, rinse and repeat the above...) ------------- ...Since I was linking to a database directly in my linked server setup, I also had the drop server handy for when I finished with one database and needed to move to the next one) EXEC master.dbo.sp_dropserver @server='MYSQL', @droplogins='droplogins' ------------- Mostly, this worked great for me because the table that I hit the most problems with had about 1.5MM records, which was going to take a while for me to hand-code 1000 records at a time. Hope this helps someone save time, since I battled with this for 2 or 3 days before I got it to work! -nate Sign In·View Thread·PermaLink

Last Visit: 9:38 8 Nov '09     Last Update: 9:38 8 Nov '09 12 Next »