Looking for advice on drive setup for new SQL Server (SSD vs SAS 15k.7 & RAID Setup)

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
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?
Who is Participating?
andyalderConnect With a Mentor Commented:
Regarding your RAM I think you'll find 16GB DIMMs are cheaper than 2*8GB at the moment, plus it gives you more room to add more later.

SSDs do not eliminate the requirement to have the transaction logs on a different array than the data, in the full recovery model the logs and data are separate so if you lose the array the data is on you can do a full restore and then play the transaction logs back to get the data right up to date.

Database is so small that a mirrored pair for data and a mirrored pair for logs will do, you can put the logs on the OS disks if you want. You might as well use SSDs since you don't need much space,
CompTechNSAuthor Commented:
I forgot to also mention that the new server will also have Server 2008 R2 and SQL Server 2008 R2 on it (they have a volume license and we're trying to also stay within the limits of the SBS 2011 CALs).
What's the version of the server 2008?
Standard or Enterprise?
Standard  is limited to 32 gigs ram.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

CompTechNSAuthor Commented:
It's Enterprise.
CompTechNSAuthor Commented:
It takes 8 chips to run 2 processors in 4-channel mode. So if I go with 16GB chips it won't save any money.

What about the issue of wear on the SSD's and the fact that the mirrored pairs are likely to failed at the same time (maybe this is more in support of a separate pair for database and transaction log)?
Benefit of using all 4 channels is minimal, see  http://docs.ts.fujitsu.com/dl.aspx?id=a17dbb55-c43f-4ac8-886a-7950cb27ec2a where the STREAM benchmark shows a significant improvement but the SPECint benchmark shows little difference. There again the price difference isn't enough to quibble about although you could probably get away with a single CPU.

SSDs are unlikely to fail at exactly the same time although they will admittedly have similar wear. You can always mix manufacturers to avoid that problem, so long as you use enterprise ones that have reserve space they should last pretty well.
CompTechNSAuthor Commented:
So based on what you're saying, Andy, here's what I'm thinking:

2 x Seagate 600 PRO SSD @ 240GB for OS, Database Files, etc...
2 x Seagate 600 PRO SSD @ 120GB for main database TLOG
2 x Seagate Constellation ES 1TB SAS 7200RPM for backups & possibly storage of network shares down the road if the old server goes offline.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.