Slashdot Mirror

← Back to Stories (view on slashdot.org)

Manual migration from MS SQL Server to MySQL

Posted by timothy on 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."

44 comments

  1. Uh, for simple databases maybe by Anonymous Coward · · Score: 4, Insightful

    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.

    1. Re:Uh, for simple databases maybe by tha_mink · · Score: 4, Insightful

      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.

      --
      You'll have that sometimes...
    2. Re:Uh, for simple databases maybe by gl4ss · · Score: 1

      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.

      --
      world was created 5 seconds before this post as it is.
    3. Re:Uh, for simple databases maybe by jpkunst · · Score: 3, Informative

      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.

      JP

    4. Re:Uh, for simple databases maybe by innosent · · Score: 2, Insightful

      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 MySQL. Now figure out how you can track who changed record #325782910, when they changed it, and what program changed it. On tables of that size, MSSQL blows, but if you're going to do all that work, at least move to something better!

      --
      --That's the point of being root, you can do anything you want, even if it's stupid.
    5. Re:Uh, for simple databases maybe by DAldredge · · Score: 0

      Maybe not, but is damn near impossible to come up with a single GOOD reason.

    6. Re:Uh, for simple databases maybe by ignorant_newbie · · Score: 1

      >>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?

      --
      Sitting Walrus Blog
    7. Re:Uh, for simple databases maybe by tha_mink · · Score: 1

      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 exists? Or maybe it's too hard to learn? I don't get it.

      --
      You'll have that sometimes...
    8. Re:Uh, for simple databases maybe by Gwar9999 · · Score: 1

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

    9. Re:Uh, for simple databases maybe by ManxStef · · Score: 1

      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 therefore unavailable to most of their users. They then proclaimed that foreign keys are great, but that you still don't need rubbish like stored procedures or views.

      Anyone with a clue about ACID databases would feel somewhat resentful that MySQL had called them an idiot for wanting proper referential integrity for their data, then turned around and pretended that they'd been saying the same thing all along. THAT is why people still bad-mouth MySQL, and I can't say that I entirely blame them. Things may have changed greatly since V3, though you say it's taken until v4.1.5 for foreign keys to become standard which is a bit shocking for a database that claims to share the same enterprise space as MS-SQL, Oracle & DB2, but the mud stuck a long time ago for most...

      (Disclaimer: I use MySQL and think it's great for basic website backends and the like.)

      I'm also quite surprised that no-one's mentioned Firebird:
      http://firebird.sourceforge.net/

      Based on the highly-stable code of Borland Interbase, Firebird "...is a relational database offering many ANSI SQL-99 features that runs on Linux, Windows, and a variety of Unix platforms. Firebird offers excellent concurrency, high performance, and powerful language support for stored procedures and triggers. It has been used in production systems, under a variety of names since 1981." It rocks. (end plug!)

    10. Re:Uh, for simple databases maybe by llefler · · Score: 1

      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 as well, particularly for an entrenched MSSQL shop. I did some work with it, but it has one behavior that drove me nuts and blew away any cost savings. Postgres claims to be case insentive, but... when you type in a SQL statement it lowercases it (unless it is in quotes) and then does a case sensitive compare to tables and attributes. So say you have a table in MSSQL called OrderHeader. With MSSQL your existing code has a bunch of: select * from OrderHeader. PostgreSQL will not work unless you change all your code to: select * from "OrderHeader" or change your table name to orderheader.

      MySQL on the other hand is probably doing so well because of the utilities to support it. I would have really preferred to base my projects on Firebird, since it is truly enterprise ready. But compare MySQLAdministrator and myPHPAdmin to FlameRobin. And when I was looking at PostgreSQL all I could find were command line utilities.

      --
      It is amazing what you can accomplish if you do not care who gets the credit. -- Harry Truman
  2. Practical? by youknowmewell · · Score: 5, Insightful

    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.

    1. Re:Practical? by xoboots · · Score: 2, Insightful

      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, porting to MySQL is not particularly unreasonable.

    2. Re:Practical? by the+eric+conspiracy · · Score: 2, Informative

      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.

    3. Re:Practical? by youknowmewell · · Score: 2, Informative

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

    4. Re:Practical? by youknowmewell · · Score: 1

      How so? Is it because of dialectic or feature reasons?

    5. Re:Practical? by the+eric+conspiracy · · Score: 2, Informative

      How so? Is it because of dialectic or feature reasons?

      For example PG and Oracle use sequences, MySQL and MS use autoincrement fields.

    6. Re:Practical? by xoboots · · Score: 3, Insightful

      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.

    7. Re:Practical? by innosent · · Score: 1

      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.

      --
      --That's the point of being root, you can do anything you want, even if it's stupid.
    8. Re:Practical? by youknowmewell · · Score: 1

      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.

  3. Never had anyone ask about it. by WebHostingGuy · · Score: 3, Insightful

    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.

    --
    Quality Hosting e3 Servers
    1. Re:Never had anyone ask about it. by BrookHarty · · Score: 3, Insightful

      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.

    2. Re:Never had anyone ask about it. by superpulpsicle · · Score: 1

      Every organization I have seen that actively use MS SQL are already M$ fanatics with the MSDN subscription. Which pretty much include windows OS anyways.

    3. Re:Never had anyone ask about it. by innosent · · Score: 1

      No, it doesn't. MSDN will give you single seat licenses to test OS releases, but does not give you free upgrades for your 5,000 users.

      --
      --That's the point of being root, you can do anything you want, even if it's stupid.
    4. Re:Never had anyone ask about it. by FullMetalAlchemist · · Score: 3, Interesting

      That depends on the subscription, at my current job and at my previous we get and got all the stuff for free.
      Though in all fairness we where just 3000 people at my last job and now we are just 50 :)=

      Still being a Microsoft Partner and IBM Premium Partner is a real advantage.

  4. One more thing to move, not mentioned in TFA... by Anonymous Coward · · Score: 0

    ...would be the migration of consistency together with your data. But I guess that's reserved for the PostgreSQL sequel.

    1. Re:One more thing to move, not mentioned in TFA... by RealBorg · · Score: 1

      I usually don't need DB provided consistency when I can do a "SELECT COUNT()/MIN()/MAX" without the performance penalty of a sequential scan. MySQL can do this from an index, PostgreSQL cannot. Also I would not want to dump and restore my company's databases every few month in order to perform a release update. MySQL 5 in contrast can still use databases generated by version 3.

    2. Re:One more thing to move, not mentioned in TFA... by Anonymous Coward · · Score: 2, Insightful

      I usually don't need DB provided consistency when I can do ... without the performance penalty ... MySQL can ... PostgreSQL cannot.

      Obviously you don't process any important data.

    3. Re:One more thing to move, not mentioned in TFA... by Christopher+Cashell · · Score: 1

      Yes, I find msyelf with a need to count all the rows in a table daily. Sometimes hourly. There's just so many places that I need that information. On the other hand, having the database enforce the consistency of my data. . . who needs that esoteric feature?

      There are very good reasons for Postgres being slower at that, and it involves overall improved performance. If you *really* need that information that often (I can't honestly remember the last time I needed it), just set up a counter and a couple of triggers to increment/decrement it. Boom, issue resolved. Now you have your counts, but the rest of us, who don't care about that information, don't have to take the performance hit for it.

      As for the dump/restore. . . for a non-trivial application, migrating to a new Database version is a rather large undertaking. Adding a few minutes to include a dump and restore is trivial next to the rest of the upgrade, in my experience.

      --
      Topher
    4. Re:One more thing to move, not mentioned in TFA... by RealBorg · · Score: 1

      I was involved in the development of a web based forum application that involved showing message counts for each topic, topic counts for each area, posting and topic counts for each user,... Obviously you are running very small databases, it always took us a long downtime to dump and restore all the postings, topics,... Older versions of postgresql even had trouble restoring the dump sometimes and required manual edititing of the big dump files.

  5. Similar migrations by MobyDisk · · Score: 5, Funny

    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

    1. Re:Similar migrations by bluGill · · Score: 1, Funny

      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 you learn it does much better layout, and python is a nice scripting language overall)

    2. Re:Similar migrations by rsax · · Score: 1
      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. It's GPL software but if you want the PDF manual then you have to pay $190. Notice how that isn't $200? Now that's savings ;)

    3. Re:Similar migrations by innosent · · Score: 2, Insightful

      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, then the features disappear the first time you run it, taking half your data with it.

      --
      --That's the point of being root, you can do anything you want, even if it's stupid.
    4. Re:Similar migrations by Anonymous Coward · · Score: 0

      I'm not sure I'd ever trust an accounting package that uses a database schema that stores dollar amounts as floats.

  6. Great Revenue source for MS by Lord+Kano · · Score: 2, Funny

    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

    --
    "Hi. This is my friend, Jack Shit, and you don't know him." - Lord Kano
  7. MsSQL to MySQL by truz24 · · Score: 1

    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.

    1. Re:MsSQL to MySQL by Wudbaer · · Score: 1

      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.

  8. Database newbies by TheToon · · Score: 1

    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 performance. What more do you need?

    --
    //TheToon
    1. Re:Database newbies by youknowmewell · · Score: 1
      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.
  9. Slashdot.org runs on InnoDB by davidag · · Score: 1

    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)

    1. Re:Slashdot.org runs on InnoDB by Wudbaer · · Score: 1

      You mean the one with the "Nothing to see hear"'s and the for some time constant authentication problems ? There's a recommendation...

  10. Enjoy it while it lasts by lorcha · · Score: 1
    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.
    --
    "Avoid employing unlucky people - throw half of the pile of CVs in the bin without reading them." -- David Brent