Professional Microsoft® SQL Server® 2008 Administration

18.1. Types of Failure

A variety of failures can bring down your database. Anything from a user error to a natural disaster could take your database offline. Your backup and recovery plan needs to account for the possibility of these failures and more.

18.1.1. Hardware Failure

Hardware is more reliable than it has been in the past. However, components can still fail, including the CPU, memory, bus, network card, disk drives, and controllers. A database system on a high-availability solution can mitigate a hardware failure such that if one database server fails, SQL Server will failover to the standby database server that includes fault-tolerant disk arrays and perhaps redundant I/O controllers. All of this helps keep the database online. However, high-availability solutions cannot protect against a faulty controller or a disk that causes I/O failures and corrupts the data.

Use SQLIOSim to help identify the optimal disk configuration or troubleshoot I/O faults. SQLIOSim replaces SQLIOStress in prior releases. You can get more information or download SQLIOSism from http://support.microsoft.com/default.aspx?scid=kb;en-us;231619.

18.1.2. User Error

A common user error is not including a restrictive WHERE clause during an update or delete operation and modifying more rows than expected or intended. As a preventive measure, users should start data modifications inside a BEGIN TRANSACTION, and then they can verify that the correct number of rows were updated before executing a COMMIT ...