|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Announcements
Chapters
Services
Feature Zones
|
Note: This is an unedited contribution. If this article is inappropriate,
needs attention or copies someone else's work without reference then please
Report This Article
IntroductionThis 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. BackgroundInitially 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 databaseThis 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:
Create a MS SQL link to your MySQL databaseIn 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 databasesCreate 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 InterestDuring 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 History2008-09-05: First version of this article.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||