SQL Server 2005 Recovery Architecture

Before we can use the acuity index I discussed earlier and derive backup plans to meet an agreed-upon index, let's see what SQL Server 2005 is made of.

Past and present transaction log architecture in SQL Server allows the DBMS to recover to the last commit point written to the transaction log just before or at a system crash. As discussed in Chapter 2, the transaction log is a write-ahead log, so any committed transaction written to the transaction log (flushed from the main memory buffers of the DBMS) can be recovered upon system recovery and permanently committed to the database. Naturally this recovery process depends on the availability or consistent state of the transaction log after system recovery.

As soon as the system recovers, the DBMS rolls back all transactions that failed due to the collapse of the system, as is determined by built-in mechanisms that ensure the atomicity of the transactions. Client processes will thus be allowed to represent the transaction (I discuss how in later chapters that discuss distributed transactions, transactions in message queues, and so on). Transactions that survived the crash are committed to the database.

SQL Server 2005 introduces advanced recovery architecture to this DBMS platform in the form of recovery models. The recovery model centers on the transaction log as the object or mechanism of recovery, as mentioned earlier, that can restore to the last commit point written to the transaction log. The recovery model is really a transaction- and operation-logging mode (I am not sure why Microsoft calls it a model) that determines how much and what data is written to the transaction log and how it is used in recovery. The recovery model also determines how successful the transaction log will be in recovering the transactions between actual full and differential database file backups up to and including the last transaction written to the log.

There are three such recovery models in SQL Server 2005: full, bulk-logged, and simple. Table 7–1 discusses the attributes and benefits of each model.

Table 7–1: SQL Server 2005 Recovery Models

Recovery Model	Attributes	Benefits	Transaction Loss	Recovery
Full	Logs all transactions. Optimized for high-end OLTP systems.	Can recover to any point in time after database backup.	None, if the transaction log file is available.	To any point in time up to the last written transaction in the write-ahead log.
Bulk-logged	Optimized for bulk operations (BCP, BULK INSERT, and so on). Minimal log space is used.	Permits high-performance bulk operations with a recovery safety net.	None, if log is consistent and recovered or bulk copy is not the cause of the damage.	To the end of the last database and transaction log backup.
Simple	Optimized for low-end OLTP and database operations, and high-performance bulk operations.	Keeps log footprint small in bulk copy or insert operations because log is not normally backed up.	None, If log file installs consistent data to the database.	To the end of the last database backup.

Recovery models are set for a database as soon as the system is installed or a new database is created. The recovery model set for the model database is full. This does not mean that every time you create a database it will inherit the full setting installed for model (see Chapter 2). New databases created in Management Studio are usually set for simple recovery. Changing the recovery model is done through the ALTER DATABASE statement using T-SQL as follows:

 ALTER DATABASE <databasename> SET RECOVERY [FULL | BULK_LOGGED | SIMPLE]

Do not use the angle brackets. For example:

 ALTER DATABASE Modelize SET RECOVERY FULL

To determine the recovery model set for your database, you can query the setting in the DATABASEPROPERTYEX property of the database as follows:

 SELECT databasepropertyex('<databasename>', 'recovery')

You can also change the recovery model on the Options tab of your database’s Properties dialog box. But don’t go looking for radio buttons marked “recovery model” on the Options tab; you will not find them. To change the recovery option on the Options tab in your Database Properties dialog box (setting database options is fully discussed in Chapter 6), you need to select the recovery model from the Recovery model drop-down list, which is found on the Database Properties, Options tab in Figure 7–2. Other options, such as Log truncation, is set in the Backup Database options dialog box.

Figure 7–2: The Database Properties dialog box

Understanding the Recovery Models

Let’s now examine the so-called recovery models a little closer.

The full recovery model provides the greatest recoverability possibility for line-of-business OLTP operations. It is thus ideally suited to meet our 0/0 acuity index as discussed earlier. In other words, we get the fastest and safest or most complete restore and recoverability of data if the database operates under full recovery. The full recovery model also provides you with the greatest flexibility in terms of recovering a database to any point in time after the last full or differential backup.

The bulk-logged recover model was engineered for recovery under bulk-copy or BULK INSERT operations. The transaction log writing algorithms have been optimized for speed and log space reduction for operations such as CREATE INDEX and BULK COPY.

The simple recovery model is the easiest way to back up SQL Server databases because no transaction log backups are done and the transaction log is maintained in such a manner as to ensure the simplest recovery from server failure. After log truncation, log space is no longer needed for system recovery (after the transactions have been committed to the database) and the log space is reused.

Obviously you have a higher risk of data loss if a database is damaged or lost during normal business operations. However, most LOB applications do not need the more critical recovery models because backups are usually done during the night in any event. Also, a high-level backup/restore acuity index will not apply to “simple” data entry because the business will determine that to manually recover and reenter data costs far less than the technology to close the void-time backup window.