Microsoft generally recommends that you use a Storage Area Network (SAN) or locally attached disk for the storage of your Microsoft SQL Server database files because this configuration optimizes SQL Server performance and reliability. By default, use of network database files (stored on a networked server or Network Attached Storage [NAS]) is not enabled for SQL Server.
However, you can configure SQL Server to store a database on a networked server or NAS storage server. Servers used for this purpose must meet SQL Server requirements for data write ordering and write-through guarantees, which are detailed in the "More Information" section.
Windows Hardware Quality Lab (WHQL) qualified devices
Microsoft Windows servers and networked servers or NAS storage servers that are Windows Hardware Quality Lab (WHQL) certified automatically meet the data write ordering and write-through guarantees required to support a SQL Server storage device. Microsoft supports both application and storage-related issues in these configurations.Note
To be supported by SQL Server, the NAS storage solution should also meet all the requirements that are listed on the following Microsoft website:
If you use a non-WHQL qualified storage device with SQL Server that supports the I/O guarantees for transactional database use described in this article, Microsoft will provide full support for SQL Server and SQL Server-based applications. However, issues with, or caused by, the device or its storage subsystem will be referred to the device manufacturer. If you use a non-WHQL qualified storage device that does not support the I/O guarantees for transactional database use described in this article, Microsoft cannot provide support for SQL Server or SQL Server-based applications. To determine whether your non-WHQL qualified storage device supports the I/O guarantees for transactional database use described in this article and/or is designed for database use, check with your device vendor. Also, contact your device vendor to verify that you have correctly deployed and configured the device for transactional database use.
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 generates either of the following error messages: Error Message 1
5105 "Device Activation Error"
Error Message 2
5110 "File 'file_name' is on a network device not supported for database
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:
SQL Server 7.0, SQL Server 2000, and SQL Server 2005 logging and data storage algorithms extend data reliability
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.
SQL Server, similar to 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 is completed
- 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, an 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 an 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 for 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 that is running on the SQL Server computer. The restore operation 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, they may also reduce restore times by orders of magnitude.
Backups that are stored on NAS are vulnerable to the same failures that affect database files that are stored on the NAS. You should consider protecting these backups by copying them to alternative media.Caution
You may experience database corruption in the backup if you use NAS backup technologies without SQL Server VDI support. Such corruption 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 the use of NAS backup technologies that are not coordinated with SQL Server.
Backup support and NAS vendor support for SQL Server VDI varies. Check with your NAS and backup software suppliers for details regarding VDI support.
Microsoft urges customers who are considering a deployment of an NAS solution for SQL Server databases to consult their NAS vendor to make sure 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, in SQL Server 2000, or in SQL Server 7.0, without trace flag 1807, if you execute a CREATE DATABASE statement with mapped or UNC syntax, error 5105 occurs in SQL Server 7.0 and error 5110 occurs in SQL Server 2000.
- In SQL Server 2005, SQL Server 2000 or in SQL Server 7.0, with trace flag 1807, a CREATE DATABASE statement that is performed by using mapped or UNC syntax is successful.
Note that SQL Server provides support only 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.
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.
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:
Using disk drive caching with SQL Server
SQL Server Books Online; topic: "Trace Flags"
SQL Server requires systems to support ‘ guaranteed delivery to stable media ’ as outlined under the Microsoft SQL Server Always-On Storage Solution Review program. Fo
For more information about the input and output requirements for the SQL Server database engine, click the following article number to view the article in the Microsoft Knowledge Base:
Microsoft SQL Server Database Engine Input/Output Requirements