Solved

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

Posted on 2013-10-26
7
293 Views
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
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?
0
Comment
Question by:CompTechNS
  • 4
  • 2
7 Comments
 
LVL 1

Author Comment

by:CompTechNS
Comment Utility
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).
0
 
LVL 30

Expert Comment

by:pgm554
Comment Utility
What's the version of the server 2008?
Standard or Enterprise?
Standard  is limited to 32 gigs ram.
0
 
LVL 1

Author Comment

by:CompTechNS
Comment Utility
It's Enterprise.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 55

Accepted Solution

by:
andyalder earned 500 total points
Comment Utility
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,
0
 
LVL 1

Author Comment

by:CompTechNS
Comment Utility
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)?
0
 
LVL 55

Expert Comment

by:andyalder
Comment Utility
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.
0
 
LVL 1

Author Comment

by:CompTechNS
Comment Utility
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.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now