Use of a write caching (also called write back caching) disk controller canimprove SQL Server performance. Write caching controllers and disksubsystems are safe for SQL Server, if they are specifically designed foruse in a data critical transactional database management system (DBMS)environment. These design features must preserve cached data if a systemfailure occurs. Using an external uninterruptible power supply (UPS) toachieve this is generally not sufficient, because failure modes that areunrelated to power can occur.
Caching controllers and disk subsystems can be safe for use by SQL Server.Most new purpose-built server platforms that incorporate these are safe.However, you should check with your hardware vendor to be sure that thedisk subsystem has been specifically tested and approved for use in a datacritical transactional relational database management system (RDBMS)environment.
SQL Server data modification statements generate logical page writes. Thisstream of writes can be pictured as going two places: the log and thedatabase itself. For performance reasons, SQL Server defers writes to thedatabase via its own cache buffer system. Writes to the log are onlymomentarily deferred until COMMIT time. They are not cached in the samemanner as data writes. Because log writes for a given page always precedethe page's data writes, the log is sometimes referred to as a "write-ahead"log.
Transactional integrity is one of the fundamental concepts of a relationaldatabase system. Transactions are considered to be atomic units of workthat are either totally applied or totally rolled back. The SQL Serverwrite-ahead transaction log is a vital component in implementingtransactional integrity.
Any relational database system must also deal with a concept closelyrelated to transactional integrity, which is recovery from unplanned systemfailure. A variety of non-ideal, real-world effects may cause this failure.On many database management systems, system failure may result in a lengthyhuman-directed manual recovery process.
In contrast, the SQL Server recovery mechanism is completely automatic andoperates without human intervention. For example, SQL Server could besupporting a mission-critical production application, and experience asystem failure due to a momentary power fluctuation. Upon restoration ofpower, the server hardware would restart, networking software would loadand initialize, and SQL Server would restart. As SQL Server initializes, itwill automatically run its recovery process based on data in thetransaction log. This entire process occurs without human intervention.Whenever the client workstations restarted, users would find all of theirdata present, up to the last transaction they entered.
SQL Server transactional integrity and automatic recovery constitute a verypowerful time-and-labor saving capability. If a write caching controller isnot properly designed for use in a data critical transactional DBMSenvironment, it may compromise the ability of SQL Server to recover, hencecorrupting the database. This can occur if the controller intercepts SQLServer transaction log writes and buffers them in a hardware cache on thecontroller board, but does not preserve these written pages during a systemfailure.
Most caching controllers perform write caching. The write cachingfunction cannot always be disabled.
Even if the server uses a UPS, this does not guarantee the security of thecached writes. Many types of system failures can occur that a UPS does notaddress. For example, a memory parity error, an operating system trap, or ahardware glitch that causes a system reset can produce an uncontrolledsystem interruption. A memory failure in the hardware write cache can alsoresult in the loss of vital log information.
Another possible problem related to a write-caching controller may occur atsystem shutdown. It is not uncommon to "cycle" the operating system orreboot the system during configuration changes. Even if a careful operatorfollows the operating system recommendation to wait until all disk activityhas ceased before rebooting the system, cached writes can still be presentin the controller. When the CTRL+ALT+DEL key combination is pressed, or theRESET button is pressed, cached writes can be discarded, potentiallydamaging the database.
It is possible to design a hardware write cache which takes into accountall possible causes of discarding dirty cache data, which would thus besafe for use by a database server. Some of these design features wouldinclude intercepting the RST bus signal to avoid uncontrolled reset of thecaching controller, on-board battery backup, and mirrored or ERC (errorchecking & correcting) memory. Check with your hardware vendor to ensure that the write cache includes these and any other features necessary to avoid data loss.
SQL Server requires systems to support ‘ guaranteed delivery to stable media ’ as outlined under the Microsoft SQL Server Always-On Storage Solution Review program. FoFor 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:
967576 Microsoft SQL Server Database Engine Input/Output Requirements
Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Analysis Services, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Enterprise Evaluation, Microsoft SQL Server 2008 Express, Microsoft SQL Server 2008 Express with Advanced Services, Microsoft SQL Server 2008 Integration Services, Microsoft SQL Server 2008 R2 Analysis Services, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Express, Microsoft SQL Server 2008 R2 Express with Advanced Services, Microsoft SQL Server 2008 R2 Integration Services, Microsoft SQL Server 2012 Analysis Services, Microsoft SQL Server 2012 Business Intelligence, 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 Service Pack 1, Microsoft SQL Server 2012 Service Pack 2, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Web, SQL Server 2012 Enterprise Core, SQL Server 2012 Reporting Services, Microsoft SQL Server 2014 Business Intelligence, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Service Pack 1, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web, SQL Server 2014 Reporting Services