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

[Arvin Meyer]

If you make no changes at all, there will be a small and possibly
non-noticable decrease in performance due to SQL-Server's logging function.
It is certainly easy enough to test it by doing the upsize and testing the
performance on your biggest transactions.

Start replacing your bound forms with ones based on views and stored procs,
doing the slowest loading ones first. Access mdb's integrate quite nicely
with SQL-Server and tend to be slightly preferable for reporting.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:41***********************@news-text.dial.pipex.com...

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

[Tom van Stiphout]

On Wed, 29 Sep 2004 02:40:44 +0100, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Try it.
The upsize wizard will create a new app with attached tables to the
sql server. You be the judge on how well that works for your app.

Purists don't like this approach. It's not client/server and you're
not taking advantage of what the SQL Server has to offer.

Pragmatists have an app that they can use right away, and optimize
only the performance bottlenecks.

-Tom.

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

[Tony Toews]

"John" <Jo**@nospam.infovis.co.uk> wrote:

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

I'd agree with Arvin and Tom's comments.

Also see my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

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

[Rick Brandt]

Tom van Stiphout wrote:

On Wed, 29 Sep 2004 02:40:44 +0100, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Try it.
The upsize wizard will create a new app with attached tables to the
sql server. You be the judge on how well that works for your app.

Purists don't like this approach. It's not client/server and you're
not taking advantage of what the SQL Server has to offer.

Pragmatists have an app that they can use right away, and optimize
only the performance bottlenecks.

-Tom.

In what way is it not "client/server"? The "server" will still execute
the vast majority of the data processing respoding to requests from the
"client".
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Tony Toews]

Rick Brandt <ri*********@hotmail.com> wrote:

Try it.
The upsize wizard will create a new app with attached tables to the
sql server. You be the judge on how well that works for your app.

Purists don't like this approach. It's not client/server and you're
not taking advantage of what the SQL Server has to offer.

Pragmatists have an app that they can use right away, and optimize
only the performance bottlenecks.

-Tom.

In what way is it not "client/server"? The "server" will still execute
the vast majority of the data processing respoding to requests from the
"client".

Maybe not "efficient client/server".

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

[Rick Brandt]

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:4t********************************@4ax.com...

Rick Brandt <ri*********@hotmail.com> wrote:

In what way is it not "client/server"? The "server" will still execute
the vast majority of the data processing respoding to requests from the
"client".

Maybe not "efficient client/server".

Well sure, I would agree with that :)

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

[Tom van Stiphout]

On Tue, 28 Sep 2004 21:21:25 -0500, Rick Brandt
<ri*********@hotmail.com> wrote:

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.

-Tom.

Tom van Stiphout wrote:

On Wed, 29 Sep 2004 02:40:44 +0100, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Try it.
The upsize wizard will create a new app with attached tables to the
sql server. You be the judge on how well that works for your app.

Purists don't like this approach. It's not client/server and you're
not taking advantage of what the SQL Server has to offer.

Pragmatists have an app that they can use right away, and optimize
only the performance bottlenecks.

-Tom.

In what way is it not "client/server"? The "server" will still execute
the vast majority of the data processing respoding to requests from the
"client".

[David Schofield]

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

On Tue, 28 Sep 2004 21:21:25 -0500, Rick Brandt
<ri*********@hotmail.com> wrote:

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.

-Tom.
Hi Tom
Fair definition, and reducing network traffic is absolute top
priority, but I often wonder why we want to do as much processing as
possible on the server. I try to do as little as possible, essentially
just the generation of recordsets.

Most heavily used systems have much more total procesing power in the
clients than in the server. This is extremely so in the case of
successful web apps, even with Jscript. You can't change this by
promoting simpler clients as processing power is very cheap and users
like having it. The only advantage of using a server at all
(admittedly this is an OVERWHELMING advantage) is the collection of
data, updating etc in a single place for integrity and sharing.

For non-web client-server there is the problem of deployment and
updating of the client-side software. However this can be done using
the Internet in various ways.
David Schofield
Just attaching to SQL Server tables doesn't give you these benefits.

I certainly agree with this! David

[Tom van Stiphout]

On 29 Sep 2004 09:29:12 -0500, d.***************@blueyonder.co.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.

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.

-Tom.

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

On Tue, 28 Sep 2004 21:21:25 -0500, Rick Brandt
<ri*********@hotmail.com> wrote:

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.

-Tom.

Hi Tom
Fair definition, and reducing network traffic is absolute top
priority, but I often wonder why we want to do as much processing as
possible on the server. I try to do as little as possible, essentially
just the generation of recordsets.

Most heavily used systems have much more total procesing power in the
clients than in the server. This is extremely so in the case of
successful web apps, even with Jscript. You can't change this by
promoting simpler clients as processing power is very cheap and users
like having it. The only advantage of using a server at all
(admittedly this is an OVERWHELMING advantage) is the collection of
data, updating etc in a single place for integrity and sharing.

For non-web client-server there is the problem of deployment and
updating of the client-side software. However this can be done using
the Internet in various ways.
David Schofield

Just attaching to SQL Server tables doesn't give you these benefits.

I certainly agree with this! David

[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
exponentially 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.co.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.com:

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