Re: Newbie storage questions... (RAID5, SANs, SCSI)

David Sworder wrote:
> Hi,
>
>     I'm reading a book that describes how to plan an SQL Server
> installation. The book warns that one should never use RAID5 unless
> the volume receives less than 10% writes (i.e. >90% reads).
> Apparently the performance penalty for data writes is quite high with
> RAID5 but I'm having trouble understanding exactly what the penalty
> is. Consider the following example:
>

A lot of the "don't use RAID5" for databases relates to software RAID
implemntations on the host where instead of performing a single I/O
operation the host has to perform the multiple operations and also to
calculate the parity. If you offload the RAID functions an external
subsystem (especially a relativiely modern one with plenty of write-back
cache) the write performce of RAID5 will almost certanly be more than
adequate for your needs. The caveat of "almost" is there because you don't
mention any specific performance requirements.

> This is how my book describes it, but in practice RAID5 doesn't seem
> to be as slow as this. Could someone please confirm?

RAID-5 will certainly be slower than say RAID 0+1, it's just the nature of
the beast, but its performance is more than adequate for an awful lot of
applications, otherwise it wouldn't be so popular. A common approach with
databases is to put the more write intensive portions (like transaction
logs) onto a less write-sensitive RAID device (say mirror a couple of drives
and use those for the transaction log device) and use RAID5 for the less
write intensive data tables.

>
>     Now let me ask you a question about bandwidth for data transfer
> from hard drives. Consider the following device that Dell sells which
> appears to be a stand-alone rack-mountable RAID device:
>
>
>
http://www1.us.dell.com/content/products/compare.aspx/sanet_fibre?c=us&cs=555&l=en&s=biz
>
>     This thing costs nearly $12,000, but yet its bandwidth of
> 200MB/sec is inferior to standard SCSI which is 320MB/sec. My
> question is: What is the advantage of this device over traditional
> SCSI RAID?

First, that 200MB/s is misleading, FC is a duplex protocol, it can do
200MB/s in each direction simulataneously, so it's really more like 400MB/s.
Second in a database application, you'll almost certainly never see 200MB/s
of throughput, let alone 320MB/s so raw throughput is a poor measure, what
you are really interested in is I/O operations/sec and this is the Fibre
channel protocol is much more efficient in terms of how it uses the
available bandwidth becuase it can setup and tear down an I/O transaction
much more quickly than typical SCSI allowing it to handle more
transactions/sec for a given amount of bandwidth.

There are other benefits to using FC in terms of clustering and other
advanced functions which brings me to...

>
>     One last question: The SQL Server seems to be a single point of
> failure. If the motherboard or power supply in the SQL Server machine
> goes down, my entire application will go down. Is it possible to set
> things up in such a way that TWO machines running SQL Server can be
> attached to the same harddisk? If one SQL machine dies, the other
> machine automatically comes online? In this scenario, where is the
> harddrive located? It can't be in one of the machines because if that
> machine were the one that crashed, the other SQL Server machine would
> be unable to access that drive! That's why I'm wondering if that Dell
> external RAID solution (see above) might be appropriate for me. What
> do you think?

Micrsoft has a multinode clustering function for its operating systems and
applications to address just this problem, do a search on Microsoft's
website for clustering, you'll find plenty of information. But in order to
cluster, you'll need a shareable subsystem and today that pretty much mean a
Fibre channel attached subsystem like the one you mentioned earlier.
Theoretically, you can do the same with SCSI, but's its usually ugly and I
don't if MS still supports any external SCSI subsystems for clustering
applications.


-- 
Nik Simpson