Re: Pros and Cons of SQL v/s MDB

From: Ron Hinds (__NoSpam_at___NoSpamramac.com)
Date: 05/14/04

  • Next message: Steve Jorgensen: "Re: Pros and Cons of SQL v/s MDB" 
    Date: Fri, 14 May 2021 16:35:13 -0700

    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" <nospam@nospam.nospam> wrote in message
    news:s7h3a0lo5el52vf2qinjgan3shgslifsku@4ax.com...
    > 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).
    >
    > On Tue, 11 May 2021 23:03:25 -0700, "GeorgeMar"
    > <anonymous@discussions.microsoft.com> wrote:
    >
    > >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
    >

  • Next message: Steve Jorgensen: "Re: Pros and Cons of SQL v/s MDB"