Need help choosing a Database Server

[kenalex]

At work we have a system that generates records for all calls made on our network. The system has 4 components with each component outputting records into a text file in CSV format. Each component outputs approximately 100,000 records daily.
I am trying to choose a database server where i can store these records so they can view so as get certain statistics about the calls being made ,and also details of the calls when they are requested .So far i have a couple of databases i am looking at such as Postgresql ,Mysql and MS SQL server 2005.

 

[oradba101]

Greetings,

Out of the options you listed, PostgreSQL is free and very powerful and should do the job. However, Oracle pricing starts at $5,000 per CPU or if you prefer you can get it at the per user price of $149 with a minimum of 10 users for a total price of $1,490.

Regards,


William Chadbourne
Programmer/Analyst

 

[jrbarnett]

Any database server will be able to handle that level of output easily. Basically, your choice comes down to the following decisions:

i) What you are familiar with, in terms of both database engine and underlying operating system.
If not, its worth investing in training for yourselves or hiring somebody with the expertise to do the installation, setup and day to day management.

ii) Your budget, to encompass the physical database server, operating system licenses, database engine and other necessary components for a production server including backup/restore utilities and an uninterruptible power supply.

iii) Requirements that the application software has for the database engine to record or report on the data. For example, if it won't work with an SQL Server database, then there's no point pursuing that particular option.

John

 

[mrdenny]

Progress and MySQL are both free, but the user base is smaller than SQL Server. Microsoft SQL Server has a free edition of the database engine called SQL Server Express Edition which based on your information will work just fine.

Express editions main limitation is that it supports databases up to 4 Gigs in size. After that you would need to upgrade to Workgroup, Standard or Enterprise edition (probably Workgroup).

SQL Server has a very large and active user base of thousands of people actively posting on forums who can assist you if you get into trouble. (I'm not sure how large the active online user base for MySQL or Progress are.)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Olaf Doschke]

I wonder why so many answerers point out free databases. While many of those free and open source BMS are powerful I would suggest looking at jrbarnet's answer. And I assume in a system where 4 x 100,000 calls are recorded in CVS files there should be a budget for a decent database server.

Security is most important, which means you need to know the database. If it's a black box to you it doesn't help much if it's capable to do the job, if you can't restore a backup, because you don't know how to maintain the database,
just as an example. When collecting data there's also issues about who should have access.

400,000 records a day isn't all that much, even if it's within a 8 hour interval it boils down to about 14 calls per second, if you want to insert it live that is not much of a db server load. To read in several CVS files with 400,000 lines is a job of seconds, perhaps minutes. Depends on the length of a record.

But even though 4GB sound pretty much, even calculated with short 128 Bytes/record 4GB will be exceeded after about 80 days. So I'd rather go with something commercial, which could also mean a commercial MySQL license, but it sounds more like Oracle, DB2 or at least MS SQL Server.

Bye, Olaf.

 

[mrdenny]

Out of curiosity, why is it "at least MS SQL Server"?

These days SQL Server can play in the same sand box as Oracle and DB2 provided that you give it the care and resources that you'd give a large Oracle and DB2 system.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mrdenny]

As for why I recommended SQL Express, it's a good starting point (you'll notice that I did say that an upgrade would be needed). This would allow the project to get started right away while waiting for the higher end version to come in later to. This would also allow the OP to make sure that the platform would work for them without any cash outlay.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[oradba101]

There is no way that SQL Server can compete with Oracle on large scale systems. Oracle has something called RAC that has not been matched by any other vendor. This allows you to put together a bunch of servers and they all are in active mode and the processing is shared among the CPUs of all connected servers. The best SQL server can do is to have one server in active and the others in passive.

Regards,

William Chadbourne
Programmer/Analyst

 

[mrdenny]

SQL Server can have the load spread across multiple servers by using a technique called Federated databases. Basically part of the database is hosted on each server and the servers all know how to call each other and the connections are then load balanced between all the database servers in the system creating an active/active setup. It's not cheep or easy, but it can be done.

I know of RAC, I haven't ever seen it setup and working correctly. I've heard of engineers that Oracle has sent out who can't get it working in customer environments.

If we are talking about spreading work load across SQL Servers, I'd like to point out that MySpace is all Microsoft SQL Server and the transaction load count there is higher per minute than most places are per day or per week.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[kenalex]

I would like to thank you guys for the replies. Base on the what you guys have said the important thing is using a database that has good support via the developers and also by an active community.So I have decided to use sql server and at a later date get the standard edition.

Thanks again guys

 

[SQLSister]

Sorry oradba101 but I know people who run databases that are terrabytes in size with SQL server. All depends on whether you have someone competent to set it up properly. I've also run applications the had an Oracle backend that were slower than dirt but I don't think that was becasue Oracle was slow, just because that particular database was badly designed. Oracle and SQl Server are basically equivalent products all up and down the product line.

"NOTHING is more important in a database than integrity." ESquared

 

[ousoonerjoe]

I have found Oracle does NOT play well with Windows. I installed a version on a 2003 Server and it's numbers were horrible compared to a comparable Sun Workstation. I've found Solaris had the same effect when installed on Intel machines.

It's all a matter of what you are willing to spend and what your requirements are. Oracle on a Sun Solaris Server is one of the hardest working and longest running (reboot only 4-6 months verses Windows weekly) system I've worked on. You pay for it too.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[jrbarnett]

Erm, I run several SQL Servers (mix of NT4, Win2K, Win2K3 OS; SQL Server 7, 2000, 2005 plus MySQL) and the frequency between reboots is certainly more than a week. Some don't get rebooted for months.

John

 

[ousoonerjoe]

All my experience with SQL 6.5, 7, and 2000 has required reboots on a regular basis for what ever reasons. The only time we ever rebooted Oracle is when the power went out or we upgraded the system. May not be true for all, but over the last 10 years, it's been pretty consistent.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[SQLSister]

We almost never need to reboot our SQL server, maybe two or three times a year if that.

"NOTHING is more important in a database than integrity." ESquared

 

[MDXer]

My current company sends out maitenance schedules of when servers will be rebooted. Only time our SQL servers have been rebooted is when they were moved to a new rack.

Those trying to defend MS SQL server to the Oracle zealots should no by now this is a losing battle that die hard oracle fans will always find a reason why Oracle is better than ms even if it is "When I used 6.5 we had to reboot is every week, after walking to work everyday in the snow uphill both ways in July"

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ousoonerjoe]

haha... i'm not an "Oracle Zealot". In fact most of my knowledge is MS SQL. However, I have worked with both over the years, in some cases was a hybrid network with both running, and just found that Oracle was a lot more stable. It "twitched" less than MS SQL. Maybe the Oracle admins knew how to setup the system better than the MS SQL admins (in a couple of cases i'm sure of it). At my former company the director ordered that all MS SQL 2000 Servers (2003 Server OS) be set up to reboot every Sunday night and to notify him when it happened. If it failed to notify him, he'd manually reboot it first thing Monday morning. So, since I've never seen anything like that from an Oracle server running on Solaris, I'm naturally gonna call it more stable.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[MDXer]

I have been at companies where the same mandate was placed and it was typically due to someone forcing their opinion as fact rather than the individual making the mandate actually doing some research.

As for poorly configured SQL servers being an issue I would say without a doubt that is a cause for the illusion that SQL Server is less of a platform than Oracle. In my opinion the impression is that to some the beilief that SQL Server is less of a platform allows them to spend less effort in properly configuring or desining a system for SQL Server. Ive seen companies dedicate a hand me down server to an enterprise BI system that still had half a dozen other systems running on it. The lack of effort they direct towards the SQL Server system results in a poorly running system reinforcing their belief in SQL Servers abilities.

My Current company is a mixed enviroment and every one of our MS SQL Server Solutions is magnitudes faster than it was on Oracle.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

I'm pretty sure that everyone here knows that I'm a hard core SQL guy, so I'm probably a little biased, but in 10 years, and probably 7-8 companies I've never had to schedule SQL Servers to reboot on a regular schedule.

Unless I've done something stupid (like yesterday when I had a service broker activated procedure stuck in a endless loop) SQL Servers should be rebooted for patching or because the power goes out.

If someone tells you that SQL needs to be rebooted every week to free up resources then they need more resources available, or some major database tuning needs to be done.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Olaf Doschke]

quote Twitter conversation:

A: Anyone have a recommendation for white paper or book on What's new in SQL Server 2008?

B: Introduction to DB2, (c) IBM, 1994? /gd&r/

;-)

By the way I'm neither an SQL Server guy nor DB2 or some of the other mentioned, so it was more of a hearsay opinion on database servers.

I'm deep in an SQL2005 project now - migrating from another database. And I had a peek at Datamining, OLAP Cubes and MDX queries. It is already rather impressive and over my head.

Bye, Olaf.