548
Part V: Enterprise Data Management
Although backups tend to be boring, restores tend to occur when people are excited. For this
reason, it makes sense to be as familiar with restoration as with backup. Without restoring a
backup, there is no way to tell if the backup is good and can be used when it is needed.What’s New in SQL Server Recovery?
SQL Server 2012 offers an exciting, new high-availability and disaster recovery feature called AlwaysOn
Availability Groups. AlwaysOn enables you to confi gure one or more databases into groups called
Availability Groups. Each group enables you to fail over a set a user databases together. This feature
is covered more in-depth in Chapter 27 “Database Mirroring.”Recovery Concepts
The concept of database recovery is based on the D in the transactional-integrity ACID
properties — transactional durability. Durability means that a transaction, when commit-
ted, regardless of hardware failure, must be persistent.SQL Server accomplishes transactional durability with a write-ahead transaction log. Every
transaction is written to the transaction log prior to being written to the data fi le. This
provides a few benefi ts to the recovery plan:■ The transaction log ensures that every transaction can be recovered up to the last
moment before the server stopped.
■ The transaction log permits backups while transactions are processed.■ (^) The transaction log reduces the impact of a hardware failure because the
transaction log and the data fi le may be placed on different disk subsystems.
The strategy of a recovery plan should be based on the organization’s tolerance level, or
pain level, for lost transactions. Recovery-plan tactics involve choosing among the various
backup options, generating a backup schedule, and off-site storage.
SQL Server backup and recovery are fl exible, offering three recovery models from which to
choose. The transaction log can be confi gured, based on your recovery needs, according to
one of the following recovery models:
■ (^) Simple: No transaction log backups.
■ Bulk-logged: The bulk-logged recovery model minimally logs bulk operations,
although fully logging other transactions.
■ Full: All transactions are logged.
In addition, SQL Server offers the following backup options:
■ Full: Complete backup of all data.
c21.indd 548c21.indd 548 7/31/2012 9:22:46 AM7/31/2012 9:22:46 AM
http://www.it-ebooks.info