Become a fan of Slashdot on Facebook

 


Forgot your password?
Close
typodupeerror
×
Databases Programming Data Storage Software IT Linux

Manual migration from MS SQL Server to MySQL 44

Posted by timothy from the minimizing-handwork dept.
Mark Brunelli, News Editor writes "It's no small feat to manually migrate an enterprise from Microsoft SQL Server to MySQL, but it can be done. Here, Mike Sheffey, CEO of Versora, gives step-by-step instructions for two manual tasks: moving the data structure and the data itself."
This discussion has been archived. No new comments can be posted.

Manual migration from MS SQL Server to MySQL More

Manual migration from MS SQL Server to MySQL

Comments Filter:

  • Uh, for simple databases maybe (Score:4, Insightful)

    by Anonymous Coward on Tuesday March 22, 2005 @07:35PM (#12018107)
    If you got lots of stored procedures (Transact SQL or whatever MS SQL uses these days) then you're in for a world of hurt.

    Not to mention that MySQL doesn't even enforce referential integrity would would seem much less functional than MS SQL.

    I would maybe move to Postgress if you are looking for something on the cheap. I don't understand why MySQL has such a following, it's not that great as for as databases go. Barely a step above Berkley DB.

    • Re:Uh, for simple databases maybe (Score:5, Insightful)

      by tha_mink ( 518151 ) on Tuesday March 22, 2005 @07:44PM (#12018194)
      FROM THE ARTICLE:
      A manual migration from Microsoft SQL Server to a MySQL database running on Linux is an extremely tedious undertaking. Each step can require numerous man hours to complete. In addition, the possibility of error is high. Tread carefully.



      Why are we doing this again? I am all for hating on MS but yo...Microsoft SQL to MySQL in an Enterprise??? Are you that bored? PostGreSQL would be hard enough...but at least you could keep some of the functionality. I though the purpose fully featured database servers was to keep the logic in the DB.
      • because MsSQL doesn't run in other operating systems than microsofts? because the other one is free and the other one isn't?

        it's not that hard to come up with reasons.
        • because MsSQL doesn't run in other operating systems than microsofts? because the other one is free and the other one isn't?

          Dogshit doesn't cost one penny and is freely available but that doesn't mean you should be eating it.

          Seriously though, I am all for free software but if you're going to switch to some OSS solution, you should at least pick the best tool for the job, and frankly, mySQL is not a good replacement for Microsoft SQL in an enterprise situation. Do people not know that PostgreSQL exis
      • Agreed, actually we're considering a move from MSSQL to pgSQL at work, because we have data that would benefit from bitmap indexes (and we prefer to split our DB into several DBs, which makes Oracle/MSSQL expensive very quickly). MySQL is good for small databases, but it's certainly not enterprise ready. Stored procedures, triggers (or at least stored procedures), advanced indexes, and proven storage techniques are REQUIRED. Load up a billion records from one of your big tables, see what happens with MyS

    • Re:Uh, for simple databases maybe (Score:4, Informative)

      by jpkunst ( 612360 ) on Wednesday March 23, 2005 @01:11AM (#12020796)

      Not to mention that MySQL doesn't even enforce referential integrity would would seem much less functional than MS SQL.

      Will people ever stop saying this? InnoDB tables, which provide referential integrity, have been available for MySQL since version 3.23.34a (2001), are included in binary distributions by default as of MySQL 4.0, and are the default table type on Windows starting with version 4.1.5. See InnoDB Overview. [mysql.com]

      JP

      • >>Not to mention that MySQL doesn't even
        >>enforce referential integrity

        >InnoDB tables, which provide referential integrity

        they do? without stored procedures and triggers? how?
      • InnoDB is used in production at numerous large database sites requiring high performance.
        The famous Internet news site Slashdot.org runs on InnoDB.
        (from the InnoDB Overview [mysql.com])
      • The reason people are still saying this is because of MySQL AB's attitude in the first place, i.e. "You don't need foreign keys, stored procedures, views, or any of that crap. All that's a waste of time; look at how fast our database is!" Then, all of a sudden, they get foreign keys in v3 -- not v1, not v2, but v3 FFS! -- and *only with InnoDB*, which wasn't even written by them (by Heikki Tuuri, I seem to recall) and was not included in the "standard" package so was never deployed by most ISPs and was th
    • If you got lots of stored procedures (Transact SQL or whatever MS SQL uses these days) then you're in for a world of hurt. Not necessarily. I've successfully migrated 1200+ MS SQL stored procedures to raw SQL with a single custom script. Theoretically, I could then port the generated raw SQL to MySQL as plain SQL (rather than stored procs).
    • If you got lots of stored procedures (Transact SQL or whatever MS SQL uses these days) then you're in for a world of hurt.

      If you have stored procedures and you want to continue to use them, MySQL just isn't possible. As others have mentioned, MySQL doesn't support stored procedures at all.

      I would maybe move to Postgress if you are looking for something on the cheap. I don't understand why MySQL has such a following, it's not that great as for as databases go.

      Moving to PostgreSQL is far from painless

  • Practical? (Score:5, Insightful)

    by youknowmewell ( 754551 ) on Tuesday March 22, 2005 @07:38PM (#12018132)
    Is it practical to switch from SQL Server to MySQL? MySQL doesn't even have views! I can't imagine any scenario where one would want to take the big hit in features, except if you absolutely needed more speed, and didn't have a need for the features. Still, it would make more sense to switch to PostgreSQL since it has as many, if not more, features than SQL Server.

    • Re:Practical? (Score:3, Insightful)

      by xoboots ( 683791 )
      I wouldn't doubt that a lot of databases sitting on MS SQL can just as comfortably run on MySQL. Of course MS SQL is far more capable and has more features but that doesn't mean that they are needed for all databases and applications that a company may deploy.

      Does application logic belong on the database server? In a lot of cases, absolutely! In those cases MySQL is obviously not going to be a candidate. Then again, a lot of applications only use the database server as a storage backend; in those cases, po

      • Re:Practical? (Score:2, Informative)

        by youknowmewell ( 754551 )
        True, and MySQL can be alot cheaper as well. Still, the flexability PostgreSQL gives for future feature needs (without the need to migrate AGAIN) seems to appeal to me more than MySQL's features (or lack thereof).

        • Re:Practical? (Score:4, Insightful)

          by xoboots ( 683791 ) on Tuesday March 22, 2005 @11:52PM (#12020312) Journal
          I agree that PostgreSQL is far more feature rich than than MySQL. It is also a better development environment. Still, I don't think that implies it is the best choice for every application. It is very hard to argue against the applicability of MySQL -- what it does do, it does very well. If you are migrating an application that doesn't require the additional features of a more robust database, it is a worthy candidate. Particularly since if you do plan to need advanced features you actually have much less reason to port to another environment to begin with.

          Perhaps a more important point is to consider the OS environment one is likely to find MS SQL users running -- windows. While PostgreSQL has just very recently began fully supporting windows, MySQL has been there for a long time. In many ways, its a much softer sell for that reason.

          I haven't been arguing for MySQL over PostgreSQL because I see them as different tools suitable for different jobs. For the same reason, there are many cases where it would be more appropriate to stay with a MS SQL (or Oracle) solution.

          What I like about the article is that it is another signpost. The more that any of the leading FOSS rdms's get into the corporate space, the better it is for all FOSS rdms's to prove their case and improve their chance of acceptance and adoption. Its really hard to break the stranglehold of Oracle and MS SQL in the corporate environment so even baby steps are important at this time.

          Best Regards.
          • I agree, it's good for FOSS when we see the adoption of any of the free rdbms. MySQL certainly is more popular than PostgreSQL, so it would be easier to get the boss to switch.

            I wonder why MySQL has become so popular? Perhaps it could be that at one point it was much faster than PostgreSQL (which used to be slow), perhaps it could be GUI administration tools for it, perhaps both. I guess that's a bit off-topic though.
      • Aside from very simple website applications, the only situations where this migration makes sense are (required) 1) Don't want to use pgSQL, (optional) 2) Can't afford MSSQL, and (optional) 3) Have a middleware app (SOAP maybe) that handles all logic, security, and auditing.
    • Is it practical to switch from SQL Server to MySQL?

      I guess it depends on how minimal your needs are. But one thing I have noticed is that MySQL maps better to MS-SQL than Postgres. Postgres maps better to Oracle.

    • MySQL doesn't even have views!
      Your days of being able to say this are numbered, buddy. Views are available in MySQL binary releases from version 5.0.1 and up. Feel free to DL and have a look [mysql.com].

  • Never had anyone ask about it. (Score:3, Insightful)

    by WebHostingGuy ( 825421 ) on Tuesday March 22, 2005 @07:47PM (#12018220) Homepage Journal
    Being a web hosting company and offering MySQL, MS SQL and PostgreSQL, we have NEVER had anyone ask about converting from MS SQL to MySQL. While the article is comprehensive the people and applications using the MS SQL database are not the same ones using MySQL. When someone setups a MS SQL database they are doing so because they are looking for certain features and/or requirements from software they are installing. Under these circumstances the two are not interchangeable.

    I suppose if you have a roll-your-own db interface and wanted to drop the license fee for MS SQL you would do this. But realistically the ones who are using MS SQL have already sunk big $$$ into the license (or are renting for a fraction of the cost which would make switching for the money irrelevant). I doubt they would chuck MS SQL just for the security issues associated with Microsoft products.

    • Re:Never had anyone ask about it. (Score:4, Insightful)

      by BrookHarty ( 9119 ) on Tuesday March 22, 2005 @08:27PM (#12018569) Journal
      And those MS SQL licenses dont include the OS, or other MS related products. What about if you are required to upgrade to Windows 2003 server for all your servers, it could be so expensive that the one time headache of converting is worth it.

      I think any articles that helps you from being locked into one product is worth it. Having dealt with multiple products and for the telcom industry, they love taking a standard product and adding some piece of software that seems to lock you in. Radius, DHCP, DNS, all standard products, but put a gui and database, provide some backend API for provisioning, now your locked in.

      Give me unix and text configs anydays over a gui anyday.

  • Similar migrations (Score:5, Funny)

    by MobyDisk ( 75490 ) on Tuesday March 22, 2005 @08:52PM (#12018744) Homepage
    This is the kind of news that PHBs need to hear. These types of migrations are possible! I plan to write some similar articles on migrating some other proprietary produces to F/OSS software, including:

    Microsoft Office --> VI
    ASP.NET --> sh scripts
    Peachtree Accounting --> GnuCash
    PeopleSoft --> awk
    Rational Rose --> GIMP

    • As one who sides with the "should have done PostgreSQL instead" crowd:

      Microsoft Office --> TeX
      ASP.NET --> python and php.
      Rational Rose --> Umbrello

      I'm not sure what PeopleSoft does, but I suspect there are better alternatives. I've been told there is a nice web based tool for accounting that works well, but I forget what it is. Though the double entry bookkeeping of GnuCash is a good choice, better than MySql as a replacement for MSSQL.

      Not that my first two choices are better. (TeX once

      • I've been told there is a nice web based tool for accounting that works well, but I forget what it is.

        Probably SQL-Ledger [sql-ledger.org]. It's GPL software but if you want the PDF manual then you have to pay $190 [sql-ledger.org]. Notice how that isn't $200? Now that's savings ;)

    • Peachtree Accounting --> GnuCash

      Wait, I thought you were being sarcastic! Or were you just testing us for "which one of these is not like the others?" Seriously, have you tried Peachtree? Had to delete lock files 10 times a day? Had it crash and destroy all your data? Had it create an incomplete backup? I mean, GnuCash isn't even the same product family. GnuCash actually works. Peachtree is more closely related to the Vaporware family, since it makes all sorts of claims about how well it works,
  • If they released a product to automate the migration from their SQL server to MySQL or PostgreSQL.

    Seriously, they could squeeze a few more bucks out of the people who are no longer going to be using one of their products.

    LK
  • This is actually very easy, as I have recently done this same conversion. As noted in "The Definitive Guide to MySQL" by Michael Kofler, all you need to do is simply use the DTS wizard to export the table to an access database, and then use a mysql odbc driver to export it from access to mysql. This has worked flawlessly for me on many different tables.
    • There often is much more to a database then just the data itself, you know ? Moving the data is the easy part. Let's see you move the logic you have implemented inside your database in form of triggers, stored procs, constraints etc in that way.
  • Idiots! Moving DDL and data from tables is the easy-peasy part of database migration. Stored procedures, referential integrity, federation of databases, seamless integration with other SQL Server, Oracle and Informix data sources....

    You need to interact with other data repositories out there. mySQL is not a bad ISAM type database, but a ACID RDBMS it is not. Far from it.

    Just install DB2 on your linux servers and forget about it all. Easy to install, easy to admin, easy to integrate in shell scripts, top p
    • Just install DB2 on your linux servers and forget about it all. Easy to install, easy to admin, easy to integrate in shell scripts, top performance. What more do you need?


      Money.

Slashdot Top Deals

As you will see, I told them, in no uncertain terms, to see Figure one. -- Dave "First Strike" Pare

Close