Upgrading backend from Access 97 to sql server

John (info@nospam.infovis.co.uk) writes:
> 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, esquel.RemoveThis@sommarskog.se

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
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

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

> 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
>
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

ADP, yes, yes, yes!



On Jun 28, 4:58 am, "John" wrote:
> 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
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

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



On Jun 28, 5:09 am, "Rick Brandt" wrote:
> John wrote:
> > 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.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com
 >> Stay informed about: 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/
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

TheSQLGuru (kgboles@earthlink.net) writes:
> 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, esquel.RemoveThis@sommarskog.se

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
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

TheSQLGuru wrote:
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

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)




"John" wrote in message

> 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
>
>
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

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."

"a a r o n . k e m p f @ g m a i l . c o" wrote:

> ADP, yes, yes, yes!
>
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

Since MS Access is a file server based database, so any data pages accessed
by your queries or updated are being pulled across the wire for processing on
the client side, plus the Jet database engine probably does not provide the
same granularity of locking that you would get with SQL Server.
A MS Access related forum could probably better answer this question; there
are a lot of people out there using ADP projects on the front end with SQL
Server on the back end. I've done a couple of small projects using ADP and
SQL Server Express, and I can say that I was pleasantly surprised by how easy
it was to link SQL Server tables and build slick usable forms in Access 2007.

"John" wrote:

> 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
>
>
>
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

You mentioned that you are currently using Access 97. This uses page level
locking, but I think that row level locking is available in Access 2000+.

"John" wrote:

> 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
>
>
>
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

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

On Sat, 28 Jun 2008 12:58:28 +0100, "John"
wrote:

>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
>
 >> Stay informed about: Upgrading backend from Access 97 to sql server 

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



"John" wrote in message

> 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
>
 >> Stay informed about: Upgrading backend from Access 97 to sql server