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:
2x73GB SAS 15k in RAID1 (73GB usable space)
73GB LUN dedicated for SQL Logfiles
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?