Best SQL config for SAN

  • We are getting our first SAN (yeah!) and I think a good disk approach for an OLTP sql 2008 server is this:

    drive C: OS, pagefile

    drive D: tempdb

    drive E: log files (RAID 0 + 1)

    drive F: data files (RAID 5)

    What is your opinion of this layout? OK, overkill, not enough?

    TIA,

    barkingdog

  • Don't get too excited, setting up a SAN incorrectly can ruin performance. Make sure your SAN vendor helps you make good decisions.

    As far as the configuration you are suggesting. I like seeing TempDB on it's own drive (are C and D internal drives?). I would avoid RAID 5 for data files if this is a high-transaction OLTP system (RAID 5 is slow for writes).

  • Barkingdog (12/5/2008)


    What is your opinion of this layout? OK, overkill, not enough?

    Where are you going to put your backups?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Only C is an internal drive.

    What would you recommend for a high speed OLTP data drive if not RAID 5? (RAID 0 + 1)?

    Barkingdog

  • Either Raid 0 + 1 or Raid 10.

    You really should have a separate disk for your backup files to land as well.

  • Don't forget to use diskpart.exe to align the disk partitions (align=64)and don't format the drives with the default (4k) blocksize. Test this in your environment.

  • The backups themselves will go on the SAN (initially) on our low I/O drives. They will ultimately be saved to tape.

    Barkingdog

  • >>>Don't forget to use diskpart.exe to align the disk partitions (align=64)and don't format the drives with the default (4k) blocksize

    I don't know what "algn=64" refers to but I'll read up. Also, as far as testing disk speed when changing default blocksize what tool(s) do you use to check if the change improved matters?

    TIA,

    barkingdog

  • I found this tidbit too:

    >>

    Yep, you're absolutely correct. This is an issue with Windows 2003 and prior versions for every SAN out there. 2008 handles it by automatically aligning the partitions during creation, thankfully, but for older versions, you're still stuck going into DISKPART.

    This affects more than just SQL, too - it's a big part of Exchange 2003 storage setup, since Exchange 2003 hammers drives pretty hard too.

    >>

    barkingdog

  • Another thing to consider would be the number of datafiles to add to the DB. I usually go with the number of CPU sockets. So if I have 4 dual core procs, I would use 4 data files. I would then place those datafiles on separate drives. Also, is your SAN able to grow the LUNS on the fly without negatively impacting Windows?

  • >>>is your SAN able to grow the LUNS on the fly without negatively impacting Windows?

    I don't know the answer and didn't even think of the question (i.e. "on-the-fly" or autogrowth).

    I'll get the info and write back.

    Barkingdog

  • Barkingdog (12/5/2008)


    We are getting our first SAN (yeah!) and I think a good disk approach for an OLTP sql 2008 server is this:

    drive C: OS, pagefile

    drive D: tempdb

    drive E: log files (RAID 0 + 1)

    drive F: data files (RAID 5)

    What is your opinion of this layout? OK, overkill, not enough?

    TIA,

    barkingdog

    drive C: OS, Pagefile RAID 1

    drive D: tempdb RAID 10

    drive E: log files RAID 10

    drive F: data files RAID 5 or 10 (but for high OLTP 10 would be more suitable)

    using RAID 0+1 is like kissing your dog after he licked his behind, you could but you wouldn't 😉

    Just bear in mind that RAID 10 has a high disk overhead but on the plus is extremely fault tolerant and gives good performance

    speak to the SAN admin and get the best configuration

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

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

Viewing 12 posts - 1 through 11 (of 11 total)

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