Home / server / Questions / 559389
In Process
ChrisNZ
Asked: 2013-12-05 23:49:26 +0800 CST

Suitability of DL380p-Gen8 + D2700 for SQL 2012 Standard Edition

  • 772

A question for those who know about HP server performance and SQL...

I am trying to spec a suitable server that has the following requirements:

  1. Run SQL 2012 Standard Edition (I cannot get approval for Enterprise edition)
  2. Main database is currently 600 GB, allow for growth up to 1,200 GB over next 3 years
  3. Log files for main database is currently 120 GB, allow for growth to 300 GB
  4. OLAP database is 60 GB, allow for growth up to 120 GB
  5. Reporting Services is about 5 to 10 GB

Windows 2012 Standard Edition allows >32GB (yay!) and SQL 2012 Standard Edition allows 64 GB RAM usage, so I was thinking that a server with 96 GB of RAM would be sufficient for the OS, SQL, OLAP, and RS...

Now my concern is disk space requirement, I would like to put each component on its own Raid 1 or Raid 1+0 volume, so by that measure I would need:

  1. 2 x 300GB 15K RPM 6GB DP SAS disks in RAID 1 for Boot OS (300 gb usable)
  2. 8 x 300GB 15K RPM 6GB DP SAS disks in RAID 1+0 for main database (1,200 gb usable)
  3. 2 x 300GB 15K RPM 6GB DP SAS disks in RAID 1 for log files for main db (300 gb usable)
  4. 2 x 300GB 15K RPM 6GB DP SAS disks in RAID 1 for OLAP
  5. 2 x 300GB 15K RPM 6GB DP SAS disks in RAID 1 for Reporting Services
  6. 2 x 300GB 15K RPM 6GB DP SAS disks in RAID 1 for Temp DB

OK, I know that Reporting Services and Temp DB do not need 300 GB each, but I think there is something to be said for having all the same disk size

Since the HP DL380p-Gen8 only has 8 disk slots, I would need an external SAS drive array such as the D2700 which would hold the remaining 10 disks.

I am not concerned about having too much disk space.

My main goal is to get the maximum performance out of this server.

So my primary question is: Is the IO throughput between the DL380 and the D2700 sufficient? It appears it only uses a single mini-SAS cable to connect the external drive enclosure to the server.

My secondary question is, does the overall server spec confirm to good/best practice?

I have been given a ballpark budget limit of $60K, which I think is insufficient, so I may be able to ask for a bit more if I can justify it.

Many thanks in advance!

sql-server

4 Answers

  1. I dont know aobut HP but the disc layout smells "SLOW" to mee.

    Seriously.

    First - separate RAID 1 for everything means that if something is not in use, the IOPS is - wasted. I would go with one RAID 10.

    Second, you use expensive 15k ROM discs and - whow - would get about 100 times the IO Performance - for a lower price - with SATA based.... SSD.

    So, I would personally make sure you don't go on a hirig spree in my company ever again after this proposal. It is very state of the art - for the year 2000. Now we have some years later.

    With the DL 380p having 8 drives I would go with an ALL OUT SSD SETUP, using 480GB Samsung 843T enterprise level SSD. Raid 10. THat is 1200gb rougly usable space. with 8 drives, 1600 with 8 - and the performance will fly around your more expensive setup. Buy 9-10 drives so a replacement is there and there you go. Not sure the raid controller will be able to handle that bandwidth ;)

    • 4

  2. I'm trying to avoid the discussion going on in the comments, so I'll throw in my ideas here..

    • Do not use one large RAID array with busy SQL servers. There are very good reasons to physically separate data, logs and tempdb on different spindles. You do not want I/O queues against the same array when doing OLTP or any other kind of transactional-heavy environment
    • SQL servers greatly benefit from SSD's, as they nearly always write/read in a sequential matter. The increased bandwith helps tremendously. Make sure that you use dual-port SAS SSD's, do not use the SSD's with S-ATA interfaces.
    • Memory (RAM) is still the most important factor of how fast your queries will run. The more memory, the more data and execution plans cached. Do not underestimate this. 64GB of RAM can quickly be a limiting factor in the future, so factor in that you might want to upgrade to SQL Server Enterprise one day. Leave room for RAM upgrades in other words.
    • Controller cache is super important for writes in sequential workloads. Read cache only helps when you either have hotspots (not very common with SQL), or when the controller is smart enough to read-ahead on the disks.
    • Having a separate array just for analysis services and reporting services seems a bit overkill. It does however depend on your specific situation. Only you can answer how much IOPS/response time you need for each component.

    That being said - if you do go the SSD route then I'd suggest this as a minimum:

    • 2 x 300GB 10k in RAID1 for OS+SQL program files (No point in wasting money on SSD for this)
    • 8 x 400GB eMLC SSD in RAID10 for DB-data/OLAP/Reporting (get the SSD with the fastest READ iops/bw you can get)
    • 2 x 400GB SLC (or eMLC) SSD in RAID1 for DB-log (SLC are expensive, but very trustworthy. Use eMLC if you can't afford it)
    • 2 x 200GB SLC (or eMLC) SSD in RAID1 for TempDB (same as above)
    • 4

  3. This is a bad design... Part superstition, part misunderstanding of how storage technologies have evolved.

    But there's hope!

    • You should be looking at the 25-bay HP ProLiant DL380p Gen8 server. It accommodates 25 x 2.5" disks on a SAS expander backplane. That elminates the need for an external D2700 storage enclosure.

    • The sweet spot for 2.5" SAS enterprise disks right now is 900GB. You can get them in 300GB, 450GB, 600GB, 900GB and 1.2TB capacities nowadays. 900GB disks are relatively cheap now.

    • HP Smart Array controllers allow you configure multiple logical drives per group of physical disks. Something like 16 spindles of 900GB drives (in one array) could be carved into the respective logical volumes you need. That way, you get the volume isolation you need, but the collective I/O capabilities of 16 or more disks.

    • HP controllers have the ability to leverage SSDs as read cache to back a drive array.

    • Going with all spinning disks at this point today is old-school and won't be the most effective use of resources.

    • You really can't use third-party SSDs in Gen8 HP servers.

    • I could build an HP spec for $25,000 that would maximize the performance of the platform.

    • 3

  4. HP ProLiant DL380p Gen8 16-bay.

    2 x 300GB 10k raid 1 OS
8 x 300GB 15k raid 10 mainDB
1 x 128GB Pcie fusion IO card enterprise class for temp DB
2 x 600GB 10k raid 1 for all logs
2 x 300GB 10k raid 1  for reporting and OLAP
2 x 900GB 10k raid 0 flat file backup

    Two raid controllers with write back and battery 1GB memory

    Make sure you put the raid 10 on 1 and the rest of them on the other card and all SAS.

    • 1