SQL Server Perfect Configuration

  • David, yes, more spindles is good. Lots more is better! I should have clarified. I recommend figuring how how many spindles you need (or can afford), but then getting the largest drives you can manage. A bad thing to soon be running at 80% drive capacity, for space that is. I agree with Steve, more controllers are better, though I'd say it depends on what kind of controller. Onboard memory really helps, I think ours has 512m maybe??

    I use R5 for data, R1 for logs. Raid 10 would be nice, but cost is high and for the load I currently have, R5 is working fine. Even with the 240 or so log files I have on the R1, average utilitization is maybe 10%. I know the numbers support R10 for anything over 10% writes, but I think that number is low, don't be afraid to deploy R5!

    I definitely support building in redundancy, UPS, fans, whatever. We bought an 8 way Dell box last year with a 10 disk external Fibre setup, total with a enclosure, UPS, everything except software was $80k. That's a lot if Im writing the check, but for a corporate purchase, given how important it is, not bad at all.



  • Hi all

    Talking briefly on raid 0+1, anyone have comments on placing all data,log,etc files onto the single fat raid0+1 array?? here I am talking a minimum of 8 drives. Primarily from a performance perspective, esp trans logs and data. Ive never had the luxury of raid0+1 🙂



    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Conventional wisdom says not to, Im not so sure myself. The biggest reason to me would be the additional safety net of having logs on a separate array if something breaks.


  • Thanks everyone for your input !! I'll let you know what we build in the coming weeks.


  • Has anyone used EMC before, and what was the


  • I spoke with a few DBAs that had an array and they loved it. It performed beautifully and notified them when disks failed. It was the most painless array and they loved it, of course, they spent over US$500K on it.

    Steve Jones


  • We're using a small Dell/Clariion Fibre setup, 10 disks, very nice. No problems with it all and it is damn hard to saturate the disks compared to scsi.


  • From a performance standpoint, it's not recommended to mix transaction logs and data. T logs are sequential, so this means that your drive heads can find the information quickly and very efficiently. Data is random, so you want to throw more spindles at it so you can have faster writes and reads, though there is a performance degradation when using RAID 5. This is due to the number of reads/writes required when employing RAID 5. When you mix sequential logs and random data, all of your information now becomes random and you experience a performance hit on your entire array. You should also consider keeping logs separate from data for backup/recovery purposes.

    Compaq has a new RAID level, called DataGuarding. It allows you to employ two spare drives in a RAID 5 array, which is an added safety feature. The more spindles you have in an array, the faster you can access data, but the more likely you are to experience drive failures. Compaq recommends DataGuarding whenever you employ eight or more drives in a RAID array. It costs an extra drive per array, so is it worth the cost?

    I recently deployed an SQL Server 2000 two-node Active/Passive cluster on Windows 2000 A/S on a Compaq StorageWorks SAN. It was an easy deployement and the customer was extremely surprised by the performance, ease of administration the ability to easily add more databases and more TerraBytes to his system. What he really appreciated was the ability to tie the SAN to several Alpha and Sun servers that had been in his data center for some time. And, clustering in Windows 2000 is a breeze compared to NT.

    Always Learn!

  • Two spares is interesting - not sure I'd do it, but interesting! Seems like a lot would depend on your support contract. We have guaranteed 4 hr delivery of parts if needed, so what are the odds that I'll lose a drive, go to the hot spare, lose it...and then lose still one more, since I could run with minus one with a heavy perf hit? Then again, couple hundred bucks for the drive? The downside I see is that depending on your cabinent setting one aside means one less doing work - and that will hurt.

    Saw your notes about the log files - one of these days maybe I'll have time/money to challenge that a bit more. I agree about putting on separate drive for safety, no doubt about that. But I think the sequential read thing is WAY overrated - primarily because so few of us have just one db/one log. If you've got multiple logs on the same drive, how could you be even close to sequential? Once you lose the "sequential" factor, is safety really enhanced by using separate drives? The other downside is that usually the data is sitting on a big fast redundant array - depending on your read/write ratio, you might get faster logging moving the logs to the main array.

    Gotta swim upstream once in a while!


  • Absolutely agree with comments above concerning losing sequential data due to maintaning multiple logs on mirrored drives/RAID sets. I'm real interested in learning more from others regarding maintenance/storage of logs and whether multiple logs on mirror/RAID sets is a common practice.

    The industry is headed toward Heterogeneous storage (SANS and other similar products) and away from external SCSI arrays. So, companies are begining to spend a larger portion of their budgets on storage (SANs) vs. servers. Regarding HDDs, Wide-Ultra3 SCSI(15K)drives are the way to go. Wide-Ultra3 (15K) doubles the Maximum Transfer Rate of Wide-Ultra2 drives from 80MB/s to 160 MB/s.

    Always Learn!

Viewing 10 posts - 16 through 24 (of 24 total)

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