Raid Level for SQL

Question

Experts,

For SSD Disks which is the best RAID level :

For Data Files
For Log Files
For Tempdb

Answer 1

Hi @Vishu ,

RAID 0 is a disk array with no redundancy and no parity. Data is distributed on each disk at the same time, and there is no fault tolerance. The read and write speed is the fastest in RAID, but because any one disk is damaged, the entire RAID system will fail, so the safety factor is lower than that of a single disk.
Working principle: RAID0 distributes data evenly on each disk in the array in the form of stripes (2 or more, the minimum is 2)
Advantages: extremely high disk read and write efficiency, there is no verification, does not take up too much CPU resources, and the design, use and configuration are relatively simple
Disadvantages: no redundancy, cannot be used in environments with high data security requirements

RAID 1 is a mirrored disk array. Each disk has a mirror disk, and the mirror disk keeps the same content as the original disk at any time. RAID1 has the highest security, but only half of the disk space is used to store data. Mainly used in occasions where data security is very high and the damaged data can be recovered quickly
Working principle: RAID1 uses mirroring as a redundancy method to make multiple copies of data on virtual disks and place them on member disks (2N, (N≥1), the minimum is 2)
Advantages: It has 100% data redundancy, provides the highest data security guarantee, theoretically can achieve 2 times the reading efficiency, and the design and use are relatively simple
Disadvantages: high overhead, space utilization is only 50%, little improvement in write performance

RAID 5 is a parity disk array without independent parity disks. The parity check is also used to check errors, but there is no independent check disk. Instead, a special algorithm is used to calculate the storage location of any band check block. This ensures that any read and write operations on the parity block will be balanced among all RAID disks, which not only improves system reliability but also eliminates the possibility of bottlenecks, and is good for both large and small data reads and writes. performance.
Working principle: RAID5 adopts an independent access array method, and the check information is evenly distributed to each disk in the array (3 or more, the minimum is 3)
Advantages: relatively high read performance, medium write performance, distributed access to check information, avoiding write operation bottlenecks
Disadvantages: The controller design is complicated, and the disk reconstruction process is more complicated

RAID (10)1+0 is to first make the members in the disk group form RAID 1 to mirror each other, and then strip RAID 0 between the disk groups, that is, to mirror and then partition the data, and then divide all the hard disks into two groups, which is regarded as It is the lowest combination of RAID 0, and then these two groups are operated as RAID 1
Working principle: RAID10 combines RAID1 and RAID0, mirroring first, then striping (2N, (N≥2), minimum is 4)
Advantages: high read performance, better write performance, good data security, allowing N disk failures at the same time
Disadvantages: The space utilization is only 50%, and the overhead is large. It is not allowed to damage the above Disk0 and Disk2 or Disk1 and Disk3 together.

RAID (01)0+1 is the opposite of the RAID 10 procedure. It partitions and then mirrors the data to two sets of hard drives. As long as one hard disk of RAID 01 is damaged, all hard disks in the same group of RAID 0 will stop functioning. Only the hard disks of other groups will operate. The reliability is low. Except for data distributed on multiple disks, each disk has its own. Physical mirroring disk, providing full redundancy, allowing one or less disk failures without affecting data availability, and having fast read and write capabilities
Working principle: It divides all hard disks into two groups to become the lowest combination of RAID 1, and treats the two hard disks as RAID 0 operation (number of disks N>=4)
Advantages: good write performance, allowing DisK0 and Disk1 to be damaged together and still work normally
Disadvantages: When a disk is damaged in a group, the RAID 0 group in which it is located is considered damaged, and RAID 0+1 has actually degenerated into a RAID 0 structure

RAID 1+0 and RAID 0+1 are shown as follows:

Best Practices:
• RAID 1 is often chosen to store operating systems, binaries, index file groups and database transaction log files. Critical to log and index performance is fault tolerance and a good write speed.
• Since log file are written sequentially and are read only for recovery operations, recommended RAID for Log Files is RAID 1 or 10. If your RAID 1 is at a 100% usage, choose RAID 10 for better performance.
• For data files with random access and read heavy data volumes, striping is important. So recommended RAID is 5 or 10.
• For data files which require good write performance, RAID 10 is recommended. Use a battery backed caching RAID controller for better write performance
• For tempdb files with good read/write performance RAID 0, 1 or 10 is recommended. Although tempdb contains temporary data and DBA’s often go in for RAID 0 for tempdb, keep in mind that SQL Server requires tempdb to carry out many of its activities. So if you want your system to be always available, rethink RAID 0.
• Putting log files or tempdb on a RAID 5 array is not recommended, since RAID 5 does not perform well for write operations. DBA's although have conflicting opinions over this point.

Please refer to this log: https://sqldbarealtime.blogspot.com/2015/03/raid-levels-and-recommendation-for-data.html

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks , primarily performance