Need SAN configuration advice

Good afternoon Experts,

This isn't so much of a problem, more like just needing some advice.

I'll start with a little information about our environment.  We have a few MSSQL servers (version 2005/2008) that we need to setup in a failover cluster for fault tolerance.  We'll call them Production, Staging and DocumentStorage.

The Production server is the back-end database for an Internet portal that end-users access to run reports, generate statistics, etc.  Fairly high activity of mostly reads from SSRS reports running against it.  Database size is approximately 500 GB.

DocumentStorage is simply a document imaging system.  Database full of metadata, and a path to a document stored on a shared drive.  Database size only a few GB and probably 700GB of documents stored in file shares. Pretty low activity.

The Staging server loads raw data from multiple sources such as CSV's, XML, fixed-width text files, DBF, PDF, MS Access, pretty much any format you can imagine.  Data is loaded from their raw sources, transformed, scrubbed, cleaned up, etc. on the Staging server via SSIS packages that run through the SQL Server Agent and then sent to Production.

The database size on this server isn't enormous, maybe 80GB or so, but only because data is deleted XX days after it is sent to production, we don't keep alot of history here.  The amount of data that passes through this machine each day is pretty massive; between all the different types of text files and other raw data, I would estimate we are loading maybe 100-200 million records per day.

Disk I/O has always been the biggest bottleneck on the Staging server (as is with most SQL server instances).  I'm looking for some advice on how we should configure out SAN for the best performance on the Staging server.

The Staging server is on the SAN currently, with drives like this:

Array 1:
2x73GB SAS 15k in RAID1 (73GB usable space)
   73GB LUN dedicated for SQL Logfiles

Array 2:
5x300GB SAS 15k in RAID5 (1,200GB usable space)
   215GB LUN for SQL Data files
   80GB LUN for "raw data" (drive holding txt/csv/dbf/etc. files for loading)

The SAN is actually much larger than this, we have other machines using it for other stuff as well, I'm just explaining what my current SQL configuration is.  As a part of this project, I am authorized to COMPLETELY re-allocate EVERYTHING on the SAN to provide maximum performance on the Staging server.  Data loading on-time is critical for us and right now we're lagging quite a bit.

The SAN is a Dell MD3000i (1GbE iSCSI).  Available on the SAN for this project is:
4x73GB 15k SAS drives
17x300GB 15k SAS drives
6x2TB 7.2k SATA drives

In the past I've always chopped up SAN's into many smaller arrays.  Say for example 3x300gb drives in RAID5 for SQL data on Server 1, 2x300GB drives in RAID1 for SQL Logs on Server 1, etc.  This keeps dedicated spindles available for SQL logs and SQL data files one each server.

I'm wondering if I'm better off instead creating LARGE arrays of drives and then carving smaller LUN's for each server instead.  Say for instance, creating a RAID10 array of 8x300GB drives (1.2TB usable space) and carving into several LUN's for SQL Data files on each server, and another RAID10 array of 8x300GB drives and carving into LUN's for SQL logfiles on each server..

Which is the better solution for maximum IO performance?  Large arrays, split into many LUNs, or smaller arrays that dedicate drives to a single machine?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I/O performance is measured in throughput and I/Os per second.  These two numbers are pretty much mutually exclusive.  Optimize for one by sacrificing performance on the other.  To a lesser extent, optimize for reads by sacrificing write performance.   Optimize for large block I/O, you sacrifice small block I/O.

Controllers, file systems, NTFS file system settings all weigh in.  You didn't bother to mention much of anything other than a RAID level & number of disks in the arrays.  

So, bottom line, it is not that simple, and certainly nobody can tell you with authority which will be "faster".    In situations such as this, best to run perfmon as a starting point and model your I/O to know what your applications are requesting, and where the delays are.  The storage doesn't care anything about what applications you are running, whether the files are PDFs, or MS access, or even configuration of the file system.

The RAID sees read and write requests for X blocks at a time.  So, measure X, the read/write mix, and so on, then you tune so that the most amount of I/Os take the fewest amount of time.  Nobody is going to be able to tell how this will shake out, because you have not actually described the I/O itself.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I would probably define separate logical volumes (LVs) for SQL Server data, logs, tempdb, and file landing area (what you call raw data). Each LV would have its dedicated set of LUNs to minimize contention at the LUN level. If you have enough drives, go with RAID10 instead of RAID5. At a minimum, tempdb and log LVs must be RAID10.
I don't know what options you have available as far as formatting of the LUNs, but look into the block size.  Some SANs (IBM, for instance) allow you to specify a different block size for each physical volume.  Build the physical volumes individually, selecting RAID level based on read/write performance.  I personally favor RAID 10, but that gets pricey quick at 50% usable..  Then, when you format the volume after it's presented to the server, make sure to format NTFS with the same block sizes.

There are lots of little things you can do to optimize performance.  However, a 1Gig iSCSI SAN just isn't going to give you any kind of REAL horsepower.  After Ethernet and TCP/IP overhead, a fully saturated 1GB iSCSI link will only get you ~400Mb/s.  Either move to 10Gig, or look at FC.

Just my $.02.  YMMV.

Apart from the MS best practice of putting transaction logs on different physical disks from the data for a no-loss restore there isn't really any advantage of having dedicated sets of spindles for different databases, tempdb etc. In your case I'd make 3 sets of spindles rather than split the 17*300GB disks up. Sure you get disk contention between two DBs that way buy you also get the benefit of having all those spindles available to each DB so if one is quiet the other has more IOPS available to it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.