6k for SQL Server

[Diode20]

I've been building PC's as far back as I can remember. So seeing that anandtech topic about buying a built PC reminded me that I had to build an SQL Server for work.

I have 6k to build the server. I don't want to build it ground up because it's for work. So I was wondering....

What do you guys recommend? The SQL Server handles many versions of data varying from 1 million records to 15 million records.

Most users at a time would be something like 10. But I think that is max. Lot's of software connecting to it for big jobs.

I would love more than 300gb raid 10 scsi 10k or 15k type performance. But I have it understood that 300gb scsi is still freakin expensive right?

Any help would be appreciated.

Thanks,
JP

 

[HannibalX]

Well since no one has commented on this yet I will.

Some things to consider first:
1. Is your OS and SQL licensing included in your $6k budget?
2. Are you looking for a rack or pedestal server?
3. Have you thought about power backups and data backups for this server?
4. Does your company already have service contracts with any hardware vendors? If yes you may be able to get a discount on hardware.

Without knowing more info I would have to recommend a Dell PowerEdge 840 pedestal server. I would configure it with a Pentium D 925, 4GB of Ram, Raid 5 with 4x160GB drives (about 420GB of formatted storage) and Windows 2003 server. This is a good entry level server and configured this way will cost around $4,400 plus tax/shipping. Alternately you could configure it with a Xeon processor, more memory and larger hard drives to use up your entire budge, but I don't think that?s necessary. This leaves you extra cash to think about power needs and data backup.

 

[Diode20]

Thanks for the reply.

We currently already have licenses for Win2K3 Server and SQL 2005. We also have UPS and also a service contract with a hardware vendor. I was hoping to maybe get a good price from them. I considered rack, but we only have one, and it's used by another department and I haven't had the OK to use it. So it would have to be able to stand on it's own until I get the OK, and then maybe convert over. But being able to put it on a rack isn't to big of a concern.

We have 3 old SQL servers, 3ghz P4's with raid 10 10k scsi's. Basically each has 1 IDE OS drive, and either 4 scsi's, or 2 scsi drives. 2gb ram.

Another person in our company has dual 3.2ghz P4 Xeon, 4 scsi 15k in raid 10 for sql data, and 2 10k scsi in Raid1 for OS 4gb ram.

The second setup kicks our setups ass. In other words, I really feel the difference and would love that setup. Obviously on a more up to date platform.

But from what I can tell...scsi is still as expensive as it ever was? And some of the PC's I see have sata/sas. Sata seems cheap enough, but I have tried a Raid 10 with WD 500gb sata, the one that is suppose to be good for multi user performance. Unfortunately, I really did not like the performance I got out of it. It did bench reasonably well though. But real world SQL performance was not at all what I expected. SAS...omg, price is through the roof.

So I guess my problem is choosing the right server/HD combination. I'm currently trying out your recommendation on dells website and configuring it.

 

[Moose1309]

My preference has usually been HP ML350. I'm deploying a few of the newer G5's now. With the configuration I'm deploying they came to around 4k each. That's with 3 SAS drives in RAID 5, and a Xeon 51xx which is the only thing you should be considering IMO.

SAS 146 GB are ~$400 each. For SQL I would go with 4-5 of those in RAID 5, possibly another 2 smaller ones mirrored for OS install. Otherwise you can just make a small sys partition on the RAID 5 array. 4GB RAM.

Dell Poweredge would be fine too. Either way, with the Xeon 51xx you will kick the mustard out of the P4 xeons.

 

[zephyrprime]

SAS is expensive unfortunately but you will not be able to beat the performance. If you insist on going with SATA, be sure to get the enterprise SATA which isn't too much more expensive than SATA. Also be prepared to build a big raid array if you go this route to get decent performance. Not worth it in my opinion.

The real secret to good DB performance is lots of RAM. The ML350 looks pretty nice. I don't like the pricing that dell has for extras. They always charge a huge premium for any drives or processors bought through them. Like the other day some other at member was asking about this and I saw that Dell SAS drives were more than 100% markup over the price for the same drive at newegg! HP on the other hand had only a ~20% markup which is reasonable in my opinion.

 

[Diode20]

Thanks guys.

I'm pricing out an ML350 G5 setup.
4 146gb SAS Raid 10
4gb ram
5130 cpu

I haven't decided on OS Drive, maybe 2 SATA 500gb in Raid 1 to keep it cheap.


But umm....SAS Raid 10 controller....recommendations? I take it it's not an onboard item on the motherboard.

 

[Moose1309]

What's your justification for RAID 10? BTW you need at least 6 drives for that, and it would net you 2 drives' worth of capacity. That's where you have a stripe of mirrors, that is, 3 mirrored sets and then RAID-5 them. Maybe you thought you were doing RAID 6?

... Anyway, RAID 5 will give you far more storage and slightly more performance for your $$. The fact that you're considering RAID 10 and THEN SATA OS drives to keep it cheap... dude... It would be better just to include the OS on the big array if you can't afford separate OS array. Nothin' wrong with that. You don't want even one desktop drive in there if you're spending $6k.

PS: ML350 doesn't support RAID 10 without going with an additional controller. Stay with the onboard "Smart Array E200i Controller"

 

[MerlinRML]

Originally posted by: Moose1309
What's your justification for RAID 10? BTW you need at least 6 drives for that, and it would net you 2 drives' worth of capacity. That's where you have a stripe of mirrors, that is, 3 mirrored sets and then RAID-5 them. Maybe you thought you were doing RAID 6?

... Anyway, RAID 5 will give you far more storage and slightly more performance for your $$. The fact that you're considering RAID 10 and THEN SATA OS drives to keep it cheap... dude... It would be better just to include the OS on the big array if you can't afford separate OS array. Nothin' wrong with that. You don't want even one desktop drive in there if you're spending $6k.

PS: ML350 doesn't support RAID 10 without going with an additional controller. Stay with the onboard "Smart Array E200i Controller"

I'll first admit, I'm not extremely familiar with HP's x86-based hardware. If this is the same controller, then the E200 should support RAID 10. http://h10010.www1.hp.com/wwpc/pscmisc/...s/en/ss/proliant/ml350g5-qanda.html#c3

As for RAID 10, you only need 4 disks. And the performance of a RAID 10 array should be higher than the performance on a RAID 5. The XOR's on a RAID 5 array kill performance, but with RAID 10 you've got RAID 1 (mirroring) running on top of 2 RAID 0 (striping) arrays, so there are no parity calculations. Since you can probably expect about 80MB/sec sustained on the SAS 3.5" 15k RPM disks, you should see pretty decent performance in the 150-160MB/sec range while still having high availability and fault tolerance in the event of a single disk failure.

While I do believe that the RAID 10 array should offer better performance than the RAID 5, I'm not sure that it is the ideal disk layout for your DB server. You've got the entire database running on a single disk array. I would defer to someone more familiar with database server administration, but I do recall seeing that says it makes sense to separate different parts of your database onto different drives.

 

[Moose1309]

My mistake, I thought there was a parity stripe in RAID 10.

 

[Kwaipie]

I'm a complete Dell shop for my windows platform servers. I have 85 of them in the back room with about 10 running SQL 2K or 2K5. For your low amount of users and relatively smallish DBs, RAID 5 with SCSI drives should be plenty. Check out the Dell PowerEdge 2800 or 2900, don't forget to downgrade support to Silver at the most.

On the other hand, we run our Oracle DBs on 60 HP ML350 G5s running SLES 9, our linux admins have HP out here at least once a week fixing something.

 

[Diode20]

PowerEdge 2900 Dual Core Intel® Xeon® 5130, 4MB Cache, 2.00GHz, 1333MHz FSB
Operating System No Operating System, Microsoft
Additional Processor Single Processor only
Memory 4GB 533MHz (4x1GB), Dual Ranked DIMMs
Primary Controller PERC 5/i, Integrated Controller Card
Network Adapter Dual Embedded Broadcom® NetXtreme II 5708 Gigabit Ethernet NIC
CD/DVD Drive 48X IDE CD-ROM
Hard Drive Configuration Integrated SAS/SATA RAID 10, PERC 5/i Integrated
Chassis Configuration Tower Chassis Orientation
Power Supply Redundant Power Supply with Y-Cord
Primary Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive
2nd Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive
3rd Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive
4th Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive
5th Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive
6th Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive

The configuration above comes in just under budget. Is the controller OK?

I am trying to see if I can place the order with Dells promotion. Without it I go over 6k.

 

[Moose1309]

I'm going to nitpick a few things just because...

Originally posted by: MerlinRML
As for RAID 10, you only need 4 disks. And the performance of a RAID 10 array should be higher than the performance on a RAID 5. The XOR's on a RAID 5 array kill performance,

That is not true - in fact, reads from a RAID 5 array can be faster than RAID 10. ACNC refers to RAID 5 as having "highest Read data transaction rate, medium Write data transaction rate." It seems to me that current RAID controllers are just fine at calculating parity on the fly up to a pretty heavy write load.

but with RAID 10 you've got RAID 1 (mirroring) running on top of 2 RAID 0 (striping) arrays, so there are no parity calculations.

You've got it backwards. What you just described is RAID 0+1, sometimes called RAID 01. RAID 10 is opposite - a striped set of mirrors, i.e. striping on top of mirroring. (The diagram on that AC&NC site is wrong, but their description is right, as far as I can tell.) I now see why it's such a good solution now that I know 10 striping has no parity - why did I think that -

While I do believe that the RAID 10 array should offer better performance than the RAID 5, I'm not sure that it is the ideal disk layout for your DB server.

On the one hand, I think the opposite - RAID 5 will generally offer higher performance, particularly read perf. However you've convinced me that RAID 10 is actually a very good solution from a fault-tolerance perspective. OTOH, for a small DB server RAID 5 requires less capacity overhead (yielding more data storage), so I would still stick with that.

You've got the entire database running on a single disk array. I would defer to someone more familiar with database server administration, but I do recall seeing that says it makes sense to separate different parts of your database onto different drives.

I've also heard that you want say, data vs. logs on separate arrays, and OS on yet another array - but for such a small DB, it won't matter at all (in my experience with dinky databases).

Originally posted by: Kwaipie
On the other hand, we run our Oracle DBs on 60 HP ML350 G5s running SLES 9, our linux admins have HP out here at least once a week fixing something.

OK that just makes me nervous I've never had a problem with ML350's - of course I don't administer a whole farm of them.

 

[Moose1309]

Originally posted by: Diode20
PowerEdge 2900 Dual Core Intel® Xeon® 5130, 4MB Cache, 2.00GHz, 1333MHz FSB
Operating System No Operating System, Microsoft
Additional Processor Single Processor only
Memory 4GB 533MHz (4x1GB), Dual Ranked DIMMs
Primary Controller PERC 5/i, Integrated Controller Card
Network Adapter Dual Embedded Broadcom® NetXtreme II 5708 Gigabit Ethernet NIC
CD/DVD Drive 48X IDE CD-ROM
Hard Drive Configuration Integrated SAS/SATA RAID 10, PERC 5/i Integrated
Chassis Configuration Tower Chassis Orientation
Power Supply Redundant Power Supply with Y-Cord
Primary Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive
2nd Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive
3rd Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive
4th Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive
5th Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive
6th Hard Drive 300GB, SAS, 3.5-inch, 10K RPM Hard Drive

The configuration above comes in just under budget. Is the controller OK?

I am trying to see if I can place the order with Dells promotion. Without it I go over 6k.

Looks fine to me. I've used a PERC 5-something controller to make two separate arrays and it works great.

You've got a ton of capacity there- good to plan for the future I guess

 

[Diode20]

My main customer is Toyota. They ussualy require a list of risks and countermeasures sent for work we're doing for them.

That's why I think I lean towards Raid 10. And in the setup I showed above, it may be safer for me to make a 4 drive Raid 10, then have two hot spares.

Over redundancy. =(

Good to hear about the controller. They worry me a lot. I have tried cheaper scsi raid controllers in the past, and have had performance issues. Not huge ones, but writes for example were not great, and read did not scale as I expected etc. So if the PERC one works out well, I'll be happy.