Re: The right database for the job?

Hallo again

John Currier wrote:
> Ditto again for Jim and Kenneth's comments.
>
Thanks for the advice.

> What do you mean by "fails"? That word can mean lots of different
> things ranging from severe slowdowns to crashing the machine.
The program freezes. Sometimes Windows freezes as well and needs to be
restarted.

>
> Do you have a DBA that can analyze the failure as well as overall
> performance characteristics of the database? Note that "many updates
> per second" shouldn't be an issue (depending on your definition of
> "many", of course).
No DBA, just a systems administrator that do some DBA functions. I'm
the programmer that have to fix somebody elses code and/or database
design.

Maybe I should give more details of the present setup. There are a
number of networked PC's, each connected to a number of hardware
devices. Whenever a PC gets a hardware interrupt from one of the hw
devices under its control, it connects to a central "DB server" program
that runs a SQL query on the database and returns an answer to the PC
on what the appropriate action should be. Then, when the PC lets it
know that this action was successfull (or not), it updates the database
with a log entry. There are also several (but fewer) "Update stations"
(talking to this same "DB server" program) used to update the database
(these updates change the rules for what actions the different hardware
should do). It is this "DB server" program that freezes and not the
actual MS SQL server DBMS. We suspect that the DB updates and SQL
queries don't get processed fast enough, causing a backlog of queued
new log updates to be serviced, with winsock (or the program itself)
being overwhelmed on the network side.

Change in scenario: The hardware devices should keep on functioning
when the network is down (and electricity off). We changed them so that
they can keep a very small subset of the main database locally (just
the set of rules for the specific device in an embedded file system)
and also keep a local log file whenever they go offline. (This again,
would be a subset of the data normally needed by the PC that control a
number of these hardware devices). We need to change the software
anyway to be able to handle the new hardware. (So why not try to fix
its current problems at the same time?)

So I revisited my old database textbooks (Rob & Coronel, Database
Systems: Design, Implementation & Management) and it seemed as if a
DDBMS would solve many of our problems
(http://www.course.com/downloads/presentation/21323-xppt-chap10.ppt).
The PC's don't need to query a remote "DB server" everytime, because
they'll have the relevant portion of the Database locally. They don't
need to update the remote logtable all the time because they keep a
local copy of it. This should result in less pressure on the "DB
server" that can act now more like a backup and reporting tool. There
are drawbacks to this solution (you mentioned probably the most
important: too complicated). One alternative is having a Fully
Distributed DBMS with no central server. Another might be to have the
PC's send SQL queries and updates directly to the MS SQL server and
process them locally... but I'm wary of the ammount of network traffic
this would generate. If I understood correctly, a DDBMS can automate
the process and minimize the ammount of network traffic by fragmenting
the database in a optimized manner. It should also take care of data
integrity, concurrency control and transaction management. The problem
is that I have only experience of single site databases and no idea how
much of of Date's "Twelve Commandments for Distributed Databases" have
been implemented in working (R)DBMS's, hence my questions.

Thanks again
Chavoux

.

Relevant Pages

  • ESE Error...Fix? Or buy new hardware???
    ... I've been seeing this error for a while now, but don't know how to fix it. ... Before I just go out and buy a new server though, ... me to check hardware as that is the main cause of this error. ... Event Category: Database Page Cache ...
    (microsoft.public.windows.server.sbs)
  • Re: The right database for the job?
    ... Note that "many updates ... > the programmer that have to fix somebody elses code and/or database ... > devices under its control, it connects to a central "DB server" program ...
    (comp.databases)
  • Re: The right database for the job?
    ... Note that "many updates ... > the programmer that have to fix somebody elses code and/or database ... Whenever a PC gets a hardware interrupt from one of the hw ... it connects to a central "DB server" program ...
    (comp.databases)
  • Re: Updating DB Locally
    ... and overwriting just wipes out all the ... >> changes made in the production database from the web and replaces ... >> etc.) but in practice it may be a disaster (lost updates). ... > server which is not mine. ...
    (comp.lang.php)
  • Re: How to prevent 404 error when a page is updated via ftp?
    ... the data is 1-minute averages and hourly average values for about 10 ... updates once a minute. ... file and uses ftp to send it to the web server, ... database is then used by the Visual Interdev website to display the data. ...
    (microsoft.public.frontpage.client)