ASP website: Access to SQL Server?

[urban]

Hello,

I'm hoping for some general advice, because I'm feeling a bit out of
my depth.

I have an ASP website with ms access as the database. The db is
realtively simple, and I think it's hit fairly lightly: all pages
displaying info from the db use the getrows method to return arrays,
and the connection and recordset objects are promptly closed.

I've previously used similar techniques in an app that supported a
base of about 250 users (although seldom more than 10 simultaneous
connections).

Now, my current ASP website is about to become the pulic site for our
department. We don't anticipate heavy usage, but even so I'm worried
about whether access is up to the job.

I have no experience of SQL server. Yesterday I installed it (2000) on
an older pentium 2 machine, accepting defaults and hoping for the
best. I ran the import wizard and imported from the .mdb file. After a
day of hair-tearing I managed to get an OLEDB connection string that
worked. All the ASP pages intended for the public site work off SQL
server 2000.

My problem is, I feel a bit uncomfortable about having tried to setup
and configure the sql server db in such a mad rush. I can make sure
the system's patched to the hilt, but am totally ignorant about what
admin/security horrors I may have committed in configuring it.

So I'm not sure what to do. Should I -

Stick with Access behind the website (hoping usage is light) while I
try and sensibly plan a later upsize to SQL server?

Or, given that I can run the the site off SQL server, should I switch
over straight away (assuming the site will be much more robust), and
worry about the details later?

Any advice would be much appreciated

[urban]

Thanks for the reply

"Bob Barrows" <reb_01501@yahoo.com> wrote in message news:<#xiVtTDWDHA.2008@TK2MSFTNGP11.phx.gbl>...

> If all you are doing is displaying data from the database using GetRows,
> then Access should certainly be up to the task. it's when you get into data
> maintenace that Access can bog down. Make sure you don't use cursors
> (recordsets) for data maintenance: queries, especially saved parameter
> queries, should be used. And make sure you keep this mantra in mind: open
> late...close early. In other words, do not open your database connection
> until you are ready to use it, and close it as soon as you are finished with
> it.

I think this is what I'm doing. I don't do anything at all with the
recordset except dump it into an array (GetRows) and then close it.
The page display is built entirely by manipulation of the 2-D array
returned by the GetRows method.

Will this maximize the number of connections Access can handle? I
mean, I know that access has a limit of 255 connections, but I don't
really know what this means in practice. Does it mean that if 254 user
simultaneously hit a page making a db call it would be OK? How about
if 1000 users hit the same page in sequence with one or a few seconds
between them?

I guess I'm just not sure what sort of site traffic it's reasonable to
expect access to support. My feeling is that most of the time it would
be OK, but but it might get pushed close to or over its limit during a
few busy periods.

[Bob Barrows]

urban wrote:

> Thanks for the reply
>
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:<#xiVtTDWDHA.2008@TK2MSFTNGP11.phx.gbl>...
>

>> If all you are doing is displaying data from the database using
>> GetRows,
>> then Access should certainly be up to the task. it's when you get
>> into data
>> maintenace that Access can bog down. Make sure you don't use cursors
>> (recordsets) for data maintenance: queries, especially saved
>> parameter
>> queries, should be used. And make sure you keep this mantra in mind:
>> open
>> late...close early. In other words, do not open your database
>> connection
>> until you are ready to use it, and close it as soon as you are
>> finished with
>> it.

>
> I think this is what I'm doing. I don't do anything at all with the
> recordset except dump it into an array (GetRows) and then close it.

Good

> The page display is built entirely by manipulation of the 2-D array
> returned by the GetRows method.
>
> Will this maximize the number of connections Access can handle?

Not really - that number is fixed. What it will do is minimize the number of
connections being used since connections can be re-used if not in use. (this
is referred to as OLEDB Session Sharing if using OLEDB, which you should, or
ODBC Connection Sharing if ODBC)

> I
> mean, I know that access has a limit of 255 connections, but I don't
> really know what this means in practice.

This means that the maximum users that can be created in a secured database
is 255.

> Does it mean that if 254 user
> simultaneously hit a page making a db call it would be OK? How about
> if 1000 users hit the same page in sequence with one or a few seconds
> between them?

If you keep the connections short, this could amount to 1 connection being
re-used 1000 times: no problem at all. However, if you aren't religious
about minimizing the time that the connection is open on the page, the 60th
user could start seeing some delays ...

>
> I guess I'm just not sure what sort of site traffic it's reasonable to
> expect access to support. My feeling is that most of the time it would
> be OK, but but it might get pushed close to or over its limit during a
> few busy periods.

You might want to search the MS website for and download and try WAST, the
Web Application Stress Testing tool.

HTH,
Bob Barrows

[urban]

> > "Bob Barrows" <reb_01501@yahoo.com> wrote in message

> > news:<#xiVtTDWDHA.2008@TK2MSFTNGP11.phx.gbl>...
> >

>

> > Does it mean that if 254 user
> > simultaneously hit a page making a db call it would be OK? How about
> > if 1000 users hit the same page in sequence with one or a few seconds
> > between them?

>
> If you keep the connections short, this could amount to 1 connection being
> re-used 1000 times: no problem at all. However, if you aren't religious
> about minimizing the time that the connection is open on the page, the 60th
> user could start seeing some delays ...
>

OK, so this makes me feel more confident about going live and sticking
with Access for now.

>You might want to search the MS website for and download and try
>WAST, the Web Application Stress Testing tool.

Found it, I'll give it a try tomorrow.

Thanks again