Re: The right database for the job?
- From: "Ed Prochak" <ed.prochak@xxxxxxxxxxxxxxxxxx>
- Date: 14 Jul 2005 11:07:29 -0700
Chavoux wrote:
> 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
This appears to be a process control operation. Am I right?
Have you considered getting real process controllers from experts like
Rockwell Automation (formerly Allen-Bradley, a previous employer of
mine). Lots of companies try to do their own home-grown process control
based on DOS PCs and customized communications. It often ends up
costing more in the long run.
But if you persist in doing it yourself, consider:
Are the failures due to network issues? (lost packets leaving the
application waiting for its reply)
Would it be cheaper in the long run to upgrade the central DBMS to
something more robust, like ORACLE or DB2? ( the cost of one big
central license versus many on-site DB licenses, where cost include
mainenance and support calls)
Changing the design so more of the inteligence exists in the on-site
nodes. You description makes it sound like the nodes query the main DB
on EVERY event to download what ACTION to take. This seems like a very
weak design. But strengthing it might not require having a DB at every
node.
Since this is less a DB issue than it is a distributed control issue,
drop me an email (reply to this but also copy me at,
ed prochak @ alltel . net
and remove the spaces) and I may be able to give more specific
suggestions.
Ed Prochak
.
- Follow-Ups:
- Re: The right database for the job?
- From: Chavoux
- Re: The right database for the job?
- References:
- The right database for the job?
- From: Chavoux
- Re: The right database for the job?
- From: John Currier
- Re: The right database for the job?
- From: Chavoux
- The right database for the job?
- Prev by Date: Re: Basic SQL
- Next by Date: Re: How do I get this query to work?
- Previous by thread: Re: The right database for the job?
- Next by thread: Re: The right database for the job?
- Index(es):
Relevant Pages
|
