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?

MDAC is a DBMS??

[matthewg]

Correct me if I'm wrong, but isn't MDAC an API for accessing data sources, such as a Microsoft SQL Server, Microsoft Access Server, or ODBC data source (which could be a MySQL or PostgreSQL DBMS)? I didn't think that MDAC was a database server.

Dump?

[matthewg]

If your data and table structures are simple enough, a simple dump would work. A database dump, which any decent database tool can do, takes the data and converts it into SQL syntax. It will give you your CREATE statements to make the tables and INSERTs to insert the data into the tables.

Re:MDAC is a DBMS??

[matthewg]

The lack of the things you mentioned are not MySQL flaws, by MySQL designed decisions. MySQL is optimized for speed, speed, and speed. It does need to sacrafice some functionality to do this. If you need this functionality, don't use MySQL. PostgreSQL makes different tradeoffs; it is not as fast as MySQL but, as you mention, it is more full-featured.

ODBC - Its Easy

[the_ed]

There is an ODBC driver that you can use to connect any Windoze box to a MySQL server. Get it (along with instructions) here:

http://www.is erver.com/support/addonhelp/database/mysql/msacces s.html [iserver.com]

Then just link in the table, and you can use an Append Query to dump all the data.

--Ed

Re:Why not PostgreSQL?

[Malcontent]

I agree but watch out for a couple of things. One is that Postgresql does not explicitly suppoert outer joins. There are a couple of workarounds but it means manually translating all your queries. Also there is a 32 char limit on the default compilation for all object names. If you want longer object names you'll need to recompile (no big deal though). You will also want to put the postgres on it's own drive and format using bigger blocks. Oh yea also run without sync otherwise a bit performance hit.

Postgtres is very nice but limited in some ways. It's improving very fast though. If your main goal is to get rid of NT then you can also consider Oracle or interbase. Interbase will be opened up soon and oracle just dropped their prices (or atleast made them a little more flexable.)

Read O'Reilly MySQL and mSQL book

[XenonOfArcticus]

There's a review here on Slashdot [slashdot.org]. It will help you greatly, and answer most of your questions. Virtually anyone involved in SQL servers or thinking about getting involved with SQL servers will get something useful out of this book.

Sorry

[GuardianKnight]

Response to "Incorrect Tittle - And why?"
After reading that I realized that I need to be a little more specific. The Tables are made in Access...then exported to a MS-SQL server (that's VERY easy to do)
Thanks for all the help!

Database Conversion

[jedm]

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

Look into ODBC. I know, it has been tainted by microsoft -- but seriously, it is intended as a standard for communicating with databases. It requires a driver to connect to a database, and then the driver handles all conversions into the native SQL dialect. Code written to access an ODBC driver will work in Sybase/M$ SQL Server, Oracle, MySQL, Informix, Access -- you name it, providing you have a driver to integrate with the database.

As for the Database decision, you may want to take a look at the linux port of Sybase SQL Server ASE 11.0.3 [sybase.com]. I am more comfortable with the TransactSQL than I am with the MySQL dialect (specifically I found setting up users and permissions to be much easier). The downside (as pointed out to me by a friend) is that Sybase does not dynamically allocate space for the database. This means that you will have to manually increase the size of the database as it grows. This can be a major headache, or a minor irritation depending on your knowledge of SQL.

As a side note: Sybase's SQL Server can be linked with an Access database (what's this mean? you can set up a table in Access that references a table in the Sybase database. This is good for data entry / modification -- but you cannot modify the columns or the underlying database from Access). To do this, download the M$ SQL Server 7 driver from the M$ site (M$ SQL Server and Sybase SQL Server were the same until 6, then they both diverged -- but are still almost identical -- so you can use the free M$ driver to connect with the Sybase database). Then, make sure that your Sybase database is running on port 1433 (default for M$ SQL Server). Then open your Access database, select File->Get External Data->Link Tables. From here you aught to be set, follow the directions to connect to an ODBC datasource (if the MySQL database was set up with an ODBC driver you could use this database instead -- I only tried it with Sybase though). I was able to link tables from a Sybase database running at home, to an Access database at work. Fun Stuff!

MDAC4? You mean MSDE

[yugami]

You may be thinking of the Microsoft Data Engine.

Its fully compatible with MS SQL 7, but only as scalable as Access. Supposed to make it so you can program MS SQL apps on a laptop if you don't want to waste the space for the laptop version of MS SQL(or whatever their lite install is called) Also a good way for workgroup apps that might scale up later to start.

Database data load

[pdoubleya]

You can do this, I think, writing a generic utility, because SQL databases should provide "system" or "catalog" tables that give meta-data about the user tables in the system. This means you can query the catalog tables to find out what user tables there are, what the relationships are between them, and what columns they have, then build and execute the SQL statements dynamically to extract the data. The trick is on the import. Unless the tables have exactly the same name and structure in both databases, you'll need to provide some mapping between them. If we assume they do have the same structure, you can use the same catalog tables to write CREATE statements (if necessary) and the INSERTs to load the data. Some tricks: need to watch out for indexes, table triggers, relationship constraints, all of which might prevent a clean load. Also need to check for datatype conversions as necessary. In Java, the JDBC libraries provide an abstraction for catalog information. However, each JDBC driver I've worked with implements this slightly differently, so beware. I found some functions wouldn't work with some drivers at all, and others returned catalog information in different ways (converting all object names to uppercase, for example, regardless of the case in the database). For portability, I'd probably model this as a set of classes that represented an idealized SQL database, then subclass as necessary for particular vendors (ms, mysql, sybase, whatever) to handle idiosyncrasies in naming, syntax, etc. This sounds like it could be a neat project.

Um, MS Access is good for one thing...

[hatless]

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.

Criteria for a portable database

[PhiRatE]

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.

Incorrect Tittle - And why?

[evarlast]

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.

Re:Incorrect Tittle - And why?

[Fjord]

MySQL does not support subselects or transactions. It's nowhere near as good as MDAC4 (Office 2000)

Re:MDAC is a DBMS??

[Fjord]

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 [mysql.org] 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.

Watch out with SQL logic when moving to mySQL

[dave_aiello]

Putting on my Sybase/MS SQL Server DBA hat for a moment, be very careful that you understand the differences between Transact-SQL and NmySQL's SQL language implementation. If you are using a lot of server side logic in MS SQL Server -- stored procedures, for example -- you ought to consider moving to Sybase Adaptive Server Enterprise on Linux.

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

Why not PostgreSQL?

[Megaweapon]

Not that I have anything against MySQL, but depending on your needs, PostgreSQL [postgresql.org] 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.

Exportsql - that litle script...

[PolKa]

I use a script thats very usefull if you need to convert MS Access Data to MySQL.. http://www.cynergi.net/exportsql/ [cynergi.net]

that's my 2 cents