Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 8

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 8 In the Data Protection Concepts section of this document, we'll characterize various data protection and recovery methods that are available to you in this environment. We'll touch on topics such as instance recovery and fault tolerance, but our main goal is to outline your options in the area of database backup and recovery. Automatic data protection and instance recovery consist of the system's ability to preserve the effects of all committed transactions and insure the database is in a consistent state after recovery from any single point of failure. This single point of failure includes a hardware component failure, software component failure, power loss, etc. SQL Server provides this automatic data protection and instance recovery through its write-ahead log, called the transaction log, where SQL Server records changes before it modifies the actual data pages. In the case of a server failure and recovery, SQL Server, upon startup, uses the transaction log to roll back uncommitted transactions, and roll forward transactions that were committed but not applied to the data files. All data definition changes (such as creating or dropping tables, views, stored procedures, rules, etc.) and all data manipulation changes (inserts, deletes and updates, but not selects) are logged in the transaction log. Certain other events such as checkpoints and dumps are logged as well. Naturally, any data definition changes, checkpoints, dumps, etc., will not be rolled back or forward. This data protection scheme cannot be turned off, and guarantees that data remains consistent at any point in time. The automatic data protection and instance recovery, in terms of the write-ahead transaction log, is inherent to SQL Server. More detailed information about transaction logging can be found in the Microsoft SQL Server manuals and other publications. Fault tolerance and data archival should coexist on the same system. Fault tolerance, both hardware and software based, provides you with immediate protection against a hardware or a software component failure. In conjunction with hot-pluggable drive systems, hardware-based fault tolerance allows for continuous data availability, maximum performance5 and automatic data recovery. Database archival allows for a long term data retention, and recovery from operational errors and catastrophic failures. The drive subsystem has a substantially higher chance of failing than any other component in the system. Here, various levels of fault tolerance are available, ranging from no fault tolerance to fault tolerance for the entire drive subsystem. Naturally, higher levels of fault tolerance carry higher implementation costs. Many times, a high level of fault tolerance can seem to be a rather expensive solution. However, you need to weigh the cost of implementing a good, fault tolerant system against the cost associated with down-time. Sites requiring access to data with minimal down time must implement hardware fault tolerance for the entire drive subsystem. With fault tolerance implemented on the entire drive subsystem, a drive failure will not interrupt any operations and will not cause any data loss. With the Compaq SMART and SMART-2 Array controllers and the hot-pluggable drive subsystem, a failed drive can be replaced and fault tolerance restored without taking the system off-line. 5 For performance characteristics of various RAID levels of the Compaq SMART Array Controller, and configuration considerations, refer to the document Configuring Compaq RAID Technology for Database Servers.