SQL Server Performance

Storage Area Network (SAN) Configuration for SQL

Discussion in 'SQL Server 2008 Performance Tuning for Hardware' started by Brandon S, Sep 12, 2011.

  1. Brandon S New Member

    I am currently trying to figure out what would be the best configuration for a new SAN for SQL Server 2008 Cluster. Right now I have a SQL cluster that is using a LUN on my companies existing SAN but the performance just isn't cutting it anymore and they have allowed me to purchase a new SAN, however I need to come up with the Hard Drive configuration first (we don't have a real SAN Administrator). Based on some articles I have read (I have been at this for 2 days) I have come up with the following configuration. Our databases are a combination of heavy Read and heavy Write databases.

    16 Drive SAN:
    Virtual Disk 1 (8 Disks): RAID 10 - Data files
    Virtual Disk 2 (2 Disks): RAID 1 - Log files
    Virtual Disk 3 (2 Disks): RAID 1 - Temp DB
    Virtual Disk 4 (4 Disks): RAID 5 - Backups

    Can anyone offer any advice on this configuration? If it is good or bad, or if anything should be changed. Also, for the Quorum and MSDTC shared drives I was thinking about carving them out of Virtual Disk 4, would there be any negative effects to do this?

    Also, since we are going to present the new SAN to the Cluster through iSCSI I was thinking about puting 2 NIC's in each of my server nodes and have one NIC for transactions between SQL and my application servers and the second NIC for connectivity between SQL and the SAN. Is there any advantage to configuring my new SQL cluster in this manner or will I gain nothing from it? We have multiple VLAN's in my office and usually SAN's are put on the management network and the servers on a server network. The VLAN's seem like they are used more for device organization since the devices can talk to each other across the VLAN, however I am not a network guy so I don't know all the intricacies of what my networking team is doing.

    Thank you for any input anyone can provide.
  2. preethi Member

    Welcome to forums.
    My two cents. (I need to admit I too am not an expert in SAN administration; I am sharing from the little experience I have)
    You need to answer a couple of additional questions:
    1. How many databases you are hosting?
    2. How many data files you are planning to keep in these files?
    3. What is the configuration of the disks? (Size, speed, Form Factor)
    4. How many processors you have in your server?
    5. What is the load and size of the databases?
    6. Do you have a tape backup unit already?
    7. What is your backup strategy?
    Generally SAN is connected to the server via fiber optic cables.
    I prefer to have more disks for RAID 10, so that we can create more LUN on physically different disks.
    Why you want to have RAID 5 for backups? If you are planning to keep more than one backup in the disk, (including full backup, couple of differential and log backups, you may need more space than what you need for database.)
  3. Brandon S New Member

    I have about 30 different databases, the majority are SharePoint databases. The databases range in different sizes with the largest at 150 GB. For the SAN I am looking at 300 or 600 GB 15K drives, depending on how much space I need. Our current backup strategy is Full DB on the weekends, and diff's nightly throughout the week. We usually save 2 weeks worth of backups.

    I was looking at doing RAID 5 so that I would get a performance increase and still have redundancy in case one drive dies. Would you recommend using something else for backups? Would a different server be the best?
  4. preethi Member

    By Default, sharepoint databases are created with 2 files. I prefer you to have little more disks for data files, you that you can create 2 physically separate LUN and balance the load of the data files. This gives you some more flexibility on sharing the highly used instances in different disks.
    You didn't answer some of the other questions.

    Having more than one temp db files is a good option. But to get optimal performance, they should be in different disks.
    If you have a tape unit, that could be considered as redundant copy of backup.
    I generally use normal disks for backup and try to take the backups span into multiple disks.
  5. Shehap MVP, MCTS, MCITP SQL Server



    As a first phase ofSAN implementations that matches up with DBs needs for both Performance + Storage capacity:

    1- Changing Read : Write Ratio from 10:90 (Default as for EMC SANs) to 30:70



    2- Ensuring 2 dual connections (either FC or UTP ) from SAN to each DB server

    For the second phase of SAN LUNs configurations , you could get it enumerated comprehensively as next:

    1- SAN LUNs Maps




    RAID Group

    Cluster A

    Usage

    Disk #


    1

    200 GB

    Master +Model+Msdb DBs


    2

    100

    User DBs Files (.mdf +.ndf)


    3

    100


    4

    75

    Tempdb


    5

    75


    6

    50

    All log files


    7

    400

    Backup Files


    8

    1

    Quorum Drive


    9

    1

    MSDTC Drive


    2- For yellow ones , they are should be configured with RAID 1/0

    3- For green ones , they should be configured with RAID 5

    4- Each 8 SAN LUNsof each cluster group A should in isolated RAID group and thus we should have 9 different isolated RAID groups or 7 at least

    5- Much recommended more, if these 7 RAID groups could be in 7 isolated enclosures.

    6- All SAN LUNS should be mapped to the DB Server as offline disks only without any additional configurations.

    7- All SAN LUNs should be identified by disk # as per table above.

    8- Then you could format them as 64 KB by the appropriate drive letters as MBR not GUID (Since GUID is for > 2 TB)

    9- ensure partition offset alignment is configured with the best practice = 1028 KB = 1 MB (Default of windows server 2008 + Win 2008 R2)
    
    10- Strip Unit size should be 64 KB at least.

    If any furhrer help , please le me know


    Once SAN installed + Patched well , we could round down into the second phase concerning
    SAN LUNS maps for each DB server
  6. satya Moderator

    A good line up Shehap, keep it up .. indeed.
  7. Brandon S New Member

    Thanks for the replies. One last question, does anyone have a recommendation for a good, cheap SAN. My company is looking at Equalogic, but it seems like overkill for what I need.
  8. satya Moderator

    Brandon
    Don't get me wrong.. .quality doesn't come cheap, or if you mean cheap in terms of costing its better to compare costs from the vendor on your own. In any case the changes in hardware world do bring lot of options. Also sometime you may not need SAN itself DAS itself is an option too.

Share This Page