quick sql setup check

davetherave2 · 31 Jul 2013 at 11:58

Just got a new server with 16 x 10K (300GB) disks and just want to make sure this is the best way to set up the disk config for sql 2012

Disk 1 - 2 (OS Raid 1) (2 disks)
Disk 3 - 7 (Data Drive Raid 5) (5 disks)
Disk 8 - 12 (Transaction log drive Raid 5) (5 disks)
Disk 13 - 14 (Database file backup drives Raid 1) (2 Disks)
Disk 15 - 16 (Hot swap spares) (2 disks) - In event of failure

Does this seem sensible or should I be looking at a different setup.

Has been a while since I set up a server and would usually consider this to be a safe bet for setup for sql but just wondering if there is a better config.

Thanks in advance.

Little_Crow · 31 Jul 2013 at 12:42

Transaction logs are generally what you need the fastest disk access for, so RAID1 or 10 is recommended.
This is a very generic recommendation you'll read just about everywhere, but it really boils down to how busy your DB will be.

Backup drive is really your preference, RAID1 is probably overkill for it, but it depends on your tape backup regime, how long you have to keep backups on disk, and how big your transaction logs grow and how quickly that would become a problem without backups.

davetherave2 · 31 Jul 2013 at 12:56

thanks for the info.

The backup drive is purely to put full file backup files to be able to restore quickly in the event a database goes down.

We have a barracuda backup solution in place to do full backup's etc.

Will there be much of a performance decrease from having the transaction logs on a raid 5 as opposed to raid 10

ASE001 · 31 Jul 2013 at 16:20

SQL Data is mainly sequential writes and reads. So RAID 5 is perfectly adequate, especially if the RAID control has a Read/Write cache. Make sure it has battery backup though.
SQL transaction logs are mainly random read/writes, which RAID 5 is not particular good at, even when it has a read/write cache. RAID 1 or RAID 10 by it's nature is faster at random access. In fact the more spindles used in the stripe the better!
You should separate out the following onto separate disks if you are looking for performance.

System Database RAID 5
Data RAID5
Logs RAID 10
Tempdb data RAID 5
Tempdb logs RAID 10
Backup RAID 5 (cheap SATA or similar)
Indexes (Maybe?) RAID 10

If you combine Tempdb logs and data use RAID 10

Don't forget that all volumes should be formatted with an Allocation Size of 64K except for the backup volume which is best left at default.

With Server2012 and SQL2012 MS have gone backwards in that you should again do partition alignment

like the old days (Server 2003 and before); see http://support.microsoft.com/kb/2023571 or you could get performance issues?

davetherave2 · 31 Jul 2013 at 19:37

well this will be going onto a 2008 r2 server with sql 2012 standard license.

I have advised to move the tlog drive to raid 10 (6 disks) which should give us enough head room for now. If we need to upgrade performance in the future then we will attach a dedicated san array to handle more disk i/o

it certainly gives me more things to look at.