SQL Server and Network Attached Storage

By Chris Miller on 6 July 2000 | Tags: Storage/Sizing

This article delves into Network Attached Storage (NAS) including a step-by-step guide on how to move a database from local storage to NAS.

There are a large number of network attached storage devices that are on the market now (Network Appliance) and that will be coming to market in the next few months. Making SQL Server 7 use these devices is neither obvious nor straightforward, and this article attempts to explain what a NAS device is, what advantages it offers, and how to use it from SQL Server.

Network attached storage refers to a device that implements a large file system on a normal Ethernet network. In the case of SQL Server and Windows systems we're largely interested in devices that offer Windows-type file services (CIFS/SMB), but many NAS devices also offer NFS services for us Unix geeks.

NAS is not really a classic Storage Area Network, or SAN. A SAN is usually interconnected with FibreChannel or other similar technologies and relies on a front-end server to provide client services for the user, and is usually directly accessible as a disk device to Windows. A NAS device looks like a network server, and operates through the network layer instead of through the disk layer. Given that a network card costs a relatively small amount of money, it is very inexpensive to have many servers access a storage area network device. It also doesn't require any "special" hardware, like a FibreChannel card, and it doesn't require any drivers that you wouldn't already be using.

It's very easy to see that a NAS device would be very effective for doing things like traditional file services or storing static web pages. These devices can also do a lot for SQL Server. These devices as a class are very fast, and offer most of the features that are normally expected of high-dollar storage systems, such as RAID, hot-swap drives and warm-spare drives. In addition, they usually have enough internal and network bandwidth to support multiple SQL Servers, which can lower the overall cost of storage for SQL Server, since each server can share the storage system and there tends to be less inaccessible space. Also, most of these systems tend to support backups off of their back-end, freeing the database server from worrying about backups.

NAS devices have a couple of drawbacks. The initial costs can be very high, depending on how many servers are connected to the NAS it may be more expensive than local storage for each server. The best case solution for a NAS solution is usually read-only databases, like those that would be used to serve data to a web site. The problem with putting a read-write database on a network attached device is that a traditional network tends to lose more data than a SCSI or FibreChannel system. That said, the only time I've had data loss it was related to a network switch failure, not a failure of either the SQL Server to write data or the device to receive the data and commit it. Your mileage may vary.

So, what's the upside? Did I mention that these devices are fast? They tend to use specialized hardware and operating systems that are designed for fast disk access. When a system is designed for a single purpose, it can be very optimized for those types of systems. Usually, they run some type of firmware UNIX variation. They also usually don't use Intel procesors, but instead tend towards Alpha or PowerPC designs. So they're built for speed pulling data off of disk and putting data onto disk. They don't have high-impact graphical interfaces, sound cards, or fancy USB support, the lack of overhead of all of this unnecessary goo leaves more room for moving data.

Microsoft only supports Network Appliance devices for network attached storage right now, but will probably support the upcoming devices from Compaq. Compaq already has a line of traditional SAN devices, marketed under the "StorageWorks" name, which they inherited in their acquisition of Digital, and the rumors are that they will be releasing a network attached storage system soon.

If you just want to play with network attached storage and don't have the cash laying around for a Network Appliance device you can actually just use any server on your network. Doing so is not supported by Microsoft, so don't blame me if you screw up your data.

There are a few things that need to be done in order to make SQL Server play nicely with a storage area network. First, we need to make sure SQL Server doesn't start before the network is online, otherwise SQL Server may start before it can get access to the databases, and that would be bad. To make this happen, you need to add a string registry value into HKEY_LOCAL_MACHINE\ System\ CurrentControlSet\ Services\ MSSQLServer. The value should be named "DependOnService", and have the value NetLogon. The NetLogon service depends on the Workstation and Server services, both of which depend on the network being in place. By setting up this far in the chain, SQL Server may start a little bit later, but it will always start and find its devices.

NAS devices should always be accessed using UNC names, such as \\server\share. In its default state, SQL Server won't allow you to use a UNC path in place of a drive letter. So, you'll need to set a trace flag in order to change this behavior. The trace flag is 1807. If you're not sure how to set a trace flag, you'll want to check out Books Online.

Creating a device on a network path isn't any different from creating one on local storage, provided that the trace flag is turned on. So, if you're creating a database this isn't a big deal. If you need to copy a database from local storage to network storage, there are a couple extra steps. The problem is that the log file uses a different block size if it's on local storage or network storage. Which means it's not possible to use a log file from local storage on a remote storage device. Since the new backup format includes the log file, even if you go through the trouble of emptying out the log file, it isn't possible to use a backup to move data to network storage. Which means you'll need to copy the data file and create a new LDF file. Fortunately, Microsoft provided us with the sp_detach_db and sp_attach_db (and sp_attach_single_file_db) stored procedures. So, detach the database, copy the MDF files to the network device, and then use sp_attach_db (or sp_attach_single_file_db, as appropriate) to attach the database on it's network device. One additional note here, make sure you can checkpoint the database before you detach it. If you can't checkpoint, don't detach, because you won't be able to attach either.