February 5, 2003 at 2:28 pm
I'm going to create new WIN2K server with SQL2K. I have 4 x 18GD SCSI drives, 1 of which will be hot spare.
What RAID configuratin should choose?
Is any performance considerations for splitting the drive ( if I go with RAID5 ) to several partitions and keep logs, temp, page, datafiles on separate partitions?
Thank!
February 5, 2003 at 3:02 pm
Ideally the log should be on a mirrored drive as you cannot afford to lose it and the activity on it will be mostly writes. The data files may have a majority of read or writes depending on the usage of your database(s). RAID 5 is good for reads but slow on writes.
The nature of the log is that it is 100% writes that are sequntial nature so it is best on its own mirrored disk away from the random access of other files and where fault tolerancy is best.
Partitioning disks is not the same has having thee files on separate physical disks and won't result in great performance gains.
Nigel Moore
======================
February 5, 2003 at 3:16 pm
I agree with nmoore, although with only 4 disk, and 1 for hot swap, that only gives you 3 disks to work with. I really don't think you have but a single option, to allocate those 2 disks to a single RAID5 configuration. Other opinions are always encouraged!
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 5, 2003 at 3:22 pm
Spend a few $$ and get a couple more disks. Drop a RAID 1 (2 drives) for the OS and Log. Assuming you won't hit the OS drive much once you get going. Build a RAID 5 from the other 4 drives. If you can't get more, then go with one big RAID 5 with all 4 drives. Gain in efficiency is worth it. A hot spare means you are expecting 2 drives to fail in relatively short time. Shouldn't happen. If you're that concerned, get another 1 drive.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 5, 2003 at 4:12 pm
Thanks guys!
Provided I can't get additional drives beyond 4, I do RAID5, but how about logical drives/partitions considerations?
Thanks!
February 5, 2003 at 4:31 pm
I think I would just have one big partition, although I suppose if you want to seperate the operating system and sql server you could have two.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 6, 2003 at 3:30 am
I agree, after some recent updating I would say that due to the fact the logical partitions would in fact be on the same array and channel you will actually hurt perfomance as this will cause you to loose the benifit of synchronous writing on the disk when switches between partitions. The would be especially bad if the Swap File was on one partition and the data on the other, the constant switching between patition writes will degrade performance. 1 Big drive.
February 7, 2003 at 12:24 pm
I agree that you would get no benefits in partitioning the RAID 5 configurtion, but disagree that you create this partition without using an online spare. It comes down to this: is a signficant performance hit acceptable to you when you loose a drive? With RAID 5, parity is distributed across all drives in the array. This means if you loose a drive in the array, data on that array will have to be rebuilt "on the fly" by the controller thus resulting in a performance hit. If you plan on purchasing an additional drive to keep on-site for when this happens and the extra space of an additional drive is appealing, then that might be acceptable. It depends on what you are comfortable with.
Also be aware that not all RAID controllers can rebuild an array automatically. If yours does not have this capability and you lose a drive then the server would have to be brought down, the drive replaced, and then the array rebuilt and data restored from backup. Make sure that you know the capabilites of your array controller.
February 8, 2003 at 7:59 am
With only three drives (and one for hot swap) you have limited your options. If this machine is your sandbox then it's fine to create a single R5 array. If this is a production box then you really need to spend a few more $'s and get at least one more drive. I have found that a single R10 device can carry a fairly substantial load. R10 performs better than R5 or R1. The trick will be to break out your hot tables and indexes into separate files so the load can be spread over a number of spindles (more spindles ... better performance).
February 10, 2003 at 1:24 am
I may add, for a seriuos production situation, and if you can afford it a SAN box would be the way to go. Dedicate a spindle for logs with RAID 0+1 or is it RAID 10. More, dedicate 1 spindle with RAID 0+1 for data. The more drives you add to a spindle the better. Of course , RAID0+1 has to have an even number of drives.
February 10, 2003 at 1:30 am
If by chance you're using an IBM serveraid adapter you can go with raid 1E which is sorta like raid 10 in that it has less write overhead than raid 5 but still gives you good read performance and fault tolerance. Can be done with just 3 drives too.
Edited by - tomwiggin on 02/10/2003 07:58:18 AM
February 10, 2003 at 8:41 am
Assuming 1 raid 5, I completely disagree with having 1 big partition. for 1 huge reason. Recovery. If your os fails, reload it and your apps to the one partition and have another partition with your data and log files. Once SQL is reinstalled to the other partition, all you need to do is reattach your database (hopefully).
February 12, 2003 at 2:52 pm
While I would still stick with the general concensus of having one big raid 5 and no logical partititions - there is one advantage I can think of to having the partitions. If you were doing transaction log backups on a frequently updated database and so much activity occurs that transaction log backups can no longer fit on the drive, the log file will grow and.. and..
-Ken
February 13, 2003 at 4:09 am
I did not have the option with our servers as they were configured & installed before I got hold of them but we have a raid 5 array with a seperate partition for the data.
I heard from microsoft that you can obtain a 5% preformance increase if your DB is stored on a RAW partition this has the downside of needing regular backups as the chances of recovering the data from an os crash are minimal.
Also I have been informed that SAN only works properly with ENTERPRISE Edition is this true??
Ensure that your raad controller is battery backed (the majority of the new ones are) ... if not turn off write caching...
February 13, 2003 at 1:54 pm
Does anyone know whether an IBM 10 disk array can be configured with two drives mirrored and the rest in a RAID 5 or 10 ?
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply