Link to home
Start Free TrialLog in
Avatar of mortimer452
mortimer452

asked on

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)
   820GB UNUSED

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?





ASKER CERTIFIED SOLUTION
Avatar of David
David
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start 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.

-exx
Avatar of Member_2_231077
Member_2_231077

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.