Article ID: 917043 - View products that this article applies to.
This article outlines some of the key factors customers should be aware of when evaluating a third-party file caching system.
Third-party file cache implementations may increase performance of Microsoft SQL Server databases when properly implemented. Specific implementations, however, and configurations of these products may leave SQL Server databases at a high risk of data loss. Customers should completely test the configuration to ensure proper data integrity.
Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted in examples herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
© 2006 Microsoft Corporation. All rights reserved.
Microsoft, Windows, Windows Server, and SQL Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
This article is written specifically for SQL Server but generally applies to the Jet databases used by Active Directory and Exchange Server products as well.
This section outlines requirements and provides detailed examples that should be completely discussed with a third-party vendor before deploying any solution. Customers should also take special care to test various recovery scenarios to make sure data integrity is properly maintained.
Input/Output (I/O) requirements of SQL ServerAny SQL Server database or backup file requires storage fundamentals supporting the Write-Ahead Logging (WAL) protocol. These fundamentals are outlined in the following articles:
SQL Server 2000 I/O basicsNote The article also applies to SQL Server 2005 and later versions.
230785The following is a list of some important requirements:
(https://support.microsoft.com/kb/230785/ )SQL Server 7.0, SQL Server 2000, and SQL Server 2005 logging and data storage algorithms extend data reliability
Partner product certifications are not a guarantee of compatibility or safetyA third-party product or a particular vendor can receive a Microsoft logo certification. However, partner certification or a specific Microsoft logo does not certify compatibility or fitness for a particular purpose for SQL Server, Exchange Server, or Active Directory.
FILE_FLAG_WRITETHROUGH and FILE_FLAG_NO_BUFFERINGMicrosoft database products specifically use the write-through and no buffering flags when opening database files to prevent data loss. Any write request to these files must be secured to stable media. Otherwise, data loss can occur. Listed below are specific examples that file system caches may expose:
Talking points and examplesThe following section provides key examples and talking points pertaining to data integrity and safety. It uses power failure as a common point of failure and clarity, but this can often be substituted with various other problems leading to similar database integrity issues.
Example 1: Data loss and physical or logical corruptionConsider the following scenario:
There are various other issues that could arise with small changes to how the cache handles the I/Os. A brief derivation assumes the transaction was rolled back, but page 100 made it to physical media. Crash recovery again does not know about the log record (never made it to stable media), so page 100 will not receive undo operations during crash recovery, leaving the database logically and possibly physically corrupted.
Example 2: Suspect databaseSome vendors allow "opt out of files" and often recommend leaving the database log file (.ldf for SQL Server) opted out of the cache. The "opt out" policy is such that the administrator specifically has to mark files to be ignored by the caching software. Otherwise, the file is automatically included.
This is a poor assumption, as the following examples highlight. Microsoft recommends all database and backup files be opted out of such a cache.
Crash recovery uses the LSN value stored in the page header to determine recovery needs.
Example 3: Backups are not valid - Silent backup chain breakExample 2 is just a fraction of these types of problems that could be experienced. For this example, instead of recovery mode "SIMPLE," let us put the database in "FULL RECOVERY" mode but take regular backups of the log and database. At first, it appears that this would be better because you have an intact log chain and could just execute a restore sequence to correct the problem.
This might not be a valid assumption, as some cache implementations use the "opt out" policy so the backup file or portions of it can be unexpectedly cached. When SQL Server flushes the backup file, SQL Server requires that all writes to the backup media are properly stored on or in stable media using the Win32 API FlushFileBuffers function. Thus, if the cache vendor does not ensure all writes are properly flushed, during the FlushFileBuffers function call, to stable media before the operation successfully completes, the database engine can truncate the log without a secured backup. Again, a power failure at this point results in a condition where the proper log records are missing and can cause crash recovery to fail. What is more important is that crash recovery may not be able to detect this because of the missing log records in the database and the backup chain may be silently damaged. Only when a restore of the backup is attempted will the database engine be able to indicate the backup has been damaged.
Example 4: Invalid database statesDatabase files contain dependencies among each other requiring strict write-through and ordering compliance to apply to all of them as a group. Checkpoint, file size changes, differential backups, non-logged operations, and the BULK LOGGED recovery model are among a few of the key database activities that require write through to occur on data files, making policies like opting out only the log file an invalid assumption.
Example 5: Snapshot database data loss – May be silentSQL Server 2005 introduces snapshot databases for point in time queries. This uses copy-on-write database technology to help secure a copy of a data page in the snapshot data database before a new modification is made to the data page in the base database. This process requires that the page be secured in the snapshot database before the transaction can continue. If the page is not secured on or in stable media, data integrity problems will persist. The snapshot database does not contain a transaction log, so the write of the page is critical. If something like a power outage occurred, it may be possible that the main database page has been changed but the snapshot does not reflect the previous image because the cached write was lost.
How to configureHow to configure a product providing file cache from something like non-battery backed cache is specific to the vendor implementation. A few rules, however, can be applied:
Read-only databasesRead-only databases may be a good example where these types of products excel. If the database was first created and stored on or in stable media to ensure data integrity, the ALTER DATABASE statement used to mark the database READ ONLY, and the database subsequently assigned to the caching mechanism, performance gains may be encountered. Some implementations keep compressed images of the database pages in the cache, allowing more physical data to be retrieved from the cache and reducing physical I/O.
Caution The database should never be made READ WRITE when assigned to a cache that does not uphold the WAL protocol.
SecurityIntroducing a cache, such as the RAM-based file system cache, introduces another "in-memory" location for data. Products such as a database engine may assume critical data has been stored in or on stable media and properly retain access control list (ACL) protections. The RAM-based cache could expose the data to a set of security issues that are unique compared with stable media. For example, if the application is designed to use something like the SecureZeroMemory function every time that it has finished using critical information, the application has an expectation that the data no longer exists in RAM. However, if a form of the data can remain cached when the application expected it to be in or on stable media, it could alter the security considerations.
Data integrity checksMicrosoft always recommends a strong and clear data integrity strategy. This should include, but is not limited to, restoring backups and regular DBCC CHECKDB operations on both the production and the restored database.
Microsoft also recommends increasing the frequency of these safety tests when evaluating and implementing changes to the environment or if any concerns arise pertaining to the stability of the environment.
For more information about how to use the SQLIOStress utility, click the following article number to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/231619/ )How to use the SQLIOStress utility to stress a disk subsystem such as SQL Server
The TEMPDB databaseIt is possible to locate the TEMPDB database on certain caching systems. Several factors should be carefully considered and tested when evaluating the storage location of the TEMPDB database in this configuration. The following Microsoft Knowledge Base article outlines the I/O requirements, associated support boundaries, and possible performance gains.
(https://support.microsoft.com/kb/917047/ )Microsoft SQL Server I/O sub-system requirements for the TEMPDB database
SupportMicrosoft SQL Server Support will assist customers, using standard data recovery techniques. If products installed on the computer draw data integrity into question, Microsoft SQL Server, Active Directory and Exchange Support may ask that the product be uninstalled and will not engage in root cause analysis until such time the problem can be reproduced without said product.
Microsoft does not certify or validate that third-party products work correctly with SQL Server. Additionally, Microsoft does not provide any warranty, guarantee, or statement of any third-party product's fitness for use with SQL Server.
Carefully consider the additional information provided by the following references to evaluate the improvement of SQL Server performance:
(https://support.microsoft.com/kb/826433/ )Additional SQL Server diagnostics added to detect unreported I/O problems
(https://support.microsoft.com/kb/828339/ )Error message 823 may indicate hardware problems or system problems in SQL Server
(https://support.microsoft.com/kb/234656/ )Using disk drive caching with SQL Server
(https://support.microsoft.com/kb/304261/ )Description of support for network database files in SQL Server
(https://support.microsoft.com/kb/910716/ )Support for third party Remote Mirroring solutions used with SQL Server 2000 and 2005 user databases
(https://support.microsoft.com/kb/913945/ )Microsoft does not certify that third-party products will work with Microsoft SQL Server
Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
SQL Server requires systems to support 'guaranteed delivery to stable media' as outlined under the MSQL Server I/O Reliability Program Requirements
(http://download.microsoft.com/download/F/1/E/F1ECC20C-85EE-4D73-BABA-F87200E8DBC2/SQL_Server_IO_Reliability_Program_Review_Requirements.pdf). 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:
(https://support.microsoft.com/kb/967576/ )Microsoft SQL Server Database Engine Input/Output Requirements
Article ID: 917043 - Last Review: May 12, 2015 - Revision: 4.0