Thinking of Access/jet to SQLServer or MySQL

Ian Baker

We have developed an Access/Jet database (2000, XP & 2003 versions) that has
been used by clients all around the world for several years and is extremely
robust with 55 tables, 172 hard stored queries (plus about the same in VBA
SQL code), 60 forms and about 18,000 lines of code. The biggest advantage
with Access is that it doesn't cost the client anything for if they don't
have Access already then we provide the Runtime version which makes our
application extremely cost effective for the client.

The most concurrent users recorded has been 23 without any problems and in 5
years we have only ever had 1 corruption when a server fell over.

We now need to expand the concurrent user abilities with the most being say
around 175 at the worst possible time with the objective being to allow
employees in an organisation to enter a record (for eg. logging an IT Help
Desk Call themselves rather then contacting the Help Desk and getting them
to log the call). We know that which ever way we go there will be a massive
learning curve and there WILL be an impact to the cost to the client as only
some clients have MS SQL Server.

Strategically we POSSIBLY may down the track be moving away from Access and
to ASP.NET as the front end (or both) but at the moment we believe we have 2
options - either MS SQL Server or MySQL for the backend. I must stress that
the most important factors are cost to the client, ease of self
installation, size of learning curve for us and the ease of future
enhancement development and support to the client.

We would very much like to hear from others on their thoughts on which way
we should go and why although I do assume that there may be a MySQL
bias here in this forum ;-).

Thankyou for your thoughts
Ian

Nov 23 '05 #1

Markus Popp

Well, of course, I can only tell you from the MySQL point of view - as you
expected. Just a few thoughts about it.

I must stress that
the most important factors are cost to the client, ease of self
installation, size of learning curve for us and the ease of future
enhancement development and support to the client.

The commercial license for MySQL, as far as I know (but please check it back
to be sure) costs about 500 dollars or 500 euros (I'm not sure, but the
difference is not that big ;-)) per server. That's probably something where
SQL server can't compete.

MySQL 5 (I highly recommand you to choose version 5, if you go for MySQL) is
very conform to the SQL:2003 standard, so whatever your developers have to
learn about MySQL does also apply in a high degree to other RDBMS. At least,
it won't be harder to learn MySQL from scratch than SQL server. For support,
take a look at http://www.mysql.com/network/ - for 245 euros/server & year
you get support and a lot of additional services compared to the community
version (that most people use). I'm not sure, but maybe it even includes a
commercial license - if that's the case you'll certainly be better of with
MySQL Network.

Performance is definetly no problem with MySQL. It's of course important to
choose the correct database design, but this applies to any database system
;-). Just want to say, if you might hear critisism that MySQL offers bad
performance (maybe some hard core SQL Server freaks could come up with this
lousy argument) - that can only be a result of bad database design or an
incredible weak hardware equipment (in which cases all the other RDBMS also
would fail). Here's also an interesting link to read:
http://www.linux-mag.com/features/monty.html

Another argument for MySQL is that it's plattform independent. Should you
ever choose to use a Unix/Linux server (or most other operating systems),
you'll have no problem at all.

There's one argument for SQL Server (in comparison to MySQL) that comes to
my mind - SQL Server still has some features that MySQL doesn't have
(although much less since MySQL 5 is out). But I (my personal opinion - of
course you'd probably hear different opinions in a SQL server newsgroup ;-))
would say, unless you really absolutely need a feature that SQL Server
provides and you can't live without it or create a decent workaround, I
would choose MySQL rather than SQL Server. If you're missing an important
feature in MySQL, you could also check out PostgreSQL
(http://www.postgresql.org), which is another very good Open Source database
server.

Markus

Nov 23 '05 #2

Ron Hinds

We have already completed a project exactly like you describe. We chose SQL
Server as the backend for a number of reasons:

1.) Easy to install;
2.) Easy to maintain with a host of GUI tools for managing AND development;
3.) Microsoft provides support for SQL Server (you only have to support your
app);
4.) It works better with Access than anything else out there - in fact
Access is optimized to work with SQL Server and vice-versa.

I only recently started looking at MySQL for another purpose. While it seems
to be a decent database, I was astonished to learn that it didn't support
features like stored procedures, triggers and views (which are extremely
useful with Access Jet for performance reasons) until the most recent
version, which only came out in the last month or so. The MySQL ODBC driver
seems to be a bit buggy, too.

MSDE is a redistributable (minus the GUI tools) version of SQL Server that
is as full-featured as the other versions with only some throttling of the
engine after a certain number of connections, which makes it ideal for the
smaller installations as it is cost-free. I believe the Standard Edition can
easily handle the larger loads you are talking about paired with some decent
hardware. SQL Server also comes with a COM object interface (SQL-DMO
[Distributed Management Objects]) that is easily manipulated from Access VBA
code. We are able to do backup and restore from Access with only a few lines
of code.

I'm sure I'll be flamed by the MySQL faithful ;-) so I'll just repeat: I'm
not disrespecting MySQL - I definitely see the potential and usefullness of
it in certain circumstances, and we will, in fact, be using it. But IMHO it
just isn't as well suited to *this* particular purpose as SQL Server is.

"Ian Baker" <so*****@microsoft.com> wrote in message
news:43***********************@news.optusnet.com.a u...

We have developed an Access/Jet database (2000, XP & 2003 versions) that
has
been used by clients all around the world for several years and is
extremely
robust with 55 tables, 172 hard stored queries (plus about the same in VBA
SQL code), 60 forms and about 18,000 lines of code. The biggest advantage
with Access is that it doesn't cost the client anything for if they don't
have Access already then we provide the Runtime version which makes our
application extremely cost effective for the client.

The most concurrent users recorded has been 23 without any problems and in
5
years we have only ever had 1 corruption when a server fell over.

We now need to expand the concurrent user abilities with the most being
say
around 175 at the worst possible time with the objective being to allow
employees in an organisation to enter a record (for eg. logging an IT Help
Desk Call themselves rather then contacting the Help Desk and getting them
to log the call). We know that which ever way we go there will be a
massive
learning curve and there WILL be an impact to the cost to the client as
only
some clients have MS SQL Server.

Strategically we POSSIBLY may down the track be moving away from Access
and
to ASP.NET as the front end (or both) but at the moment we believe we have
2
options - either MS SQL Server or MySQL for the backend. I must stress
that
the most important factors are cost to the client, ease of self
installation, size of learning curve for us and the ease of future
enhancement development and support to the client.

We would very much like to hear from others on their thoughts on which way
we should go and why although I do assume that there may be a MySQL
bias here in this forum ;-).

Thankyou for your thoughts
Ian

Nov 23 '05 #3