3

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?

Improve this question
1

5 Answers 5

Reset to default
4

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.

Improve this answer
5
  • 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, 2009 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, 2009 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, 2009 at 22:22
  • +1 for suggesting not to use a non-RAID disk for tempdb.
    – Evan Anderson
    Sep 16, 2009 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, 2009 at 9:43
2

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
Improve this answer
1

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

Improve this answer
5
  • 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, 2009 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, 2009 at 21:10
  • +1 for teaching me something ;)
    – user3914
    Sep 17, 2009 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, 2009 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, 2009 at 13:46
0

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 = ?

Improve this answer
3
  • 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, 2009 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, 2009 at 21:08
  • Maybe that's the reason he suggested RAID 1 for the OS ;-)
    – Massimo
    Sep 16, 2009 at 22:17
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .