Re: The right database for the job?
- From: Paul <paul@xxxxxxxxxxxxxx>
- Date: Thu, 14 Jul 2005 14:42:58 +0100
Chavoux@xxxxxxxxx wrote:
>I need some advice from people with experience in a number of different
>databases.
>Background: Application is running MsSQL Server on central database:
Hardware, RAM, OS, version and Service Packs please.
>Main server and failover server. DB server-side application access MS
>SQL server and handles all transactions from remote stations (many
>updates per second during day-time).
How many exactly per second? Volume of data per day?
> It can't handle this load and
>fails (even with back-up!). We plan to replace this setup with a kind
>of "thick client" or distributed Database, but I'm not sure what (RDMS)
>would be the best tool for the job.
Could you kindly explain what business you're in? You have 150 sites,
that's fine, but sites that do what? Sell CD's? Car parts? Body parts?
Why was a central solution used first and why didn't the design start
with 150 separate apps running a much lighter db, and then the
collation of all site info could be done at night, or courriered disks
if necessary in some sort of data warehouse/mart scenario?
>The idea is to have different zones, with up to 150 local database
>servers each having only a section of the main database (all data
>applicable only to that zone).
The data being?
> Instead of updating and querying the
>main Database all the time, updates of the main Database occur only
>when requested by the main database or at night (when little or no
>transactions are triggered) and local queries are handled locally.
So, you wish to move to some sort of data warehouse solution for your
HQ, but keep the 150 local sites up to date at all times?
>Databases systems I'm thinking about are the following:
>PostgreSQL
>MaxDB
>Cache'
>Firebird
MaxDB isn't free. PostgreSQL has a good rep, and so does Firebird.
I've programmed in Delphi against Interbase and Firebird. It does the
job. I know nothing about caché.
>If we use the aJile zone DB servers, we might need something like
>HSQLDB, Berkeley DB JE or Derby (Cloudscape)? Else port one of the Open
>Source databases to Java?
No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No,
No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No,
No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No.
Many fine programmers have spent many many man years developing
decent, transaction capable databases. The number of times I've seen
people here and elsewhere complain about people who've tried to "roll
their own" is beyond count.
If you want to write a db, then fine, but be prepared to put years of
your life into it, it's not just something you do for one project.
It's like if you had to go somewhere. Do you choose public transport,
use an aircraft or drive (think of these as different db solutions
(cost, speed, &c.)) or would you design and implement your own mode of
transport (rocket propelled skates perhaps - might work for a while,
but would be very unstable)?
>Requirements of the (R)DMS:
>1. Possibility to automatically update the distributed zone DB servers
>with the apllicable portions of the database.
OK, so now you're talking about pushing data from the data mart to the
mini servers at your 150 sites? Why? What is the business case for
doing this?
>2. Possibility to keep the data "in sync". If certain changes happen at
>the zone server level (triggers?), the main database needs to be
>updated immediately and also update the other zone databases for which
>the change might matter.
Ah, so what you're looking for is some sort of partial concurrency?
You can do this in your application. What sort of cable speeds do you
have between your 150 sites and the main data warehouse site?
>3. The possibility to store log files locally at the zone database
>server and update the main DB server only periodically.
Batch job at 3 in the morning, or better still stagger them, so the
server won't be overloaded all at once.
>4. If the main DB is queried (happens less frequently), it should
>update itself automatically by getting the latest/newest version of the
>queried tables from the different zone DB servers.
Ah, so what you want is not partial concurrency, but full concurrency
between your data mart and your 150 sites. Best to use one db.
>5. From the application's point of view all of this should be
>transparent. It should just need to query or update the database and
>not know whether it is the central database or one of the zone
>databasis that it talks to.
Do you mean from the app's view, or the end-user's? Who are your end
users?
>6. Obviously standard DB functions like referential integrity,
>cascading updates/removes etc. should be part of the DMS.
>7. Speed, data consistency, accuracy and reliability (no falling down
>under heavy loads) are the main requirements.
And I'll take the sun, the moon and stars as well, please.
>I have too little experience to make an informed choice and because
>this is a large project, we cannot afford to choose the wrong DMS only
>to find out later that it is unable to do what it must.We would prefer,
>to use Open Source Software, but something with a reasonable price like
>Cache' is also an option.We could stay with Microsoft, but paying for
>MS Sql server on all zone DB servers will be too expensive and I'm not
>sure if the Desktop Edition would be able to do everything we need.
>From what you've written, my impression is that you need a main db
server which serves all of your 150 sites. You'll need to explain more
about what it is you do, how many transactions there are per second,
and the volumes of data added per site per day for anybody to be able
to even begin to answer your problem.
Paul...
>Any advice appreciated. No flame wars about best DB please!
>Chavoux Luyt
--
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.
.
- 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
- The right database for the job?
- Prev by Date: Re: The right database for the job?
- Next by Date: Re: Basic SQL
- Previous by thread: Re: The right database for the job?
- Next by thread: Re: The right database for the job?
- Index(es):
Relevant Pages
|
