Upgrading backend from Access 97 to sql server

[John]

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

 

[Rick Brandt]

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?

Performance (in almost all cases) is a design problem (lack of proper
indexing) or hardware problem (LAN or disk i/o).

Moving to a server based system has advantages. Speed is seldom one of
them. You could easily see some operations get even slower.

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?

No. Just move the tables to a server and link to them from your Access 97
app. This will not be optimal at first, but it's a starting place from
where you can further refine the design to take advantrage of a server back
end.

Moving from Access 97 to a newer version is neither required for this nor
recommended unless you need something specific for your app that a newer
version provides. A97 with a SQL Server back end works just fine.

Any pointers to help upgrade access to sql server would be
appreciated.

Just a nit-pick here. You don't convert your application to SQL Server, you
only convert the database (tables and such) portion of it. As such you are
not really converting "from Access to SQL Server" so much as "Jet to SQL
Server". You would still be using Access for the front end.

 

[Erland Sommarskog]

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?

It is not unlikely if the migration is performed properly. Access and
SQL Server are two very different products, and you cannot really upgrade
in the sense that you just install something and you are on the air.
Yes, there is a migration tool, but all I've heard from people with
experience of products (I'm an SQL Server person), is that you are better
off rewriting the application manually. Essentially, to achieve something
that really is an improvement, you will have to rewrite the application
substantially.



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[a a r o n . k e m p f]

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

 

[a a r o n . k e m p f]

bullshit

Speed and reliability are reasons #1 and #2 to move to SQL Server.

Maybe if you weren't a cry baby Access kid; you'd learn some real SQL
Skills

It might not be reccomended by _YOU_.
Of course you're an Access cry baby; I mean jesus.

Why don't you go in the SQL group and ask them if you should upgrade?

Seriously

The dipshits around here are not good people to ask-- they don't know
how to spell SQL.

-Aaron

 

[a a r o n . k e m p f]

ADP, yes, yes, yes!

 

[Eric Isaacs]

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/

 

[Erland Sommarskog]

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.

I only like to chime in and say that I completely agree with Kevin. I
don't want to denigrate John, but judging from his initial post, his
experience on SQL Server is thin. Bringing in someone with knowledge about
SQL Server is very likely to pay off manyfold.

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.

In this case I'd say that it depends a little on the size of the
application, but it's true that to keep an SQL Server installation going
you need to have some basic understanding on backups and that, or
else you can have a disaster further a field. If this is a small
application, one alternative could be to run it at a hosting service.



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Guest]

Is the problem the 20 users? Or perhaps the data has increased
in size? Or just the effect of TCP/IP signed packets after a server
upgrade?

But I really just wanted to warn you that Access (dao) transactions
against SQL Server are broken in later versions of Access. So if
you are using dao transactions in your code, you need to think
carefully about your upgrade plans. :~(. You can't 'upgrade'
DAO transactions to Access 2K and SQL Server.

(david)

 

[Rick Brandt]

You are welcome to disagree. But please reread my post carefully. I
never said it wasn't EASY to migrate. I said primarily that it will
take some work (probably significant) to get an optimally performing
application. Most Access applications I have encountered (and in fact
built in a previous life) were primarily procedure-based (i.e.
row-by-row) processing, not set-based processing. That is where real
performance comes in. Schema's created by many Access application
designers are often suboptimal as well - again in my experience.

I don't think it is very productive to talk about the efficacy of migrating
sub-optimal Access/Jet designs to Access/SQL Server. Sub-optimal is
sub-optimal and the back end used makes little difference. The row-by-row
processing you describe is mostly produced by "programmers" with little
database work in their background. I do not see that much in Access users
(even novices) that do not come from a programming environment.

The truth of the matter is that when looking at a *proper* Access/Jet
application that has evolved to a point where a server engine's advantages
reach a tipping point to make such a move a requirement, it really is not a
difficult nor expensive transition to make.

Aaron has a unique talent in making a sound argument sound like utter
bullshit, but if you look past the messenger (or consult with other
messengers), and examine just the premise, SQL Server's free or
close-to-free variants really are viable options for just about anyone that
has those requirements.

Moving to an ADP is a separate question altogether and would likely require
a more significant amount of work to transition to. I cannot speak with
much experience about ADPs as I have always had requirements that make a
*SQL Server only* solution completely out of the question.

In situations where SQL Server exclusivity is not a problem then perhaps
ADPs are perfectly suited to the task. I would however need to see them far
surpass the abilities of MDB/ODBC simply because that exclusivity might not
last forever and then I am stuck with an application that cannot deal with a
requirement to change engines. Taking that risk means that ADPs cannot
simply be "as good as" MDB/ODBC, but must be so much better as to make that
risk tenable. I have not seen much evidence to suggest that.

 

[So Sorry For Poor Aaron]

Aaron head done been depecrated. Aint stuffed wid no brain no mo. All 'em
Big brothers do miss he, dey say, "U com back soon, baby."

 

[Mary Chipman [MSFT]]

You might find an online session we did at TechEd useful in weighing
your options:

Go to http://msdn.microsoft.com/en-us/events/teched/cc676818.aspx and
select Database Platform from the drop-down. Scroll to:
"Are we there yet? Successfully navigating the bumpy road from Access
to SQL Server".

Four industry experts (Luke Chung, Armen Stein, Stephen Forte and Paul
Sheriff) discuss their experience in migrating their customers'
applications to SQL Server. Cumulatively they probably have in excess
of 50 yesrs experience between them, so they're worth listening to ;-)

--Mary

 

[Raymond]

Hi,

I have the experience of upsizing Access 2000 to sql server. Below are my
comments

a) Would this help in performance significantly i.e. is it worth the time?
No, unless you rewrite your Access VBA in SQL stored procedure. But it is
your first step to improve your performance, you can fine tune much easier
with SQL server. You will not have data corruption like in MS Access. You
will find SQL server is much secure, stable than MS Access.

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?

You can choose to go for the SQL server directly. You do not need to waste
your time to upgrade your MS Access version even Access 97 is a relatively
old version.

Wish I can help,
Raymond

 

[a a r o n . k e m p f]

suboptimal access applications.. need to be moved to SQL Server-- so
that you can run a wizard and build some indexes.

and optimize queries.
and optimize schemas.

working on a database-- where it's impossible to change anything--
while anyone else is using it-- and then bitching about sub-optimal
schemas-- is like shooting yourself in the foot.

USE A DATABASE THAT SUPPORTS MULTIPLE USERS.

Only a retard or a WOMAN-- would be stupid enough to use MS Access for
a database.

-Aaron

 

[a a r o n . k e m p f]

bullshit

you can upgrade DAO to ADO any day of the week

 

[a a r o n . k e m p f]

Kevin;

Maybe if you had the balls to be a decent SQL Server developer / DBA_-
then maybe you could have helped him out.

Anyone using Access MDB / ACCDB against SQL Server -- is too stupid to
be employed

ADP is vastly superior-- in every regard.

#1 - name the steps to build a form-- based on a sproc-- in MDB
#2 - it takes _ZERO_ config for ADP.

Maybe if you had a backbone-- and could carry 3,000 applications--
then maybe they'd be more successful.
But your premise is that 'Access is neat' is done and over.

Anyone using Access as a database should be fired and then spit upon.

-Aaron

 

[a a r o n . k e m p f]

of course, they sell us on the wrong message.

Having a vendor-- change directions-- every 10 years-- is not friggin
acceptable.

stick your sharepoint shit and shove it

fix your existing bugs before introducing new ones.

 

[marinorona]

Aaron head done been depecrated. Aint stuffed wid no brain no mo. All 'em
Big brothers do miss he, dey say, "U com back soon, baby."

 

[Guest]

hola como estas

"So Sorry For Poor Aaron" <[email protected]>
wrote in message