Additional Backup Considerations

There are three additional backup considerations.

Frequency of Backups

How often you back up your databases will depend on many factors. These will include:

• The size of your databases, and your backup window

• The frequency of changes to the data, and the method by which it is changed

• The acceptable amount of data loss in the event of a failure

• The acceptable recovery time in the event of a failure

First you must establish what your backup window will be. The backup window is the time allocated to you to complete the task of backing up the database. As SQL Server allows dynamic backups, users can still access the database during backup; however, it will impact performance. This means you still must schedule backups for low activity periods, and have them complete in the shortest possible time.

After you have established your backup window, you can determine your backup method and schedule. For example, if it takes four hours for a full backup to complete, and the database is quiescent between midnight and 6:00 a.m., you have time to perform a full backup each night. On the other hand, if a full backup takes 10 hours and you have a two- hour window, you will have to consider monthly or weekly backups perhaps in conjunction with filegroup, differential, and transaction log backups. In many decision support databases that are populated with periodic data loads, it might suffice to back up once, after each data load.

Backup frequency is also directly tied to acceptable data loss. In the event of catastrophic failure, such as a fire in the server room, you can only recover data up to the point of the last backup that was moved offsite. If it is acceptable to lose a day's worth of data entry, nightly backups might suffice. If your acceptable loss is an hour's worth of data, then hourly transaction log backups would have to be added to the schedule.

Your backup frequency will also affect your recovery time. I have worked on sites where transaction log backups complemented weekly full backups every 10 minutes, which was the acceptable data loss factor. A failure a few days after backup meant a full database restore and the application of hundreds of transaction logs. Adding a daily differential backup in this case would vastly improve restore time. The full and differential backups would be restored, and then six logs applied for each hour between the differential and the time of failure.

Using a Standby Server

If the ability to quickly recover from failure is crucial to your operation, you might consider implementing a standby server. Implementing a standby server involves backing up the production server and then restoring it to the standby server, leaving it in recovery mode. As transaction logs are backed up on the production server they are applied to the standby server. If there is a failure on the production server, the standby server can be recovered and used in place of the production server. If the production server is still running, don't forget to back up the current log with the NO_TRUNCATE option and restore it to the standby server as well before bringing it online.

NOTE

Another advantage of restoring backups to a standby server is it immediately validates your backups so you can be assured whether they are valid. There is nothing worse than finding out during a recovery process that one of the backup files is damaged or missing.

Prior to SQL Server 7.0, a standby server was often considered prohibitively expensive as it was always in recovery mode and therefore unavailable for anything else. It only came into play in case of failure. As management is rarely as pessimistic as the database administrator, it was a hard sell. A new option STANDBY = undo_file_name changed all that. When the database and subsequent log backups are restored to the standby server with this option, the database is left in recovery mode but is available as a read-only database. Now that the standby database is available for queries, it can actually reduce load on the production database by acting as a decision support system (DSS). Database Consistency Checks (DBCC) can be run on it as well, further reducing load on the production system.

For the database to be available for reads, the data must be in a consistent state. This means that all uncommitted transactions must be rolled back. This is usually taken care of by the RECOVERY option during restore. In the case of a standby server, this would cause a problem as you intend to apply more logs, which could, in fact, commit those transactions. This is taken care of by the undo_file_name clause of the STANDBY option. The file specified here holds a copy of all uncommitted transactions rolled back to bring the standby server to a read consistent, read-only state. If those transactions subsequently commit a log restore, this undo information can be used to complete the transaction.

SQL Server 2000 has introduced log shipping, which automates the transfer of logs to the standby server. Log shipping, which is configured with the Database Maintenance Plan Wizard, uses SQL Server Agent jobs on the primary server to back up the transaction log and copy it to a folder on the standby server. SQL Server Agent on the standby server then executes a load job to restore the log. Automating your standby server with log shipping reduces administration and helps to ensure that the standby database is up-to-date. For details on configuring a standby server, Chapter 22, "Data Replication."

Considerations for Very Large Databases

When it comes to backup and recovery, special consideration must be given to Very Large Databases, or VLDBs as they are known. A VLDB will have special requirements for

• Storage ”Size might dictate the use of tape backups over network or disk.

• Time ”As your backup window grows, the frequency of backups might have to be adjusted.

• Method ”How you back up your database will be affected by its size. Differential, or File and Filegroup, backups might have to be implemented.

• Recovery ”Partial database recovery, such as restoring a file or filegroup, might be required due to the prohibitive time required to restore the entire database.

When designing a VLDB, your backup plan must be integrated with storage, performance, and availability requirements. Refer to the previous sections in this chapter on File and Filegroup backups, as well as Differential backups. For a complete discussion on large databases, including information specific to backup and recovery, refer to Chapter 21, "Administering Very Large SQL Server Databases."