Optimal drive configuration for SQL Server 2008 R2 server

Have a new server with (6) 800GB SSDs SATA.  Need input on the best way to configure the drives for optimal performance.  Space is important as well.  It will be running SQL 2008 R2 Enterprise.  Heavy read database.  Was considering
RAID 1 – OS/Programs and TempDB (data and logs)
RAID 5 - Database Files (data and logs)

Thanks in advance.
FBCSAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lionel MMSmall Business IT ConsultantCommented:
I assume you have read the differences on RAID. The only downside to raid 5 is if one drive goes down and then another goes down before the previous one goes down. It also takes a long time to rebuild after a drive failure. I use raid1 on my OS drives too. https://en.wikipedia.org/wiki/RAID
ste5anSenior DeveloperCommented:
An optimal configuration includes separate volumes on separate paths and spindles for: OS, DATA, LOG, TEMPDB.

Depending on the usage of tempdb, you'll need at least four tempdb files (in a perfect world on their own volumes).

For optimal read performance the amount of RAM and the database size, respectively the amount of data read per day is important. When the data fits into memory (hot buffers), then the read performance of the IO subsystems are not that critical.

How big are your database files?

You said you' have 6 drive, you want for optimal performance. So redundancy is not necessary. Thus RAID1 and RAID5 are a poor choice. Use two small SAS drives for OS and system dbs. Create two RAID0 (2x for TEMPDB, 4x for DATA/LOG).
Vitor MontalvãoMSSQL Senior EngineerCommented:
RAID 1 – OS/Programs and TempDB (data and logs)
 RAID 5 - Database Files (data and logs)
RAID 1 or 10 for TempDB.
RAID 5 for datafiles since you'll have heavly read operations. Is the recovery model of the database simple or full? If full I would move the transactional log file to a RAID 1 or 10 since it will have constant write operations.

About the size, at least TempDB should have at minimum the size of the largest database table. And that's the minimum. You can split TempDB data file in the same number of processors until a maximum of 8 files and all must have the same size. This is only a recommendation to improve performance.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

FBCSAuthor Commented:
Database file sizes
Data files - 772 GB
Log file - 35 GB


Recovery model is full

We would like some redundancy.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Have a new server with (6) 800GB SSDs SATA
So, in total is 6x800GB = 4.8TB, right?

We would like some redundancy.
Which kind of redundancy are you talking about?
FBCSAuthor Commented:
So, in total is 6x800GB = 4.8TB, right?
Correct.

Which kind of redundancy are you talking about?

I would like to be covered for at least one drive failure
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would like to be covered for at least one drive failure
Only RAID 5 then.
But for tempDB you won't need redundancy since it's getting recreated everytime the SQL Server service starts, so it can be in a RAID 10.
FBCSAuthor Commented:
RAID 1 or 10 for TempDB.
 RAID 5 for datafiles since you'll have heavly read operations. Is the recovery model of the database simple or full? If full I would move the transactional log file to a RAID 1 or 10 since it will have constant write operations.
So considering that I have 6 drives to work with, would the below be the recommended config.:
RAID 1 (2 x) – OS/Programs, TempDB (data and logs), and database log file
RAID 5 (4 x) - Database data files
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, with 6 drives and going for RAID 5, there will be no plenty of choice so the one you posted should do it fine. I would go for that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.