How does mdb data travel across a network?

[Bob H]

Hi,

Can anyone advise on the following security question:

We have a data mdb b/e (with Workgroup file) on a central server with
clients connecting via application mdb f/e's installed on their own PC's.
When a client connects to the data, how does the data travel between the b/e
and f/e? Does the whole of the data mdb travel across the network or just
data that is requested via SQL on the application f/e?

Many thanks for any advice.

Regards

Bob

 

[Scott McDaniel]

Access (actually Jet) is a file server type of database ... your frontends
do ALL processing, so Jet basically sends the entire contents of the table
across the network to the workstation, which will then parse the table to
return the requested data. Properly indexing your tables will speed this
process (a SQL statement requesting only indexed columns will return a small
set of data), but adding too many indexes to your table can slow data entry
somewhat. A true client-server database, like MS SQL Server or Oracle,
however, includes a query parser and CAN process your SQL requests and send
only that data back across the wire (assuming you've written your
application correctly, that is).
--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

"Smash forehead on keyboard to continue ... "

 

[Thilo Immel]

Hi Bob!

We have a data mdb b/e (with Workgroup file) on a central server with
clients connecting via application mdb f/e's installed on their own PC's.
When a client connects to the data, how does the data travel between the b/e
and f/e? Does the whole of the data mdb travel across the network

No, not the whole file.

or just
data that is requested via SQL on the application f/e?

It's not so easy. Access is a file based database. That means, that
the Access program at the memory at the client computer manage all
read and write actions upon the file. To get a resultset, Access has
to read some directory information inside the MDB file and some index
pages too, before it is ready to read the data.

All these information will be transfered via network. Access reads the
informations page for page, so a hacker must have byte level
information about the way Access stores the data.

It would be very easyier for him to copy the whole file and analyse it
back at home. Access inbuild security is'n very good.

Regards

Thilo Immel
Access Repair Service www.atroplan.com
New: Access Memory Reporter 1.0: shows the use of memory by the tables
and indices.

 

[Albert D. Kallal]

Access (actually Jet) is a file server type of database ... your frontends
do ALL processing, so Jet basically sends the entire contents of the table
across the network to the workstation, which will then parse the table to
return the requested data.

Properly indexing your tables will speed this
process (a SQL statement requesting only indexed columns will return a small
set of data)

Lets be VERY clear here. You request a record from the file..the whole table
IS NOT brought down if you have a index. Anyone who designs a database will
as a general rule not retrieve records without a index. What is sent down
the wire is SOME of the index, and the page where the record resides (often,
that will be only 1 or two records). In some cases this process is actually
LESS TRAFFIC then what goes on with sql server to request that one record!
(you see, sending a sql string down the wire can actually be MORE data then
JET doing a seek on a ISAM file sitting on the hard disk!). Ask yourself
what does JET send down the write to grab that data from the server?

Try making a table of 1 million records..and request one record via a index
field..and take a look at how much network traffic you get (you will need to
force a persistent connection to see the reduction in network traffic..but
give it a try!..some have showed me numbers of LESS traffic then sql
server....of course JET does not have to do the authentication each time
either).

, but adding too many indexes to your table can slow data entry
somewhat.

Gee, the above applies to any database product..and that includes sql
server.

It is important to thus note that with good designs, as a general rule
looking up a customer name, or invoice number DOES NOT send the whole table
down..but the only the record needed (and, possible 1 or 2 other records
that happen to reside in the same page of data).

 

[Scott McDaniel]

Lets be VERY clear here. You request a record from the file..the whole table
IS NOT brought down if you have a index. Anyone who designs a database will
as a general rule not retrieve records without a index. What is sent down
the wire is SOME of the index, and the page where the record resides (often,
that will be only 1 or two records). In some cases this process is actually
LESS TRAFFIC then what goes on with sql server to request that one record!
(you see, sending a sql string down the wire can actually be MORE data then
JET doing a seek on a ISAM file sitting on the hard disk!). Ask yourself
what does JET send down the write to grab that data from the server?

Thanks for clearing that up, Albert.

Try making a table of 1 million records..and request one record via a index
field..and take a look at how much network traffic you get (you will need to
force a persistent connection to see the reduction in network traffic..but
give it a try!..some have showed me numbers of LESS traffic then sql
server....of course JET does not have to do the authentication each time
either).


Gee, the above applies to any database product..and that includes sql
server.

You're right, of course, but since we're in the Access newsgroup I thought
it unnecessary to point this out. Perhaps I should have. Thank you for
correcting me on this point.

 

[Albert D. Kallal]

Often, the next question is if JET don't bring down the whole table, then
why is sql server SOOOO much better in terms of reduced bandwidth..and why
does it scale so much better then JET as a file share?

Well, for one thing, all updates with sql server occur server side. So, if I
for example have customer, and I want to void all of the customers invoices,
I can go:

currentdb.Execute "update tblInvoice set Void = true where custid = 123"

In the above, the customer might have 100 invoices. With sql server, only
the above command is sent to the server, and the engine server side executes
the above command, and those 100 invoices are voided..and NO additional
network traffic occurs.

With a jet file share, ALL processing occurs on the pc, that means the 100
records must travel down the wire to the pc, get updated, and then travel
back to the file share. You just make 200 records worth of bandwidth as
compare to sql servers NO records.

And, you rightly pointed out..that if NO index is available..then JET will
grab ALL records down the wire. More important, even if we did a bad job,
and had no indexes on the sql server example..STILL no traffic will occur.
(the hard disk on the server side will get a good workout...but not our
network). So, you can start to see how superior sql server is.

However, if we have a JET file share, and grab a single customer record to
the screen to edit, in both sql server, and JET, there is little traffic IF
we can retrieve the record via some key field like a customer number.

Once the editing of the one record is done..then jet sends it back to the
file share, and in the case of sql server, a sql update string is sent back.
So, in this example..not a whole lot more, or less network traffic occurs in
either case you choose.

However, as the application does more and more..you get more and more cases
where that reduced traffic really starts to add up (and more and more
users). Hence...sql server starts to scale much better.

And, even in those cases when you use ms-access to sql server and there is
NO index...only the one record gets sent..where as with the file share...you
get the whole table...

So, sql server is far more forgiving in some areas when it comes to
efficient use of the network.