RAID recommendation

Question

RAID recommendation

rbnstudio

Valued Member

Points: 61
More actions
December 21, 2005 at 8:45 am

#96083

Hi,
I'm about to make a new SQL server.
I've read several places that a good hardware configuration is to place database on on disk drive and logfile on another drive - because harddisk head won't have to move on the logfile drive since it is written sequentiel.
But what is the scenario if you lets say 30 databases on that server?
Then the harddrive head on the logfile drive would still have to jump/search between different logfiles...
So is the above mentioned recommendation only good for 1 single database ??

Viewing 5 posts - 1 through 4 (of 4 total)

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

Bulent Gucuk SSCertifiable Points: 5497 More actions · Answer 1

Hi,

If you have the money and space to add 30 hard drives you will definitely have the I/O performance edge. I think that would not be realistic. When making your storage decision you should consider designing a solution that will provide fault tolerancy with some performace. Use mix of RAID 5, RAID 10 or RAID 1 settings. We had limited budget when upgrading our db server. We used RAID 5 (consist of 4 physical hd) to store mdf files and RAID 10 (4 physical drives) to store log files.

rbnstudio Valued Member Points: 61 More actions · Answer 2

Thanks...

But lets say you'll have to host 30 databases.

What would be best:

a) 1 RAID1 (2 disks) for the data files + 1 RAID1 (2 disks) for the logfiles

or

b) 1 RAID10 (4 disks) - both data and logfiles on same logical drive

What would you choose - and why?

David le Quesne SSCertifiable Points: 5229 More actions · Answer 3

Hi,

We have six or seven databases running and use two separate SCSI disk controllers and three logical drives.

Controller 1 has:

Drive1: Operating System and Software, and backup repository on two mirrored 80gb drives

Drive2: Log Files on two 80Gb mirrored drives

Controller 2 has:

Drive 3: Databases on 4 x 136Gb drives in a RAID5 config with a spare 'hot swap' drive.

Chose this configuration because we have a lot of PERL scripting work going on on the software drive, and I wanted to separate the software, the log files and the database. Used two separate controllers because I got the idea from somewhere that controller contention was also a potential bottleneck.

David

If it ain't broke, don't fix it...

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

Even with 30 databases (or more), the best advice is to put data and log files on separate physical drives (regardless of RAID configuration).

The I/O traffic is different. As you mentioned, the log file access is sequential and data file access is random. Log file changes must be written immediately to complete a transaction, data file updates can be handled by the lazy writer thread in the background. The cache built into data disks may be useful if a data page is accessed again (and has been flushed from the buffer pool), but log data is seldom re-read.