Microsoft SQL Server Database Engine Input/Output Requirements

Article translations Article translations
Close Close
Article ID: 967576 - View products that this article applies to.
Expand all | Collapse all
Source: Microsoft Support

RAPID PUBLISHING

RAPID PUBLISHING ARTICLES PROVIDE INFORMATION DIRECTLY FROM WITHIN THE MICROSOFT SUPPORT ORGANIZATION. THE INFORMATION CONTAINED HEREIN IS CREATED IN RESPONSE TO EMERGING OR UNIQUE TOPICS, OR IS INTENDED SUPPLEMENT OTHER KNOWLEDGE BASE INFORMATION.

Introduction

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

SQL Server I/O Reliability Program Overview

SQL Server I/O Reliability Program Requirements


This requirement includes, but is not limited to, the following: 
  • 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 the SQL Server Always-On site, 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.

WAL references
  • SQL Server Books Online outlines the ACID Properties (Atomicity, Consistency, Isolation and Durability)
  • KB230785 SQL Server 7.0, SQL Server 2000, and SQL Server 2005 logging and data storage algorithms extend data reliability

SQL Server data can be stored on I/O systems that guarantee delivery to stable media as outlined under the Microsoft SQL Server Always-On program.

Warning:Incorrect use of SQL Server with an improperly 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 have, or 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
If you use a solution that is not Windows logo certified, Microsoft cannot provide support for SQL Server or SQL Server-based applications. To determine whether your solution supports ‘guaranteed delivery to stable media’ as outlined under the Microsoft 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.

For more information, Click the following article number to view the article in the Microsoft Knowledge Base:

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

841696 Overview of the Microsoft third-party storage solutions support policy

More Information

The following table provides links to additional information related to specific I/O configurations.
Collapse this tableExpand this table


SQL Server Books Online


NTFS Compression 231347 - SQL Server databases not supported on compressed volumes (except 2005 read only files)

NTFS Encryption

ACID Properties

WAL Protocol

Scalable Read Only Databases 910378 - Scalable shared database are supported by SQL Server 2005


SQL Server Always On Program


http://www.microsoft.com/en-us/sqlserver/solutions-technologies/mission-critical-operations/io-reliability-program.aspx


SQL Server I/O Internals


http://technet.microsoft.com/en-us/library/cc966500.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx

230785 - SQL Server 7.0, SQL Server 2000 and SQL Server 2005 logging and data storage algorithms extend data reliability

78363 - When Dirty Cache Pages are Flushed to Disk

WebCast- Data Recovery in SQL Server 2005

MSDN article - Read-Only Filegroups


I/O Caching


234656 - Using disk drive caching with SQL Server

86903 - SQL Server and caching disk controllers

46091 - Using hard disk controller caching with SQL Server

917043 - Key factors to consider when evaluating third-party file cache systems with SQL Server


Physical Layout and Design


Physical Database Layout and Design


TEMPDB


917047 - Microsoft SQL Server I/O subsystem requirements for the tempdb database

Working with tempdb in SQL Server 2005 http://technet.microsoft.com/en-us/library/cc966545.aspx

Optimizing tempdb Performance http://msdn.microsoft.com/en-us/library/ms175527.aspx


Utilities


Performance: SQLIO http://www.microsoft.com/downloads/details.aspx?familyid=9A8B005B-84E4-4F24-8D65-CB53442D9E19&displaylang=en


Diagnostics


156932 - Asynchronous Disk I/O Appears as Synchronous on Windows NT, Windows 2000, and Windows XP

826433 - Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems

897284 - SQL Server 2000 SP4 diagnostics help detect stalled and stuck I/O operations (applies to SQL Server 2005)

828339 - Error message 823 may indicate hardware problems or system problems in SQL Server

167711 - Understanding Bufwait and Writelog Timeout Messages


NAS (Network Attached Storage)


304261 - Description of support for network database files in SQL Server


iSCSI


833770 - Support for SQL Server 2000 on iSCSI technology components (applies to SQL Server 2005)


Mirroring


910716 - Support for third-party Remote Mirroring solutions used with SQL Server 2000 and 2005

White paper - Database Mirroring in SQL Server 2005

White paper - Database Mirroring Best Practices and Performance Considerations


I/O Affinity


298402- Understanding How to Set the SQL Server I/O Affinity Option


DISCLAIMER

MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, RELIABILITY OR ACCURACY OF THE INFORMATION CONTAINED IN THE DOCUMENTS AND RELATED GRAPHICS PUBLISHED ON THIS WEBSITE (THE “MATERIALS”) FOR ANY PURPOSE. THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE.

TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.

Properties

Article ID: 967576 - Last Review: September 23, 2014 - Revision: 4.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 Reporting Services
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2012 Analysis Services
  • Microsoft SQL Server 2012 Business Intelligence
  • Microsoft SQL Server 2012 Community Technology Preview 1
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Parallel Data Warehouse
  • Microsoft SQL Server 2012 Parallel Data Warehouse Beta
  • Microsoft SQL Server 2012 Service Pack 1
  • Microsoft SQL Server 2012 Service Pack 2
  • Microsoft SQL Server 2012 SP1 PowerPivot for Microsoft Excel 2010
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • Microsoft SQL Server 2014 Beta
  • Microsoft SQL Server 2014 Business Intelligence
  • Microsoft SQL Server 2014 Business Intelligence Community Technical Preview 2
  • Microsoft SQL Server 2014 Developer
  • Microsoft SQL Server 2014 Developer Community Technical Preview 2
  • Microsoft SQL Server 2014 Enterprise
  • Microsoft SQL Server 2014 Enterprise Community Technology Preview 2
  • Microsoft SQL Server 2014 Enterprise Core
  • Microsoft SQL Server 2014 Enterprise Core Community Technical Preview 2
  • Microsoft SQL Server 2014 Express
  • Microsoft SQL Server 2014 Standard
  • Microsoft SQL Server 2014 Standard Community Technology Preview 2
  • Microsoft SQL Server 2014 Web
  • Microsoft SQL Server 2014 Web Edition Community Technical Preview 2
Keywords: 
kbnomt kbrapidpub KB967576

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com