• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

Best way to spread SQL databases/logs across hard drives

I currently have a SQL server with 2 hard drives.  One drive has the OS, the SQL installation, logs and some databases.  The other has additional databases.  We added the 2nd drive when the first one started to fill up.

I was basically given the green light to add more drives to this if we believe it will help with performance.  The problem comes up every morning when we do our data loads.

The database serves mostly as a data warehouse, we we use it all day for analysis.  Every morning we load about 60mm records into the database.  As part of our daily analysis we make frequent use of temp tables.

I am trying to figure out what the optimal setup would be in terms of spreading the current setup across multiple drives.  I was thinking:

Drive 1 = OS/SQL Installation/logs
Drive 2 = TEMP DB
Drive 3 + 4 = Databases

I guess my questions are:

1.  Does it matter that the SQL logs go on the same drive as the OS and the SQL installation?
2.  Does it help to have temp DB on its own drive?

Any other general pointers/wisdom about setting this kind of stuff up would be appreciated.

Thank you.
0
collages
Asked:
collages
  • 3
  • 2
  • 2
  • +1
1 Solution
 
chapmandewCommented:
1.  It should be fine since the logs are only written to, and not read from.  If you can though, it would help to have them on their own drive.
2.  Yes, almost certainly going to help to have its own drive, especially if you're doing a lot of IO intensive operations that make use of it.

Also, are these drives on a raid array?  If so, what kind?  If not, why not?
0
 
collagesAuthor Commented:
I'm also ashamed to say no, they are not raided.  When we setup this server it was a quick once and done thing.  Now we're using it for more and more stuff.  We have the server included in the BackupExec backups every night, so we are not too concerned with data loss, only speed.
0
 
chapmandewCommented:
What happens if you have a problem mid-day?  You'll lose the transactions for the first part of the day.

You'll definetly get more speed if you have the logs on different drives than the data files.  
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mastooCommented:
If you're not worried about data loss, you might still want some Raid 0 as that will easily double your drive throughput.  Obviously though, you stripe 2 or 3 drives and you've got double or triple the failure rate and loss of data.
0
 
collagesAuthor Commented:
Well I think we are certainly concerned about data loss.  Raid 0, while adding speed, wouldnt help with loss.  Would the better option be raid 1+0?
0
 
collagesAuthor Commented:
We actually have 3 drives setup in a raid 5, and 1 drive with no raid.

SO there are 2 right now, with 2 open bays.  Assuming we fill those 2 bays, we'll have 2 drives.  What do you think the optimal raid configuration would be?
0
 
mastooCommented:
Raid 10 gives the best of both worlds (performance and redundancy).  I haven't heard a definitive answer on the optimal raid arrangement.  When you're constrained on drives you might have to decide between 2 or 3 raid 1 sets vs. a single raid 10.  A raid 10 is faster than raid 1, but the individual raid 1's let you put db / logs / os on different drive sets which gives better performance.  I've heard people recommending both.  Maybe someone else will chime in with an opinion.  When we bought our last round of servers I tried some of our typical activities using both configurations and found the raid 1 sets to be faster.  In particular the single raid 10 degraded performance quite noticeably when running a full backup.  Your mileage might vary.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now