Upsizing to SQL Server

[John]

Hi

We have a front end/back end type access app. We would like to upsize the
app to sql server but can not re-write the whole app immediately. Is it
feasible to just upsize the backend (data part) only to sql server using
upsizing wizard without any drastic effect on the performance of the front
end access app (which would link to the tables on the sql server after
upsizing)?

Thanks

Regards

[David Schofield]

On Wed, 29 Sep 2004 19:15:49 -0700, Tom van Stiphout
<no************ *@cox.net> wrote:

The two are related.
In order to reduce network traffic, you have to process on the server.
The example I always use:
select * from customers where state='AZ'
In Access, that pulls the whole table over the wire, or at least the
index on state if there is one, and the query is processed locally.
49/50 of the results are thrown out (assuming equal distribution of
customers over the country), and the customers of Arizona are left to
play with.
In SQL Server, the server processes the request, and only sends 1/50
of the data over the wire.

I certainly agree with this.
Upgrading all clients to gain more speed is
exponentiall y more expensive.

-Tom.

This is true, but for the cases I deal with (web mainly), the clients
are already more than powerful enough. The sort of processing I like
to see on the clients (referring to data-related examples) are things
like sorting, filtering and spreadsheet-like stuff which most systems
do by resending the dataset from the server in a different format.

The true server zealot (I'm sure you are not one!) is a megalamaniac
who wants to control every keystoke and doesn't think the user should
hava a computer at all. He is prepared for whatever network traffic is
necessary to achieve this, which is increasingly possible. Maybe that
is what I was reacting against.
-David

[Rick Brandt]

Tom van Stiphout wrote:

On 29 Sep 2004 09:29:12 -0500, d.************* **@blueyonder.c o.uk
(David Schofield) wrote:

The two are related.
In order to reduce network traffic, you have to process on the server.
The example I always use:
select * from customers where state='AZ'
In Access, that pulls the whole table over the wire, or at least the
index on state if there is one, and the query is processed locally.
49/50 of the results are thrown out (assuming equal distribution of
customers over the country), and the customers of Arizona are left to
play with.
In SQL Server, the server processes the request, and only sends 1/50
of the data over the wire.
If you are suggeting that this is what happens with ODBC linked tables a
simple SQL Trace will show you that this is incorrect. This is what
would happen with an mdb on the server. With an ODBC linked table to a
Server Engine the SQL statement WILL be passed to the server for
processing and only the results sent back. Even the use of DLookup
against a linked table sends a perfectly formatted SQL statement to the
server and only the matching rows returned.
Another reason for server processing is that SQL Server supports
multiple processors very well, and can take advantage of all available
server hardware (including >4GB of memory), and you can also scale to
a server farm to process requests more quickly (or for failover/
redundancy). Thus it becomes more realistic to "throw more hardware at
the problem". Upgrading all clients to gain more speed is
exponentially more expensive.

I agree with minimizing *network traffic* but even the most basic of
modern PCs has more than enough processing power to do any processing
beyond the selection of the data.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

Tom van Stiphout <no************ *@cox.net> wrote in
news:72******** *************** *********@4ax.c om:

It depends of course on your definition of client/server. My
definition is that most processing occurs on the server, and the
app is written so as to reduce network traffic as much as
reasonably possible.
Just attaching to SQL Server tables doesn't give you these
benefits.

Er, Jet is pretty smart in handing off an awful lot of things to the
server, so you do get the benefit of server-side processing for the
kinds of queries that *can* be handed off to the server.

If you're selecting and/or sorting on an Access/Jet expression,
you'll lose that benefit (though not entirely all of it in all
cases, since joins and other criteria on indexed fields could
significantly reduce the umber of records that will need to be
retrieved to calculate the expression values).

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