RAID (Redundant Array Independent Disk) As disk/storage plays a very important role in any application, everything is saved on disk sql server is no exception. Generally all activity happens on memory as memory is very fast compared to Disk. Earlier days disk were attached to server but as system become huge and enterprise this will not fulfill the requirement and if any disk gets failed all our data will be lost. So for redundant data multiple disk can be grouped together. There are different types of RAID per their functionality. RAID 0, 1, 5, 6 and to manipulate it can also be grouped as 1+0 and 0+1. RAID 0 :
- it does not provide any redundancy for your data,
- no data backup if any disk fails all you data will be lost.
- It distributes the data on multiple disk called stripping.
- Good for read and write as well.
- It is called Mirroring as it mirrors the data of one disk to other
- Double the cost of the disk.
- it is that’s the reason it requires double the disk required. (only half of the disk would be in used).
- Good for Read but ok for low on Write
- it is called “stripping with Parity”,
- it keeps one disk extra for parity and that disk is having redundant,
- so one Parity disk will be stand by to be used if it fails but if two disk gets failed one disk data will be lost.
- Data will be distributed on by other equally with Parity set.
- Good Read but not for Write.
- This is similar to RAID 5 but it is added double parity means now for this it provides backup for two disk
- Keeps parity check for 2. Double work.
- Requires 2 disk extra and two disk failover can be survived.
- Good Read but very low Write.
RAID 1 + 0:
- This is a combination of RAID 1 and RAID 0.
- Means first it will mirror the disks and that mirrored disk it will Strip to distribute the data.
- It also requires double the disk for mirroring.
- Good Write but low Read
RAID 0 +1:
- Combination of RAID 0 and RAID 1
- Means first it will Strip (distribute) the disks and that disk it will mirrored the data.
- It also requires double the disk.
- *single disk fails on one side of array will allow data loss (higher risk of data loss) .
RAID is generally on multiple DISKS mostly SAN environment. So you need to co-ordinate with SAN administrator to get more information.
Sql server files placement is recommended to be on separate Disk for DATA, LOG and TEMPDB files.
Data File: sql server retrieves the data from data file into memory and work on memory and periodically (on Checkpoint/LazyWritter) it will write back to disk. So generally Data file requires DISK which is good for READING
data from disk.
Data File: RAID1, RAID 1+0, If very small system RAID 5.
Log File: for any changes on the data on memory all the changes has to be recorded on the log file. So log file is need good disk with Writing data sequentially.
Log File: RAID1, RAID 1+0, If very small system RAID 5.
Tempdb: It require frequent read and write should be good. As Tempdb is common for all the databases.
Tempdb: RAID1, RAID 1+0, If very small system RAID 5.
Reference : Troubleshooting SQL Server A Guide for the Accidental DBA by Jonathan Kehayias and Ted Krueger — Its free