Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2008
    Posts
    4

    Unanswered: Database Migration

    HI Guys

    I have a large MYSQL database that I need to migrate to MS-SQL Server 2005. Are there any good tools available to do this with ?


  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, MS-SQL 2005 comes with many strong tools for doing just that. SQL Server's "linked server" is part of the database engine itself, and it is more powerful than the tools provided by many database engines. The SQL Server Integration Services are frequently compared to many ETL tools that cost more than SQL Server does, and they are also provided with SQL Server.

    These tools are more than adequate for dealing with terrabyte sized imports into SQL Server. They aren't for the "SQL For Dummies" crowd because they do take some time to set up and use properly, but I'm talking a few hours not a few months to learn.

    -PatP

  3. #3
    Join Date
    Jan 2008
    Posts
    4

    Database Migration

    Thanks,

    Wil the tools you mention lift and migrate the entire structure as well ?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Tools are tools. They will do whatever you make them do. Describe structure in more detail, what does "structure" mean in your question? I'm more than 99% sure that the ETL tools supplied with SQL Server 2005 can transport any schema and data that you need to move, but I need to understand what you expect to get certainty.

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the mysqdump utility to dump the database to a text sql file

    for those of you unfamiliar with it (pat), the mysqldump utility generates not only the CREATE TABLE statements, but also the INSERT statements to populate the tables (a "duh!" feature that i still don't see in sql server, although i must admit i stopped looking after sql 2000)

    then use a text editor to make a small number of minor changes to the CREATE TABLE statements

    then run the sql file into your sql server database, and VWALAH!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    for those of you unfamiliar with it (pat)
    Hmmm, what caused you to forget that we've discussed this on the phone before?
    Quote Originally Posted by r937
    then use a text editor to make a small number of minor changes to the CREATE TABLE statements

    then run the sql file into your sql server database, and VWALAH!!
    Why, cutting a script, editing the non-standard features out and correcting the syntax errors... Editing the generated SQL scripts is so much simpler for the novice than using a GUI tool that is built to do this kind of processing! I must have missed a meeting somewhere.

    -PatP

  7. #7
    Join Date
    Jan 2008
    Posts
    6
    Yo

    Why go through all that effort ?

    Suppose you like sql. I mean I also used to give the GUI tools a skip, because they usually imply a lot of learnng and trial-and-error and often does'nt do the trick in anyways - so you land up doing the scripts yourself, or edititing generated scripts to fit and work!

    But hey !!! I found this tool called ThougthScape Designer, worth having a look at ...

    -Db independent - Supports Sql Server, MySql, Oracle and Microsoft Access
    -It has a Visual designer
    -Can do data migration
    -Can handle your shema


    To move your BIG DB and data from mysql to sql server you need to:
    1. Import Schema from mysql
    2. Copy the schema and export to SQL Server
    3. Define a Migration - All viusual of course - Drag and drop mappings ....
    4. Execute migration - GIves a detailed report of all the failed records (should not have any if you used a default/straight mapping)


    And viola, your db has been moved, the tool automaticallly does all the sql for you (it knows mysql and sql server etc.)

    Have a look at their website http://www.teleiossoftware.com

    You can also download a community edition for free, which can only import and design, print, view, browse data etc.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I guess I didn't include enough "smileys" in that last post. I was being completely facetious suggesting that it was easier to edit SQL than to simply use a GUI tool.

    I meant that I thought the OP ought to use the GUI tools provided with SQL Server to migrate the data. They are relatively easy to use, extremely efficient, and the learning time is an investment because you can use the same tools to do any ETL (Extract, Transform, and Load) task that doesn't require professional assistance.

    The ThoughtScape product is just released, and based on their web site I have some serious concerns about the way that they do business. They might be fine, and the tone of the site reads like they sincerely want to help their customers, but:

    1) Teleios Software is not based in the United States
    2) They are brand new, less than a year of experience and no track record
    3) Some of their business practices concern me.

    They might be fine, but I would approach them with great care.

    -PatP

  9. #9
    Join Date
    Jan 2008
    Posts
    6
    Thanks for clearing up the smiley misconception

    You are right, they seem to be fairly new in the game, but I must say, it seems like their developers know their way around - if you look at what their product has to offer, that may count for a few under "the table" bonus points in my books, especially from a developer's point of view.

    In terms of support and consistency, well only time will tell, and sql server (especially 2000) has never let me down.

    I guess some bravery is in the order of the day (At least the tool has already been worth what I had to fork out)

    I am of course a developer myself and face value of a company doesn't concern me in my daily routine and thoughts that much.

  10. #10
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    You can also install the ODBC driver for mySQL and use the DATA IMPORTATION of MSSQL.

    Easy, fast, all you have to do is to check all the tables you want.

    Next step is to have a beer and enjoying it!


    BTW, mysql is a great db engine... I prefer this free engine for my web sites but that's a personal choice.

    Et voilà!
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PetruvdM
    I guess some bravery is in the order of the day (At least the tool has already been worth what I had to fork out)
    nice to hear, considering that you're so close them and all...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2008
    Posts
    4

    Database Migration

    Thanks guys, all I wanted was some help ! I did however solve my problem.

    (I would not try adding a face)

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Now you've piqued my curiousity. How did you finally solve the problem? What tools and process did you use?

    -PatP

  14. #14
    Join Date
    Jan 2008
    Posts
    4

    Database Migration

    This is what I did, I retyped the structure of each table in MS-SQL 2005 disigner, I then wrote programs to extract al the data from the Access tables one at a time and added the records to the SQL-Server.

    A hell of a job ! But its done.

  15. #15
    Join Date
    Jan 2008
    Posts
    6

    Thumbs up

    Nice, as long as you get the job done right !

    I mean there is always a next time to do it the easier way, at least now you know how long it would takke to do it "manually".

    Just so you know, with ThoughtScape Designer it is practically a "couple" of clicks (minutes not hours or days), no retyping, not "a lot" of work at all !

    Pat I'm sure you also know a much shorter way with some or other SQL 2000 GUI based tool right ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •