SQL Server Performance Forum – Threads Archive
RAID configuration question
I would like to get some experienced input on which RAID configuration any of you would recommend for my current situation. This is timely because I am currently building 2 new SQL Servers to support an existing application#%92s data. * Our company is an outsourcing company compiling government compliance reports for numerous Fortune 1000 companies#%92 HR Departments. * The app is a client/server app (heavy on the client). Inserts, updates, and deletes are intense for each database on a quarterly basis. (Monthly in some cases) * We#%92ll have between 30 – 40 databases on each server with 10-15 of those databases (per server) in the quarterly ‘active#%92 mode on any given day. * The databases range in size from 50 MB to 8 GB. * The app cannot support multiple instances of SQL Server due to the use of an older InterSOLV ODBC driver on the front-end. * I do not have the option of a SAN or any external storage devices, so I#%92m stuck with just the 8 drive bays available in each server for storage. * The servers will be dedicated to SQL Server running Win 2000 Server. One is a Dual Pentium Xeon 400 and the other is a Quad Xeon 400. Each will have 2 GB of memory. Each will have new, identically sized 10K RPM SCSI drives. (Yes, I#%92m dealing with ‘reallocated#%92 [a.k.a. USED] hardware.) * Tuning of the app or it’s SQL code is not an option at this point. The app is being completely rewritten for release next year. I#%92ve been planning to run a configuration of: * Array 0 (Data Array): 3-Disk RAID 5 Array* Array 1 (Log Array): 3-Disk RAID 5 Array
* Array 2 (OS/Backup Array) : 2-Disk RAID 1 Array The reasoning behind the striped array for the logs is that multiple databases will be accessed at a given time thus the idea of sequential writing to the logs goes out the window, anyway. However, I had a thought about possibly configuring in this manner: * Array 0 (Data and Log Array): 6-Disk RAID 10 Array
* Array 1 (OS/Backup Array): 2 Disk RAID 1 Array The reasoning behind RAID 10 being the better performance of RAID 0 in the constant WRITING/DELETING that will be taking place in these databases. However, will the performance increase in the different RAID config outweigh the performance hit taken because the DATA and LOGS are on the same array? Any thoughts, comments, other ideas, etc. are greatly appreciated. Thanks, Steve
I would go with the following setup:
OS RAID 1 (2 disks array)
if you have multiple Chanels on you RAID controller
LOGS RAID 10(2 disk array)
DATA RAID 10(4 disk Array) I would also suggest using multiple physical files in you file groups for example:
if you implement the above create 4 physicals os files for a single filegroup Then SQL Server will be able to use up to 4 threads per filegroup(one for each physical file) as opposed to a single thread you should see some improvement with this configuration…
Thanks, Big Jim. The multiple thread idea is a good one. I’ll be checking into it. One thing I don’t understand about the configuration in your response… "LOGS RAID 10(2 disk array)" This would essentially be RAID 1, wouldn’t it? There would be no striping since I would only have one disk on each side of the mirror. RAID 1 for the logs does not work for my configuration since we’ll have multiple database manipulation at any given time. Anyone else have any opinions about the benefits or deficiencies of my first proposed config as opposed to my second proposed config? Thanks, Steve
Why is RAID1 not an option, even if you have multiple database manipulation? RAID1 could work if it gives you enough space. An other option would be to create
Array 0 (Data): 4 disk RAID 10
Array 1 (Log/Backup): 4 disk RAID 10 Then create two logical devices on array 1 and put OS on one of them. So if you have 8 * 36 Gb you would have something like:
C: 4Gb – OS on array 1
D: 72Gb – Data on array 0
E: 68Gb – Log/backup on array 1 It depends on how large your databases, logs and backups will be. If the data/log and backup sizes will be hard to predict it might be easier to go with the solution you mentioned before with one large RAID 10 on 6 disks where you put data/log. It also depend on if you have one or two channels on the controller. If you have two channels then you definitly gain more by putting one array on each channel. /Argyle
Anytime you can get rid of RAID 5 and go with RAID 10, that is your best option. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
yup it was just a typo Raid 1 is good option for logs espcialy if you have some smaller disks laying around(Lower average seek time) if your using simple recovery mode it might not make much of a difference
So is there a compelling reason to have a RAID 1 for OS and logs and RAID 10 for data rather than everything on a RAID 10?
RAID 1 is often used for the OS and/or log files because it offers fault tolerance and acceptable performance. For best performance of log files, they should be isolated on their own array. This is because you want don’t want random reads and writes to be mixed with sequential reads and writes on the same array, as this hurts performance. Log files are mostly sequential reads and rights, while other SQL Server activity tends to learn more toward random reads and writes. On the other hand, if you can afford RAID 10 for everything, that will always provide you your best I/O performance. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
Thanks for the explaination on RAID 10 vs. RAID 1. Now for a hardware clarification, please. I guess what I am trying to figure out is the best way to configure a server I will be buying. The configuration I am looking at has one controller with two internal channels, RAID 10 and 8 73GB drives. If I want two seperate RAID 10 arrays of four drives each, do I need a second controller card? If I understand correctly, no, as each array will take one channel. Am I correct? If not, please explain. Also, this setup would yield two drives worth of space (146GB in this case), correct? Sorry for the newbie-ish questions.
A single controller card should be adequate, as its two channels will support two arrays, and you will get 146GB of available space per array, based on your configuration. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
]]>