Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Unanswered: MySQL in business use

    Hi,

    I'm coming from a business point of view... I'm dealing with an (engineering) company that currently operates a main database (DB2 v6) and a few licenses for smaller databases (Access & FoxPro). There are around 2000 computers.
    I am definately considering replacing DB2 with MySQL as the main corporate database.

    My Question is:
    Is it feasible to replace MS Access with MySQL for smaller, low level databases?
    This would mean non-specialists who can maybe create their own databases with Access, instead using something like PHPmyAdmin (after some training).

    Obviously there is potential for much cost saving, but I am particularly concerned about issues of "ease of use" - whether there is a way of getting the same sort of functionality as Access without having to train 2000 mysql experts.
    The 2000 users definately need to be able to create their own smallish databases from time to time, and I would like to make sure can all do it, with the same software, at a low cost.

    Would really like to hear what everyone thinks about this.

    Thanks in advance.
    T Moakes.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    MySQL saves a bunch on hard costs. The place that it can kill you for "desktop" use is the soft costs.

    When you set up MySQL, you are essentially "tethering" your users to a server. They can no longer carry their database with them when they go. This drastically limits functionality for some kinds of users, especially the most mobile, which are often the most highly compensated and therefore the most expensive for the company to have down.

    You also loose a lot of portability. Users can no longer email a database to a client with any assurance that the client can use the database.

    In exchange for these soft costs, you can save a lot of hard costs. You save a bundle on disk and maintenance. You allow the company to hire a group of dedicated MySQL "experts" for tuning and consulting, but you have to hire enough of the experts to meet your user's demands. You also save the licensing costs IF you elect to give up Access altogether and replace the front end with phpadmin or something like it, but that also completely eliminates your ability to exchange data with others.

    This is a two-edged sword. I've known a couple of universities and governmental agencies that have gone this route, and a few of the agencies that have stayed on it for more than a year. So far, I don't know of any businesses that have made this switch.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    1

    MySQL in business use

    I'm a c# and c++ developer who works with MySQL and MS Access alot. There is a significant difference in the "feel" of using MySQL and Access...beginning with the fact that you often end up doing alot of typing in the console with MySQL...most windows users have never seen a console, which could be a barrier for you.

    There is now a MySQL control center which gives a nicer point and click administration capability...but it lacks the most productive feature of Access in my opinion. Access lets even low-skilled users create rather complex reports in a point and click environment. The data entry forms, reports, and VBA features of access make it very attractive to me for little organizer projects.

    You can email your mysql data files back and forth to each other...just look in the /data folder.

    My brother works on a huge db2 project doing directory services for the whole US and alot of canada. They migrated from MySQL to db2 because of it's lack of enterprise features like replication, stored procedures, and triggers. That may be an issue for you as well?

  4. #4
    Join Date
    Apr 2004
    Posts
    11
    Thanks for the replies!
    Thats certainly been very informative, given me plenty to think about.

    I think the difficult interface, and lack of convienient features in the frontends (that exist in Access) are the main concerns with MySQL.
    Would you say though that with practice eventually non-experts would learn to do the same things in MySQL in about the same time as it took in Access?

    I think the advanced features of DB2 maybe unecessary, so still keen on mysql for the bigger scale databases.

    One thing I was thinking with respect to portability, was that if the operations are done through something like PHPmyAdmin, then users could gain access through a web browser from anywhere. Is that possible?
    Also I thought perhaps there are some good tools around for exporting the data to a more standard format? (I was just guessing) Perhaps data could be stored in mysql and manipulated in Excel.

    I felt there would be a major benefit in terms of 'team' applications, where there are like 10 or more people working together on the same database.. I think this is an area where Access struggles to cope.

    thanks again,
    T Moakes.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by tristanm81
    I think the difficult interface, and lack of convienient features in the frontends (that exist in Access) are the main concerns with MySQL.

    Would you say though that with practice eventually non-experts would learn to do the same things in MySQL in about the same time as it took in Access?
    Based on the University experience, they finally got things to the point that MySQL only took a bit more than 3 times as long (in man-hours) as MS-Access IF the MySQL developer team at the computer center was engaged early in the project.

    Quote Originally Posted by tristanm81
    One thing I was thinking with respect to portability, was that if the operations are done through something like PHPmyAdmin, then users could gain access through a web browser from anywhere. Is that possible?
    As long as the PHPmyAdmin is visible outside the firewall, or the users have a portable way to "tunnel in" this is true. However, that also exposes pretty much all of your data to the Internet, which makes backups and security auditing much more important.

    Quote Originally Posted by tristanm81
    Also I thought perhaps there are some good tools around for exporting the data to a more standard format? (I was just guessing) Perhaps data could be stored in mysql and manipulated in Excel.

    I felt there would be a major benefit in terms of 'team' applications, where there are like 10 or more people working together on the same database.. I think this is an area where Access struggles to cope.
    Actually, the portability can be acheived through MS-Access. The users can easily export MySQL data to an MDB (aka Jet) file, which gives them back their portability which also opens them back up to the wealth of MS-Access support available.

    Quote Originally Posted by tristanm81
    I felt there would be a major benefit in terms of 'team' applications, where there are like 10 or more people working together on the same database.. I think this is an area where Access struggles to cope.
    Yes, if you prefer MySQL to MS-SQL, this is true.

    One other thing to keep in mind is that you can use MySQL as a pure data store. By this, I mean that you can replace Jet (the underlying engine that supports the MDB file format) with MySQL as long as you have ODBC (or better yet OLEDB) drivers for MySQL, but still continue to use MS-Access as your client side tool. This can be mildly problematic for conversion since MySQL sytax is slightly different than Jet syntax, but it offers many of the benefits you want with fewer restrictions.

    -PatP

  6. #6
    Join Date
    Apr 2004
    Posts
    11
    Thanks again for your reply, Sorry to keep asking more questions...

    From this last reply I get the impression that perhaps finding a good frontend for MySQL, it could be made to work well on people's desktops?

    I've heard of Navicat, which seems a potentially quite good. Do you know of any other frontend that could be used? I guess my priorities would have to be price and ease of use. (I could live without some of the extended features of Access).
    Or if mysql is just going to create too much hassle, what package would you say is the best alternative to Access in terms of price?

    thanks again,
    T Moakes

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If "price" means purchase price, then most database tools will do at least the basic tasks and many of them are free.

    If you mean the total cost to the business, for purchase, support, and the "soft" costs that have to do with ongoing training/use/support, then MS-Access is probably cheaper than any other end-user database tool.

    By the time you figure in a decent support team (at around six techs per thousand users), the cost of MySQL is already more than the cost of MS-Access. When you start to figure in the cost of hours in the workforce lost to database development, combined with lost opportunities because of the time that it takes to create a working solution to the user's problems, the cost skyrockets.

    If you want the lowest "hard" price, switch you end users to MySQL, and don't worry about putting anything on the desktop. The users will then buy MS-Acces out of other budgets, but that won't show up in your budget.

    If you want the lowest total price, leave them on MS-Access. It doesn't require you to buy an additional server, hire additional staff, or deal with the security problems that will eventually arise. It also means that your users will be more productive and happier, and the business as a whole will make more money.

    When you look at technology purchases, you have three basic classes of users. There are the "bleeding edge" adopters, that have to have the latest toys and often prefer the open source tools because they are free or low cost and relatively technologically advanced. There are the "rank and file", who will use whatever tool you provide them. Then there are the "slow adopters", they prefer "easy to use" tools, usually because they consider their time too valuable to waste on "techy stuff" until they really need it. In any technology rollout, the cost is usually 30% on the front end, 20% on the middle, and 50% on the back end, even though this is nowhere near the body count distribution.

    MySQL definitely has a place in the data center. When you have enough GQ (Geek Quotient) to support it, it is a wonderful tool. I can't recommend MySQL for end users, at least in my experience it just doesn't work well in that environment.

    I'm not going to even try to convince you, I'm only telling you what I've seen. Pick your favorite business school. Go read what they've written on the adoption of open source tools for end users. There are a few schools that recommend it, mostly because they've never actually done it. The ones with real experience almost universally recommend going with the packaged solution for the best total ROI.

    -PatP

  8. #8
    Join Date
    Apr 2004
    Posts
    11
    It is definately the long term total costs that matter to me (training etc.). And I'm not averse to sticking with MS if that works out the most effecient.
    Does anyone know of some good places to find data to allow me to make a solid complete comparison of the options? I trust your experience, but I may need some statistics for reporting purposes...
    I've struggled to find good price information, especially for microsoft licensing (its very confusing stuff).

    I have heard that Access struggles with tables larger than around 80,000 rows, and also as I mentioned earlier struggles when there are several users trying to manipulate the same table.
    Would using Access merely as a client for MySQL (as you mentioned) get around these limitations or would they remain? It seems to me that this could be a setup that could work well.

    thanks again again,
    T Moakes

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That is actually a very complex question. Trying to figure out just what might or might not work, and if it works then what it might cost borders on majik. If I could do that with what little I know about your planned usage, I'd be richer than Bill Gates and Larry Ellison combined!

    There are a number of possible sites that could help, but I'm reluctant to single out any site or group of sites. Look around, especially at business colleges for papers on the ROI of open source packages. If you can't find something that suits your needs, then tell me as much as possible about your end-user body count, projected usage (skill levels, tasks occuring now, and what you expect to see in the next 36 momnths), and geographic dispersal and I'll see what I can rustle up for you.

    Yes, Jet (the actual database engine) used by MS-Access gets cranky when you hit it with large tables. Most of those problems can be overcome by design improvements, even after you hit them. Jet has a hard and fast limit in terms of bytes, which is currently one of 1, 2, or 4 Gb (depending on the version of Jet).

    MS-Access (the GUI tool) can act as a client "front end" for any ODBC (or OLEDB) database engine. Most current versions of MS-Access include MSDE, (the Microsoft Database Engine) as well as using the default Jet engine. MS-Access can also use DB2, MS-SQL, MySql, Oracle, etc.

    You might want to consider posting this question in the MS-Access forum to see if someone there can help better than I can.

    -PatP

  10. #10
    Join Date
    Apr 2004
    Posts
    11
    Hi,
    Just wanted to say thanks very much for your advice, I think this has been the best source of information in my research!

    I am planning to recommend MySQL, but perhaps with two options:- with something like phpmyadmin for the frontend (cheap risky option) or Access as a frontend (expensive, less risky).

    I'm still not entirely sure on the cost of running Access on 2000 workstations though...

    Anyway, thanks again.
    T Moakes.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That smells like a good plan to me.

    With 2000 machines, you definitely qualify for Microsoft SELECT licensing. The sales creatures at Microsoft would dearly love to talk with you about such a thing. If you want, I can certainly sic them on you if you'll PM or Email me your contact information.

    -PatP

  12. #12
    Join Date
    Apr 2004
    Posts
    11
    Thanks again! but I think it will be a while before any real action happens, a fair bit more planning ahead I expect

    T Moakes

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Another little "goodie", just to stir the pot. There is such a beast as the Access 2003 Developer Extensions, which allow you to build a custom database file with an included Access Runtime package. This allows you to build and distribute "canned" applications as you see fit, at no additional cost. The user doesn't have 100% of the normal functionality of MS-Access, but many users don't need that... The users that do need those features, the company can buy full blown copies of MS-Access.

    This can significantly reduce the total cost if many of your users only want to use the applications you build, not actually tinker with the data on their own, while it preserves the ability of the users that do want to tinker with the data.

    -PatP

  14. #14
    Join Date
    Oct 2003
    Posts
    706

    Thumbs down

    Quote Originally Posted by tristanm81
    I'm coming from a business point of view... I'm dealing with an (engineering) company that currently operates a main database (DB2 v6) and a few licenses for smaller databases (Access & FoxPro). There are around 2000 computers.
    I am definately considering replacing DB2 with MySQL as the main corporate database.
    "Coming from a business point of view," do exactly that, namely: look, and calculate, before you leap. Your factors include not only simple issues like license-costs, but complex ones like how to convert every single database from DB2 to MySQL... not a trivial operation, and if an app requires something that MySQL does not provide, not possible. Those "microcomputer databases" may support single-office applications of which you are not even aware.

    Ask yourself why you are "considering" a change; exactly what do you intend to gain, and exactly how will you know that you have gained it. What is the Return On Investment? And what is that "investment," exactly? You must know, before you do anything!
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  15. #15
    Join Date
    Apr 2004
    Posts
    11
    Thanks
    ..that was sort of the plan...
    various cost benefit calculations are in motion. I was mostly after feasibility advice and some of the technical details, a lot of this information isn't readily available from google searches.
    The priority here has been getting some power, with some of the "user-friendly" interface aspects that MySQL lacks. I need something in this case thats capable of handling up to 150,000 records (if there is a small database app that can do this, I haven't found it).
    As for replacing the DB2 database, I'm not saying its confirmed, just that I'll definately recommend it.

    anyway, hopefully things will work out,
    thanks for the advice,

    T Moakes

Posting Permissions

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