ex-webby

Hi All

I'm doing the technical spec for the new event management software to run the World Rally Championship and thought you would be the best guys to give me some advice on which database to use.

Originally I was just going to go for SQLServer, but thought it would be best to look at all options.

Spec :
Windows Platform on load balanced clusterd servers.

3 locations.. each with a local database.

Single software datalayer user to each database.

Very low traffic..
Estimate around 1 insert a second, and about 1 query every 2 seconds returning about 90 records from maybe 3 different tables.

Total records maybe 300,000.. and most in 1 table, maybe 30,000

Multiple user capability is not important, but what IS important is that it is ROCK SOLID stable and reliable and can return big complex queries quickly.

What do you reckon then?

Cheers

Simon

AndrewW

Oracle.

But then you'd really want to be running on Unix rather than NT (please tell me that when you say windows you mean NT )

Oracle will give you rock solid reliability, but when you run it on a windows platform you are definitely introducing the potential for failure.

Andrew.

ex-webby

NT, yes.

Unfortunately, the windows platform is fixed, so Oracle is a non-starter.

We have even thought of going for something really small as the hits are going to be nothing, and the amount of data tiny, so maybe we can get away with something REALLY small?

Cheers

Simon

EvilBevel

Simon, what would be your reasons for not going MS SQL ?

Pricing perhaps ?

Are you foreseeing any increase in traffic from wrc.com ? What happens if there would be a WRC event in the US ?

You have more experience than I have on MySQL, so can't comment about that, but MS SQL is one of the most stable products MS has ever produced. All IMHO of course.

ex-webby

Theo

I completely agree, and want to go with SQLServer but people here don't as they have heard horrow stories. The only reason I am starting to think about it is that we don't actually need anywhere near the power of it.

WRC.com is completely seperate and is hosted in the UK.

All the best

Simon

EvilBevel

D'oh, will read properly next time - this is internal software I now understand.

Horror stories ... well, OK ... this is a hughe subject LOL

Any need for replication at all ?

ChrisB

Can of worms really. Everyone has their own preferences.

Horror stories on SQL - a product is only as good as the admin looking after it...

ex-webby

replication is required... BUT... for lots of other reasons we have to handle that at a software level...

one thing that would be useful though would be a way for the two or three databases to be able to look at each other and fire an event if one is too far behind another one...

in other words.. if it is say 10 updates out of synch with the master...

this may be difficult as it is not just a case of "does a match b".

that would be a really good selling point if SQLServer can do this though?

Cheers

Simon

simon_prickett

Simon - we run a pretty major app on MySQL, Oracle, or DB2, so do all the 'clever' stuff in software and just use the database as a SQL based store. MySQL seems to be pretty reliable for this, and we run it on Windows 2000, XP, Solaris and Red Hat Linux.

Thought about doing the whole project on Linux? You'd get more grunt out of the same spec PC than when it is running an MS OS.

Simon.

ChristianR

iTrader: (1)

We have oracle running on a win2k box - no problems with it.

ex-webby

Hi Simon

I think I'm definitely erring towards MySQL. It has proven to be incredibly solid on scoobynet, and the WRC stuff will get WAY WAY less traffic and demand than the scoobynet database, so I can't see a problem.

Thanks for all the replies chaps

Simon

GaryK

Simon,

Yes SQLServer can do what you want, I think you are referring to transactional replication, (adding a record in on db adds it to another) and SQL handles this nicely through push and pull (oo er!) subscriptions, therefore no need to write any client side SQL to do it. Cost wise you might want to look at Interbase fully featured RDBMS www.interbase.com which also supports replication and is free!

Gary

simon_prickett

Simon (webmaster) - we've also found MySQL to demand less memory than the others we use.

All the best,

Simon (not webmaster).

Eagle7

an ecommerce company i work for uses SQL server 7 and we have had very few problems with it. We have hundreds of customers accessing the web site daily and most of the processing is done in SQL Server stored procedures.

It is a very versatile product if looked after properly, yet it is easier to configure and use than things such as oracle. It is certainly my database of choice unless there are other restrictions such as cost etc.

Lee

I've got 5 years Oracle experience and frankly it runs just as stable on win32 as it does on unix. Win32 has been regarded as a primary port by Oracle for a long time now so it receives the same attention as solaris, hp-ux, aix etc.

Performance on unix is better though. Maybe more to do with the architecture of sparc/risc chips though.

have touched Oracle on linux but imho its just not quite there yet. My belief is that linux evolves too rapidly (consider the kernel release frequency and then think about Solaris and Win2k! - they are static by comparison which is good for stability)

As a hosting company we run lots of mySQL, some Oracle and SQL Server 2000.

Frankly mySQL is overrated. I am a fan of it, given that its a true client/server rdbms for free and works very well, however nested subqueries PLEASE ! I would not want to run a critical application on it since transactional capabilities are relatively new. Stored objects ? pah.

By people who know both well, postgreSQL is rated better. I've looked briefly but it scared the **** out of me

So..when you are talking stability and rock solid transactional reliability you are talking SQL Server and Oracle. Since your platform has been fixed at Windows I would say SQL Server. Had no problems, done many a restore and it recovers from hardware failures with no problems.

So.. SQL Server

IWatkins

Hi Si,

Had no problems doing non-stop 24/7/365 processing against an Oracle DB on NT. However, I think it is safe to say you really really need to have it setup properly by someone who knows exactly what they are doing, especially if using something like Oracle 9i and doing stuff like web retrievals etc.

There again, had no problems running SQL Server storing small amounts of data non-stop 24/7/365 (approx. 5 - 10 writes/reads a second). I actually prefer SQL Server as (IMHO) it is easier to setup and admin.

So, I'm with Lee and will vote on SQL Server.

As an aside I think MySQL sucks (Ian kicks off a holy war).

Cheers

Ian

Lee

Yeah sorry forgot to add - all the talk about Oracle is all well and good but there is a simple reason why Oracle let you download the full products from their website without license keys etc

YOU NEED A DBA TO RUN IT !

It is *not* an out of the box database like SQL is. Certainly when you get into things like replication you'll need a DBA.

SQL is traditional point and click microsoft (or should that be guess, point and click, hope? )

RallyMarshal

Simon,

Looking at the specs you mention.. if the traffic level is going to be so low, why load balanced servers and running in 3 locations?

Paul..

ex-webby

The 3 locations are just literal physical locations that the results need to be delivered to.

The servers are serving much more than just the results, hence the load balancing, etc.

Cheers

Simon

ex-webby

Oops!!!

sorry guys, I didn't notice the other replies before RallyMarshal's.

Thanks for the replies.

The only thing I would say against SQLServer is that we just don't need the power (cap'n), so it might be overkill.

Almost all the processing happens at a software level as there is huge caching going on to keep everything fast across really low bandwidth connections in places like Kenya and Argentina.

Remember also that this stuff is karted around the globe on trucks, boats and planes and lands in a building somewhere in the middle of nowhere, or runs out of a truck, etc so anything that is really light and easy to set-up and maintain will carry a benefit.

Cheers

Simon

Lee

You did mention replication though..SQL Server and Oracle have this as a builtin...

IWatkins

... and there is no such thing as overkill when it comes to DBs

ex-webby

oh yeah apologies...

the kind of replication I'm talking about though is not straight replication. (and it's not necessary, it would just be a plus)

What it would need to do is say...

This database has had 45 less transactions than the main database, so therefore it needs the following transactions...

not... this database is wrong compared to the other, so the data should read as follows.

Is this possible?

(it is a lot more complex than that in that there is a lot of business level processing that happens on each transaction, so it would probably be as much or more work to do it this way anyway)

Cheers

Simon

GaryK

Simon,

well if you were using transactional replication the two DBs *should* never get out of step anyway, so you wont need to do compare's. You will always get a mirror regardless of a single transaction doing a single table update or a multiple table update.

Gary

ex-webby

the problem is.. I don't actually want the databases to synchronise themselves.. i just want them to tell me if they get TOO FAR out of synch.

GaryK

Simon,

you are making life difficult for yourself!

Gary

ex-webby

hehe

Unfortunately, world rallying has already made it difficult for us!!

There is lots of stuff that we need to do with each update (prior to each update in fact) that cannot be done by a database as there is no way to work the logic back from the end result which is inserted / updated into the database. For this reason the replication needs to be done at a software / business layer level.

It would just be handy to have something that can tell the software that two db's are WAY out of synch which means something must have gone wrong.

Cheers

Simon

Andrewza

If you don't need the power of something like oracle or sql server just use MySQL, as you've said you it's proved it's stability here on scoobynet. I run two MySQL servers, replicated, running the queries providing content on itv-f1.com and have had no problems since the 3.23.x released (some nightmares on 3.22.x). As Lee has pointed out, MySQL is missing some stuff (Sub-queries! ), but if you don't need them it doesn't matter.

If you do need them consider PostgreSQL, it's another free database, but has a far more complete sql implementation and is more focused on stability and correctness than MySQL's speed, not to say it's slow either, but absolute speed doesn't seem to be a requirement for your uses.

EvilBevel

So you need a scheduler, time stamps (or unique ID's), and row counts.

Are we talking inserts or updates BTW ?

Now MS SQL *does* have a great scheduler, and allows stored procedures to access other databases/servers. It also allows external stored procs to be called (xp_blablabla), DLL's if you wish that you can write in VB...

The others probably can too, I haven't followed this for a year or two now.

A 5 user licence is not that expensive, and even if it may seem "overkill", on an NT platform it's not really.

Not saying MS SQL is your only way, but it still seems up to the task you describe so far.

Apart from the horror stories that is.

PS: agree with Oracle needing a DBA ... but once past the initial click & pray with MS SQL, when in trouble, a DBA comes in handy there as well...

PS2: great to see Interbase mentioned here ... we had a long struggle/fight/test back in 1995 to pick (at that point in time) the best RDBMS, and technically Interbase was absolutely the dogs things... we only weren't sure Borland would keep on supporting it (or survive itself after buying dBase) ...

netclectic2

If you're still considering sql server and "Multiple user capability is not important", why not use MSDE?

If my memory serves me correctly - it is sql server with a 2GB database size limitation and limited to 5 concurrent users and it's free!