Reliability and Corruption

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello!

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
 
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

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
 
Chris said:
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.
Click to expand...

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?
Click to expand...

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

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
 
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),
Click to expand...

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?
Click to expand...

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.....
 
Chris said:
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?
Click to expand...

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
 
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
 
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
Click to expand...

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....
 
hi Tony (and Albert)

A bit of a follow-up question to this post, around the use of ASP and/or DAP

Are there the same considerations when accessing a backend database where
the front end screens are either DAP or ASP. Does this technology change the
way things happen, or are you still vunerable to database corruptions as with
a normal front end, or does this technology mean that you could have a less
reliant connection between you and the data itself?

Regards

Chris
 
Chris said:
hi Tony (and Albert)

A bit of a follow-up question to this post, around the use of ASP and/or DAP

Are there the same considerations when accessing a backend database where
the front end screens are either DAP or ASP. Does this technology change the
way things happen, or are you still vunerable to database corruptions as with
a normal front end, or does this technology mean that you could have a less
reliant connection between you and the data itself?
Click to expand...

DAPs wouldn't be any better as the data needs to go over the slow
connection.

ASP would be better as just the HTML goes across. The processing
would be on the web server. However if you have a lot of ASP users
inserting, deleting or updating records then that could be troublesome
with an MDB data engine. Lots of ASP users reading data is fine.

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
 
Back
Top