SQL Server on a SAN

As a DBA, one of your many tasks is to manage your SQL Server databases' ever-expanding storage requirements. How often do you find yourself adding more disk, trying to accurately size a database, or wishing you could more efficiently use your existing disk capacity? Storing database data on a SAN can make such tasks much easier and can also improve disk performance and availability and shorten backup and restore times. Start your search for a SAN here, as you learn the basics of SAN technology and the benefits of using a SAN to store SQL Server databases. And the sidebar "Selecting a Storage Array for a SAN," covers several features you'll want to consider when selecting a storage array for your SAN.

SAN Fundamentals

A SAN is basically a network of switches that connect servers with storage arrays. SAN topology is similar to how Ethernet switches are interconnected, as Figure 1 shows. A SAN's physical layer comprises a network of either Fibre Channel or Ethernet switches. Fibre Channel switches connect to host bus adapter (HBA) cards in the server and storage array. Ethernet switches connect to Ethernet NICs in the servers and storage array.

A storage array is an external disk subsystem that provides external storage for one or more servers. Storage arrays are available in a range of prices and capabilities. On the low end, an array consists simply of a group of disks in an enclosure connected by either a physical SCSI cable or Fibre Channel Arbitrated Loop (FC-AL). This type of plain-vanilla array is also commonly called Just a Bunch of Disks (JBOD). In high-end arrays, storage vendors provide features such as improved availability and performance, data snapshots, data mirroring within the storage array and across storage arrays, and the ability to allocate storage to a server outside the physical disk boundaries that support the storage.

Two types of SANs exist: Fibre Channel and iSCSI. Fibre Channel SANs require an HBA in the server to connect it to the Fibre Channel switch. The HBA is analogous to a SCSI adapter, which lets the server connect to a chain of disks externally and lets the server access those disks via the SCSI protocol. The HBA lets a server access a single SCSI chain of disks as well as any disk on any storage array connected to the SAN via SCSI.

iSCSI SANs use Ethernet switches and adapters to communicate between servers and storage arrays via the iSCSI protocol on a TCP/IP network. Typically, you'd use a Gigabit Ethernet switch and adapter, although 10Gb Ethernet switches and adapters are becoming more popular in Windows server environments.

On a SAN, a server is a storage client to a storage array, aka the storage server.The server that acts as the primary consumer of disk space is called the initiator, and the storage server, which provides the disk space, is called the target.

The disks that the storage arrays provide on the SAN are called LUNs and appear to a Windows server on the network as local hard drives. Storage-array vendors use a variety of methods to make multiple hard drives appear local to the storage array and to represent a LUN to a Windows server by using parts of multiple hard drives. Vendors also use different RAID schemes to improve performance and availability for data on the LUN. Whether the SAN uses Fibre Channel or Ethernet switches, ultimately what appears from the Windows server through the Microsoft Management Console (MMC) Disk Management snap-in are direct-attached disks, no different from those physically located within the server itself. In addition, most arrays have some type of RAID protection, so that the storage that represents a given LUN is distributed across multiple hard drives that are internal to the storage array.

SAN Security

SAN architecture provides two measures for securing access to LUNs on a SAN.The first is a switch-based security measure, called a zone. A zone, which is analogous to a Virtual LAN (VLAN), restricts access by granting only a limited number of ports on several hosts an access path to several, but not all, storage arrays on the SAN.

The second security measure is storage-array-based; a storage array can use LUN masking to restrict access. Depending on the vendor, this security feature comes free of charge with the storage array or is priced separately as a licensed product. LUN masking can be configured either by the administrator or by the storage-array vendor for a fee. When masking is configured, the array grants only explicitly named ports of named hosts an access path to the specified LUNs. LUN masking functions similarly to ACLs on Common Internet File System (CIFS) shares in a Windows environment.

SAN Benefits

Now that you have a grasp of what a SAN is, you're probably wondering how a SAN could benefit your SQL Server environment. To address this question, we'll first examine problems inherent in local DAS, then explore how using a SAN avoids these problems.

Performance and availability. As part of the typical process of designing a database that will reside on a local disk, or DAS, you'd determine how the disks on which the database will be stored are attached (i.e., which disks are attached to which SCSI adapter).You want to carefully organize the database files to minimize contention for disk access—for example, between a table and indexes on the table, two tables that are frequently joined together, or data and log files.To minimize contention (i.e., disk I/O operations), you'd try to ensure that the two contending objects are separated not only on different disks but also across SCSI adapters.

Another disk-related issue that you must consider in designing a database is availability.You need to use some type of disk redundancy to guard against disk failures. Typically, you'd use either RAID 1 ( mirroring) or RAID 5 to provide redundancy and thus, improved availability.

After you create the RAID devices by using Windows' Disk Management, you might lay out the database across these multiple RAID storage structures. When allocating such structures, you have to decide how to size them. Determining the amount of storage each server needs is like estimating your taxes: If you overestimate or underestimate taxes or storage needs, you'll be penalized either way. If you overestimate your storage and buy too much, you'll have overspent on storage. If you underestimate your storage needs, you'll soon be scrambling to find ways to alleviate your shortages.

A SAN addresses the issues of contention, availability, and capacity. On a SAN, the storage array typically pools together multiple disks and creates LUNs that reside across all disks in the pool. Different disks in the pool can come from different adapters on the storage array, so that traffic to and from the pool is automatically distributed. Because the storage array spreads the LUNs across multiple disks and adapters, the Windows server that's attached to the SAN sees only a single disk in Disk Management.You can use just that one disk and not have to worry about performance and availability related to the disk, assuming that your storage or network administrator has properly configured the SAN.

How complex or simple a storage array is to configure depends on the vendor's implementation. I recommend that you meet with the IT person responsible for configuring your storage and ask him or her to explain your storage array's structure. Also, determine your storage requirements ahead of time and give them to this person. In addition to storage size, note your requirements for performance (e.g., peak throughput—40Mbps); availability (e.g., 99.999 percent availability); backup and recovery (e.g., hourly snapshot backups take 1 minute; restores take 10 minutes); and disaster recovery, based on metrics for recovery time objective (RTO)—the time it takes to restore your database to an operational state after a disaster has occurred—and recovery point objective (RPO)—how recent the data is that's used for a restore. Using these metrics to define your requirements will help your storage administrator better understand your database-storage needs.

Some vendors' storage arrays let you dynamically expand a LUN that you created within the disk pool without incurring any downtime to the SQL Server database whose files reside on that LUN.This feature lets DBAs estimate their disk-space requirements more conservatively and add storage capacity without downtime.

Backup control. As a database grows, so does the amount of time needed to perform database backups. In turn, a longer backup requires a longer backup window. Partial backups—such as database-log-backups— take less time but require more time to restore. Increasingly, upper management is mandating smaller backup windows and shorter restore times for essential applications, many of which access SQL Server databases. SANs can help decrease backup windows and restore times. Some storage arrays can continuously capture database snapshots (i.e., point-in-time copies of data), which are faster to back up and restore than traditional database-backup methods. The snapshot doesn't contain any actual copied data; instead, it contains duplicate pointers to the original data as it existed at the moment the snapshot was created. (For more information about SQL Server 2005's snapshot capability, see "What's New for the DBA in SQL Server 2005?" November 2005, InstantDoc ID 47911.)

To back up SQL Server database data by using snapshots, you'd typically want to put your database in a "ready" state, more commonly called a hot-backup state, for a few moments to perform the snapshot. If you didn't put your database in a hot-backup state, the snapshot could take a point-in-time copy of your database before SQL Server has finished making a consistent database write. Storage-array vendors often use Microsoft's SQL Server Virtual Backup Device Interface (VDI) API to enable their software to put the database in a hot-backup state.This lets the system copy the point-in-time snapshot image to separate backup media without causing a database outage.

Snapshots are minimally intrusive, so you can use them frequently without affecting database performance. Restoring data from a snapshot takes only a few seconds. By using a SAN-connected storage array along with a snapshot capability, DBAs can minimize backup windows and restore times, in part because snapshot images are maintained on distributed disks in the array, instead of on one local disk.

Reduced risks for database updates. Changes to a database, such as SQL Server or application upgrades or patches, can be risky, especially if the changes might cause database outages or worse, database corruption. To test changes without putting the production database at risk, you'd need to set aside an amount of storage equivalent to the size of the production database. On this free storage, you'd restore the last recent backup of that database (typically 1 week old).You'd spend a few hours (maybe even days) restoring the database from tape to disk, applying the changes, then testing to see whether the changes were successfully applied and whether they adversely affected the database. After you verified that the changes were successfully implemented, you'd apply them to the production database.

Some vendors' SAN storage arrays let you quickly clone your database data for testing purposes. Cloning the data takes only a few seconds versus hours to restore it from tape. The added benefit of cloning is reduced disk utilization. Some cloning technology lets you take a read-only database snapshot and turn it into a writeable clone. For testing purposes, the clone consumes far less disk storage than a full backup of a database because only modified blocks of data are copied to the clone database.

When Using DAS Makes Sense

Storing database data in a SAN gives you features not available with DAS, such as local and remote mirroring,data cloning,the ability to share data across multiple hosts, and the ability to capture data snapshots. However, if you don't need these features, storing your SQL Server databases on DAS might make more sense. A SAN environment consists of multiple SAN clients with multiple HBAs on SAN switches connected to storage arrays. If the SAN wasn't properly designed and configured (i.e., to provide redundancy), the storage array or a component on the SAN could fail, so that servers on the SAN couldn't access data on the storage array.

To enable you to troubleshoot storage problems, you'll need to make sure that SQL Server binaries and message-log files stay on the local disk. Storing the message log and binaries on a disk other than the local disk puts the database in a Catch-22 situation, in which a database-access failure caused by a storage-connection failure can't be logged because logging occurs only for the device on which the logs and binaries are stored.

I'm Ready for a SAN. Now What?

If your organization doesn't already have a Fibre Channel SAN switching network in place, iSCSI will most likely give you a greater ROI and minimize your equipment investment. For a Fibre Channel SAN, you need to buy a storage array, Fibre Channel SAN switches,and HBAs.For an iSCSI SAN, you need to buy a storage array, but you can use your existing Ethernet switches and Gigabit Ethernet adapters. To include your Windows servers in the iSCSI SAN, you need only download and install an iSCSI driver for your particular OS. (You can download the latest version of Microsoft's iSCSI driver, Microsoft iSCSI Software Initiator. Carving up the storage array and presenting it to your Windows server could get complicated, depending on the storage vendor. As I mentioned earlier, you should discuss your storage requirements with your storage administrator.

Most modern storage arrays let you access LUNs on the same storage array via either Fibre Channel or iSCSI. I've found that many IT environments don't take full advantage of their SAN's features. If your organization already uses a Fibre Channel SAN switching network, you can try out some storage-array features such as cloning and snapshots in a development or test environment. If your organization doesn't have a SAN yet, you can still try some of these features relatively inexpensively by setting up an iSCSI SAN.

Step Up to a SAN

As you can see, housing databases on a SAN can benefit DBAs in various ways. SANs can reduce the pain of sizing storage requirements for databases, enhance overall storage throughput, simplify storage performance tuning, and improve availability. Using a SAN can also decrease backup and restore windows and enables quicker and easier testing cycles and reduced overhead in test storage.The availability of iSCSI removes the cost barriers that have until now inhibited some users from investigating SANs. Now's the time to check out SAN technology and see whether it can improve your database-storage environment.