I'd like to pass along a couple of tips for those new to using SAN storage for SQL Server. SAN Storage is quite expensive, and doubly so if your storage doesn't deliver on the performance front. SAN disk arrays are not magic, and sadly they don't just automagically perform well, marketing to the contrary. These are some items I have found helpful in configuring them:
- Create and maintain a good relationship with your SAN admin. You need to know the details of how the storage is laid out for your SQL Server, and how to describe what you need in a way that she can understand. Don't treat it like a black box.
- Find out about the disk offset / alignment issue. I won't cover that here, as it's well documented elsewhere. Just be sure to use DISKPART and the ALIGN parameter on Windows 2003 or earlier.
- When you spec anything that is demanding performance-wise, to your SAN administrator, don't just ask for a quantity of space -- include a performance threshold in IO's per second. The number of physical disks required in the array, just as with direct attached storage, will most likely be driven by your performance needs and not the capacity you ask for. DB's that need a lot of disks on DAS also need a lot of disks in a SAN attached array, for all the same reasons. (Here's a great recent post about performance-centric design from Rod Colledge.)
With larger LUNs, it's common practice to build them by ganging together smaller groups of disks - for example, if you need 24 disks, they might be organized first into LUNs of 6 disks each, then grouped into the 24-disk set (sometimes called a "meta-LUN"). The details of how that is done are vital to performance.
For the LUN spec, it's fairly straightforward if you have an existing system that you can use as a benchmark (even if it's underperforming). Do project the quantity of storage that you'll need, but don't stop there -- use Perfmon to record the physical disk counters describing IO per second disk performance with the system under load. If the existing system's disk performance isn't adequate, apply a multiplier to get to the requirements for your new LUN. Provide the SAN administrator the quantity of storage, the IO measure in Reads and Writes per Second and the bandwidth you need in MB per second. It often happens that the IO performance measures are the limiting factor, and that they are limited by physical disk hardware, implying that the SAN Admin will have to provide more disks than would be required for raw capacity.
Next, be aware of the issue with Concatenated LUNs vs. Striped Luns. To take the example of the 24 disk LUN above, there are two ways to gang together those groups of 6 disks: striping and concatenation. Concatenation will chain them together in sequence, such that the first six disks work in concert to provide the first 1/4 of the total storage, then when those are full, the second set provides the next 1/4 of the storage, and so on. That may be OK for another app, like file storage, but this is exactly the wrong effect for a database! If you imagine those flashing busy lights on the disk array, it means that as you start using the LUN, the first six disks will light up and the others will be essentially idle. The resulting IO performance will be about that of a 6 disk array, despite all the empty space provided.
Striping, on the other hand, means that the data will be "banded" across all the disks right from the start, and you'll get the corresponding performance as all the disks work together. This is essential, or else the large quantity of (expensive!) disks is basically wasted.
Worth noting: our EMC arrays' management software issues an ominous message when creating a striped meta-LUN like this, warning that it could take a long time, maybe more than a week, to stripe. The key here is that, as far as I can tell, that only applies if you are dealing with LUNs that already have data on them, and that need to be reorganized. Striping a large, empty volume of space doesn't really take that long.
Finally, test the IO performance of the LUN before putting it into production, and make sure it'll deliver what you require. This can be as simple as a stress-testing script or a utility like SQLIOSim, combined with Performance Monitor's physical disk counters.