Access and SQL Server

[amn]

I have an application developed in Access 97 Front-End and Back-End
also.

I own licenses of Access 2003 retail version and Access 2003 Developer
Edition.

I would like to explore the SQL Server world and I would appreciate
your advices about following:

- Which Access versions (including Runtime versions) are suitable with
which SQL Server editions, including SQL Server 2008 Express.

My plans are to install SQL Server 2008 Express (because is free) in
order to get familiar with SQL Server technology and convert my Access
97 application Front-End to Access 2003 Runtime using linked tables to
SQL Server.

In my customer site I would install the SQL Server 2008 (not the
Express).

Thanks in advance.

 

[Klatuu]

First, convert your 97 app to 2002/2003 format, both the front end and back
end.

Now, the next, and most serious question is what is driving the decision to
upsize to SQL Server?

More times than not, it really isn't necessary. A properly contstructed and
implemented Jet configuration will provide equivilant performance. There are
really only two reasons to upsize:

1. The data is very sensitive and ULS security is not sufficient.
2. The volume of data will grow beyond the 2GB limit for a mdb file.

For the first reason, there is not much argument. Access ULS is not too
hard to crack. For the second, there are some considerations before moving
to SS. Since an Access front end application can link to multiple back end
databases, it may be that some tables can be kept in one mdb and some in
another. Or, as in a case we have here, if the data can be logically split
into groups where the groups would not be combined for any reporting
purposes, you can use that approach. What we do here is have our data split
out by client and region. The reporting never goes beyond that level. When
a user wants to select a different client/region, the app programmatically
relinks to a different back end.

Now, with that out of the way, The upsizing wizard will do most of the
heaving lifting for your. There may be some things you have to clean up. I
have an aversion to using an adp format, so I keep all mine in mdb. adp
applications have some issues and Microsoft has made no improvements on them
since version 2000. It is interesting to note that even after all the hype
about how wonderful ADO was, they have reverted to DAO as the default format
for 2003. You will also find there are some limitations with adp. For
example, you can't do a normal link to another mdb.

I would also download the 2008 management studio as well. You will want to
use it to work with your tables in SS. You can't modify tables from Access.
I think you can from an adp, but since I don't use them, I don't remember for
sure.

You will also want to learn to write stored procedures and views. In most
cases, a view in SS is faster than using an Access query. And sometimes, but
not always, a pass through query seems to be faster than a view.
When you link to an SS view, Access sees it as a table. The same is true
for stored procedure that returns records. The difference is, you can't pass
paramenters to a view, but you can to a stored procedure. But, to make life
a bit more simple, I do only filtering in my views that would be applicable
in all situations. Then for form and report recordsets, I will use an Access
query built on the tables and views so I can use the filtering I am familiar
with.

 

[amn]

First, convert your 97 app to 2002/2003 format, both the front end and back
end.

Now, the next, and most serious question is what is driving the decision to
upsize to SQL Server?

More times than not, it really isn't necessary.  A properly contstructed and
implemented Jet configuration will provide equivilant performance.  There are
really only two reasons to upsize:

1. The data is very sensitive and ULS security is not sufficient.
2. The volume of data will grow beyond the 2GB limit for a mdb file.

For the first reason, there is not much argument.  Access ULS is not too
hard to crack.  For the second, there are some considerations before moving
to SS.  Since an Access front end application can link to multiple backend
databases, it may be that some tables can be kept in one mdb and some in
another.  Or, as in a case we have here, if the data can be logically split
into groups where the groups would not be combined for any reporting
purposes, you can use that approach.  What we do here is have our data split
out by client and region.  The reporting never goes beyond that level.  When
a user wants to select a different client/region, the app programmatically
relinks to a different back end.

Now, with that out of the way, The upsizing wizard will do most of the
heaving lifting for your.  There may be some things you have to clean up.  I
have an aversion to using an adp format, so I keep all mine in mdb.  adp
applications have some issues and Microsoft has made no improvements on them
since version 2000.  It is interesting to note that even after all the hype
about how wonderful ADO was, they have reverted to DAO as the default format
for 2003.  You will also find there are some limitations with adp.   For
example, you can't do a normal link to another mdb.

I would also download the 2008 management studio as well.  You will want to
use it to work with your tables in SS.  You can't modify tables from Access.  
I think you can from an adp, but since I don't use them, I don't rememberfor
sure.

You will also want to learn to write stored procedures and views.  In most
cases, a view in SS is faster than using an Access query.  And sometimes, but
not always, a pass through query seems to be faster than a view.
When you link to an SS view, Access sees it as a table.  The same is true
for stored procedure that returns records.  The difference is, you can't pass
paramenters to a view, but you can to a stored procedure.  But, to makelife
a bit more simple, I do only filtering in my views that would be applicable
in all situations.  Then for form and report recordsets, I will use an Access
query built on the tables and views so I can use the filtering I am familiar
with.

Dave,

Thank you so much for your prompt response.

My main decision to move to SQL Server is just number of users
connected.

There is no issue with sensitive data nor volume of data.

I currently have 28 users working against the app although not all of
them permanently. When it is said "concurrent" users I am not sure the
real meaning of term. Does "concurrent" mean working at the same time
all together?

As summary my main concern is number of users.

Angel.

 

[Klatuu]

I would disagree with this statement"
But the back-up and availability tools are better with SQL Server:
what do you do if you loose a couple of hours work?

Backup and restore procedures are external to database engines, so neither
has a true advantage. With SQL Server, you do have an internal mechanism
that allows periodic backups internally, but if you loose the entire server,
it is still lost.

The easiest definition of concurrent users is users actually working the
database at the same time. Having the front end application open does not
necessarily constitute concurrency. Performing some work that is actually
accessing data in the back end database does. You can have the front end
open and in an idle state and that will not be using any resource from the
back end.

One of the techniques used to enhance performance is to allways keep a table
in the back end open in the front end application. That maintains the
connection so it doesn't have to be reestablished.

28 users may or may not be an issue, depending on the nature of the
application. If it is very heavy and constant data entry, then it may be an
issue. But, even more important is the quality of the database design and
implementation. Is the database properly normalized? Are relationships
established? Is the indexing done properly so that all searches and lookups
are using indexed fields? If not, even moving to SQL Server will not improve
performance.

I would recommend a set of books to you. Access 2002 Desktop Developer's
Handbook and Access 2002 Enterprise Developer's Handbook by Litwin, Getz, and
Gunderloy, published by Sybex. The Enterprise volume goes into great detail
on using SQL Server. Chapter 15 of the Desktop volume has exceptional
information on application optimization.

 

[amn]

I would disagree with this statement"
But the back-up and availability tools are better with SQL Server:
what do you do if you loose a couple of hours work?

Backup and restore procedures are external to database engines, so neither
has a true advantage.  With SQL Server, you do have an internal mechanism
that allows periodic backups internally, but if you loose the entire server,
it is still lost.

The easiest definition of concurrent users is users actually working the
database at the same time.  Having the front end application open does not
necessarily constitute concurrency.  Performing some work that is actually
accessing data in the back end database does.  You can have the front end
open and in an idle state and that will not be using any resource from the
back end.

One of the techniques used to enhance performance is to allways keep a table
in the back end open in the front end application.  That maintains the
connection so it doesn't have to be reestablished.

28 users may or may not be an issue, depending on the nature of the
application.  If it is very heavy and constant data entry, then it may be an
issue.  But, even more important is the quality of the database design and
implementation.  Is the database properly normalized?  Are relationships
established?  Is the indexing done properly so that all searches and lookups
are using indexed fields?  If not, even moving to SQL Server will not improve
performance.

I would recommend a set of books to you.  Access 2002 Desktop Developer's
Handbook and Access 2002 Enterprise Developer's Handbook by Litwin, Getz,and
Gunderloy, published by Sybex.  The Enterprise volume goes into great detail
on using SQL Server.  Chapter 15 of the Desktop volume has exceptional
information on application optimization.

Dave,

Thanks again for your comments.

I already own the books you mention and some others. The main issue is
that I was comfortably working with Access 97 Front-End and Jet Back-
end; I had everything under control; it was very easy to do back-end
maintenance, to change table structure, to move back-end to my desktop
PC for testing, etc.

But I think that excessive users can put the app in risk and I need to
start thinking in alternatives before that can arise.

My application and database is well-designed, normalized, relations,
indexes, etc. and performance is very acceptable. It is not heavy and
constant data entry but user can have it opened almost the full day. I
already have a procedure to kick-off users after 2 hours of
inactivity.

So in general, I am satisfied and do users also but I like to
anticipate to future problems.

In summary, I will start downloading the SQL Server Express 2008 and
doing tests in calm and I promise to begin to read the various books I
already have.

As conclusion, thanks so much to everybody who helped me with their
comments, all of them have been very valuable.

Angel.