Raid 0 relays on stripping. No Fault Tolerance. If a disk fails data can be recovered using special tools, however, this data will be incomplete and most likely corrupt, and data recovery is typically very costly and not guaranteed. Performance is sightly better than other Raid systems in random and sequentials read/writes.

Raid 1 relays on mirroring. If a disk fails, the data is available in the other disk. RAID 1 can be an effective protection against physical disk failure, it does not provide protection against data corruption due to viruses, accidental file changes or deletions, or any other data-specific changes. Raid 1 is great in sequentials writes/read, cause of mirroring. Raid 1 is not good as Raid 5 in random operations.

Raid 5 relays on stripping with parity. It saves the partity of one disk into the others. Raid 5 is very powerfull in random reads. Is not good as Raid 0 or 1 in sequential ones and random writes. Raid 6 is similar to Raid 5 xcept for an additional parity block. If a disk fails, the data can be recover througth the other disks. It has an performance penalty on write operations because of the overhead associated with parity calculations.

Raid 10 relays on stripping with mirroring. All but one drive from each RAID 1 set could fail without damaging the data. However, if the failed drive is not replaced, the single working hard drive in the set then becomes a single point of failure for the entire array In most cases RAID 10 provides better throughput and latency than all other RAID levels except RAID 0 (which wins in throughput).

RAID 0: Offer only disk striping and there is no redudancy. No redudancy - no fault tolerance, if disk fails all data is lost. RAID 0 is not recommended for storing SQL Server data files, log files or operating system files.

RAID 1: known as mirroring (duplicates your data disk). High level of fault tolerance. Raid1 is costly bacause you must double the number of disks. RAID 1 support split seeks, so both disk drives perform read operation. RAID 1 can support twice the number of random reads that a single disk can. Writing to RAID 1: write capacity of a single disk drive.

RAID 5: Good level of fault tolerance.It's an economical, fault-tolerant choice. Fast with random reads, all disk drives can perform read operation. Slow with write operation, when data is written to RAID 5, disk stripe and the parity must be read, parity need to be calculated and then both stripes must be written.

RAID 10: Mirroring a disk stripe, each disk have a duplicate. High performance and high level of fault tolerance, but very expensive. Good random read performance, all disk drives can perform read operation. Write performance of RAID 10: same as with RAID 1.

1. Disk array configuration:

   OS files and SQL binaries:RAID 1

   Data files: if database is read only use RAID 5 (or >= 90% read and 10% write), for other databases use RAID 10 (better write performance).

   Log files: dedicated RAID 10 (or RAID 1 ) for better write performance

   Tempdb: Best to use RAID 10

Raid level 0,1 and 5 are typically implemented in SQL Server.

Raid Level 0 is also known as disk striping because it uses a disk file system called a stripe set. Theis method divides Data into blocks and spread in a fixed order among all disks in an array. This level improves read and write performance by spreading operations across multiple disks. Operations can then be performed independently and at the same time.

Raid Level 1 Also known as disk mirroring because it uses a disk file system called a mirror set. Disk mirroring provides a redundant, identical copy of a selected disk. All data written to the primary disk is written to the mirror disk. RAID 1 provides fault tolerance and generally improves read performance but may degrade write performance.

Raid Level 2 is not as efficient as other RAID levels and is not generally used. This level adds redundancy by using an error correction method that spreads parity across all disks.

Raid Level 3 RAID 3 provides some read-and-write performance improvement.

Raid Level 4 uses larger blocks for striping. RAID 4 is not as efficient as other RAID levels and is not generally used.

Raid Level 5 Also known as striping with parity, this level is the most popular strategy for new designs. RAID 5 is similar to RAID 4, because this level stripes the data in large blocks across the disks in an array. However, RAID 5 differs because it writes the parity across all the disks. Data redundancy is provided by the parity information. The data and parity information are arranged on the disk array so that the two types of information are always on different disks. Striping with parity offers better performance than disk mirroring (RAID 1). However, when a stripe member is missing, read performance is decreased, for example, when a disk fails.

Another combination is Raid 10 which provides highest performance in read-write. It is also is also known as mirroring with striping. RAID 10 uses a striped array of disks that are then mirrored to another identical set of striped disks. For example, a striped array can be created by using five disks. The striped array of disks is then mirrored using another set of five striped disks. RAID 10 provides the performance benefits of disk striping with the disk redundancy of mirroring. RAID 10 provides the highest read-and-write performance of any one of the other RAID levels, but at the expense of using two times as many disks.

RAID 0: improves read and write performance by spreading operations across multiple disks, no redundancy, does not compete for processor cycles, no fault tolerance.

RAID 1: Its fault tolerance and generally improves read performance but may degrade write performance, provides a redundant, identical copy.

RAID 5: it writes the parity across all the disks. Data redundancy is provided by the parity information. RAID 5 provides excellent read performance except when a stripe member is missing. RAID 5 has penalty for write performance because of the parity in check.

RAID 10 (it’s also known as 1+0): known as mirroring with striping. RAID 10 provides the performance benefits of disk striping with the disk redundancy of mirroring. RAID 10 provides the highest read-and-write performance of any one of the other RAID levels, but at the expense of using two times as many disks. its most fault tolerance.

How does this question related to SQL. Should it be leave to the network hardware guy?

@sqldude:

This has a lot to do with SQL. As was pointed out above RAID 1 (and RAID10) are better at sequential reads and writes, while RAID 5 is typically better at random IO.

What does that mean to the DBA?

It means that data files (and index files if you separate out indexes into their own file group(s)) are best placed on RAID5 arrays. It also means that transaction log flies, which are generally read and written to sequentially (read sequentially in backup operations and written to sequentially in day-to-day operations) are best placed on RAID 1 or RAID10 arrays.

One thing that hasn't been mentioned is the cost in storage.

With RAID5, you lose the capacity of one disk. This makes it more effective to have more disks in a RAID5 stripe. In a RAID5 stripe with 3 disks, you lose 33% to parity. In a RAID5 strip with 10 disks, you lose 10% to parity.

With RAID 1 (mirroring), you must have two disks and you lose half of the storage to the mirror. With RAID 10 (mirrored stripes), you must have an even number of disks and you still lose half of them to the mirror.

RAID 0 (striping) is not a bad choice for backups that will be written to tape. You get all the storage of all the disks and the benefit of having many spindles to write across. Since the files will only stay on disk for a short period of time, many people consider this an acceptable risk.

Some really good answers have come out mostly around what RAID is. So from a best practices perspective what are the RAID level decisions you would take for your production databases for say non-critical data, clustering, Backup, DW data, OLTP data, tempDB etc?

It would be great if you can share those decisions and the reasons for the same.

Marc - You surely have touched parts of those.

sqldude - Though this is done by the HW guys, it is critical to suggest an deployment strategy else you are not going to get optimal performance.

On a contrary, what if you are on an Virtualized environment? Share your answers for benifit of all.

@vinodk_sql

The questions around storage area networks and virtualized environments are questions that I've asked to Microsoft, EMC and NetApp. The answer has always been "it depends".

For example, if I'm using dedicated SAN storage on a virtual machine vs. SAN storage on a physical machine, over the same connection type (iSCSI vs. FibreChannel), then the guidance above stands. By dedicated, I mean all data goes on to dedicated RAID5 storage, all logs go on to dedicated RAID1/10 storage, etc. This limits the number of spindles and read/write heads to the number dedicated to that pool of storage.

But what happens when you use storage that spans all the drives in a SAN, but is shared by multiple workloads (SQL Server, Exchange, File Services,etc)? Does the benefit of the huge number of spindles and read/write heads outweigh the cost of working with mixed workloads and mixed utilization patterns?

The answer I've always gotten from vendors is "It depends" or "Why don't you test and let us know".

What I have found is that on huge (100TB+) data warehouses, it is often beneficial to have a dedicated SAN, with dedicated storage. Mixing workloads interferes with performance.

But what about smaller workloads like data marts or transactional systems? "It depends"

There is also a cost-benefit ratio to pay attention to. Dedicated, provisioned storage means that empty storage is sitting empty. It's been bought, but is not currently providing business benefit. While there may be a performance cost in co-mingling workloads, there is a financial benefit.

Tabulate the RAID Level considerations based on - Reliability, Storage Efficiency, Random Read, Random Write, Sequential Read, Sequential Write and Cost etc. http://www.pcguide.com/ref/hdd/perf/raid/levels/comp.htm

callout any specific workloads or deployment you do on specific RAID Levels

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel1-c.html RAID Level 1 Recommended Uses: Applications requiring high fault tolerance at a low cost, without heavy emphasis on large amounts of storage capacity or top performance. Especially useful in situations where the perception is that having a duplicated set of data is more secure than using parity. For this reason, RAID 1 is popular for accounting and other financial data. It is also commonly used for small database systems, enterprise servers, and for individual users requiring fault tolerance with a minimum of hassle and cost (since redundancy using parity generally requires more expensive hardware.)

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel2-c.html RAID Level 2 Recommended Uses: Not used in modern systems.

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel3-c.html RAID Level 3 Recommended Uses: Applications working with large files that require high transfer performance with redundancy, especially serving or editing large files: multimedia, publishing and so on. RAID 3 is often used for the same sorts of applications that would typically see the use of RAID 0, where the lack of fault tolerance of RAID 0 makes it unacceptable

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel4-c.html RAID Level 4 Recommended Uses: Jack of all trades and master of none, RAID 4 is not as commonly used as RAID 3 and RAID 5, because it is in some ways a "compromise" between them that doesn't have a target market as well defined as either of those two levels. It is sometimes used by applications commonly seen using RAID 3 or RAID 5, running the gamut from databases and enterprise planning systems to serving large multimedia files.

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel5-c.html RAID Level 5 Recommended Uses: RAID 5 is seen by many as the ideal combination of good performance, good fault tolerance and high capacity and storage efficiency. It is best suited for transaction processing and is often used for "general purpose" service, as well as for relational database applications, enterprise resource planning and other business systems. For write-intensive applications, RAID 1 or RAID 1+0 are probably better choices (albeit higher in terms of hardware cost), as the performance of RAID 5 will begin to substantially decrease in a write-heavy environment.

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel6-c.html RAID Level 6 Recommended Uses: In theory, RAID 6 is ideally suited to the same sorts of applications as RAID 5, but in situations where additional fault tolerance is required. In practice, RAID 6 has never really caught on because few companies are willing to pay for the extra cost to insure against a relatively rare event--it's unusual for two drives to fail simultaneously (unless something happens that takes out the entire array, in which case RAID 6 won't help anyway). On the lower end of the RAID 5 market, the rise of hot swapping and automatic rebuild features for RAID 5 have made RAID 6 even less desirable, since with these advanced features a RAID 5 array can recover from a single drive failure in a matter of hours (where without them, RAID 5 would require downtime for rebuilding, giving RAID 6 a substantial advantage.) On the higher end of the RAID 5 market, RAID 6 usually loses out to multiple RAID solutions such as RAID 10 that provide some degree of multiple-drive fault tolerance while offering improved performance as well.

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel7-c.html RAID Level 7 Recommended Uses: Specialized high-end applications requiring absolutely top performance and willing to live with the limitations of a proprietary, expensive solution. For most users, a multiple RAID level solution like RAID 1+0 will probably yield comparable performance improvements over single RAID levels, at lower cost.

http://www.pcguide.com/ref/hdd/perf/raid/levels/mult.htm RAID Levels 0+1 (01) and 1+0 (10) Recommended Uses: Applications requiring both high performance and reliability and willing to sacrifice capacity to get them. This includes enterprise servers, moderate-sized database systems and the like at the high end, but also individuals using larger IDE/ATA hard disks on the low end. Often used in place of RAID 1 or RAID 5 by those requiring higher performance; may be used instead of RAID 1 for applications requiring more capacity.

http://www.pcguide.com/ref/hdd/perf/raid/levels/mult.htm RAID Levels 0+3 (03 or 53) and 3+0 (30) Recommended Uses: Not as widely used as many other RAID levels. Applications include data that requires the speed of RAID 0 with fault tolerance and high capacity, such as critical multimedia data and large database or file servers. Sometimes used instead of RAID 3 to increase capacity as well as performance.

http://www.pcguide.com/ref/hdd/perf/raid/levels/mult.htm RAID Levels 0+5 (05) and 5+0 (50) Recommended Uses: Applications that require high fault tolerance, capacity and random positioning performance. Not as widely used as many other RAID levels. Sometimes used instead of RAID 5 to increase capacity. Sometimes used for large databases

RAID Levels 1+5 (15) and 5+1 (51) Recommended Uses: Critical applications requiring very high fault tolerance. In my opinion, if you get to the point of needing this much fault tolerance this badly, you should be looking beyond RAID to remote mirroring, clustering or other redundant server setups; RAID 10 provides most of the benefits with better performance and lower cost. Not widely implemented.

There are various levels of RAID r(edundant array of independent disks) available out of which 0,1, and 5 usually implemented with SQL Server. In Total there are 7 Levels available. RAID 0, RAID 1, RAID 2, RAID 3, RAID 4, RAID 5, RAID 10 (1+0) OR RAID 01.

RAID 0 This level is also known as disk striping because it uses a disk file system called a stripe set. Data is divided into blocks and spread in a fixed order among all disks in an array. RAID 0 improves read and write performance by spreading operations across multiple disks. Operations can then be performed independently and at the same time.

RAID 1 (BETTER READ OPERATIONS) This level is also known as disk mirroring because it uses a disk file system called a mirror set. Disk mirroring provides a redundant, identical copy of a selected disk. All data written to the primary disk is written to the mirror disk. RAID 1 provides fault tolerance and generally improves read performance but may degrade write performance.

RAID 2 This level adds redundancy by using an error correction method that spreads parity across all disks. RAID 2 also uses a disk-striping strategy that divides a file into bytes and spreads it across multiple disks. This strategy offers only marginal improvement in disk use and read-and-write performance over mirroring (RAID 1). RAID 2 is not as efficient as other RAID levels and is not generally used.

RAID 3 This level uses the same striping method as RAID 2, but the error correction method requires only one disk for parity data. The amount of disk space used varies with the number of data disks. RAID 3 provides some read-and-write performance improvement.

RAID 4 This level uses striped data in much larger blocks or segments than RAID 2 or RAID 3. Like RAID 3, the error correction method requires only one disk for parity data. This feature keeps user data separate from error-correction data. RAID 4 is not as efficient as other RAID levels and is not generally used.

RAID 5 (BETTER WRITE OPERATIONS) Also known as striping with parity, this level is the most popular strategy for new designs. RAID 5 is similar to RAID 4, because this level stripes the data in large blocks across the disks in an array. However, RAID 5 differs because it writes the parity across all the disks. Data redundancy is provided by the parity information. The data and parity information are arranged on the disk array so that the two types of information are always on different disks. Striping with parity offers better performance than disk mirroring (RAID 1). However, when a stripe member is missing, read performance is decreased, for example, when a disk fails.

RAID 10 (BETTER READ /WRITE OPERATIONS) This level is also known as mirroring with striping. RAID 10 uses a striped array of disks that are then mirrored to another identical set of striped disks. For example, a striped array can be created by using five disks. The striped array of disks is then mirrored using another set of five striped disks. RAID 10 provides the performance benefits of disk striping with the disk redundancy of mirroring. RAID 10 provides the highest read-and-write performance of any one of the other RAID levels, but at the expense of using two times as many disks.

Giving some comparison amond different RAID levels.

RAID Implementation:Hardware-based RAID 3, 5, or 10 Advantage:Excellent performance. Does not compete for processor cycles. DisAdvantage:Higher Cost

RAID Implementation:Hardware-based RAID 1 Advantage:Excellent redundancy. Does not compete for processor cycles. DisAdvantage:Additional cost because of more hardware.

RAID Implementation:Hardware-based RAID 10 Advantage:Excellent performance. Excellent redundancy. DisAdvantage:Additional cost because of more hardware.

RAID Implementation:Windows 2000-based RAID 5 Advantage:Excellent read performance. Low cost. DisAdvantage:Uses system processing resources.

(Reference - Microsoft)