Database Reference
In-Depth Information
Database Backup
Database backup is a broad topic and can't be given due justice in this query optimization book. Nevertheless,
I suggest that when it comes to database performance, you be attentive to the following aspects of your database
backup process:
•
Differential and transaction log backup frequency
•
Backup distribution
•
Backup compression
The next sections go into more detail on these suggestions.
Incremental and Transaction Log Backup Frequency
For an OLTP database, it is mandatory that the database be backed up regularly so that, in case of a failure, the
database can be restored on a different server. For large databases, the full database backup usually takes a long time,
so full backups cannot be performed often. Consequently, full backups are performed at widespread time intervals,
with incremental backups and transaction log backups scheduled more frequently between two consecutive full
backups. With the frequent incremental and transaction log backups set in place, if a database fails completely, the
database can be restored up to a point in time.
Differential backups can be used to reduce the overhead of a full backup by backing up only the data that has
changed since the last full backup. Because this is potentially much faster, it will cause less of a slowdown on the
production system. Each situation is unique, so you need to find the method that works best for you. As a general
rule, I recommend taking a weekly full backup and then daily differential backups. From there, you can determine the
needs of your transaction log backups.
Frequently backing up of the transaction log adds a small amount of overhead to the server, especially during
peak hours.
For most businesses, the acceptable amount of data loss (in terms of time) usually takes precedence over
conserving the log-disk space or providing ideal database performance. Therefore, you must take into account the
acceptable amount of data loss when scheduling the transaction log backup, as opposed to randomly setting the
backup schedule to a low-time interval.
Backup Scheduling Distribution
When multiple databases need to be backed up, you must ensure that all full backups are not scheduled at the same
time so that the hardware resources are not hit at the same time. If the backup process involves backing up the
databases to a central SAN disk array, then the full backups from all the database servers must be distributed across
the backup time window so that the central backup infrastructure doesn't get slammed by too many backup requests
at the same time. Flooding the central infrastructure with a great deal of backup requests at the same time forces the
components of the infrastructure to spend a significant part of their resources just managing the excessive number of
requests. This mismanaged use of the resources increases the backup durations significantly, causing the full backups
to continue during peak hours and thus affecting the performance of the user requests.
To minimize the impact of the full backup process on database performance, you must first determine the
nonpeak hours when full backups can be scheduled and then distribute the full backups across the nonpeak time
window, as follows:
1.
Identify the number of databases that must be backed up.
2.
Prioritize the databases in order of their importance to the business.
3.
Determine the nonpeak hours when the full database backups can be scheduled.
