RAID-Best Practices ?

I am moving from an Oracle Db server to a SQL server running on a new w2k3
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery options.
I heard something about RAID 10?
Appreciate it!
RPMHere are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>> I am moving from an Oracle Db server to a SQL server running on a new
>> w2k3
>> member server. Prior to purchase I would like to select the appropriate
> RAID
>> level for the server which gives me the best performance and recovery
> options.
>> I heard something about RAID 10?
>> Appreciate it!
>> RPM
>|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RAID
> level for the server which gives me the best performance and recovery options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
> TX again
> Ron P
> "Ron P" wrote:
> > I am moving from an Oracle Db server to a SQL server running on a new
w2k3
> > member server. Prior to purchase I would like to select the appropriate
RAID
> > level for the server which gives me the best performance and recovery
options.
> >
> > I heard something about RAID 10?
> > Appreciate it!
> >
> > RPM|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
> > I am moving from an Oracle Db server to a SQL server running on a new
w2k3
> > member server. Prior to purchase I would like to select the appropriate
RAID
> > level for the server which gives me the best performance and recovery
options.
> >
> > I heard something about RAID 10?
> > Appreciate it!
> >
> > RPM|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>|||If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
place for SQL backup files?|||Another server.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F8350DA6-12A5-4930-B131-9CD2C217BBC4@.microsoft.com...
> If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
> place for SQL backup files?|||I agree that would be my first choice. However in the senario where there is
no option but to have the SQL backups on the same server, where is the best
place for an successful recovery.|||USB drive so you can attach it to a recovery server.
Local backups aren't.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F87D91FF-1B21-4646-83DB-4DE7BD2C3725@.microsoft.com...
>I agree that would be my first choice. However in the senario where there
>is
> no option but to have the SQL backups on the same server, where is the
> best
> place for an successful recovery.
>|||As Geoff suggests, you need to get the data off the server. However, if
your .mdf data files are on the RAID 5, and you need to first backup to the
local disks, backup to the other physical disks on your server. It sounds
like your only option is the RAID 1 partition. If two of your RAID 5 disks
die, you still have your latest backup on the RAID 1 partition.
Mark
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F8350DA6-12A5-4930-B131-9CD2C217BBC4@.microsoft.com...
> If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
> place for SQL backup files?