ASP.NET With MS Access

[baramee]

I develop web application by asp.net with ms access.
In general , it work fine.
But if hit rate of web is very much, it occure error 'Unspecified error'.
it error on conn.open.
My code will be closed and destroyed connection object always.
I think that problem come from database concurrency.
I can't change database to sql server because it about budget.

How can i fix this error?
Thanks in advance.

 

[Andrew de la Harpe]

upgrade to MSDE

 

[Peter O'Reilly]

Cache your web pages.
In your code, open connections as late as possible and explicitly close them
as soon as possible.

MS Access (Jet) can handle up to 10 concurrent write operations
(insert/update/deletes) and many more read operations (SELECTs). MSDE has
the same limitations while consuming more memory resources. The limitations
of these two data sources is the file system. Between the two choices, I'd
stick with Jet.

If you really need a 24x7 database - upgrade to SQL Server.

 

[George Ter-Saakov]

A correction.
MSDE is exactly the same server as MS SQL server excluding some rarely used
features (replication, ...).

1. Does not have connection limit. (Reasonable)
2. It does have artificial limit = 5 of the amount of queries executed at
the same time. But the next (after 5) query is not rejected but is put on
hold. And considering that queries are running within milliseconds it's not
an issue with not heavy trafficked website.


If you can choose MSDE over Access since later you will upgrade to MS SQL
without changing anything.

George.

 

[Peter O'Reilly]

A correction.
MSDE is exactly the same server as MS SQL server excluding some rarely used
features (replication, ...).

Sorry, not so. It is a tool "built and based on core SQL Server
technology". It's purpose is to provide "a seamless upgrade path from MSDE
2000 to SQL Server if an application grows beyond the storage and
scalability limits of MSDE 2000."
(http://www.microsoft.com/sql/msde/productinfo/overview.asp) Try installing
SQL Server on Windows 98 desktop - you can't, but you can for MSDE. There's
a good reason for this.

1. Does not have connection limit. (Reasonable)

It does have a connection limit of 25 connections.

2. It does have artificial limit = 5 of the amount of queries executed at
the same time. But the next (after 5) query is not rejected but is put on
hold. And considering that queries are running within milliseconds it's not
an issue with not heavy trafficked website.

Other limitations of MSDE versus SQL Server:
a.. No Enterprise Manager

b.. No Query Analyzer

c.. No Index Tuning Wizard

d.. Only 2GB RAM

e.. Only 2GB database size limit

f.. Only 2 CPUs

g.. Only five concurrent batch workloads or 25 concurrent connections for
websites

h.. No Database Server Failover Support

i.. No Full-text search

j.. No SQL Server Profiler

k.. No Import and Export Wizards

l.. No OLAP

m.. No English Query

n.. No SQL Books Online

o.. No Full or Bulk-Logged recovery model support (only simple)

(http://www.teratrax.com/articles/msde_vs_sql_server.html)

The bottom line is MSDE is a file based database like MS Access (Jet) and
its limitations are due to the Windows file system which is not optimized in
this regard. (That's why there's a true OLAP database like SQL Server).
The plus to MSDE is that your application may migrate a _little_ bit easier
to SQL Server. The downside is Jet is much less of a resource hog, (e.g.
memory for sure and disk space depending on your setup) than MSDE. Most
importantly, Load testing I have found Jet to be able to handle 10
concurrent write operations and only 5 for MSDE.

From my personal experience using both, I'd stick with your existing
Access/Jet application. MSDE is not an "upgrade" option.

 

[George Ter-Saakov]

1. Here is the link
http://msdn.microsoft.com/library/?url=/library/en-us/architec/8_ar_sa2_0ciq.asp?frame=true

Read the paragraph
"Like all versions of SQL Server 2000, SQL Server 2000 Personal Edition and
MSDE 2000 allow 32,767 connections to an instance of the database engine.
There is no limit for the number of connections that can be executing
operations at the same time."

There is no limit on connections. 25 concurrent users you probably met in
documentation is a reasonable number Microsoft suggest you aim for. They
just simply telling you that if you have more than 25 concurrent users,
simultaneously running quires on your website, it's time to upgrade to real
SQL since users will get a slow response. Since 20 of them will be on hold.

2. There are no tools like "Enterprise Manager". They just do not come with
it inside of that download you downloaded from Microsoft site. But if you
have it installed already because you have real MS SQL somewhere else then
they are going to work perfectly.
The same goes to Book online. Just use it online. I would not call it a
limitation of MSDE.

3. What exactly do you mean by "MSDE is a file based database like MS
Access". I am affraid you are confusing terms here.
Let me give you an example of "file based database like MS Access"

Let say you have MS Access database on one machine. And 10 others are trying
to write to it.
Every machine will have to open the remote file, read necessary info, scan
indexes using File IO,....
And everything done through the network. Just to get one record Access
should issue hundreds disk IOs to scan the index. Over the network it's a
killing.
So "file based database" is the database that using Disk IO commands. And as
a consequence it's very inefficient. There is no coordination in those IOs.

As opposite MSDE works exactly the same as MS SQL. It receives SQL commands
over the network then does local disk IOs and returns you only that one
record.
This is called SQL database. (Not MS SQL, Oracle is SQL database too.)
It's much more efficient since there is a coordination between disk IOs.
Caching is possible.


So the bottom line is MSDE is a perfect database for not heavy trafficking
web sites. Yes, it does eat more resources than MS Access but it's a good
price to pay for benefits.


George.

 

[Peter O'Reilly]

I think we are talking about the same thing from two different viewpoints.

You are correct about the I/O hit with Access, but I still contend the same
is true for MSDE as well. The ability for it to run on Windows 98 and the
2GB file size limit sure make it smell like disk i/o intensive db like
Access. Perhaps I'm wrong about this - but I haven't seen any docs that
dispel such and my opinion is biased a bit from experience using the two
products.

I viewed your link (thanks), and then clicked through to I/O Architecture
link where it describes SQL Server taking advantage of memory cache and disk
performance features of Windows NT/2000. Noticeably absent was the
mentioning of MSDE with respect to the I/O Architecture.

I do not see the benefit of converting an existing Jet Database, used by an
existing application to MSDE to improve performance or scale as I have not
found a significant performance gain using MSDE over Jet. (Yes, Jet is old
hat and MS is pushing MSDE, perhaps in response to MySQL or lure folks onto
SQL Srvr or both). If the original poster is starting from scratch, then it
makes sense to use MSDE as it provides an easier upgrade path to SQL Srvr.
Otherwise he's unnecessarily spinning his wheels and spending more of his
time with a false economy.