Pros and Cons of SQL v/s MDB

[GeorgeMar]

I have an application written in VBA front end and MDB
back end. In 18 months, I've only had about 3 database
corruptions and all are suspicious because of other things
happening with the LAN.

The application may have up to 20 to 30 users concurrently
at its peak.

After the last corruption, which was recovered by
compacting and repair, someone mentione that MDB is not
ideal as a backend in amultiuser environment and that I
should consider at least MSDE.

The questions are what are the pros and cons of going to
MSDE; is it worth it? Is MSDE similar to Access run-time
version? What additional costs and hardware implications
will my clients incur?

many thanks
george

 

[Steve Jorgensen]

With 20 to 30 users at its peak, Access with a shared JET back-end is not
really ideal, but it can certainly work, and I'm working at an environment
that can have over 100 users on the same back-end. They are certainly having
some problems at this level, and more than you are having at 20-30, but not
crippling.

The first thing I would do in your situation, is just make sure your back-up
system is solid. If users are doing frequent updates to back-end data, I
would run a backup system capable of doing open file backups, and run that
several times per day. This handles your biggest concern which is that the
Access back-end is vulnerable to all kinds of potential corruption including
pruposeful, malicious damage. If you always have a recent backup, you can't
lose much data.

The next problem you need to work on is down-time. If the system does start
developing chronic instability, which can happen, you'll have to keep
e-mailing to get everyone out of the sytem, wait for everyone to get out, then
try to do a repair and compact while people who didn't get the message are
still trying to get -in- to the system.

For that problem, a SQL Server back-end can be a good option. MSDE might be
OK for 20-30 users depending on what kind of load is involved. More likely,
you'd end up with the Standard Edition server. If you do go this route,
you'll almost certainly have to make some changes to the front-end. You'll
want to make sure all bound result sets are kept small using filtering and/or
aggregation, and not open unbounded results that can chew up network
resources. You also may have to reimplement some queries as views or stored
procedures to get good performance.

Now, up-sizing to SQL server is often, bu not always the best answer in your
situation, and you should really try some experiments. If, for instance, some
of your users are doing data entry, and some are doing reporting, you might
consider splitting the application up into an OLTP system and the OLAP system,
and run periodic queries to update the OLAP system. Later, you can look at
up-sizing the OLTP, the OLAP, or both as needed. Another option might be to
have multiple back-ends with replication (probably not, but it should be
considered).

 

[GeorgeMar]

Steve,

Thank you for your much considered answer.

In the time that the application has been running, I have
had relatively few problems. I tend to think that the
problems occur when the network drops out and leave LDB
file open.

You mentioned a couple of things that I would like to
explore.

Firstly, do you know of a backup system that can be
incorporated into the application, that would do timed
backup?

Secondly, the greatest problem I have when the system
locks up, is how to delete the .ldb file. Sometimes, even
when all the users are out of the application, the .ldb
could have been corrupted which stops you from deleteing
or compacting the backend. The only solution seems to
reboot the server. That is not very popular. Have you
come across this situation?

regards
george

 

[Steve Jorgensen]

Steve,

Thank you for your much considered answer.

In the time that the application has been running, I have
had relatively few problems. I tend to think that the
problems occur when the network drops out and leave LDB
file open.

You mentioned a couple of things that I would like to
explore.

Firstly, do you know of a backup system that can be
incorporated into the application, that would do timed
backup?

Perhaps, one could be written, but no, I don't know of one. What I was
speaking of is that most commercial backup software has an optional module for
backing up open files. It's a good investment.

Secondly, the greatest problem I have when the system
locks up, is how to delete the .ldb file. Sometimes, even
when all the users are out of the application, the .ldb
could have been corrupted which stops you from deleteing
or compacting the backend. The only solution seems to
reboot the server. That is not very popular. Have you
come across this situation?

Yes, you need to find out which user or users have the .ldb file open, and
terminate their connections. You can do this all through the computer
management utility under Administrative Tools in the Windows Control Panel.

 

[Ron Hinds]

Just wanted to add a caveat to Steve's post: MSDE is *not* a good solution
for 20-30 users. The reason being, after 5 connections to a MSDE database
have been made, the "engine" starts to "throttle back" by introducing
delays. The delays increase as more connections are made to the server. So
you would have to go to Standard Edition (i.e., more $).

 

[Steve Jorgensen]

Usually, you're right about MDSE not being good for 20-30 users, but if he
load per user is very low, it can be fine. MDSE does not throttle by
connections, it throttles by concurrent task. If 5 tasks are being processed,
the 6th will wait to start until one of the others has finished. By this
measurement, 3 users could be too many if they are constantly hitting the
database with long-running queries and multiple simultaneous connections, but
if people spend most of their time viewing results, and run quick lookups and
updates avery few minutes (work time entry, for instance), the situation is
quite different.