Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've got a total of 8 physical disks I can use (not including 2 for the mirrored OS) for a fairly high-use SQL Server.

It's used to serve a web-app (Win2k3, IIS6, ASP.Net) which has fairly heavy usage (400K users) with a fairly even proportion of writing and reading. However the database isn't very large (yet) - currently around 10G.

My initial thought is to split the database drives up in the following way:

  • Hardware Raid-5 (3 discs) for Data
  • Hardware Raid-5 (3 discs) for Logs
  • Single disc for Tempdb
  • Single disc for on-server backups

Essentially, I'm looking for the best performance by reducing disk contention between different writes (TempDB, Logs & Data).

I'd be curious to get any opinions of whether this appears to be the optimal set-up, or if you'd set up the disc layout differently.

We could spend another £5k for an external Raid setup to allow the Tempdb & backups to be RAID'ed as well, but I think it's a bit of overkill.

Any thoughts?

share|improve this question

migrated from superuser.com Sep 16 '09 at 19:26

This question came from our site for computer enthusiasts and power users.

    
The number of data files within a single filegroup should equal to the number of CPU cores. (technet.microsoft.com/en-gb/library/cc966414.aspx) - Wow. Really? –  Dave Beer Sep 17 '09 at 13:55
add comment

5 Answers 5

up vote 4 down vote accepted

You should use RAID 1 or 10 if disk space is not your main problem, because they are a lot faster than RAID 5, especially for writes; also, RAID 5 is quite useless with only 3 disks, it gets more and more useful the bigger the arrays get.

I'd also avoid using a single non-raid disk for tempdb: if it breaks you will not lose anything, but your server will be down until you can replace it.

I'd configure the disks in 3 RAID 1 volumes of 2 disks each, one for data, one for transaction logs and one for tempdb; this leaves a disk, which you can use to RAID the backup volume or as a global hot spare; I'd avoid making the data volume a RAID 5 one, as this would make it slower.

If you need more data space and can cope with the loss of tempdb, you can use 4 disks in RAID 10 for data, 2 disks in RAID 1 for transaction logs and one single disk for tempdb.

You're not likely to need more than 2 RAID 1 disks for transaction logs: they shouldn't grow up too much, if you're backing them up regularly; and you should.

share|improve this answer
    
I was under the impression that RAID-1 (mirror) has slower writes than RAID-5 because RAID-1 only has the write speed of the slowest member (writes are always in parallel to both members) while RAID-5 can distribute parallel writes independently due to the parity distribution. We just installed a RAID-50 to get the most out of 8 disks for database access. –  hurikhan77 Sep 16 '09 at 22:08
    
RAID 5 always has a write overhead due to parity checks. RAID 1 is slower than RAID 10 (which actually can parallelize writings), but both of them are faster than RAID 5. –  Massimo Sep 16 '09 at 22:14
1  
A good hardware RAID controller should be able to recalculate the parity without reading all disks. I assume CPU load is not part of the calculation in such a setup. Combined with a file system optimized for the stripe size this should yield very good performance (at least RAID-50). I've digged through many articles which all came to the conclusion to use RAID-5/50 for database workloads or even an allround player. –  hurikhan77 Sep 16 '09 at 22:22
    
+1 for suggesting not to use a non-RAID disk for tempdb. –  Evan Anderson Sep 16 '09 at 22:33
    
I wouldn't say "RAID 5 is quite useless with only 3 disks" [my emphasis], but it does have 50% space overhead (worst case) but still a lot lower than the 100% overhead of mirroring. –  Richard Sep 17 '09 at 9:43
add comment

Read this by Kendal Van Dyke, not only has plenty of links to great articles but explains the different possible setups and performance implications.

I'd do something like this, but it depends in your server load and hardware capabilities.

  • Hardware Raid-5 for Data
  • Hardware Raid-5 for Logs
  • Raid 5 (or 1) for Tempdb
  • Raid 1 for on-server backups
share|improve this answer
add comment

RAID 10 is quicker than RAID 5. Other than that, the spec doesn't look too bad.

share|improve this answer
    
I'm not sure RAID 1+0 is an option. I think the server's controllers support RAID 0, 1 or 5. And SAS Drives aren't an option unfortunately. –  Dave Beer Sep 16 '09 at 19:44
1  
Pity, because SAS drives have the great ability to order the request queue not sequentially, but in the order that they appear on the current revolution of the disk. So 4 random reads that are after eachother on the disk platter will only take 1 RPM to retrieve, whereas on a SATA disk it takes 4. I know what you mean about SAS though, who wants to pay $800 for a 74gb drive –  Mark Henderson Sep 16 '09 at 21:10
    
+1 for teaching me something ;) –  user3914 Sep 17 '09 at 7:26
    
@Dave: If SAS drives are not an option, how about VelociRaptors --- much of the performance gain but not as expensive. –  Richard Sep 17 '09 at 9:44
    
Yeah, I have a VelociRaptor in my Dev machine. (Tempted to try an SSD next, because my laptop is blistering with one) I don't procure the hardware (another team do that) but will try and make the recommendation. –  Dave Beer Sep 17 '09 at 13:46
add comment

I would also recommend RAID10. The only drawback is you need a minimum of 4 disks per RAID10 array, so you'll be stuck without a dedicated array for the Tempdb and on server backups (assuming you put the db files and log files each on their own array).

So you'll have:

RAID1 (2 drives) = OS and SQL binaries; RAID10 (4 drives) = DB files; RAID10 (4 drives) = Log files; Tempdb = ?; On server backup = ?

share|improve this answer
1  
You really don't need RAID 10 for transaction logs, as they are written to and read from sequentially; you would not get any benefit other than bigger space, which TL usually don't need. –  Massimo Sep 16 '09 at 19:40
    
Probably don't need RAID10 for the OS and SQL binaries either. Once they're in memory they won't be touched much. –  Mark Henderson Sep 16 '09 at 21:08
    
Maybe that's the reason he suggested RAID 1 for the OS ;-) –  Massimo Sep 16 '09 at 22:17
add comment

I'd be tempted to do the following:

  • Raid 10 User Database data file (4 disks)
  • Raid 1 User Database log file (2 disks)
  • Raid 1 TempDB and System databases (2 disks)

Backup across network to another server if possible.

Go 64bit with at least 8GB RAM.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.