I'm looking for some advice on how best to setup a new SQL server for a customer. They're a company that has been able to see consistent measurable growth over the last few years so we're constantly making improvements where we can. Later this year they will be moving from a somewhat outdated SQL-based program to a new version so the owner has asked me to quote a new server to replace the current one that's approaching 4 years old (and has had a couple drives fail over the last few months). My goal is to not only add the new server to host the new software but to set it up to be an improvement on the existing system.
I'm looking at a new Intel-based system, with the following basic specs:
2 x Intel E5-2630
8 x 8GB RDIMM
Intel RS25DB080 w/ BBU & CACHE 2.0 + Fast Path or LSI 9271-4I w/ CacheVault
I'd prefer a tower chassis, but that might limit me to 8 drives total, so I'm debating between that and a rackmount 16-drive chassis with a 6Gbps SAS expander.
My primary question is regarding what drives to use and how to arrange them in the RAID. My options for drives are:
Seagate 600 Pro Enterprise SSD (120GB or 240GB)
Sandisk X210 SSD (128GB or 256GB) (about $30 cheaper than the Seagate)
Seagate SAS2.0 15k.7 Cheetah (300Gb to 600GB)
Seagate SAS2.0 Constellation ES 7200RPM (1TB or 3TB)
They currently have a server running SBS 2011 that handles domain management and a Server 2008 R2 Enterprise Edition system hosting the databases (SQL Server 2008 R2) and shared files. I posted last year for advice on reconfiguring the hard drives in the SQL server (see here for details
) and it's been running well, but we still see some lag occasionally during the day when everyone is active.
There are between 35 and 40 users on the network at any given time, with one primary active database that is currently 35GB and growing about 5GB per year. (I apologize for not knowing a lot about SQL Server so I haven't taken any real performance measurements, but the transaction log backups taken every two hours range between 20MB and 200MB so adding them up I assume that means between 80MB and 300MB is written to the database each day). I'm also currently checking integrity, rebuilding & reorganizing the index twice a week which results in a TLOG backup of as large as 60GB, which may cause a lot of wear on SSDs so that'll need figured in or stopped with the new server.
Ideally, I'd like to leave the current SQL server online to act as a file server so the new SQL server only hosts SQL (at least until the current system dies, so I'd prefer not to use up all the drive bays right away in the new one so there's room for expansion later). I figure with this setup I can fully implement roaming profiles without putting too much additional strain on the SBS system by redirecting to the file server.
I've done quite a bit of reading regarding using SSDs with SQL and the two items that seem to repeat are
1) It's a great idea, and it eliminates the need to have the database and transaction log on separate drives.
2) It's also dangerous, especially since it's likely that all of the SSDs in a RAID array may fail at the same time.
What I haven't been able to find is anything that really outlines a recommendation for SSD RAID setup for SQL.
Here's my question:
With an active 35GB database growing 5Gb per year and 3 to 4 prior years' databases online for reference only (about 100GB), trying to utilize SSDs for their IOPS and the subsequent performance gain, can anyone recommend a drive setup given all of the information above?