Is SQL Server a good candidate for this scenario? -- sql server (microsoft)

tbone
12/13/2005 8:25:24 PM

A customer has asked me to improve the performance of a system I did
for them many years ago. I would greatly appreciate informed opinions
on whether SQL Server would be a good candidate for this.

The system was originally based on Microsoft Access 2 (later upgraded
to 97) and was used only in-house. More recently, I was asked to add
web access for users. For several reasons, I kept the in-house system
to serve as the database server, and wrote an ASP application that
runs on a shared web server at the ISP's location. Communication
between the two servers is done via RDF over a 2Mbit cable link; the
web server has no database locally. The in-house and web systems are
used simultaneously.

99% of the time, this system works admirably. However, during periods
of heavy usage (both internally and on the web), performance suffers
dramatically. In-house users sometimes see a spike in what I believe
could be record locking/contention, whereas web users simply see pages
time out or take an inordinately long time to come back. This is what
the customer would like to see resolved.

I suspect that the performance problem is due to record locking or the
communication between the servers (or both).

I'm thinking that I might approach this by:

1. just using SQL Server on the database server to see if the problem
is all or mostly in the record locking; this would be the least amount
of effort on my part and therefore probably much less cost for the
customer (depends on the licensing cost; the in-house machine is
Windows 2000 Server)
2. adding a copy of the database on the web server and have SQL Server
keep the web copy in sync with the internal copy; this would have to
be done automatically and constantly so that web info is no more than
a second or two behind the main database; all updates would be made to
the main database only; my work would be limited to changing the ASP
application to look to the local database for read operations, whereas
updates would still be done through RDF

My questions then would be:

1. Does it seem like record contention could be the issue? If so,
would the use of SQL Server make enough of a difference? Is SQL Server
that much better at managing record access and locking than Access?
2. Is it fairly straightforward to substitute SQL Server for the
Access back end?
3. If the problem is the communication, does it make sense to have the
in-house database mirrored on the web server?
4. If so, can SQL server do what I need? Do I need SQL Server running
on the web server too (i.e. I need to license two copies of SQL
server)? Is this hard to set up?

Many thanks to anyone who takes the time to read and respond.
References to relevant documentation are also appreciated.

bradsbulkmail NO[at]SPAM comcast.net
12/13/2005 10:21:08 PM

Hi TBone,

SQL Server is a great product, but I wonder whether it's really what
you want to go with here. Given that your application works most of
the time running under Access 97, I'd guess that you don't have more
than 10 or so users accessing the application at the same time. If
that's the case, you might look at upgrading to Access 2003 and
migrating the database to use SQL Server 2005 Express. 2005 Express is
a fully functional version of SQL Server that's free of charge.

[quoted text, click to view]

John A. Bailo
12/14/2005 10:04:34 AM

99 percent of the time I've found that I can solve these types of
problems w/Access by tweaking the ODBC settings.

Take a look at what you can change in the ODBC administrator, try
increasing the threads and the cache sizes or maybe lower them even.

[quoted text, click to view]

tbone
12/14/2005 4:03:53 PM

Thanks for the pointer...this may save me a LOT of time.

I bumped Threads from the default of 3 to 20 and the MaxBufferSize
from 2K to 8K. I'm not sure exactly what the other parameters will do.
I searched microsoft.com and didn't get any relevant hits (and I
suspect terms like PageTimeout are too general). Can you suggest a
good reference on the topic?

I turned on performance monitoring for ODBC. I checked the available
"Performance Objects" in PerfMon and saw that one ODBC entry was
added. I added all of the performance items but none of them appear to
be updated. Do I need to reboot?

Thanks for your help!
tbone

On Wed, 14 Dec 2005 10:04:34 -0800, "John A. Bailo"
[quoted text, click to view]

John A. Bailo
12/15/2005 9:53:43 AM

[quoted text, click to view]

It's not very well documented; I am just the sort who likes to tweak the
detail level settings and I've found that these sometimes work for me
when using Access.

[quoted text, click to view]

It's always a good idea if you can afford to just to insure that memory
is cleanedup, released and so on when making these kinds of settings.

Also, make sure you're using the latest available ODBC drivers from
Microsoft.

Also, if you do decide to convert, and don't want to change the
database, look at .NET/ASP.NET and the Sql classes as an alternative.

Ian
12/16/2005 3:46:10 AM

[quoted text, click to view]

It looks like you're facing the classic problem that three tier
architectures was designed to solve. Object relational tools like
nhibernate solve this. (Though of course predicably, they go too far,
and you get technical architects obsessed with trying to get all
business logic into the middle tier, when you might just need an
aggregate or something. Just like every other solution in the computer
industry, such as XML. No matter how good it is, (and indeed sometimes
because it's so good,) people will start trying to use it for things it
isn't good for.) (Java architects seem to be the most middletier
focussed. I know one guy who wrote an hibernate based action to pull
all of the objects in a table just so he could sum all them up on the
webserver -- how mental.)

Try reworking the ASP, to pull the data out more simply, workout what's
changed in a page, and do simple updates. The page loads slightly
slower, but it means you can add more webservers.

Failing that, I favour an architecture which uses SQL, and then
transaction replicates all the readable data, to an array of SQL
Servers hiding behind a load balancer. (This is enhanced even more now
you can replicate little changing config data to SQL Express on the
webservers) When a page elaborates, I have three connection strings,
one r/w, one r/o, and on r/olocal. This means that I can run without
caching horrendously scaleable web farms, taking advantage of SQL's
power for the content, but without any locking issues. This readonly
bit operates very client server like, and is very agile.

Also it makes the boxes into =A32K commodities. You no longer need to
spend millions on 8 way sun installations.

For the order processing bit, it becomes three tier, using all the
clever stuff for locking, but the order taking is usually a fraction of
the work on an ecommerce site as is the browsing.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (microsoft)

sql server (microsoft) : Is SQL Server a good candidate for this scenario?