[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2013-10-26
Medium Priority
Last Modified: 2015-01-05
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?
Question by:CompTechNS
  • 4
  • 2

Author Comment

ID: 39602840
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).
LVL 30

Expert Comment

ID: 39602873
What's the version of the server 2008?
Standard or Enterprise?
Standard  is limited to 32 gigs ram.

Author Comment

ID: 39602891
It's Enterprise.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 56

Accepted Solution

andyalder earned 2000 total points
ID: 39603735
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,

Author Comment

ID: 39603750
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)?
LVL 56

Expert Comment

ID: 39603773
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.

Author Comment

ID: 39609775
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.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
New style of hardware planning for Microsoft Exchange server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question