Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Re: Reliability and Corruption

Discussion in 'Information Technology' started by Chris, Oct 26, 2005.

  1. Chris

    Chris
    Expand Collapse
    Guest

    Allen

    I was wondering what difference (if any) the backend architecture makes
    towards this possibility.

    It is my understanding that Access (or perhaps I should be saying Jet?) used
    as a backend will supply the contents of the table being queried to the front
    end, rather than just the results of the query as would happen with a SQL
    server backend, resulting in much more data travelling across the network.
    Would this increase the possibility of data corruption at the backend?

    Regards
    Chris

    "Allen Browne" wrote:

    > Hi Matt
    >
    > You asked for opinions; hopefully that's what you will get.
    >
    > There are lots of very badly implemented Access apps around, e.g. 200+
    > fields in one table, no relationships, no idea how to handle nulls. I even
    > saw one recently where a cartesian join meant that all they had to do to
    > cause the entire database to give answers twice as large as intended was
    > just add a record to one table. So, IT people sometimes have a bad
    > experience with Access, and they don't understand that the problem is due to
    > bad design and implementation, not with Access itself.
    >
    > Corruption of a well-designed and implemented Access database is almost
    > non-existent. It will corrupt during development when you are
    > creating/modifying forms, reports, and code. It will corrupt if used across
    > an unstable network (bad NICs, excessive collisions, or inherently unstable
    > technology such as WiFi). Like any database, it will corrupt if there are
    > interrupted writes (power failures, machine crashes, or users who switch
    > off/reset without exiting). But given a split database based on an MDE front
    > end, correctly set up with reliable network, hardware, power, and users, you
    > will run for years and never see a corruption.
    >
    > If you do see a corruption, it will most likely be a bad index, fixed with
    > just a compact/repair. Worst case is restoring a backup, which is just a
    > matter of copying over the back end file. Restoring a backup from SQL Server
    > is more powerful (e.g. rebuilding from transaction logs), but it is nowhere
    > near as easy.
    >
    > You need SQL Server as a back end if:
    > - you envisage many millions of records in your tables;
    > - you need scores or hundreds of simultaneous users;
    > - the database must run 24x7 (cannot come off-line to make a backup);
    > - security is crucial.
    >
    > As an analogy, SQL Server is a prime mover, and Access is a pickup truck.
    > The pickup costs you much less to buy, is easy to setup, costs less to
    > maintain, and gives performance and mileage. But if your loads require an
    > 18-wheeler, it makes sense to go that way.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Melvis" <Melvis@discussions.microsoft.com> wrote in message
    > news:44F19362-56F3-4D34-853F-05E313165684@microsoft.com...
    > >
    > > I recently designed a database for the department in state government
    > > where
    > > I work to be used department-wide but not for large amounts of data or
    > > high
    > > traffic volume. I used a lot of the tips provided here (security was a BIG
    > > help!), and have always enjoyed the excellent and direct responses to my
    > > own
    > > questions.
    > >
    > > I have recently learned that our department does not like to use Access
    > > for
    > > multiple user databases due to the possibility of data corruption. They
    > > suggest an Access front-end with a SQL back-end for greater reliability.
    > > While I believe them that SQL Server is probably better, the posts I have
    > > read here lead me to believe that they are not following good design tips
    > > (I
    > > never heard them mention separate front-ends on each user's machine, for
    > > instance).
    > >
    > > My question is a general, almost opinion question. Is Access, if set up
    > > properly with one back-end and separate front-ends, still vulnerable to
    > > corruption? If so, is it corruption that using SQL for the back-end will
    > > eliminate?
    > >
    > > These are very important questions for me to have answered right now, and
    > > I
    > > appreciate any and all responses on the matter. THANKS!
    > >
    > > ~MATT

    >
    >
    >
     
  2. Loading...

    Similar Threads Forum Date
    Ethnicity, Religion, Military Performance and Political Reliability — British Recruitment Policy an History of Sikhism Nov 12, 2010
    India Corruption rate in India is double of global average Breaking News Jul 9, 2013
    Partition Corruption in the Punjab... History of Sikhism Mar 24, 2013
    Bant Singh - A real anti-corruption hero Inspirational Stories Feb 27, 2013
    India US report slams India on corruption, social violence Breaking News May 25, 2012

  3. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    "Chris" <Chris@discussions.microsoft.com> wrote in message
    news:306C4352-04F0-4366-A8D6-EA2956700C87@microsoft.com...
    > Allen
    >
    > I was wondering what difference (if any) the backend architecture makes
    > towards this possibility.
    >
    > It is my understanding that Access (or perhaps I should be saying Jet?)
    > used
    > as a backend will supply the contents of the table being queried to the
    > front
    > end, rather than just the results of the query as would happen with a SQL
    > server backend, resulting in much more data travelling across the network.


    The above is incorrect. As a general rule, if you retrieve a record, then
    only that record is loaded (more specifically, the "page" with that record
    is loaded, and also index information is also loaded). If the table and
    records you are trying to retrieve are not indexed (or can not be indexed)
    by jet, then a full table scan will occur. Note how I said "scan". The fact
    that you got a mdb file sitting on your hard disk, or on a pc down the hall
    makes NO difference in what jet loads. JET is running on your pc, but like
    all pc databases, when you retrieve one record via a invoice number (that is
    indexed), then the whole file is NOT read from the disk (this is why pc
    databases can be several 100 megabytes, but a record loads instantly). To
    improve performance, JET like any database system tries NOT to read all the
    records, and if a index can be used, then all records are NOT read from the
    disk. Now, if you move the file to another machine, and put a network
    in-between JET and the file, this behaviors does not change ONE bit.

    So, the fact of a network, or not a network does not change Jet's behavior
    in anyway in terms of loading a record. If you do query that forces a full
    table scan, the whole table has to be read from the file. If that file is on
    your local hard disk, then he whole table is read into memory (we are
    talking about an full table scan here). If you move the file to another pc,
    then a full table scan will STILL cause the whole table to be read into
    memory, but the file is now somewhere else on the network.

    So, when JET can use a index, then the whole table is not read from disk.
    The location of the file on your local disk drive, or a disk drive down the
    hall makes no difference here. Of course, the key concept here is that when
    you got a network, whatever JET decides to read from this file now has a
    network between it and the mdb file....

    So, if JET can use a index, and not read in the whole table, it does so.
    And, therefore, if the files is on a pc down the hall, then again, the whole
    table will not be read into memory...

    > Would this increase the possibility of data corruption at the backend?


    Well, yes, that is exactly the problem. You got a program that is trying to
    read data from a disk drive, but between the program and the disk drive is a
    network. You break that connection, and it is much like turning off your
    computer without saving work.

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  4. Chris

    Chris
    Expand Collapse
    Guest

    Albert

    Many thanks for your prompt reply. Does this mean that if you are accessing
    a backend database as a mdb via linked tables in your front end, the work is
    still undertaken at the backend and the results sent to the front end (
    indexing permitting), so if you have a properly indexed database, and are
    accessing records using fields that are indexed, the network traffic volume
    would be similar to using a SQL server backend?

    Chris
    "Albert D.Kallal" wrote:

    > "Chris" <Chris@discussions.microsoft.com> wrote in message
    > news:306C4352-04F0-4366-A8D6-EA2956700C87@microsoft.com...
    > > Allen
    > >
    > > I was wondering what difference (if any) the backend architecture makes
    > > towards this possibility.
    > >
    > > It is my understanding that Access (or perhaps I should be saying Jet?)
    > > used
    > > as a backend will supply the contents of the table being queried to the
    > > front
    > > end, rather than just the results of the query as would happen with a SQL
    > > server backend, resulting in much more data travelling across the network.

    >
    > The above is incorrect. As a general rule, if you retrieve a record, then
    > only that record is loaded (more specifically, the "page" with that record
    > is loaded, and also index information is also loaded). If the table and
    > records you are trying to retrieve are not indexed (or can not be indexed)
    > by jet, then a full table scan will occur. Note how I said "scan". The fact
    > that you got a mdb file sitting on your hard disk, or on a pc down the hall
    > makes NO difference in what jet loads. JET is running on your pc, but like
    > all pc databases, when you retrieve one record via a invoice number (that is
    > indexed), then the whole file is NOT read from the disk (this is why pc
    > databases can be several 100 megabytes, but a record loads instantly). To
    > improve performance, JET like any database system tries NOT to read all the
    > records, and if a index can be used, then all records are NOT read from the
    > disk. Now, if you move the file to another machine, and put a network
    > in-between JET and the file, this behaviors does not change ONE bit.
    >
    > So, the fact of a network, or not a network does not change Jet's behavior
    > in anyway in terms of loading a record. If you do query that forces a full
    > table scan, the whole table has to be read from the file. If that file is on
    > your local hard disk, then he whole table is read into memory (we are
    > talking about an full table scan here). If you move the file to another pc,
    > then a full table scan will STILL cause the whole table to be read into
    > memory, but the file is now somewhere else on the network.
    >
    > So, when JET can use a index, then the whole table is not read from disk.
    > The location of the file on your local disk drive, or a disk drive down the
    > hall makes no difference here. Of course, the key concept here is that when
    > you got a network, whatever JET decides to read from this file now has a
    > network between it and the mdb file....
    >
    > So, if JET can use a index, and not read in the whole table, it does so.
    > And, therefore, if the files is on a pc down the hall, then again, the whole
    > table will not be read into memory...
    >
    > > Would this increase the possibility of data corruption at the backend?

    >
    > Well, yes, that is exactly the problem. You got a program that is trying to
    > read data from a disk drive, but between the program and the disk drive is a
    > network. You break that connection, and it is much like turning off your
    > computer without saving work.
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
    >
     
  5. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    > Many thanks for your prompt reply. Does this mean that if you are
    > accessing
    > a backend database as a mdb via linked tables in your front end, the work
    > is
    > still undertaken at the backend and the results sent to the front end (
    > indexing permitting),


    No work occurs at all on the back end. It is still a plane Jane file. The
    ONLY work that occurs on the BE part is a "file read".

    Note that you can put Excel document on that same network folder, or a Word
    document on that folder. In all 3 cases (word, access, excel), that software
    NEVER has to be instilled on the network drive/folder. Those are just files.
    So, the only real work that does (and can) occur on the remote side is plain
    Jane windows file reading. I can't exactly call that "processing", or any
    kind of work that is of much value. So, sure, some "work" occurs on the
    target end, but that work is no different then what occurs with Excel, or
    word. It is just a plain Jane disk read that occurs. Certainly no "sql"
    query processing occurs. (JET does that on the pc side..and then figures out
    what part of the file to read into memory -- and, if that file is on a
    network share, then that does not change what happens except you got a
    network between JET and the file).

    All data manipulation has to occur on the target end when you use a JET file
    share....
    > so if you have a properly indexed database, and are
    > accessing records using fields that are indexed, the network traffic
    > volume
    > would be similar to using a SQL server backend?


    Well, yes, but there is a larger story. Sql server will scale to more users
    because of the fact that sql server can do SOME processing on the server
    side.

    Take the following example:

    udpate tblCustomers set PurchaseOrder = 'approved' where customerID = 123

    With JET, the following happens:

    The one record is retrieved via the index and read into memory
    (this means, we open back end table, use index, retrieve the record ACROSS
    THE NETWORK to the memory in the pc, modify it, and SEND IT BACK across the
    network to the hard disk.

    With sql server the following happens:

    We transmit the sql command to the server

    udpate tblCustomers set PurchaseOrder = 'approved' where customerID = 123

    The server now opens the table (no network traffic...as it occurs local).
    The server reads the one record into memory (lets hope we got a index here.
    In fact, if we don't have a index, then a full table scan will occur JUST
    like it would with jet.However, while that server hard disk is going bonkers
    at full speed, there is NO network traffic occurring. This means you are do
    more stupid things with sql sever, and not kill network traffic. You will
    certainly make sql server sweat...but can work and cause NO network traffic
    to occur).

    Ok, we got the one record into memory (that memory is on the sql server
    side). We update the record, and write it back to disk. Notice how the
    record did not travel to the pc, but stayed on the server side. This is one
    reason why sql server scales well.

    Take the following:

    udpate tblCustomers set PurchaseOrder = 'approved'

    In the above, we have no index, and want to operate on all records. With
    jet, a full table will be transmitted to the local pc, and after each record
    is updated to approved, it now must be sent back. That is two trips


    With sql server, the above command is sent to the server, and then NO
    NETWORK TRAFFIC NEEDS to occur. So, sql server can do local processing,
    where as with JET, all data processing MUST occur client side.

    So, for a good amount of regular updating, you pull a record to the client
    into a form...update it, and send it back. Both jet, and sql server will not
    be much different in this kind of example (which is a major portion of how
    applications run). However, since sql server *can* do processing local side,
    then often network activity is avoided. And, when you start using stored
    procedures (code) that runs on the server side, then again you gain, as this
    code can do processing and not use network traffic.

    Also, because sql server is the one that opens the file, and NEVER the
    clients, then un-plugging the client side computer can't harm the data on
    the server side (you can un-plug your computer while ordering a book on
    www.amazon.com, and nothing happens to their computers because "their" sql
    server is opening, and reading the files, not you the client. This is also
    why you can't corrupt (damage) a sql server file on the client side, because
    YOUR computer never opens the file directly. However, if you un-plug the sq
    server machine, then watch out!!

    So, sql server is kind the exact "reverse" of JET. You got a sql engine
    running on the server side, and you tell it what to do "over there". You
    still send data back and forth, but in many cases you don't have to send the
    data to the client.

    So, when someone says that all data processing occurs client side with JET
    file shares, they are correct. It just that not all of the table needs to be
    sent to the client when updating one record.....


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  6. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    "Chris" <Chris@discussions.microsoft.com> wrote:

    >Many thanks for your prompt reply. Does this mean that if you are accessing
    >a backend database as a mdb via linked tables in your front end, the work is
    >still undertaken at the backend and the results sent to the front end (
    >indexing permitting), so if you have a properly indexed database, and are
    >accessing records using fields that are indexed, the network traffic volume
    >would be similar to using a SQL server backend?


    Just to add to Albert's reply one MVP reported that he could
    successfully use an Access FE linked to a SQL Server database on a
    dialup connection. Slow but it worked.

    But the same app in Access took a minute or two to bring up the first
    form.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
     
  7. Chris

    Chris
    Expand Collapse
    Guest

    Albert

    Brilliant - thanks for taking the time to provide such a comprehensive
    reply. I am a lot clearer now about this.

    If I understand it correctly then, there is very little difference between
    Jet and SQL Server in terms of network traffic when processing single records
    (which as you say is the majority of Form processing activity), so long as
    fields are indexed.

    Where the SQL server benefits come in (scalability aside) is where there is
    a need to do large batch processing - this is where Jet will result is large
    volumes of traffic whilst SQL will not

    Regards
    Chris
    "Albert D.Kallal" wrote:

    > > Many thanks for your prompt reply. Does this mean that if you are
    > > accessing
    > > a backend database as a mdb via linked tables in your front end, the work
    > > is
    > > still undertaken at the backend and the results sent to the front end (
    > > indexing permitting),

    >
    > No work occurs at all on the back end. It is still a plane Jane file. The
    > ONLY work that occurs on the BE part is a "file read".
    >
    > Note that you can put Excel document on that same network folder, or a Word
    > document on that folder. In all 3 cases (word, access, excel), that software
    > NEVER has to be instilled on the network drive/folder. Those are just files.
    > So, the only real work that does (and can) occur on the remote side is plain
    > Jane windows file reading. I can't exactly call that "processing", or any
    > kind of work that is of much value. So, sure, some "work" occurs on the
    > target end, but that work is no different then what occurs with Excel, or
    > word. It is just a plain Jane disk read that occurs. Certainly no "sql"
    > query processing occurs. (JET does that on the pc side..and then figures out
    > what part of the file to read into memory -- and, if that file is on a
    > network share, then that does not change what happens except you got a
    > network between JET and the file).
    >
    > All data manipulation has to occur on the target end when you use a JET file
    > share....
    > > so if you have a properly indexed database, and are
    > > accessing records using fields that are indexed, the network traffic
    > > volume
    > > would be similar to using a SQL server backend?

    >
    > Well, yes, but there is a larger story. Sql server will scale to more users
    > because of the fact that sql server can do SOME processing on the server
    > side.
    >
    > Take the following example:
    >
    > udpate tblCustomers set PurchaseOrder = 'approved' where customerID = 123
    >
    > With JET, the following happens:
    >
    > The one record is retrieved via the index and read into memory
    > (this means, we open back end table, use index, retrieve the record ACROSS
    > THE NETWORK to the memory in the pc, modify it, and SEND IT BACK across the
    > network to the hard disk.
    >
    > With sql server the following happens:
    >
    > We transmit the sql command to the server
    >
    > udpate tblCustomers set PurchaseOrder = 'approved' where customerID = 123
    >
    > The server now opens the table (no network traffic...as it occurs local).
    > The server reads the one record into memory (lets hope we got a index here.
    > In fact, if we don't have a index, then a full table scan will occur JUST
    > like it would with jet.However, while that server hard disk is going bonkers
    > at full speed, there is NO network traffic occurring. This means you are do
    > more stupid things with sql sever, and not kill network traffic. You will
    > certainly make sql server sweat...but can work and cause NO network traffic
    > to occur).
    >
    > Ok, we got the one record into memory (that memory is on the sql server
    > side). We update the record, and write it back to disk. Notice how the
    > record did not travel to the pc, but stayed on the server side. This is one
    > reason why sql server scales well.
    >
    > Take the following:
    >
    > udpate tblCustomers set PurchaseOrder = 'approved'
    >
    > In the above, we have no index, and want to operate on all records. With
    > jet, a full table will be transmitted to the local pc, and after each record
    > is updated to approved, it now must be sent back. That is two trips
    >
    >
    > With sql server, the above command is sent to the server, and then NO
    > NETWORK TRAFFIC NEEDS to occur. So, sql server can do local processing,
    > where as with JET, all data processing MUST occur client side.
    >
    > So, for a good amount of regular updating, you pull a record to the client
    > into a form...update it, and send it back. Both jet, and sql server will not
    > be much different in this kind of example (which is a major portion of how
    > applications run). However, since sql server *can* do processing local side,
    > then often network activity is avoided. And, when you start using stored
    > procedures (code) that runs on the server side, then again you gain, as this
    > code can do processing and not use network traffic.
    >
    > Also, because sql server is the one that opens the file, and NEVER the
    > clients, then un-plugging the client side computer can't harm the data on
    > the server side (you can un-plug your computer while ordering a book on
    > www.amazon.com, and nothing happens to their computers because "their" sql
    > server is opening, and reading the files, not you the client. This is also
    > why you can't corrupt (damage) a sql server file on the client side, because
    > YOUR computer never opens the file directly. However, if you un-plug the sq
    > server machine, then watch out!!
    >
    > So, sql server is kind the exact "reverse" of JET. You got a sql engine
    > running on the server side, and you tell it what to do "over there". You
    > still send data back and forth, but in many cases you don't have to send the
    > data to the client.
    >
    > So, when someone says that all data processing occurs client side with JET
    > file shares, they are correct. It just that not all of the table needs to be
    > sent to the client when updating one record.....
    >
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
    >
    >
    >
     
  8. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    > Where the SQL server benefits come in (scalability aside) is where there
    > is
    > a need to do large batch processing - this is where Jet will result is
    > large
    > volumes of traffic whilst SQL will not


    Yes, that is about right. So, at the end of the day, sql server can "update"
    records server side, but for ms-access all records must travel to the client
    to be updated. (this true statement is often mangled to mean that all
    records ALWAYS must travel to the client even when not needed!!).

    And, of course, at the end of the day, you can always replace the Back end
    with sql server, and continue to use a ms-access front end....


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     

Share This Page