December 11, 2007 at 10:16 am
I have no experience with Sql 2005 and am finally switching from Sql2k.
Setting up a brand new install for client and could really use recommendations.
Dell PowerEdge R900 4x Quad Core X7350 Xeon, 2.93GHz, 8M Cache, 32GB Memory, 2x 36G 15k SCSI
Dell PowerVault MD3000 - (15 SAS drives: 2x 36G, 2x 73G, 11x 300G)
Win 2003 Ent. & SQL 2005 Ent.
The data is: eCommerce & statistical (50% read, 50% write)
cross-selling/promotional (90% read, 10% write)
The eCommerce (non-text/varchar) inventory & invoice data is expected to grow 6M rows/10G per month in 5 separate tables each.
The text/varchar data will grow approx 25G per month.
There is one table with varchar(75) and one with a text column that each has a Full Text Index.
The cross-selling data will have a max of 3M rows/20G, is heavily accessed, and is constantly being added to & updated.
I'm thinking that the correct Raid config would be as follows:
Server: OS 2x 36G Raid 1
Drive Shelf:
tempdb 2x 36G Raid 1
translogs 2x 73G Raid 1
n/c indexes 2x 300G Raid 1
Cross-sell & Stats 2x 300G Raid 1
eCommerce data 5x 300G Raid 5 (or is raid 6 better?)
Hot Spare 2x 300G
I'm thinking that I will eventually be creating some table partitions as well, so not sure if the above is right way to go. Any help on this set up would be greatly appreciated.
December 11, 2007 at 10:15 pm
I'd avoid RAID5, even at the expense of putting stuff together to make it fit on a RAID10 parition. As much luck as I've had with R5, there are compelling arguments not to use it and go with r10 instead.
I like the log/tempdb separations, but I'd combine the data with the indexes if I had to because of space requirements and move to R10 instead.
December 27, 2007 at 1:58 pm
I agree.
All too often I see performance problems in production environments due to RAID5 or RAID6 configurations.
You will most definately want RAID 1+0 or 0+1
It's spendy, but hey....it's a production environment.
Greg Jackson
Gregory A Jackson MBA, CSM
December 28, 2007 at 2:49 am
Hi,
I would strongly agree with RAID 5 vs RAID10 recommendation, but also consider using a many disk for each volume for better throughput. If system isn't heavy on reporting, then you may want to consider combining the tempdb with nc-indexes for more I/O for these objects, as you would have 4 drives vs 2 drives, but if your application(s) are top heavy in terms of sorting and grouping data along with heavy usage of temp tables, then you may want to keep tempdb separate. In addition, if you plan to use features such as row-versioning, you will really need to consider tempdb in terms of I/O and space, as tempdb is used more often then previously with this type of feature.
You will need to increase the # of disks for Cross-sell and Stats by 1 drive to allow for Raid10 configuration. In addition, have you provisioned for backups?
Overall, make sure you fully understand the data access requirements (e.g. inserts, updates and deletes) and use Raid 10 wherever possible to gain the greatest I/O and protection. If the the application owner could provide a CRUD chart, which will outline number of reads\writes expected based on type of application operation, this will help to ensure a good I/O balance.
Thanks,
Phillip Cox
December 28, 2007 at 8:10 am
1) Ditto on the RAID 5/RAID 10 performance stuff. Don't go there.
2) Why did you get such different drive types? Are they all 15KRPM? Will a 300GB drive actually function as a hot standby for a 36/73GB drive? Do you need 2 hot standbys? Oh, and if all drives don't have the same performance characteristics, it may not be optimal to intermix them in RAID sets.
3) Spindles are what counts for throughput. By making several 2 drive RAID1 sets you are basically limiting yourself to 1 active spindle. Back to JBOD...
4) Are you aware of things such as
a) how the size of the RAID stripe affects I/O throughput for different types of access patterns and SQL file types?
b) how the size of the NTFS format allocation unit affects I/O throughput for different types of access patterns and SQL file types?
c) how the ratio of the RAID controller cache (which you got the maximum allowable amount of, right?) affects I/O throughput for different types of access patterns and SQL file types?
d) that you need to and how to sector align your partitions for maximum I/O throughput?
5) Ditto on the "where are the backups going" question.
Let me finish by saying that if you don't get an experienced and hardware-knowledgeable DBA to assist you with the setup the potential of your system will almost certainly be VASTLY underutilized. I just had to tell a CIO recently that his mid-6 figure SAN was no better than JBOD because of absolutely horrid implementation. And you better believe that it is WAY more trouble to change things after the fact! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply