Article ID: 917043
Article Last Modified on 11/2/2007
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Workgroup Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2000 Personal Edition
SUMMARY
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.
MORE INFORMATION
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 Server
Any 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 basics
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
Note The article also applies to SQL Server 2005.
230785 SQL Server 7.0, SQL Server 2000, and SQL Server 2005 logging and data storage algorithms extend data reliability
The following is a list of some important requirements:
- Write ordering must be maintained.
- Dependent write consistency must be maintained.
- Writes must always be secured in or on stable media.
- Torn I/O prevention must occur.
Partner product certifications are not a guarantee of compatibility or safety
A 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_BUFFERING
Microsoft 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:
- Write combining and write reordering
To reduce physical I/O requests, the non-battery based caches often combine and reorder write operations, immediately breaking the WAL protocol requirements.
- Block size of I/Os
Similar to the write combining and reordering are block size I/Os. The caches will attempt to complete I/Os based on the I/O path block size. Again, this can provide a performance boost but opens up the database to damage and immediately breaks the WAL protocol requirements.
Talking points and examples
The 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 corruption
Consider the following scenario:
- A log record is written for page 100 in the database.
- A log record is held in non-battery-based cache, but the database engine is told the log write is complete "secured to stable media".
- The database engine considers the LSN hardened and issues write for page 100.
- Page 100 is also held in non-battery based cache.
- Commit transaction completes without error.
The database engine continues processing as if it successfully committed the writes to stable media. A power outage at this point, however, will result in immediate data loss because the changes never physically existed outside the non-battery backed cache. Crash recovery does not indicate an error because crash recovery does not know about the lost log record and will not attempt to redo the work. Multiple object modification scenarios (e.g. primary key, foreign key inserts) expand on the various types of database damage that can occur.
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 database
Some 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.
- The log file is opted out, so writes are getting to stable media.
- Page 100 is modified.
- The database engine runs a checkpoint operation.
- The engine is told all database pages and log records are secure (point in time up to checkpoint considered hardened). However, the data pages are not all stored on or in stable media.
- The SQL Server database is in recovery mode "SIMPLE," so checkpoint now truncates the log records.
- Page 100 that was just check pointed is modified again.
This situation has exposed the database to data loss and generally results in a suspect database. Again, if a power outage takes place, crash recovery has no log records because they no longer exist due to truncation. The database engine has no information indicating that database pages are missing data that should have been secured during the checkpoint. Crash recovery attempts to analyze the second modification on page 100 but fails because page 100 was not properly secured to stable media at the time of the checkpoint.
Crash recovery uses the LSN value stored in the page header to determine recovery needs.
- If the LSN on the page is equal to or newer than that of the log record, recovery does no further work on the page because the page is already consistent with the log record based on LSN comparison.
- If the LSN on the page is older than that of the log record, recovery needs to perform the proper operations to return the page to the proper state. Recovery fails if recovery has uncovered a missing data condition and cannot properly return the page to its rightful state.
From the example, the previous LSN stored in the log record for the second modification of page 100 does not exist in the page header, and there is no prior log record present to redo the page. Therefore, the database is marked as suspect, as recovery cannot safely continue.
Example 3: Backups are not valid - Silent backup chain break
Example 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 states
Database 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 silent
SQL 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 configure
How 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:
- All writes must be completed in or on stable media before the cache indicates to the operating system that the I/O is finished.
- Data can be cached as long as a read request serviced from the cache returns the same image as located in or on stable media.
These rules essentially mean that the non-battery backed cache may be effective for read operations but should not be used for write requests. With the proper configuration, this may provide a performance gain for the database engine. This should, however, be tested carefully, because setting aside something like RAM that could be used by SQL Server may degrade overall performance. SQL Server may be able to use the extra memory with more precision than a generic cache mechanism.
Read-only databases
Read-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.
Security
Introducing 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 checks
Microsoft 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:
231619 How to use the SQLIOStress utility to stress a disk subsystem such as SQL Server
The TEMPDB database
It 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.
917047 Microsoft SQL Server I/O sub-system requirements for the TEMPDB database
Support
Microsoft 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.
REFERENCES
Carefully consider the additional information provided by the following references to evaluate the improvement of SQL Server performance:
826433 Additional SQL Server diagnostics added to detect unreported I/O problems
828339 Error message 823 may indicate hardware problems or system problems in SQL Server
234656 Using disk drive caching with SQL Server
110352 Optimizing Microsoft SQL Server performance
304261 Description of support for network database files in SQL Server
910716 Support for third party Remote Mirroring solutions used with SQL Server 2000 and 2005 user databases
913945 Microsoft does not certify that third-party products will work with Microsoft SQL Server
Keywords: kbexpertiseadvanced kbinfo KB917043
