Re: What database shall I use???

kennedy-family_at_attbi.com (Jim Kennedy) wrote in <LIfj8.20536$702.11968_at_sccrnsc02>:

>"David W. Fenton" <dXXXfenton_at_bway.net> wrote in message
>news:91CEE9458dfentonbwaynet_at_news-server.nyc.rr.com...
>> corey.lawson_at_worldnet.att.com (corey lawson) wrote in
>> <3c8c2550.31870118_at_netnews.att.net>:
>>
>> >If you're only doing read-only access to Access [sic], then
>> >Access isn't too bad, even for large MDB files. It starts
>> >bogging down hard when your MDB size gets over ~20MB or so and
>> >>5 simultaneous read-write users, generally.
>>
>> You're simply WRONG.
>>
>> Wrong.
>>
>> You obviously don't know how to design a properly functioning
>> multi -user Access database.
>>
>> I have a client with a 375MB Access database (3 tables have over
>> 300K records in them) with 5-10 simultaneous read/write users.
>> Performance is just fine, there is no corruption and there are
>> no concurrency problems.
>
>Any of these file based databases where there really isn't a
>server, but is client based - which an Access database is - the
>corruption factor - which when it hits does not tell you at the
>moment it occurs - is very dependent upon the stability of ALL the
>pieces of the network. For example, I have seen it have terrible
>problems in places where the infrastructure is not as stable as
>yours presumably is. In non-IT places there can often be problems
>with stable power which can wreck havoc on an Access and other
>file based systems database.(They are just not designed for such
>abuse.) Granted people should have stable networks, but the
>reality is that there is a lot of variance in quality of
>infrastructure. Commercial RDBMS's (eg Oracle, DB2 , et al) are
>designed to weather such abuses. They do cost more and require
>more server hardware. But hey the right tool for the right need.

So, you're telling me that my clients with 3 PCs, no file server, and peer-to-peer networking should buy a dedicated server to run Oracle/DB2, whatever?

Hello? Are you nuts?

The post I responded to said that 5 simultaneous users running against a 20MB MDB was the limit for an Access back end.

This is so completely false that it deserves to be disputed vigorously.

In regards to corruption:

I've been in this business since late 1995 (developing Access databases), and the only times I've seen corruption at all has been in these circumstances:

1. Access crashed while writing the data for an update query (a large 300K-record append query; don't know why it crashed; had to send the file to Peter Miller for recovery; cost: <$300; downtime: 2 hours while diagnosing and restoring backup; data lost: none -- the morning's work was recovered from the fixed data file when we got it back from Peter the very next day).
2. Client was using peer-to-peer network, and the PC that hosted the MDB file was running WordPerfect v. 6.0 on Win3.x. As WP6 was extraordinarily unstable, it would crash periodically, taking Win3.x down with it. Eventually, after enough such incidents, the file became unrecoverable. This would have been no problem if the client had set up their backup properly (it was backing up the wrong data), or if the user had been following my instructions (keep backup copies of the data file on her workstation, just in case the overnight backup didn't work). None of these configuration issues were my responsibility -- had they been, they would have been changed immediately (and they *were* changed after they lost data). The client lost 3 months of data, but if they'd been running their network properly (not using WP in the first place, verifying that their backup tapes were backing up what they *thought* they were backing up and that they were restorable, and the end user doing the manual backups according to my written instructions instead of relying on her imperfect memory of them), they wouldn't have experienced corruption in the first place, but if they had, they would have lost a day's work at the very most.
3. Access 2K back end on Win2K server with opportunistic locking ON, the index of the primary key of one table became corrupt. About 200 records were lost, but we recovered all but 3 of those from backups. This was very shortly after the point at which MS identified the OpLocks problem with Jet 4 databases. I don't like A2K, in any case, and keep most of my clients away from it, where possible.
4. A replicated database kept losing replicability, while otherwise, data integrity remained fine. The problem was eventually traced back to an Exchange Server hot fix that had been applied to the server on which the replicated MDB lived, despite the fact that the client did not even *use* Exchange Server. Backing out the hot fix ended the corruption. No data were lost, since I used a replica farm and scheduled synchs to keep data backed up at 15-minute intervals. Perhaps some data entered in the 15 minutes before the loss of replicability was lost, but the client was not concerned about this at all.

That's it.

We're talking about about 30-50 different databases, all shapes and sizes, all of them Access applications talking to Access back ends, running in networks of 2-25 PCs, with 2 to 15 simultaneous users (15 is the point at which client/server starts to pay off, but it also depends on the specific application and the size of the data store).

When I am hired to create an Access database application for the client, part of the specification and planning process is a review of the client's network infrastructure. If there is anything questionable, that has to be replaced and brought up to spec before I'll agree to do the work. Why? Because it's a part of the cost of having an Access application -- you must have a stable, reliable network. But the cost of that is actually not all that high when combined with the cost of the Access application overall, especially when compared to the cost of the alternatives.

In short, it's far cheaper to clean up your network (and also improves productivity when you get rid of old clunker PCs) than it is to take on the administrative expense of a database server. Installing Oracle or SQL Server just to avoid upgrading your network is one of those penny-wise, pound-foolish moves that I would never recommend to a client. If any client suggested it to me, I'd probably withdraw -- I don't need clients like that.

-- 
David W. Fenton                        http://www.bway.net/~dfenton
dfenton at bway dot net                http://www.bway.net/~dfassoc