question about drives configuration

Hi,
We will build new clustering SQL Server. The question is that what kind
drives configuration for user-defined database data files and transaction log
files will improve performance, RAID 1+0, RAID 0+1, RAID 5 or RAID 10 etc.
Regards!
-Chen
The normal drive performance rules do not change on a cluster. RAID 1
(mirror) or 1+0 (stripe of mirror sets) gives the best performance, but has
the highest per-byte storage cost. RAID 5 (stripe with parity) is very low
cost but does have a performance hit for transactional data and SQL log
usage. RAID 0 + 1 (mirror of stripe sets) is not recommended. RAID 10 is a
generic term for sometimes Raid 1+0 and Sometimes RAID 0+1. Make sure which
one you are getting.
Geoff N. Hiten
Microsoft SQL Server MVP
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:F2963003-41D2-4132-B74C-FBF92EE46984@.microsoft.com...
> Hi,
> We will build new clustering SQL Server. The question is that what kind
> drives configuration for user-defined database data files and transaction
> log
> files will improve performance, RAID 1+0, RAID 0+1, RAID 5 or RAID 10 etc.
> Regards!
> -Chen
>
|||Because many vendors confuse the terms RAID 10, RAID 1+0, and RAID 0+1, use the terms mirrored striping and striped mirroring.
Striped mirroring is the best solution for a database server requiring high performance and high availability. Capacity can be a problem, however, because of the number of disks required and their cost.
More Info:
========
Striped Mirroring
===============
Striped mirroring writes logical blocks of data across two or more mirrored sets of disks, creating a single volume with no redundant information between the mirrored sets of disks. Each mirrored set of disks consists
of two or more disks. Striped mirroring provides the same high level of availability as mirroring. The available capacity of a striped mirror is equal to that of each disk in the mirror set (in a two-disk mirror set, 50
percent of the disk capacity is used for data protection). Unlike mirroring, however, striped mirroring provides symmetrical performance. Read performance is very fast because data is available from each disk in
the mirror. In striped mirror implementation, RAID array and SAN controllers support simultaneous reads from each member of the mirror, which maximizes read performance. Write performance is also fast because
writes occur simultaneously across each mirrored set.
With striped mirroring, if a drive fails, read and write performance is slightly reduced, but only for data stored on the mirror set containing the failed disks. Read and write performance to and from the other mirrored
sets is not affected. Striped mirroring can survive the failure of multiple drives, provided at least one drive in each mirrored set survives.
Mirrored Striping
===============
Mirrored striping writes logical blocks of data across two or more disks, creating a single logical volume with no redundant information between the disks, and then mirrors the volume. Read and write performance is
very fast, but mirrored striping is not as fault tolerant as striped mirroring. When a single disk in a stripe fails, the data remains available in the other stripe, but the stripe is broken. With one broken stripe, the failure of a
disk in the remaining stripe causes the data to become unavailable. In addition, when a stripe fails, read performance is slower because only one stripe is functional.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||I was under the impression that with write-back caching the performance
difference between raid 5 and the raid 01, 10,1 variants is much the same.
Is this not true?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uqzYB7ygFHA.3608@.TK2MSFTNGP12.phx.gbl...
> The normal drive performance rules do not change on a cluster. RAID 1
> (mirror) or 1+0 (stripe of mirror sets) gives the best performance, but
has
> the highest per-byte storage cost. RAID 5 (stripe with parity) is very
low
> cost but does have a performance hit for transactional data and SQL log
> usage. RAID 0 + 1 (mirror of stripe sets) is not recommended. RAID 10 is
a
> generic term for sometimes Raid 1+0 and Sometimes RAID 0+1. Make sure
which[vbcol=seagreen]
> one you are getting.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:F2963003-41D2-4132-B74C-FBF92EE46984@.microsoft.com...
transaction[vbcol=seagreen]
etc.
>
|||If the amount of cache is sufficient to buffer all the I/O to the drives
then it may not be to much of an issue. But with direct attached storage or
low end SAN controllers you usually only get up to 512K of controller cache.
That can easily be saturated with a decent size checkpoint and then you are
at the mercy of the drives themselves. If you are looking for performance
you should not be thinking RAID 5.
Andrew J. Kelly SQL MVP
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23R0VkFghFHA.4028@.TK2MSFTNGP10.phx.gbl...
>I was under the impression that with write-back caching the performance
> difference between raid 5 and the raid 01, 10,1 variants is much the same.
> Is this not true?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uqzYB7ygFHA.3608@.TK2MSFTNGP12.phx.gbl...
> has
> low
> a
> which
> transaction
> etc.
>
|||RAID5 requires many more IO operations for a transactional change than any
of the other RAID configurations. This can impact cache performance since
it requires more cache space to handle the extra block read-writes. The
only place I find RAID5 to be good is where there is a daily load process
and everything else is read-only. transactional system, SAN or not, needs a
RAID 1+0 layoud for best performance.
Geoff N. Hiten
Microsoft SQL Server MVP
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23R0VkFghFHA.4028@.TK2MSFTNGP10.phx.gbl...
>I was under the impression that with write-back caching the performance
> difference between raid 5 and the raid 01, 10,1 variants is much the same.
> Is this not true?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uqzYB7ygFHA.3608@.TK2MSFTNGP12.phx.gbl...
> has
> low
> a
> which
> transaction
> etc.
>