SQL Server Performance

Finalized proposed production Hardware Configurati

Discussion in 'Performance Tuning for Hardware Configurations' started by rweinstein, Sep 30, 2004.

  1. rweinstein New Member

    Hello,<br /><br />I think I have finalized my final production hardware configuration for next year.<br /><br />Does anyone see any issue with my config? Does anyone have experience using a Network Appliance NetApp FAS270C redundant Enclosure?<br /><br />Just a note, my DB is 70% reads, 30% writes.<br />Thanks!<br />-----------------------------------------------------------------------------------------<br /><br />1.0Long-Term Hardware Solution Proposal:<br /><br />Server 1 (dedicated for SQL server DB):<br />Windows 2003 Server OS - 64 Bit Version<br />4 X 3.6GHz 800MHz FSB processors (Expandable to <img src='/community/emoticons/emotion-11.gif' alt='8)' /> - (Hyper-threaded to <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />16GB RAM (expandable to 64GB)<br />6 Internal RAID 1 Disks:<br />·Volume 0 (2 RAID 1 74GB 15K disks) - Windows 2003 OS and SQL Enterprise Manager Application<br />·Volume 1 (2 RAID 1 144GB 10K disks) - Backup Drive<br /><br />FAS270C Redundant Enclosure (14 drive external chassis rack)<br />·Volume 2 (2 RAID 1 74GB 15K disks) - TempDB files<br />·Volume 3 - (10 RAID 5 36GB 15K disks- 324GB usable) - File Group 1 (.mdf files)<br />·Volume 4 - (2 RAID 1 74GB 15K disks) - Transaction Log Files (.ldf)<br />·Volume 5 - (2 RAID 1 74GB 15K disks) - Spare Drive Set<br /><br />Server 2 (Load Balancing Server used for Cognos Cube processing, Data Warehouse Update processing, and a backup for server 1 in case of failure):<br />Windows 2003 Server OS - 64 Bit Version<br />4 X 3.6GHz 800MHz FSB processors (Expandable to <img src='/community/emoticons/emotion-11.gif' alt='8)' /> - (Hyper-threaded to <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />16GB RAM (expandable to 64GB)<br />4 Internal RAID 1 disks<br />·Volume 0 (2 RAID 1 74GB 15K disks) - Windows 2003 OS and SQL Enterprise Manager Application<br />·Volume 1 (2 RAID 1 144GB 10K disks) - Backup Drive<br /><br />Server 3 (Load Balancing Server used for Cognos Cube processing, Data Warehouse Update processing, and a backup for server 1 in case of failure):<br />Windows 2003 Server OS - 64 Bit Version<br />4 X 3.6GHz 800MHz FSB processors (Expandable to <img src='/community/emoticons/emotion-11.gif' alt='8)' /> - (Hyper-threaded to <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />16GB RAM (expandable to 64GB)<br />4 Internal RAID 1 disks<br />·Volume 0 (2 RAID 1 74GB 15K disks) - Windows 2003 OS and SQL Enterprise Manager Application<br />·Volume 1 (2 RAID 1 144GB 10K disks) - Backup Drive<br /><br />Server 4 (Web Server):<br />Windows 2003 Server OS - 64 Bit Version<br />4 X 3.6GHz 800MHz FSB processors (Expandable to <img src='/community/emoticons/emotion-11.gif' alt='8)' /> - (Hyper-threaded to <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />16GB RAM (expandable to 64GB)<br />4 Internal RAID 1 disks<br />·Volume 0 (2 RAID 1 74GB 15K disks) - Windows 2003 OS and SQL Enterprise Manager Application<br />·Volume 1 (2 RAID 1 144GB 10K disks) - Backup Drive<br />
  2. derrickleggett New Member

    There is no way in heaven or hell I would have my OS drives on RAID 0. Also, use the net storage device for your backups and use those extra drives for tempdb.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  3. Luis Martin Moderator

    Where is RAID 0?, I only see 2 disk in RAID 1.

    BTW: A dream hardware.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  4. rweinstein New Member

    Derrick,

    The drives are RAID 1 and not zero. The Drive "Volume" is zero (just a number), but all OS is on RAID 1.

    If I use the Volume 1 drive for my TempDB, instead of having it on the External SAN solution, won't this give me an extra point of failure if something happens to the drive holding the TempDB? I decided to put the TEMPDB on the SAN because of its redundancy and backups. Is this such a bad idea?

    Luis,

    I'm not following your input on "dream hardware." Can you expand on this? Do you mean that I am asking for too much, or is this solution not possible? I'm more concerned with the drive types, file locations, and expansion capabilities of the server.

    This is for budget purposes, but when it comes time to implement, I would rather take a phased approach. For example, maybe start out with 2 processors and 4GB of memory. The statistics we are starting to collect now and through this upgrade will let us know if we should slip in additional processors and memory.

    Please let me know if you have any thoughts about this or what you would recommend for a strategy or modification to the architecture.

    Thanks.
  5. Luis Martin Moderator

    What I mean is I don't see any problem about your plan.
    With 70% reads and 30% writes, sound a very good configuration what you choose.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  6. joechang New Member

    the Xeon MP processors will not have 800MHz FSB,
    it will just be too difficult to drive 4 CPUs on one bus at 800MHz.
    it will probably be 667MHz.
    also, i am not sure who will have an 8-way capable system for the Xeon MP. The Xeon MP has a 36-bit physical address bus, limiting it to 64GB.
    this might be enough for many apps, but not the tpc-c benchmark, which is a big deal to system vendors.
    there will definitely be an 8-way Itanium 2 and possibly an 8-way Opteron.
    however, the Xeon MP next year may have dual cores, with the system supporting 4 chips for a total of 8 cores
  7. derrickleggett New Member

    That will be interesting won't it Joe. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> <br /><br />You didn't give the SAN disk layout, so I wasn't sure where you were putting tempdb. I wouldn't have any problem having it on the SAN. If you have enough space and the disk IO is good on the SAN, you might want to look at boot-from-SAN options as well.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  8. rweinstein New Member

    Thank you all for your input.

    I am learning that this solution is pricing out at over $200K, which is too much to even budget. I will look to scale this down, does anyone have any immediate suggestions?

    I was thinking to reduce the memory to 8GB and remove my secondary load balancing server (server 3), but it may not be enough. What else can I cut?

    Luis, maybe you were right in saying this was a "dream." It puzzles me to find out that HP memory is so much more expensive than the rest. Is this memory really THAT much better as advertized? I thought memory was cheap?
  9. joechang New Member

    i would ditch the SAN and use SCSI disks, the SAN is a big single item cost with no performance advantage over SCSI, actually SAS should be available then.

    also, 4-way for the web server is a waste, consider dropping down to 2 4-way systems and 2 2-way system, memory should be cheaper next year
  10. derrickleggett New Member

    The HP servers always ship with 2GB of RAM as part of the "package". We buy Kingston RAM for everything else. It's easily as good as the HP RAM. It's also about a tenth of the ridiculous price that HP tries to rip people on. I also agree with Joe on the 4-way web servers. You might want to look at buying 4-way systems with only 2 processors in them. You can then upgrade as needed. On the DL580 model, that would knock off about $16,000 per server. Those extra processors run about $8,000 on the HP series. I'm not sure I agree on the SAN piece. Look at an HP MSA-1000 for a starter SAN. There's a big difference on pricing with that option.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page