|
|
|
date: Sat, 28 Jun 2008 17:50:01 -0700,
group: microsoft.public.access.adp.sqlserver
back
Re: Upgrading backend from Access 97 to sql server
I disagree.
SQL Server is _EASY_.
Upsize to ADP, get rid of DAO crap (it's been obsolete for a decade
now) and presto-chango-- you've got enterprise level systems.
Sure, you might need to create some indexes-- and you will need to
adjust datatypes.
but a well designed Access app should migrate to a well designed SQL
Server app quite easily.
-Aaron
On Jun 28, 6:57 am, "TheSQLGuru" wrote:
> I have a client that about 3 years ago 'upgraded' about 3000 access
> databases to sql server. Performance was a complete DOG. Partly because
> they didn't know anything about how to optimize/configure the server itself.
> But a large part of the problem was the massive amount of looping,
> row-by-row ADO code that did the processing. To get anything like 'good'
> performance you will need to upgrade your code to include more set-based
> logic.
>
> Having said that, I do think that an appropriately configured sql server
> will be a faster platform for your data even with your existing code. From
> there you can do incremental, targeted code improvements where you find the
> choke points.
>
> Oh, the client I mentioned didn't have or hire a DBA and they paid a serious
> price for it. They lost clients because of their performance issues. I
> strongly urge you to at least hire someone interim to help get you up and
> going on SQL. Long term you really do need someone knowledgeable to take
> care of your server and databases. Note that there are now several
> outsourcing companies that can do this management for you.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "John" wrote in message
>
> news:OtaUFZR2IHA.5564@TK2MSFTNGP06.phx.gbl...
>
> > Hi
>
> > I have a typical access app with both front and back ends in access 97.
> > The database is running slow due to number of users approaching 20. I
> > would ideally like to upgrade the backend to sql server if that would
> > improve performance. My questions are;
>
> > a) Would this help in performance significantly i.e. is it worth the time?
>
> > b) What route should I choose? Should I upgrade the app to a later version
> > of access first and/or switch to adp instead of mdb?
>
> > Any pointers to help upgrade access to sql server would be appreciated.
>
> > Thanks
>
> > Regards
date: Sat, 28 Jun 2008 07:40:06 -0700 (PDT)
author: a a r o n . k e m p f @ g m a i l . c o m
Re: Upgrading backend from Access 97 to sql server
Moving to SQL will not fix all your performance issues. In order to
move effectively, you'll need to rewrite a lot of the Access front-end
code to take advantage of the backend processing that SQL Server can
provide. If you just migrate all your tables to SQL Server, your
performance will likely not improve. You'll want to take advantage of
SQL Pass-Through queries and linked views, and stored procedures which
enable processing to be done on SQL Server and only the result to be
passed back to Access. Access to Access solutions processes the data
client-side, so the network can get hammered with data moving across
the network. By taking advantage of the server-side processing the
data moves much faster across the network, but this requires
significant code changes. You'll also want to make sure that SQL
Server is setup with the proper indexes as well, otherwise the server-
side processing could take a long time (on only one box, rather than
distributed across 20 boxes.) Proper indexing is important on either
platform.
With Access 97, you can't use an ADP file. ADPs were supported in
Access 2000 and above, so you would need a more current version of
Access to support an ADP solution.
Before you upgrade, try some cheap fixes:
1) Backup, then Repair and Compact the front-end and backend Access
MDB files. Access files grow fast and don't shrink themselves. An
occasional Repair and Compact can help with performance.
2) Review the index usage in the Access application to make sure that
it's has the right indexes on the right fields, and no duplicated
indexes. Indexes can really improve performance, but there is a point
of diminishing returns. Access can sometimes auto-create indexes that
are not helpful. (It defaults to creating indexes on all fields named
with a suffix of ID, but when you add that field as a primary key, it
creates another index.)
3) Since this is an Access 97 solution, it's probably been in use for
quite awhile. Possibly consider archiving portions of the data into
another MDB file and link to that database only when it's required?
-Eric Isaacs
J Street Technology, Inc,
http://www.jstreettech.com/
date: Sat, 28 Jun 2008 14:28:42 -0700 (PDT)
author: Eric Isaacs
|
|
