Slashdot Mirror
Migration from MS-SQL to MySQL?
GuardianKnight asks: "I am working for an organization who is going from WinNT to Linux. They have a fairly large database and I was wondering is there a way or does anyone know how to convert an Microsoft Access database to a MySQL database (other than retyping all the data)? Is there any software that can do this?" How difficult would it be to create a program for all SQL based databases that would create export scripts that you could import into any engine that would recognize INSERT statements? Does something like this exist?
I know we're all Linux geeks here, but doesn't anyone sometimes use a Windows machine? Last time I checked, all you needed to do to tables from MS Access to MySQL was the MySQL client and ODBC driver for Windows. You'd set up the MySQL server as a data source, and that was that. You'd click on an Acces table, select "export", and have it export directly to the MySQL server.
As for going from MS SQL Server to MySQL, once you put aside the fact that MySQL simply can't *do* some of the things MS SQL and other larger databases can do, moving the data itself isn't bad either, especially if you have SQL Server 7.0, which I'm told has a migration utility that can take data from any ODBC data source and export it to another.
I guess there are more complex and spartan ways to do this, but it should be noted that there are some things in this world that don't *require* perl and 8 or 9 libraries and packages.
Ok, you have several options here. Mostly depending on the database you plan to port. If the database is essentially flat-file or basic-relational (ie: few or no stored procedures, transactions not required) then it should shift to MySQL without any difficulty whatsoever. See the script linked in another comment, have a play, and away you go.
Problems start to appear when you require transactions, subselects, or heavily utilise stored procedures. These can be compensated for in two ways. Either use Postgres instead, which supports many of these (I'm not 100% on transactions, but it does the others for sure) or get someone who knows MySQL programming well to duplicate the functionality outside the database itself (in accessing scripts etc).
You will however, need an expert to do such a translation if it is a complex structure, you'll probably need an expert even for a port to PostGres. Databases are not easy to work with, large volumes of data often make visualisation difficult, and it can take considerable time to become familiar with structure. Trying to learn the database syntax and capabilities at the same time is asking for trouble, or at least slow progress.
And don't forget the last option: Some times its just too damn hard.
You can't win a fight.
First I wanted to point out that the title contains 'MS-SQL' which I initially thought meant MS SQL Server, upon reading the article it was obvious MS Access is what was meant.
MySQL is an excellent database and probably beats MS Access feature for feature. That said it is by no means a powerful database. Its lack of support for nested sub-queries causes many dba's to turn cheek when they see it. What it lacks in features it makes up in speed.
To answer your question directly, asp2php includes database conversion, it will convert your Access to MySQL or other SQLs (see webpage, search freshmeat)
If your database is simple, use MySQL, if you are moving because you would have otherwise moved to MS SQL Server, Oracle, or other larger database, you may want to look at PostgreSQL or even running Oracle or Informix on linux.
Much also depends on how you will access this data, if you will depend heavily on views to abstract the tables to your users, you won't want to use MySQL. If you don't really have a user base it is relatively trivial to work around this in your source. Afterall, last time I heard, slashdot was MySQL driven, all that info and its so darned fast.
My bad. You are correct, it's an API, (or maybe just a layer, I'm not sure). I was a little confused at that time. I thought I had installed MDAC 4 to add functionality that wasn't in the previous version, but I remember now that I installed to be able to add an Access 2000 ODBC source.
The original points still stand though. mySQL is nowhere near even Access because it lacks transactions and subselects. These are two very large problems with it. Another large problem for me is the lack of foreign keys, but that is from a data integrety perspective, not a functional one, so it's not such a big deal for most people. Her e are the missing mySQL functions. There isn't a real deadline on transactions yet either, although they will be adding in automic multistatements for the next version subnumber.
I want to start playing around with PostgreSQL, since it seems to have all the features I want in a linux dB.
-no broken link
I realize that Sybase costs money when it is implemented in production, and I mean no disrespect to mySQL, which I consider an unbelievable value. I just want to suggest that the analysis of your existing application needs to be quite thorough before making this move.
I think the O'Reilly mySQL book does a fairly credible job pointing out some of the issues associated with going to mySQL from a traditional, enterprise-class RDBMS. So, check that out, if you don't know what all the issues are.
However, if there is no server side logic at all, or if the logic that does exist executes equally well in Access as it does in MS SQL Server, I think the path is clear to go to mySQL. My advice to anyone that wants to scale up from Access would be to consider mySQL and Sybase ASE on Linux first.
FWIW, I have not used PostgreSQL, so I have no opinion on that. The competing Enterprise-class RDBMSes, Oracle and Informix are quite good, but they are not similar enough from SQL dialect and tools perspective to satisfy someone with an investment in understanding MS SQL / Sybase.
--
Dave Aiello
-- Dave Aiello
Not that I have anything against MySQL, but depending on your needs, PostgreSQL may be a better choice for an RDMS. It is open source, and has transactions, triggers, a procedural language, and API's for languages like C, C++, perl, and python (and MySQL may have some of these as well). Of course, your mileage may vary.
I'm sure "SlashdotMedia" will improve on all the wonders that Dice Holdings blessed us all with
I use a script thats very usefull if you need to convert MS Access Data to MySQL.. http://www.cynergi.net/exportsql/
that's my 2 cents
Life is too short...