Best practice - disks/RAID

The best thing for you to consider is to think about independent spindles.

Use RAID 1 for your OS/swap/SQL program files
Use a single disk for your tempdb (it gets created every time SQL is started
anyways)
that leaves 6 disks to split between your data and log files. Data and log
must be kept on seperate disks if you consider transactional consistency
important.
I would recommend considering a RAID1 for your log files (they are
sequential writes for most of the time), and then a RAID 5 volume with the
remaining 4 disks.

Consider this - the best possible performance would be to have 8 independent
disks if you could accurately balance your load accross those disks.
Obviously this is not feasible because you want some level of redundancy,
which means mirroring or RAID 5 as a minimum, and your load might vary, so
it is not worth trying to micro-manage your disk space. That said, data
reads are usually random, and involve reads and writes. Log files are
typically sequential writes. The biggest performance killer is moving the
disk head, so avoid placing everything on a large 6-volume RAID-10 volume as
that will guarantee that the heads have to move when you update your
database (as both the logs and the data has to be written). Keep them on
independent spindles.

Good luck!
Shan McArthur – VP, Technology
ADXSTUDIO Inc. | 200 - 1445 Park Street | Regina, SK Canada | S4N 4C5
Tel: 306.569.6502 | Toll-Free: 800.508.7811 ext. 502 | Fax: 306.569.8518
"weaverbeaver" wrote in message

>
> I am looking to order hardware for a new SQL server to run SQL 2005 for a
> high utilisation database (lots of database writes). We don't have a SAN
> so I
> was looking to spec a server with 8 15k SAS drives configured as RAID 10
> as
> all docs seem to point to this as providing the best performance (I am
> limited to 8 disks for the corporate choice of server). My question
> relates
> to other articles which say transaction logs should be on a dedicated
> disk,
> tempdb should be on a dedicated disk if highly utilised (which this is).
> Then
> there is the best practice of having the OS on a seperate mirrored pair
> for
> resillience. I am quickly running out of physical disks to use RAID10
> which
> is wasteful in terms of disks.
>
> For best performance, Should I go down the raid10 route and have
> everything
> on the same RAID volume (allbeit partitioned) or am I better off moving
> away
> from RAID 10 in favour of seperating the OS, transaction logs and
> database,
> tempdb etc?
>
> I appreciate this may be dependant on many factors but any advice would be
> much appreciated
>
> regards
>
> Karl
 >> Stay informed about: Best practice - disks/RAID 

Shan,

I need to build one server for my new start-up company. I will be hosting an online application on Windows Server 2008 R2 with SQL Server 2008 R2 Web Edition. My application is an online address book for sales people. They will be served ASPX pages that load dynamic data (viewable) per their account login. They will be able to enter data BUT most of the processing is READING their data.

That said, I need to build a server to host this thing and try to get it off the ground. I have a $1500-$2000 budget. After much reading...I have found what you said in this article (a few years ago) the most helpful.

I really want this thing to be able to server pages VERY QUICKLY for the first couple of years.

How would I build the fastest page loads for my application?

Should I go with a cheap Duo Core or go with a Quad core processor?
Should I go with 8GB ram or 12gb ram?
Most importantly, how will I set up my drives?
I'm thinking....
2 disks in RAID1 for OS/swap/SQL program files.
1 disk for the tempdb
2 disks in RAID1 for log files.
4 disks in RAID10 for Data Files

I can afford two to five small SSDs. Where should I implement SSDs in order to maximize speed?
The rest of the drives will be 320GB HDDs for price reasons.

I know that the code will have alot of effect but I'm no coder. I'm just trying to get the fastest setup for the price under the assumption that the code will be done right?

I have read that I would need one data file per core of chip and also need to "pre-size" the log and tempdb files. How would I figure out what size I would need?

Any help or opinions on this will be much appreciated.

Thank You,
Devin Webb
Louisville, KY



> On Tuesday, January 29, 2008 4:32 PM weaverbeave wrote:

> I am looking to order hardware for a new SQL server to run SQL 2005 for a
> high utilisation database (lots of database writes). We don't have a SAN so I
> was looking to spec a server with 8 15k SAS drives configured as RAID 10 as
> all docs seem to point to this as providing the best performance (I am
> limited to 8 disks for the corporate choice of server). My question relates
> to other articles which say transaction logs should be on a dedicated disk,
> tempdb should be on a dedicated disk if highly utilised (which this is). Then
> there is the best practice of having the OS on a seperate mirrored pair for
> resillience. I am quickly running out of physical disks to use RAID10 which
> is wasteful in terms of disks.
>
> For best performance, Should I go down the raid10 route and have everything
> on the same RAID volume (allbeit partitioned) or am I better off moving away
> from RAID 10 in favour of seperating the OS, transaction logs and database,
> tempdb etc?
>
> I appreciate this may be dependant on many factors but any advice would be
> much appreciated
>
> regards
>
> Karl


>> On Tuesday, January 29, 2008 5:10 PM Andrew J. Kelly wrote:

>> High Utilization is relative so it is hard to give you an exact answer. Are
>> you talking about 100 trans a sec or 10,000 a sec? Will it be large
>> transactions or small ones? If all you can have is 8 drives then you are
>> limited in what you can support. You can usually get by with a Raid 1 for
>> the OS and potentially place the transaction logs (including Tempdb) on
>> there as well, just no data files. You can then have a 6 disk Raid 10 for
>> the user and tempdb data files. Another choice is to have a 4 disk Raid 10
>> for the user data and a Raid 1 for tempdb data files. But it all depend son
>> your app and how you use the data. You should test each way to see what
>> works best under your actual load.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>>
>> "weaverbeaver" wrote in message
>>


>>> On Thursday, February 07, 2008 1:25 PM Shan McArthur wrote:

>>> The best thing for you to consider is to think about independent spindles.
>>>
>>> Use RAID 1 for your OS/swap/SQL program files
>>> Use a single disk for your tempdb (it gets created every time SQL is started
>>> anyways)
>>> that leaves 6 disks to split between your data and log files. Data and log
>>> must be kept on seperate disks if you consider transactional consistency
>>> important.
>>> I would recommend considering a RAID1 for your log files (they are
>>> sequential writes for most of the time), and then a RAID 5 volume with the
>>> remaining 4 disks.
>>>
>>> Consider this - the best possible performance would be to have 8 independent
>>> disks if you could accurately balance your load accross those disks.
>>> Obviously this is not feasible because you want some level of redundancy,
>>> which means mirroring or RAID 5 as a minimum, and your load might vary, so
>>> it is not worth trying to micro-manage your disk space. That said, data
>>> reads are usually random, and involve reads and writes. Log files are
>>> typically sequential writes. The biggest performance killer is moving the
>>> disk head, so avoid placing everything on a large 6-volume RAID-10 volume as
>>> that will guarantee that the heads have to move when you update your
>>> database (as both the logs and the data has to be written). Keep them on
>>> independent spindles.
>>>
>>> Good luck!
>>> Shan McArthur ??? VP, Technology
>>> ADXSTUDIO Inc. | 200 - 1445 Park Street | Regina, SK Canada | S4N 4C5
>>> Tel: 306.569.6502 | Toll-Free: 800.508.7811 ext. 502 | Fax: 306.569.8518
>>> "weaverbeaver" wrote in message
>>>


>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>> Styling the WPF ScrollViewer
>>> http://www.eggheadcafe.com/tutorials/aspnet/f51ddf8c-5227-4f1b-a5df-ec...b3439ca
 >> Stay informed about: Best practice - disks/RAID