Solved

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

Posted on 2013-10-26
7
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 1

Author Comment

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

Expert Comment

by:pgm554
ID: 39602873
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
ID: 39602891
It's Enterprise.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 55

Accepted Solution

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

Author Comment

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

Expert Comment

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

Author Comment

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

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When we purchase storage, we typically are advertised storage of 500GB, 1TB, 2TB and so on. However, when you actually install it into your computer, your 500GB HDD will actually show up as 465GB. Why? It has to do with the way people and computers…
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 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