February 4, 2011 at 12:43 pm
I am preparing a production system to be roled out in the next couple of weeks with the following:
- Dell PowerEdge R710 (8gb RAM, Single Xeon 5500 processor, Windows 2003 Server 64-bit)
- SQL Server 2005 SP2 (or whatever is the latest at this present time will be put in place).
- PERC 6/i RAID Array Controller (256mb cache memory)
- 8x 2.5" 146gb 10k SAS drives (will be internal - no external SAN involved).
My configuration is as follows:
RAID 1: DRIVE C: (OS, SQL, Other apps) 146gb space.
RAID 1: DRIVE D: (.ldf files ONLY)
RAID 5: DRIVES F - K
- F: 34gb (tempdb ONLY)
- G: 4gb (ROFG1 - Read Only Filegroup for static tables)
- H: 55gb (FG1 and FG4) FG1 is my heaviest hit tbls from my main db, and FG4 is the least
hit tbls from my 3 other 3rd Party db's.
- I: 55gb (FG2 and FG5) FG2 is my least hit tbls from my main db, and FG5 is the 2nd most hit
tbls from my 3 other 3rd Party db's.
- J: 55mb (FG3 and FG6) FG3 is my 2nd most heavily hit tbls from my main db, and FG6 has
the most heavily hit tbls from my 3 other 3rd Party db's.
RAID 5 is supposed to have been setup with the follow characteristics:
- Write-Back (actually - each of the above channels are supposed to be set with this behavior)
- 128KB RAID Stripe
- 64KB Offset
- 64KB allocation unit size
So - I have 2 questions. I, unfortunately, am not the one doing the setup for the server. It's being done by a 3rd party who hosts our stuff. They initially didn't even know how to properly set up the drives, and were going to do it through Windows Disk Manager instead of the BIOS setup on the PERC (nuff said on that topic). I have that all straightened up now (fingers crossed), and all the specs above have been given to our tech. Question #1: I was told by Dell that a 3 channel config from a single PERC 6/i was no problem. Can anyone stat otherwise? I just want to make certain on that - 1st and foremost.
2nd question is for all you design junkies out there who love to come up with best practices to a given hardware configuration. What would you do differently (that would require a complete reconfig of the RAID controller) with the above design, if you feel there is a better design, for better through-put?
This is going to be for an OLTP system with a web-page front end. C# was used to develop the containers/factories, and all queries coming from the front end are using a SELECT * / SELECT ALL kind of methodology (and no - I cannot change that).
Suggestions? Thoughts? Your input will be very much appreciated.
Thank you,
Rich Yarger
SQL Server DBA
February 4, 2011 at 12:59 pm
Am I correct that all of this:
RAID 5: DRIVES F - K
- F: 34gb (tempdb ONLY)
- G: 4gb (ROFG1 - Read Only Filegroup for static tables)
- H: 55gb (FG1 and FG4) FG1 is my heaviest hit tbls from my main db, and FG4 is the least
hit tbls from my 3 other 3rd Party db's.
- I: 55gb (FG2 and FG5) FG2 is my least hit tbls from my main db, and FG5 is the 2nd most hit
tbls from my 3 other 3rd Party db's.
- J: 55mb (FG3 and FG6) FG3 is my 2nd most heavily hit tbls from my main db, and FG6 has
the most heavily hit tbls from my 3 other 3rd Party db's.
...are on the same 4 physical spindles?
If so, you've deployed yourself nicely to eventually expand to a larger system and have this be useful, but it's not necessary at the moment. If you don't split the physicals, the logicals just contend with each other anyway for the physical I/O.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 1:10 pm
Hi Craig -
The RAID 5 Array is composed of a 4x146gb Array. The disk IO in this array is pure data, and some non-clustered indexing to go along with it (in the way of unique indexes - perhaps 3 or 4 of them. The rest are a clustered index on the PKey of each table).
What I am really wanting to know here is if I have 2 files on each one of the 55gb partitions, can I go ahead and set both for the maximum ceiling of 55gb? Or will that cause issues.
The idea on growth later was not the purpose for this design, rather to be able to spread the IO across multiple disks, and keep logs on a separate channel for optimal, sequential write performance.
Are you saying that this will be a bottleneck as it grows? Or should a total of 4 additional channels (making 6 in all from a single PERC 6/i) been used to create RAID 1+0 Arrays instead of a RAID 5 Array? My understanding from experience and research is if you go with RAID 5, that this is they way to go, but like I said - very open to opinions/facts/experiences.
February 4, 2011 at 1:20 pm
Rich Yarger (2/4/2011)
Hi Craig -The RAID 5 Array is composed of a 4x146gb Array. The disk IO in this array is pure data, and some non-clustered indexing to go along with it (in the way of unique indexes - perhaps 3 or 4 of them. The rest are a clustered index on the PKey of each table).
What I am really wanting to know here is if I have 2 files on each one of the 55gb partitions, can I go ahead and set both for the maximum ceiling of 55gb? Or will that cause issues.
Well, combined between the two you can max it off, yes. I personally would recommend leaving 10% of any drive free. It's an older habit from dealing with windows for so long, because when you get into that last bit of space Windows seems to hate the idea.
The idea on growth later was not the purpose for this design, rather to be able to spread the IO across multiple disks, and keep logs on a separate channel for optimal, sequential write performance.
The log usage is fine, and that's a solid approach. The I/O across multiple spindles is going to happen with a single LUN, or the four you've approached it with. Each of those logicals will be striped across the entire RAID5. When you're reading from any of your indexes and are sorting in TempDB, you're going to have contention. You'll have that no matter if they're in the same logical drive or not, they're on the same physical spindles.
Are you saying that this will be a bottleneck as it grows? Or should a total of 4 additional channels (making 6 in all from a single PERC 6/i) been used to create RAID 1+0 Arrays instead of a RAID 5 Array? My understanding from experience and research is if you go with RAID 5, that this is they way to go, but like I said - very open to opinions/facts/experiences.
It's been a while since I did configuration at the channel level, so you'll pardon me if I'm going to be a little vague here. My network guys do all that for me for the last 8 years or so, after I tell them what specs I desire. 🙂
If you want simultaneous throughput, I would create two RAID 1 (note, not 10, just 1) on your other four spindles, to allow to separate the physical I/O when using multiple indexes in a query. You would actually have more fault tolerance (you could, in theory, lose two drives, instead of just the one from RAID 5), but lose one drive's worth of space. That's the tradeoff. However, you'd only be reading from 2 spindles at most, instead of four, during reads. It's a balancing act.
You're going to hit a bottleneck, eventually, no matter what you do. One of the things here that I'm noticing though is that if you get a table with 35+ gigs on it, you're going to jam up, until you do some file manipulation.
I personally would only go through these headaches on a research system that we're not sure of expected lifetime use, and thus want options later for larger SAN distribution techniques. Otherwise, I'd solidify your logical RAID 5 (perhaps keeping tempdb on its own logical so it can't hose up the primary DBs if it grows for some wild query), and have the space available to manipulate. You can host all your Filegroups on the same logical lun with equivalent results.
Not sure if that answered your particular question, though... :ermm:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 1:37 pm
Hi Craig -
No - your evaluation hit the nail on the proverbial head! I appreciate it, and would have loved to have gone with an idea like yours, but alas - disk space is the issue (or lack there of in the RAID 1 idea).
I also forgot to mention that my K: Drive is my largest partition for my backups. Nothing fancy going on there - just a plain ol' maintenance plan for fulls, differentials, and logs.
This is planned for a 5 year life cycle. After that - we'll take a look at critical mass (if we haven't needed to prior to that point) for an external MD3000 or whatever is available at that time (The PowerVault's from Dell are not bad, and about right for smaller to mid-sized companies).
I will archive this conversation, and any other comments that come along for later review, and thanks again for your expertiese! 🙂
February 4, 2011 at 1:46 pm
Rich Yarger (2/4/2011)
Hi Craig -No - your evaluation hit the nail on the proverbial head! I appreciate it, and would have loved to have gone with an idea like yours, but alas - disk space is the issue (or lack there of in the RAID 1 idea).
The space thing is usually the case... Cost to benefit ratios and the like.
I also forgot to mention that my K: Drive is my largest partition for my backups. Nothing fancy going on there - just a plain ol' maintenance plan for fulls, differentials, and logs.
A recommendation here... don't do dat. Backup to your C:\ and immediately have a network transfer for the backup file afterwards. Don't contend with the main mdf/ldf files for disk usage. Just back it up to the spare C:\ spindle, then get it out the door to a network storage. You'll have less pain.
...thanks again for your expertiese! 🙂
My pleasure. Good luck. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 2:09 pm
I also forgot to mention that my K: Drive is my largest partition for my backups. Nothing fancy going on there - just a plain ol' maintenance plan for fulls, differentials, and logs.
A recommendation here... don't do dat. Backup to your C:\ and immediately have a network transfer for the backup file afterwards. Don't contend with the main mdf/ldf files for disk usage. Just back it up to the spare C:\ spindle, then get it out the door to a network storage. You'll have less pain.
YES!!! Great idea!!! I can set the routine up to send it all over to the C:\DRIVE and then use the Windows Task Manager to do a "move" the .bak's over to the K:\DRIVE. I just hope I have enough space to do that with, but at least to start with I should - over time, we'll see.
Thank you again!
February 4, 2011 at 2:11 pm
Rich Yarger (2/4/2011)
YES!!! Great idea!!! I can set the routine up to send it all over to the C:\DRIVE and then use the Windows Task Manager to do a "move" the .bak's over to the K:\DRIVE. I just hope I have enough space to do that with, but at least to start with I should - over time, we'll see.Thank you again!
... 🙂 Or just build yourself a nice little SSIS package that will foreach file loop the backup directories and move them where you like... and include it as step 2 of your backup agent job.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 2:31 pm
... 🙂 Or just build yourself a nice little SSIS package that will foreach file loop the backup directories and move them where you like... and include it as step 2 of your backup agent job.
Love SSIS packages for this kind of thing. The maintenance plan builds it as one as I create the job in SQL Agent. I'll make a second step to move them back over to the K:DRIVE after a successful routine.
Thanks again, and any other tips you might have - I'm taking notes!
🙂
February 4, 2011 at 2:42 pm
Rich Yarger (2/4/2011)
... 🙂 Or just build yourself a nice little SSIS package that will foreach file loop the backup directories and move them where you like... and include it as step 2 of your backup agent job.
Love SSIS packages for this kind of thing. The maintenance plan builds it as one as I create the job in SQL Agent. I'll make a second step to move them back over to the K:DRIVE after a successful routine.
Thanks again, and any other tips you might have - I'm taking notes!
🙂
I usually use a generic package for something like this, and then create a file name variable. My reasoning for this is because all of my databases usually run in independent backup routines. Keeps me sane.
What this means though is I can't trust any one backup is done (log, differential, or full) when others are complete. So, instead of looping for *.*, I'll use a variable setting on each job call to SSIS to set it to the proper naming pattern for that database. IE: MyDatabase*.ldf or MyDatabase*.bak.
This way I only have to maintain one SSIS package, and can use it for all the different items. Set your destination directory in variables and you can deal with finally getting a separate server for backup storage as well.
Something you mentioned that scares me, on a second thinking. You're storing your backups on the same array that your primary databases are on... Ummm... NOOOOOOOOOOOoooooooooo!!!!! :w00t:
Lose 2 drives simultaneously on that array and you are permanently, and utterly, hosed. Screwed. Dead in the Water. He's Dead, Jim. Rubicon. Oh my god we're all gonna die? (I did enjoy the character Wash.)
You're setting yourself up for a world of pain, just in case I wasn't clear. Never keep your databases, and your backups, on the same physical object. It's beyond bad practice, it can cost you your job.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 2:51 pm
Something you mentioned that scares me, on a second thinking. You're storing your backups on the same array that your primary databases are on... Ummm... NOOOOOOOOOOOoooooooooo!!!!! :w00t:
Lose 2 drives simultaneously on that array and you are permanently, and utterly, hosed. Screwed. Dead in the Water. He's Dead, Jim. Rubicon. Oh my god we're all gonna die? (I did enjoy the character Wash.)
You're setting yourself up for a world of pain, just in case I wasn't clear. Never keep your databases, and your backups, on the same physical object. It's beyond bad practice, it can cost you your job.
It's funny you brought this up...I was just going to ask about the location that I have chosen for them as opposed to just keeping them local to either C or D for the backup rotation that the CoLo will perform on a nightly basis.
Now that brings me to yet another point, and hopefully I can pull this off without too much trouble (because I should be able to get rid of a Virtual Drive from the PERC - free up the space - and reassign it to an existing VD without too much trouble). I got to thinking about tempdb being on the RAID 5 Array, and thought "Why not free up the 34gb partition - spead that across my other partitions in the RAID 5 Array, and just keep tempdb on the C: drive, one directory off the root?" Well - this now has me wondering if I shouldn't just put the .bak files out on C as well? I think to start with - that is what I am going to do, and kill 2 birds with 1 stone.
- tempdb to C:
- backups to C:
146gb with these files will get gobbled up quick (especially since the OS and SQL, along with several other services will already be there), but I think it is the best case / best practice I can go with.
Thanks for saving my head and butt on this one! I am not sure what was going through my mind about the backup partition (other than they get performed each evening, and then directly backed up to another device/media there after).
:w00t:
February 4, 2011 at 2:55 pm
TempDB and C:\....
Not usually a good idea, unless this server is incredibly robust and they're just being cheap on disks. If you end up needing the swapfile, which is more common if you're churning TempDB, you just shot yourself in the foot. Also, tempDB has a habit of getting... hmmm... rediculous. Chewing up your OS's space may also hurt you. Make sure you set your TempDB to have a max-size setting, to avoid that failure if you're going to go with that.
Not that I'm against the idea in general, just you have to make sure you don't whack yourself.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 2:58 pm
Not usually a good idea, unless this server is incredibly robust and they're just being cheap on disks. If you end up needing the swapfile, which is more common if you're churning TempDB, you just shot yourself in the foot. Also, tempDB has a habit of getting... hmmm... rediculous. Chewing up your OS's space may also hurt you. Make sure you set your TempDB to have a max-size setting, to avoid that failure if you're going to go with that.
Not that I'm against the idea in general, just you have to make sure you don't whack yourself.
I'm so grateful for this little chat of ours. Can you tell I am a new DBA?
😛
OK - tempdb stays where it's gonna go, but backups are going to C:. D: for ldf's and nothing else, and an ever growing RAID 5 Array!
:w00t:
February 4, 2011 at 3:01 pm
Rich Yarger (2/4/2011)
I'm so grateful for this little chat of ours. Can you tell I am a new DBA?😛
Perhaps new, but well spoken and you bring your own research to the table. You're one of the folks that makes it worth hanging around here for. A pleasure to 'meet' you. 🙂
OK - tempdb stays where it's gonna go, but backups are going to C:. D: for ldf's and nothing else, and an ever growing RAID 5 Array!
:w00t:
Sounds like a plan to me. Btw, if you've already gone through the effort of creating unique file groups for your data, keep it that way (combine the LUN to 1/2 logicals, but keep the filegroup split). When this grows out of expected scope, you'll be glad you did.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 3:10 pm
Perhaps new, but well spoken and you bring your own research to the table. You're one of the folks that makes it worth hanging around here for. A pleasure to 'meet' you. 🙂
🙂 The pleasure is all mine - believe me when I say that it's nice to have mentors to check in with. I've been working with SQL Server since 2004, but this is my first role as a titled DBA, and I am loving it!
Sounds like a plan to me. Btw, if you've already gone through the effort of creating unique file groups for your data, keep it that way (combine the LUN to 1/2 logicals, but keep the filegroup split). When this grows out of expected scope, you'll be glad you did.
Now - by this do you mean that the RAID 5 structure for the Filegroups should be replicated if for some reason this system acquires more db's over time? Which - believe it or not - has happened 4 times now prior to roleout! I think I understand what you mean, and yes - this is how I was taught when I went through formal training - spread out the disk IO.
I'll keep the Filegroups as they are, and add others with the same method and approach if and when needed.
Thank you again Craig, and may I say that it's great to finally (after 20+ years of working in I.T. in various capacities) belong to a true community of professionals in their discipline.
Have a great weekend!
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply