Description of support for network database files in SQL Server (304261)

MORE INFORMATION

By default, you cannot create a SQL Server database on a network file share. Any attempt to create a database file on a mapped or UNC network location causes either of these error messages to occur:

Message 1Message 2 This behavior is expected. Trace flag 1807 bypasses the check and allows you to configure SQL Server with network-based database files. SQL Server, and most other enterprise database systems, employ a transaction log and associated recovery logic to ensure transactional database consistency in the event of a system failure or an unmanaged shut down. These recovery protocols rely on the ability to write directly to the disk media so that when an operating system input/output (I/O) write request returns to the database manager, the recovery system is guaranteed that the write is actually complete or that the completion of the write can be guaranteed. Any failure by any software or hardware component to honor this protocol can result in a partial or total data loss or corruption in the event of a system failure. For more details about these aspects of logging and recovery protocols in SQL Server, refer to the following article in the Microsoft Knowledge Base: Microsoft does not support SQL Server networked database files on NAS or networked storage servers that do not meet these write through and write order requirements.

Because of the risks of network errors compromising database integrity, together with possible performance implications that may result from the use of network file shares to store databases, Microsoft recommends that you store database files either on local disk subsystems or on Storage Area Networks (SANs).

A network attached storage (NAS) system is a file based storage system that clients attach to through the network redirector by using a network protocol (such as TCP/IP). If access to a disk resource requires that a share be mapped, or if the disk resource appears as a remote server through a UNC path, (for example, \\Servername\Sharename), on the network, then by default, the disk storage system is not supported as a location for SQL Server databases.

Performance issues

SQL Server, like other enterprise database systems, can place an extremely large load on an I/O subsystem. In most large database applications, physical I/O configuration, and tuning play a significant role in overall system performance. There are three major I/O performance factors to consider:

• I/O bandwidth: The aggregate bandwidth, typically measured in megabytes per second that can be sustained to a database device.
• I/O latency: The latency, typically measured in milliseconds, between a request for I/O by the database system and the point where the I/O request completes.
• CPU cost: The host CPU cost, typically measured in CPU microseconds, for the database system to complete a single I/O.

Any of these I/O factors can become a bottleneck and you must consider all these factors when you design an I/O system for a database application.

In its simplest form, a NAS solution uses a standard network redirector software stack, standard network interface card (NIC), and standard Ethernet components. The drawback of this configuration is that all file I/O is processed through the network stack and is subject to the bandwidth limitations of the network itself. This can create performance and data reliability problems, especially in programs that require extremely high levels of file I/O, such as SQL Server. In some NAS configurations tested by Microsoft, the I/O throughput was approximately one-third (1/3) that of a direct attached storage solution on the same server. In this same configuration, the CPU cost to complete an I/O through the NAS device was approximately twice that of a local I/O. As NAS devices and network infrastructure evolve, these ratios may also improve relative to direct attached storage or SANs. Furthermore, if your application data is mostly cached in the database buffer pool and you do not encounter any of the I/O bottlenecks outlined, performance on a NAS based system is probably adequate for your application.

Backup and restore considerations

SQL Server provides the Virtual Device Interface (VDI) for backup. The Virtual Device Interface provides backup software vendors with a high-performance, scalable, and reliable means for performing hot backups and restoring SQL Server databases.

Backup software operates on database files stored on NAS devices through VDI with no special support specific to the NAS. However, this results in a large amount of additional network traffic during backup and restore. During backup through VDI, SQL Server reads the files remotely and passes the data to the third-party backup software running on the SQL Server computer. Restore is analogous.

To avoid the extra network overhead, the backup vendor must provide NAS-specific support by the backup vendor and the NAS vendor. SQL Server VDI allows the backup software to take advantage of hardware (split-mirror) or software (copy-on-write) technologies supported by the NAS devices to make fast copies of the database files local to the NAS. These technologies not only avoid the overhead of copying the files over the network for backup, but may also reduce restore times by orders of magnitude.

Backups stored on NAS are vulnerable to the same failures as database files stored on the NAS; consider protecting these backups by copying them to alternate media.

Caution You may experience database corruption in the backup if you use NAS backup technologies without SQL Server VDI support, which includes torn pages or inconsistencies between the log and data files if they are stored on separate devices. SQL Server may not detect the torn pages or inconsistencies until you restore the database and access the corrupted data. Microsoft does not support use of NAS backup technologies that are not coordinated with SQL Server.

Backup and NAS vendor support of SQL Server VDI varies. Check with your NAS and backup software suppliers for details regarding VDI support.

Microsoft urges anyone considering deploying a NAS solution for SQL Server databases to consult his or her NAS vendor to ensure that the end-to-end solution design is for database use. Many NAS vendors have best practice guides and certified configurations for this use. Microsoft also recommends that customers benchmark their I/O performance to ensure that none of the I/O factors mentioned previously causes a bottleneck in their application.

The text that follows describes the behavior of network-based database files on Microsoft SQL Server 2005, Microsoft SQL Server 2000, and Microsoft SQL Server 7.0, with and without trace flag 1807. Mapped syntax refers to a drive letter associated with a network path by the NET USE command. UNC syntax refers to a direct reference to a network path, such as \\Servername\Sharename.

• In SQL Server 7.0, without trace flag 1807, if you use the DISK INIT backward-compatible syntax followed by a CREATE DATABASE statement with either mapped or UNC syntax, error 5105 occurs.
• In SQL Server 7.0, with trace flag 1807, if you use the DISK INIT backward-compatible syntax followed by a CREATE DATABASE statement with mapped syntax, the file creation succeeds. If you use DISK INIT with UNC syntax, error 5105 occurs.
• In SQL Server 2005, SQL Server 2000, or SQL Server 7.0, without trace flag 1807, if you execute a CREATE DATABASE statement with mapped or UNC syntax, in SQL Server 7.0 error 5105 occurs and in SQL Server 2000 error 5110 occurs.
• In SQL Server 2005, SQL Server 2000, or SQL Server 7.0, with trace flag 1807, a CREATE DATABASE statement performed with mapped or UNC syntax succeeds.

Note that SQL Server only provides support for network-based files that use trace flag 1807 for non-failover clustered installations of SQL Server. Failover clustered installations of SQL Server do not work with network-based files because SQL Server 2005 and SQL Server 2000 require that storage devices be recognized and registered by the Microsoft Cluster Service (MSCS) Cluster Administrator.

Additional notes

Incorrect use of database software with a NAS product, or database use with an improperly configured NAS product, may result in data loss including total database loss. If the NAS device or network software does not completely honor data guarantees, such as write ordering or write-through, then hardware, software, or even power failures could seriously compromise data integrity.

REFERENCES

For more information about write ordering or write-through for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base: SQL Server Books Online; topic: "Trace Flags"