Help spec a database server (RAID)

kevkbuk · 16 Mar 2009 at 11:57

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!

daz · 16 Mar 2009 at 12:00

I would go for something like a Dell 2950, with two power supplies, dual quad cores and then 6 disks in RAID10. You'll want to be able to hold the entire database in RAM, as well as have plenty enough spare so I would recommend 16-24GB of RAM. Past this you have to use all 4GB sticks which get expensive.

kevkbuk · 16 Mar 2009 at 12:21

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?

ThomP · 16 Mar 2009 at 13:04

Hi,

Is this server going tobe installed into a rack? Does it need to be rack mountable?

kevkbuk · 16 Mar 2009 at 13:18

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.

ThomP · 16 Mar 2009 at 13:26

A PE2900 might be worth considering, more silent than PE2950. More DIMMs 12 vs 8, and HD space 8+2 Extra HD carrier 3.5" vs 6 3.5". Have you got yourself a Dell Account Mgr? If you haven't get one. You'll get a nice discount.

Also adding to this, if you really want plenty of RAM. On really tight budgets i'll have the dell system built with say 2x2GB, then i'll pickup a 5% or 10% discount coupon code of a discount code website, then i'll buy the memory of a well known quality memory maker and it will end up costing a lot less than dell. I did my personal PE2950 to 16GB(8x2GB) for about £400 of top of my head.

iaind · 16 Mar 2009 at 13:44

For that number of users I'd look at a RAID1 for the OS and logs and a RAID5 for the DB files. If this is an existing system, you can look at some performance stats to help you plan what you need.

I would definitely say 16 or 24gb RAM is massive overkill for a 4gb database, 8gb should be plenty. My main DB server is Progress based and has about 12gb of databases with 4gb RAM. My SQL server is running about 20gb data and also has 4gb RAM. Provided the box's only function is the DBs, the RAM shouldnt be a problem. If there are other services running (reporting etc) then you'll need to take that into account.

Have you considered virtualising it?

daz · 16 Mar 2009 at 14:12

Personally I'd have one large RAID10 array than splitting it up, but that's just me.

iaind · 16 Mar 2009 at 14:41

daz said:
Personally I'd have one large RAID10 array than splitting it up, but that's just me.

I would always design a database server so, in the event of a failure of an array you can restore from backup and roll forward the transaction logs.

If you go for one array, if it fails, you're stuck with the data as at your last backup. Obviously RAID10 can have 2 or 3 disks fail, but other things can go wrong, controllers etc. It's also quite expensive in terms of £/gb and I dont imagine the extra performance is necessary for a smaller scale system like this.

Personal choice though, every option has pros and cons and everyone will prefer one option

oneilldo · 16 Mar 2009 at 17:58

My netbook could run a DB of that size for that many people so the server your are speccing will cope fine.
Might as well get bigger sticks of RAM if poss for ease of adding more in the future if poss.
Your disk set up sounds spot on. Transaction logs and data files are fundamentally different types of disk access (sequential vs randomish reads) so its best to keep them on seperate arrays.
How are you going to replicate the DB for reporting? - logshipping?

iaind · 16 Mar 2009 at 20:10

oneilldo said:
My netbook could run a DB of that size for that many people so the server your are speccing will cope fine.

I agree that the spec is definitely overkill, hence my suggestion about virtualising it. I wonder if a rack crammed full of netbooks would give better density than blades... its an amusing thought

dangerstat · 16 Mar 2009 at 21:19

kevkbuk said:
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!

That spec is pretty high for a small database. I'm using a Quad Core Xeon with only 1GB of RAM for a DB about twice that size (though to be far only about half the users) and it barely breaks a sweat.

I'd say a dual core CPU, 1GB and a 73GB SAS drive should be fine.

I'll see if others agree but these are my rules:
Infrequent but queries resulting in large results sets - More RAM less importance on CPU

Frequent but queries resulting in small results sets - Less RAM higher spec CPU

Frequent queries resulting in large results sets - More RAM and higher spec CPU

iaind · 16 Mar 2009 at 21:57

I'm no DBA but that seems to coincide with my experiences too. Probably a vast generalisation/oversimplification, but thats the easiest way to do things with smaller systems

Personally, I'd spend the money on a good quality server with good redundancy and expandability. Maybe a dual socket, but with a single dual core CPU. Dual 73gb disks would probably be sufficient, or 4 (2 mirrors) to provide a bit more resilience

Then, chuck ESXi on it and set the machine up as VM. That then gives you a good platform that you can expand very easily and use for multiple systems in the future.

richard smith · 17 Mar 2009 at 09:46

daz said:
I would go for something like a Dell 2950, with two power supplies, dual quad cores and then 6 disks in RAID10. You'll want to be able to hold the entire database in RAM, as well as have plenty enough spare so I would recommend 16-24GB of RAM. Past this you have to use all 4GB sticks which get expensive.

Holy over specification, Batman!

OP, your original spec would do the job fine

kevkbuk · 17 Mar 2009 at 10:24

oneilldo said:
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).

slylittlefox · 17 Mar 2009 at 12:35

daz said:
I would go for something like a Dell 2950, with two power supplies, dual quad cores and then 6 disks in RAID10. You'll want to be able to hold the entire database in RAM, as well as have plenty enough spare so I would recommend 16-24GB of RAM. Past this you have to use all 4GB sticks which get expensive.

Pfft, sod that - might as well get the PE R905 with the four quad core Opterons, and you'll need the 256GB of RAM on top of that...

Oh no, hang on a minute, you just want a small database server... well get hold of a second hand PE2600 then

Overkill, hoooooo!

iaind · 17 Mar 2009 at 14:22

slylittlefox said:
Pfft, sod that - might as well get the PE R905 with the four quad core Opterons, and you'll need the 256GB of RAM on top of that...

Oh no, hang on a minute, you just want a small database server... well get hold of a second hand PE2600 then

Overkill, hoooooo!

Lol, a 2950 would be a good box for the job, but not at that spec. A dual core Xeon and 4gb RAM would be plenty.

I'd prefer an HP DL380 G5 or Sun X4140 though

richard smith · 17 Mar 2009 at 15:58

iaind said:
Lol, a 2950 would be a good box for the job, but not at that spec. A dual core Xeon and 4gb RAM would be plenty.

I'd prefer an HP DL380 G5 or Sun X4140 though

DL380G5 for the win. Thats what I'm currently using for my DB server.

oneilldo · 17 Mar 2009 at 20:54

kevkbuk said:
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).

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. then do select top 100 * from tablename order by duration desc. This will get your worst queries (in terms of duration - might want to do reads as well). Then you can get the query and change it so its more efficient either by adding relevant indexes, re-writing the query etc. Easy peasy. just get the sql out put it in sql management studio, ctrl+l then look for clustered index scans or table scans as these are bad.

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?

daz · 17 Mar 2009 at 22:29

He already suggested 8 disks in his original spec...

So going for a decent amount of RAM and a second CPU makes the system futureproof without adding that that much to the final price (you're looking at £200 for the CPU and probably the same again for the RAM).

Alternatively, you can do it a lot lot cheaper if you wanted. We picked up some T105 quad core Opteron tower machines for £150. Stick in some RAM and 4 SATA disks in RAID10 and it would be a very nice little system for <£500.