SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Nater
Nater
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 31
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!
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56293 Visits: 17729
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" ;-)
Nater
Nater
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 31
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!
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56293 Visits: 17729
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" ;-)
Nater
Nater
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 31
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??
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56293 Visits: 17729
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" ;-)
scogeb
scogeb
SSC-Addicted
SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)

Group: General Forum Members
Points: 492 Visits: 420
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. Smile
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56293 Visits: 17729
scogeb (9/13/2012)
Maybe I'll start a new thread to discuss this. Smile

;-)

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Nater
Nater
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 31
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56293 Visits: 17729
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search