June 16, 2008 at 9:18 am
This is something that has been discussed in other places, but I wanted to see if anyone had any wisdom to share on this particular case.
We're getting new hardware for one of our existing SQL Server installs. It's a reasonably busy database, with up to 150 interactive clients during the day, mainly OLTP-type queries, but with a few longer jobs. The hardware we are getting will have 38 72gig spindles - 8 in the main chassis, and 30 in a separate chassis. Obviously, this gives us a fair
number of possibilities - a bit spoilt for choice, and also there seems to be little discussion relating directly to what we want to do (although plenty of general discussion about this area).
We're currently thinking in terms of 2 for O/S and 6 for logs in the main unit (with appropriate RAID configurations), but then the question arises of what to do with the others. There seems to be some discussion about RAID 5 vs RAID 10 for partitions. Should I be thinking about RAID 10 for both data and logs (our current sizes are about 150 GB data and 60 GB log - but we want to leave space for expansion)
Should I be thinking in terms of a small number of a single data array, or is their any virtue in breaking off tempdb into a separate array for example? Also, how do I decide the best number of filegroups to set up, and should I split off separate disk arrays to accommodate?
Thanks in advance for any advice,
Tony
June 16, 2008 at 11:09 am
Lots of advice here.
Go with R10 or R01, no R5. The only place I might do R5 is for backup files, and I'd keep them on a separate array. That's important in case you lose an array.
If it's one database, I'd probably go with a single array, R10, for the data. If you know the data well, you could separate out some indexes or logs into filegroups and benefit by putting them on other arrays.
Be sure you keep 2 spares at least with 38 drives. I might keep 1 in the main chassis and perhaps 2-3 in the external. Things will fail.
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
June 16, 2008 at 3:50 pm
Steve -
Thanks for the reply. It's reassuring that you're not suggesting we should necessarily be looking at more elaborate partitioning schemes than that. There is one main database, and a couple of other much smaller ones for lookups (e.g. for geographical data for IP addresses). Within the main database, we have a fair idea of the data and how it's used, but if any benefits are marginal I think we would prefer to avoid any additional management overhead. As it is, the database performs reasonably well most of the time - I'm hoping the new hardware is going to make me look a hero with a very noticeable jump in peak time performance just by virtue of the several times increase in the disk bandwidth etc. I just don't want to waste any performance at this stage by being lazy about looking at some configuration options, but I'm quite happy to take the recommendation that there's no particular reason to depart from a fairly orthodox configuration.
Tony
June 17, 2008 at 6:13 am
Hi all,
Some interesting thoughts there, with regards to the raid levels and filegroups.
However seems as you have 30 drives in an external enclosure is it then worth breaking up the data further?
for example:
OS - on 2 disks in the server chassis
logs - on the 6 disks in the server chassis
External enclosure:
tempDB (maybe 4 disks in a RAID10)
indexes (maybe 4 disks in a RAID10)
SQL Data (filegroups on seperate partitions by date or transaction type):
Filegroup1
Filegroup2
......
The above config would allow for 20 disks for the SQL data and you can then partition it up how you like?
Thanks in advance
Paul
July 4, 2008 at 5:40 am
Hardware has turned up, and we're just at the point where we'll be configuring the arrays. I'm still a bit concerned about the 30 disks in the external chassis. I think what we're going to do is configure 28 of them into a single RAID 10 partition - is this a reasonable thing to do?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply