donderdag 8 september 2011

SQL Recovery Models explained

There are 3 different recovery models available in Microsoft SQL Server. However, the understanding of the differences between those recovery models doesn't seem to be well understood. That's why I decided to give a little summary of each of the recovery models.
To continue, I first need to explain the differences of logging levels

Logging Levels


Minimally logging


Minimal logging indicates it only logs the information that is required to recover the transaction without supporting point-in-time recovery. An operation is said to be minimally-logged if all it logs are the allocation changes. That means that there is not sufficient information in the log to replay the transaction.
So for example when doing a bulk insert only the allocations will be logged, not the inserted rows or the data on the pages. So this transaction can't be reproduced just by the information that was logged. SQL would be able to reallocate the pages, but it can't put the correct data in these pages.
A complete list of operations that will be minimally logged can be found here.

Fully logging


An operation is said to be fully logged if all information needed to reproduce the operation can be found in the logged information. This doesn't mean that for each operation every row will be individually logged in the transaction log. When doing a truncate on a table for example, it will only log the page de-allocations. But since this information is enough to reproduce the operation, it is still classified as fully logged.

In conclusion


Minimal logging is more efficient than full logging, and it reduces the possibility of a large-scale bulk operation filling the available transaction log space during a bulk transaction. However, if the database is damaged or lost when minimal logging is in effect, you cannot recover the database to the point of failure.

Recovery Models


Simple Recovery Model


The simple recovery model minimizes administrative overhead for the transaction log, because the transaction log is not backed up. The simple recovery model risks significant work-loss exposure if the database is damaged. Data is recoverable only to the most recent backup of the lost data. Therefore, under the simple recovery model, the backup intervals should be short enough to prevent the loss of significant amounts of data. However, the intervals should be long enough to keep the backup overhead from affecting production work. Including differential backups in the backup strategy can help reduce the overhead.
Generally, for a user database, the simple recovery model is useful for test and development databases or for databases containing mostly read-only data, such as a data warehouse. The simple recovery model is inappropriate for production systems where loss of recent changes is unacceptable. In such cases, we recommend using the full recovery model.
More information: Backup Under the Simple Recovery Model

Full Recovery Model


Provides the normal database maintenance model for databases where durability of transactions is necessary.
Log backups are required. This model fully logs all transactions and retains the transaction log records until after they are backed up. The full recovery model allows a database to be recovered to the point of failure, assuming that the tail of the log can be backed up after the failure. The full recovery model also supports restoring individual data pages.
More information: Backup Under the Full Recovery Model

Bulk-Logged Recovery Model


This recovery model bulk logs most bulk operations. It is intended solely as an adjunct to the full recovery model. For certain large-scale bulk operations such as bulk import or index creation, switching temporarily to the bulk-logged recovery model increases performance and reduces log space consumption. Log backups are still required. Like the full recovery model, the bulk-logged recovery model retains transaction log records until after they are backed up. The tradeoffs are bigger log backups and increased work-loss exposure because the bulk-logged recovery model does not support point-in-time recovery.
For a database that uses full recovery, switching to the bulk-logged recovery model temporarily for bulk operations improves performance. However, if data loss is unacceptable, to prevent data loss, it isrecommended that you switch to the bulk-logged recovery model only under the following conditions:
  • Users are currently not allowed in the database.
  • No modifications are made during bulk processing that are not recoverable without depending on taking a log backup; for example, by re-running the bulk processes.
When switching to Bulk-Logged recovery model consider these recommendations:
  • Before switching to the bulk-logged recovery model, you back up the log.This is important because, under the bulk-logged recovery model, if the database fails, backing up the log for bulk operations requires access to the data.
  • After performing the bulk operations, you immediately switch back to full recovery mode.
  • After switching back from the bulk-logged recovery model to the full recovery model, you back up the log again.

More information: Backup Under the Bulk-Logged Recovery Model

Summary of recovery models


Recovery model

Description

Work loss exposure

Recover to point in time?

Simple

No log backups.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.

Can recover only to the end of a backup.

Full

Requires log backups.

No work is lost due to a lost or damaged data file.

Can recover to an arbitrary point in time (for example, prior to application or user error).

Normally none.

If the tail of the log is damaged, changes since the most recent log backup must be redone. For more information, see Tail-Log Backups.

Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For more information, see Restoring a Database to a Point Within a Backup.

Bulk logged

Requires log backups.

An adjunct of the full recovery model that permits high-performance bulk copy operations.

Reduces log space usage by using minimal logging for most bulk operations. For more information, see Operations That Can Be Minimally Logged.

If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

Otherwise, no work is lost.

Can recover to the end of any backup. Point-in-time recovery is not supported.

Geen opmerkingen:

Een reactie plaatsen