SQL Server Database Engine Disk Input/Output (I/O) requirements

This article describes the SQL Server Database Engine Disk Input/Output (I/O) requirements.

Original product version:   SQL Server
Original KB number:   967576

Introduction

SQL Server requires that systems support guaranteed delivery to stable media, as outlined in the following download documents:

• SQL Server I/O Reliability Program Requirements

• SQL Server IO Reliability Program Review Requirements

This requirement includes, but is not limited to, the following conditions:

• Windows logo certification
• Write ordering
• Caching stability
• No data rewrites

Systems that meet these requirements support SQL Server database storage. Systems do not have to be listed on SQL Server storage solutions programs, but they must guarantee that the requirements are met.

SQL Server maintains the atomicity, consistency, isolation, and durability (ACID) property by using the Write-Ahead Logging (WAL) protocol.

Warning

The incorrect use of SQL Server with an improperly tested solution may result in data loss, including total database loss.

Technical support

Microsoft will provide full support for SQL Server and SQL Server-based applications. However, issues that are caused by the I/O solution will be referred to the device manufacturer. Symptoms may include, but are not limited to, the following:

• Database corruption
• Backup corruption
• Unexpected data loss
• Missing transactions
• Unexpected I/O performance variances

To determine whether your hardware solution supports "guaranteed delivery to stable media" as outlined under the SQL Server Always-On program, check with your vendor. We also recommend that you contact your vendor to verify that you have correctly deployed and configured the solution for transactional database use.

It is a common troubleshooting practice for a Microsoft Support professional to ask you to disable nonessential jobs and to disable or remove third-party components, move database files, uninstall drivers, and perform similar actions. We always try to reduce the scope of the issue while we work to identify it. After an issue is identified as unrelated to the jobs or third-party products, those jobs or third-party products may be reintroduced to production.

For more information, see the following article:

• Microsoft does not certify that third-party products will work with Microsoft SQL Server

• Overview of the Microsoft third-party storage software solutions support policy

More information

The following table provides links to additional information that is related to specific I/O configurations.

SQL Server I/O Internals	SQL Server 2000 I/O Basics SQL Server I/O Basics, Chapter 2 Writing Pages Database Checkpoints (SQL Server)
WAL	Write-Ahead Transaction Log ACID Properties (atomicity, consistency, isolation, and durability) Description of logging and data storage algorithms that extend data reliability in SQL Server
File System Features	SQL Server databases not supported on compressed volumes (except 2005 read-only files) Decreased performance in some features of SQL Server when you use EFS to encrypt database files
I/O caching	Information about using disk drive caches with SQL Server that every database administrator should know Description of caching disk controllers in SQL Server
Physical layout and design	Physical Database Storage Design Scalable Shared Databases Overview
Tempdb	Microsoft SQL Server I/O subsystem requirements for the tempdb database Working with tempdb in SQL Server 2005 Optimizing tempdb Performance Recommendations to reduce allocation contention in SQL Server tempdb database
Utilities	How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem Diskspd - A robust storage testing tool
Diagnostics	Asynchronous disk I/O appears as synchronous on Windows SQL Server diagnostics added to detect unreported I/O problems due to stale reads or lost writes MSSQLSERVER error 823 Recording for Resource-based Analysis of Windows Performance Recorder Xperf resources: Diagnostics in SQL Server help detect stalled and stuck I/O operations MSSQLSERVER error 823 Description of common causes of SQL Server error message 844 or error message 845
NAS (Network Attached Storage)	Description of support for network database files in SQL Server
iSCSI	Support for SQL Server on iSCSI technology components
Mirroring and Always On availability groups	Prerequisites, Restrictions, and Recommendations for Always On availability groups Requirements for SQL Server to support remote mirroring of user databases Database Mirroring: Database Mirroring in SQL Server 2005 Database Mirroring Best Practices and Performance Considerations These white papers also apply to Microsoft SQL Server 2008 and later versions of SQL Server.
I/O affinity	INF: Understanding How to Set the SQL Server I/O Affinity Option