asked
RAID levels
Hi all,Can someone please tell me, What are different RAID levels best to store the data files and log files of different databases in sql server and why?
The explaination I got when attending an SQL Server administration course some years ago was: - Use RAID5 for data-files. This is because performance can improve in writing (and reading?) speed if the data can be spread over several disks. - Use RAID1 for log files. This is because they are read and written sequentially. It was also said that the "ultimate" performance on a RAID5 is acieved if you create as many datafiles as there are disks in the RAID5-set. The reason for this I don't know - it had been discovered by extensive testing.
In addition to what I wrote - try keeping backup-files and data-files on separate arrays, or even separate controllers if possible. And yes, I agree that the full answer really is "it depends", there are many factors to consider. But for most OLTP installations, I think my suggestion is a decent setup.
This is not a straight forward question. Some RAID configs are more suited to some system activities better than others - OLAP reporting is better on one sort whereas a system that cannot tolerate data loss would be best on another. the number of spindles available will affect what RAID you can implement. Yes, split Log files on one array and data files on another, simply to avoid contention between the log and data updates, the actual config of the arrays however is a deeper decision to make. You will need to review lots of technical papers to understand the trade-offs of one config over another so that you can inform your business managers of the risks/benefits and they can choose what the business needs. A good start on general raid options is [
http://en.wikipedia.org/wiki/RAID][1], in connection with SQL Server, this is a good start [
http://msdn.microsoft.com/en-us/library/ms190764.aspx][2] [1]:
http://en.wikipedia.org/wiki/RAID [2]:
http://msdn.microsoft.com/en-us/library/ms190764.aspx
And, as I understand it, this whole area has been muddied by certain SAN vendors as well. The answer "It Depends" is certainly valid here. As FatherJack says, it depends on what sort of data activities are going on. Get a clear picture of the read/write activity, and then you can figure out which bits need the higher performing RAID levels / LUNs (for those of you with SANs).
FatherJack is right (+1), this is indeed a deep question. But let me add to what he said that SQL Server 2005 Administration (published by Wrox, multiple authors including Brian Knight and Brad McGehee) in Chapter 11 has a very good overview of this topic that also touches on SANs and other related topics. This remains just an overview, but it is a good one. Now, to provide a direct, overly simplistic answer to the question: If you do not know what type of raid to use and for whatever reason cannot do the research to properly determine the best one for your use case, use Raid 5 and split the log files and data files. The reason Raid 5 is often a safe bet is that it provides a good balancy of redundancy (protection from hardware failure) and speed, without sacrificing too high a percentage of the storage capacity of the drives used. It may not be the best choice for your situation (or it might, you won't know without actually doing the research), but it is the safest one to bet on if you cannot or will not do a thorough analysis for your particular use case. One other related note, try to use harddrives from separate manufacturing batches if possible. The value of redundant discs is greatly diminished if the time of failure of the disks is not an independent event. But the time of failure of discs that are from the same batch and experiencing roughly the same usage pattern is not independent, you expect them all to fail at roughly the same time.
Good points Timothy, I haven't seen the book you mention but based on those authors I would recommend it as a good resource. Hadnt thought about disk batches, will consider that when making future purchases. Thanks. Oh +1 too.
I'm just going to add an answer here, because nobody has said anything about filegroups. It's not a matter of simply splitting data and logs. If for example, you have a very large data dump table that is the subject of a lot of concurrent reporting processes, then you will probably find that you get benefits from using a stripe type array (think 10 or 60 here), because those array types have the highest throughput characteristics, and tend to favour sequential access. Configuration type data, that is often updated, and where updates are spread over the entirety of the existing data set can be catered for better by RAID 6. Notice the 6 and not the 5. The [Battle Against Any Raid F][1] site makes a good read if you're really interested. So, the bottom line - think about all the data in your system, ensure that filegroups are split such that the access characteristics for all data in each individual filegroup are consistent, then choose array types and spindles that serve the purpose as best you can within your budget. [1]:
http://www.baarf.com/
The standard answer for this is RAID 5 for data and RAID 1 for logs. With the justification that the logs are sequential writes and the data files are random reads/writes. This is mostly true if your server has only 1 database on it. As soon as you have multiple log files on your logs disc then you now have random writes happening and RAID 1 may no longer be a good choice. In this case your options are 1. Stick with the RAID 1. 2. Move to RAID 5 (or 6 or whatever level fits). 3. Stick with RAID 1 but have a seperate array for each log file. Obviously your final choice will be based on how busy your server is, what performance is considered good enough, how valuable your data is and how much money you have. I will also re-iterate what has been said in almost every other answer to this question. There is no one size fits all disc configuration for best SQL server performance. You need to have an understanding of how SQL server uses the discs, what sort of load your server is going to handle and then do some testing. Hopefully though we have given you a few ideas for where to start and what you need to consider.
