Help spec a database server (RAID)

[kevkbuk]

Hi all,

Long story short is that I'm a programmer tasked with speccing a new SQL Server database. I have done some research but being a past customer of OCUK a few years back I remembered how great the forum used to be for info so I thought I post here for help.

I am looking for a quad core Xeon based server with 8gb ram running Win Server 64 and SQL Server 05 or 08. I think I'm fairly happy to spec the general hardware but don't know a great deal about RAID and what would be best for a database setup.

The SQL database is currently around 3.5gb in size and runs an OLTP system for 25-30 users. It does more reading than writing generally but both need to be quick. It currently supports some reporting functions but those will be moved to another replicated SQL Server once the new server is in place.

I have done some reading and come up with the following solution, what are people thoughts on it?

All disks will be SAS 15k.

Windows Server / SQL Server: Raid 1 Mirror (2 disks)
Database data: Raid 1+0 Mirroring and Striping for best performance (4 disks, maybe more if budget allows)
Transaction Log: Raid 1 (2 disks)

Any help would be very much appreciated!

 

[kevkbuk]

Thanks for the quick reply!

Are you suggesting to use 6 disks for the Raid 10 partition or are you saying I don't need the others I listed?

 

[kevkbuk]

It won't be installed in a rack, but we have space for it. If getting a rack mount is cheaper or better suited to the job then so be it. We have used towers until now.

 

[kevkbuk]

How are you going to replicate the DB for reporting? - logshipping?

We are hoping to use SQL Servers built in replication feature as the replicated data needs to be as 'live' as possible.

You guys are making me wonder if we just have a badly designed system.... It's already running on a dual core Xeon 3ghz (approx 3yrs old)... though it's all running from one disk (win server software raid mirror, cheap SATAs).

 

[kevkbuk]

I take it your using mirroring then. A quick win might be to reindex the whole DB, if this doesnt help then just start a sql profile trace and leave it to catch most of the usage, save it to a table.

If you post a few of the worst offenders I am happy to tell you what indexes you need. Are you currently on SQL server 2005?

It's currently on a software raid mirror running SQL 2000. Thanks for the advice on the profiler. I had already started to use it and have made some progress and identified problem areas. The current plan is to continue with this and see how far we get before buying a new server.

Our problems seem to be some poor query design and lots of temporary tables and not indexing, but thanks for the offer of help, much appreciated.