I'd also like to know how to handle the OLAP environment that is based on our OLTP database, and how to deal with existing high availability clusters.
Do you have a "best practice" on these issues? Microsoft and Dell are telling me about the technical specifications, but I'm more interested in the operational part. (I'm just the DBA -- not one of the network guys.) We are using SQL Server 2000 SP3a/Windows 2000 servers.
1) OLTP-based workloads should use dedicated spindles in the SAN, and use RAID 1+0 if possible. For good performance without all the cost, you can use RAID 5 for the database and RAID 1 or RAID 10 for the log files.
2) Each server connected to the SAN should have at least two paths to disk. Each path should be on a physically or logically separate SAN fabric. Path management software would be required on each host. (Check with your SAN vendor on what they require.)
3) All backup should be SAN-based, and no backup data streams should have to be transferred over the IP network. This is called "serverless backup."
4) Use ALL 2 Gb components in the SAN. This means the HBA, switches, and storage subsystems must all be rated at 2 Gb. If any component is 1Gbit, then the entire path runs at 1Gb. The cables should all be multi-mode 50u cables, with LC type connectors on each end (unless you are using a patch panel for connectivity).
5) For a cheap and easy solution for data migration, you can use host-based mirrors to migrate all your data from internal host drives to the new SAN environment. (See your OS vendor's documentation for creating mirror sets with the OS). Install the HBAs into the hosts and connect up the new storage array, create LUNs to match the size of your existing LUNs (or larger), and reboot the server so it can see your new disks. Now use diskadmin under Windows to create a mirror for all your data drives. Leave the C: drive alone as an internal boot disk. Once the mirrors are finished copying, break the mirrors and you will have two copies of data: one in the SAN and one internal. You can now remove your internal drives, or leave them around as a backup.
6) Use LUN security on the storage array, and zoning in the SAN. ZONE each HBA world wide name into a separate zone, which includes the storage port on the array where you have ported that servers LUNS to. (Basically, each HBA is in its own zone.) This will cut down on error traffic to your servers from misbehaving components in the SAN.
7) For your clusters, you may need to create a "quorum" disk in the SAN that is accessible by each member of the cluster. Use LUN security and zoning in the SAN to make sure each cluster node has access to the quorum disk.
8) For DR, ask your SAN vendor if they provide a storage array based replication solution. This will require another storage array at your DR site to accept the data on the other side, and a SAN or IP connection between sites. If you find this method too expensive, then you can use host based (software) replication solutions. Or you can ship the log files over IP to a host on the other side that also has a complete copy of the database.
9) I have no idea how many servers you have, but limit the server-to-storage-port count to make sure high performance applications are not overwhelmed by other servers competing for storage resources over the same port.
10) Keep SAN switch "hops" to a minimum. A hop happens when data must transverse an ISL (switch-to-switch) connection in the SAN. If you have just two switches (one for each host path), with the storage connected to the same switch, you should be fine. If you have a larger and more complex SAN fabric infrastructure, then use a professional to help you design the SAN.
Read Greg Schulz' answer to this question.
This was first published in November 2004