We help IT Professionals succeed at work.

RAID configuration question for MS GP.

Wayne88
Wayne88 asked
on
Medium Priority
350 Views
Last Modified: 2016-10-27
Hi,

I have read many postings about MS SQL server raid configuration but I am getting many recommended solutions so I thought I post the question here.

First off, the server is a DELL T710 with 24gb of RAM and 8 300gb hard drives and we are rolling out a new SQL Server 2008 for our new ERP system (MS Great Plains) which will be accessed by 40-50 users.

According to Microsoft the following setup is recommended:

RAID1: O/S & applications
RAID1: SQL Database log files
RAID5: SQL Data files


After reading some of the postings, most recommended RAID10 so I assume the setup will be as follow (please correct me if I am wrong):

RAID1: O/S & applications
RAID1: SQL Database log files
RAID10: SQL Data files

Now to add a little twist to this, I was speaking to a DELL specialist about a different system configuration. We were thinking about going with a high availability system but did not because it's too costly.  The setup is still similar in a sense that there will be a storage array that can meet the required recommended configuration above but he said to me that was the old way of configuring it.  The new way and more common these days is to just do one big Raid10 for everything so I am more confused at this point.

Hopefully, you can shed some light and let me know what you think is best from your experience considering good failover, speed and of course ease of restoration (disaster recovery, we use Acronis).

Thank you,

Wayne
Comment
Watch Question

Commented:
The 'big raid 10 for everything' might work well for a lot of applications, but SQL Server can be very specific in it's needs.  It comes down to how much writes you're going to have, how often you're going to be reading from the disk, if the disk IO is performant, and how well it scales.  

You're going to load up most of your data right in ram with 48gb of memory for a little while, maybe for a long time based on your data usage patterns.  After memory stops being available in RAM due to bad code, bad queries, basic data usage, not planning for growth, or whatever, you will notice disks being an issue.  There really isn't a magic bullet solution.  

If you have the money, put the data on RAID10.  If you don't, you're stuck with RAID5.  Raid5 will give you a 50% WRITE hit.  If you have bad indexes, that write hit can be big.  If you don't have bad indexes, the write hit won't be as bad.  Seeing as GP is a MS product I'm going to assume they have properly written indexes.  Most companies I've seen do in fact put RAID5 for the data, but the current one I'm at cannot do that, there are too many changes and RAID5 would be too slow.  

For 40-50 users, RAID5 might be slow, unless they aren't doing too many writes at the same time.  If they aren't, you won't notice much of a difference.  Best thing to do is to monitor the disk IO usage and see how slow your perfmon metrics get over time, at the beginning they will be very good, but gradually get worse the more the disk has to check disks.

I'd say do not put everything on 1 raid 10.  It's bad for recoverability and optimization.  If your TLog is on the RAID array that failed and you can't get to the mirrored copy, you just lost your tail of the log which means you lost transactions.  Log files are optimized for serial reads and writes, if need be you can adjust that later.  

If you have the money put the data on RAID 10, it's just considerably faster for writes.  If you don't, put it on RAID 5 is my suggestion.
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Assuming you want full recovery mode then a big RAID 10 for everything breaks best practices because the logs are on the same spindles as the data. Also you'dhave to split it into several logical disks since the optimal stripe element size is different for the OS and the data. The old way is also more maintainable and easier to add disks to a particular array for performance improvements in the future.
CERTIFIED EXPERT
Top Expert 2015

Author

Commented:
Hi guys,

Thank you for your quick response.  I will go:

RAID1: O/S & applications
RAID1: SQL Database log files
RAID10: SQL Data files

Where can I find or can you recommned the optimal stripe size for each of the above?

Thank you,

Wayne

Commented:
SQL Server works well with the default RAID stripe size as the SQL page files are 8k and the extents are 64k
CERTIFIED EXPERT
Top Expert 2015

Author

Commented:
Thanks again for your help guys.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.