New Windows 2008 R2 server with SQL 2008 R2 - Hard Drive Setup

  • Okay I my be a little out of my league here, but I am learning. Please bear with me. Sorry if this isn't being posted in the correct area. We are staging a new SQL Server and I was wondering if i could get some recommendations how how it should be setup.

    Main thing is how should the hard drives be configured and what stripe size works best.

    What we have are (10) 146GB 15,000rpm SAS drives.

    What I was thinking was . . . and please correct me and offer advice . . .

    2 Drives in one big RAID 1 with two partitions. One partition with OS/Programs and the other with PageFile.

    8 Drives in one big RAID 10 with two partitions. One partition the SQL database and the other with SQL logs.

    Also I was thinking 128k stripe size for the RAID 1's and 64k stripe size for the RAID 10's.

    Last question I had is . . . . is it better to create one big RAID array with partitions? Like for example I suggested 8 drives in a RAID 10 with two partitions . . . . or two RAID 10 arrays consisting of 4 drives for those two arrays.

    The downside i see is if you create one big partion you will "theoritically" have 8x writes and 4x reads with one big array, so both partitions could run at that speed. But if you break them up into two 4 drive arrays then both of those will run at a maximum 4x writes and 2x reads. So essentially you will cut throughput in half . . . . but is it better to segregate the hard drives for the database and one for the logs??

    Thanks for any help guys!

  • What about backups, you have no provision for these??

    Nater (9/12/2012)


    What we have are (10) 146GB 15,000rpm SAS drives.

    What I was thinking was . . . and please correct me and offer advice . . .

    2 Drives in one big RAID 1 with two partitions. One partition with OS/Programs and the other with PageFile.

    That sounds fine

    With the remaining 8 drives i would do this

    2 drives in RAID1 for backups

    2 drives in RAID1 for logs

    4 drives in RAID10 for data

    64k stripe size to be used for the 3 RAID volumes above

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry,

    Thank you so much for your response!!! Thank you so much for mentioning backups . . . cant believe i forgot to mention that. We will definitely be taking this into consideration.

    One thing I still can't get a grasp on and if anyone could explain that would be great!!!

    But what are the benefits seperating the arrays with their own hard drives . . . instead of seperating a big array with partitions???

    Like I started to say above . . . . Example: Lets say my hard drives can do 150mb/sec READS and 150mb/sec WRITES.

    Whats so wrong with having Database and Logs on one big array but seperated with partition. Like if I were using 8 drives in RAID 10 I would get 1,200mb/sec READS and 600mb/sec WRITES for my Database partition and my Log partition.

    Switching to a 4 drive RAID 10 for Database will make it run at 600mb/sec READS and 300mb/sec WRITES. Plus if I go to a 2 drive RAID 1 for Logs that will have my logs run at 300mb/sec READS and 150mb/sec WRITES. Granted you are splitting it up . . . but does that really benefit much? Especially if you are dropping speed like that?

    Thanks for help guys!

  • Nater (9/12/2012)


    Perry,

    Thank you so much for your response!!! Thank you so much for mentioning backups . . . cant believe i forgot to mention that. We will definitely be taking this into consideration.

    You should if you intend to backup your databases 😉

    Nater (9/12/2012)


    But what are the benefits seperating the arrays with their own hard drives . . . instead of seperating a big array with partitions???

    Like I started to say above . . . . Example: Lets say my hard drives can do 150mb/sec READS and 150mb/sec WRITES.

    Whats so wrong with having Database and Logs on one big array but seperated with partition. Like if I were using 8 drives in RAID 10 I would get 1,200mb/sec READS and 600mb/sec WRITES for my Database partition and my Log partition.

    Switching to a 4 drive RAID 10 for Database will make it run at 600mb/sec READS and 300mb/sec WRITES. Plus if I go to a 2 drive RAID 1 for Logs that will have my logs run at 300mb/sec READS and 150mb/sec WRITES. Granted you are splitting it up . . . but does that really benefit much? Especially if you are dropping speed like that?

    Thanks for help guys!

    You're mixing dissimilar I\O patterns on the same physical array, they should be separated for maximum performance. 2 logical drives on the same array will have I\O pattern conflicts with the sequential log I\O and random database I\O.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thank you Perry! For once someone actually gave a straight out answer that makes sense when you sad it. All I hear from everyone else is "well thats the common practice" but nobody seems to understand WHY!

    Ha I am almost done. Just a few more questions. Now we were talking about what to do with those 8 drives and you said . . .

    2 drives in RAID1 for backups

    2 drives in RAID1 for logs

    4 drives in RAID10 for data

    Just curious . . . but we have 6 bays left. If I were able to get authorization to order 2 more drives, where do you think they would be more beneficial??

    Would it make sense to add onto the Database RAID10 and make it 6 drives? . . . or

    Make the logs a 4 drive RAID10 instead?

    Or what?

    I guess what would be the best benefit??

  • Nater (9/12/2012)


    Now we were talking about what to do with those 8 drives and you said . . .

    2 drives in RAID1 for backups

    2 drives in RAID1 for logs

    4 drives in RAID10 for data

    You could also do this

    2 drives in RAID1 for backups

    2 drives in RAID1 for logs

    3 drives in RAID5 for data

    1 drive as spare

    Nater (9/12/2012)


    Just curious . . . but we have 6 bays left. If I were able to get authorization to order 2 more drives, where do you think they would be more beneficial??

    Split out the Tempdb if you get 2 further drives

    Nater (9/12/2012)


    Would it make sense to add onto the Database RAID10 and make it 6 drives? . . . or

    Make the logs a 4 drive RAID10 instead?

    Or what?

    I guess what would be the best benefit??

    If you have budget to get 6 i would get 6 larger 300gb drives and change the config above even further

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (9/12/2012)


    You're mixing dissimilar I\O patterns on the same physical array, they should be separated for maximum performance. 2 logical drives on the same array will have I\O pattern conflicts with the sequential log I\O and random database I\O.

    What do you think about SAN LUNs? My storage guys here just give me LUNs for each SQL component(tempdb, data files, log files, system dbs, backups) Technically it's 5 different LUNs but they all live on the same physical array. I think it would be better to set up a physical array on the SAN that is used ONLY for SQL logs and then create LUNs on that array for the logs of all of the SQL instances. Really, the logs are the only component that is sequential, correct? The rest of the LUNs could all exist on the same physical array?

    Maybe I'll start a new thread to discuss this. 🙂

  • scogeb (9/13/2012)


    Maybe I'll start a new thread to discuss this. 🙂

    😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Alright I got a few more questions. How exactly do backups work in SQL Server 2008. Whats the need to seperate them onto their own drives? Do SQL backups only run at a certain time of the day or do they actively run all the time? Reason I ask is that our Database for the most part isnt going to be that large and our database is really going to sit doing nothing after work hours. Since the database isn't that huge I can't image that it will take that long and therefore would run after work hours. Would that be an issue?

    Sorry I am just dinking around with ideas on increasing drive speed and what not. It's so dang hard to get more stuff authorized and purchased for us IT folk. Just trying to work with drives we got and the whole more spindles in array equals greater performance. After reading grasshopper's seperate forum that he started . . . he stated that Logs are the only Sequential things and everything else is Random I/O's.

    Thanks for bearing with me.

    Nater

  • Nater (9/19/2012)


    Alright I got a few more questions. How exactly do backups work in SQL Server 2008.

    Are you talking native backups or 3rd party (Litespeed, Sqlbackup, etc)?

    Nater (9/19/2012)


    Whats the need to seperate them onto their own drives?

    The write I\O incurred during full backups especially can be high. You should be looking to separate all the individual SQL Server I\O channels (Log, Data, Tempdb, Backup).

    Nater (9/19/2012)


    Do SQL backups only run at a certain time of the day or do they actively run all the time?

    They only run when you execute them via a script or maintenance plan.

    Nater (9/19/2012)


    Reason I ask is that our Database for the most part isnt going to be that large and our database is really going to sit doing nothing after work hours. Since the database isn't that huge I can't image that it will take that long and therefore would run after work hours. Would that be an issue?

    Smaller databases would generally produce smaller backup files, but there caveats to this 😉

    For instance transaction logs that have grown to 100 times the size of the primary data file would produce a large t-log backup!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply