Data Warehousing - Backup

A data warehouse is a complex system and it contains a huge volume of data. Therefore it is important to back up all the data so that it becomes available for recovery in future as per requirement. In this chapter, we will discuss the issues in designing the backup strategy.

Backup Terminologies

Before proceeding further, you should know some of the backup terminologies discussed below.

• Complete backup − It backs up the entire database at the same time. This backup includes all the database files, control files, and journal files.

• Partial backup − As the name suggests, it does not create a complete backup of the database. Partial backup is very useful in large databases because they allow a strategy whereby various parts of the database are backed up in a round-robin fashion on a day-to-day basis, so that the whole database is backed up effectively once a week.

• Cold backup − Cold backup is taken while the database is completely shut down. In multi-instance environment, all the instances should be shut down.

• Hot backup − Hot backup is taken when the database engine is up and running. The requirements of hot backup varies from RDBMS to RDBMS.

• Online backup − It is quite similar to hot backup.

Hardware Backup

It is important to decide which hardware to use for the backup. The speed of processing the backup and restore depends on the hardware being used, how the hardware is connected, bandwidth of the network, backup software, and the speed of server's I/O system. Here we will discuss some of the hardware choices that are available and their pros and cons. These choices are as follows −

• Tape Technology
• Disk Backups

Tape Technology

The tape choice can be categorized as follows −

• Tape media
• Standalone tape drives
• Tape stackers
• Tape silos

Tape Media

There exists several varieties of tape media. Some tape media standards are listed in the table below −

Other factors that need to be considered are as follows −

• Reliability of the tape medium
• Cost of tape medium per unit
• Scalability
• Cost of upgrades to tape system
• Cost of tape medium per unit
• Shelf life of tape medium

Standalone Tape Drives

The tape drives can be connected in the following ways −

• Direct to the server
• As network available devices
• Remotely to other machine

There could be issues in connecting the tape drives to a data warehouse.

• Consider the server is a 48node MPP machine. We do not know the node to connect the tape drive and we do not know how to spread them over the server nodes to get the optimal performance with least disruption of the server and least internal I/O latency.

• Connecting the tape drive as a network available device requires the network to be up to the job of the huge data transfer rates. Make sure that sufficient bandwidth is available during the time you require it.

• Connecting the tape drives remotely also require high bandwidth.

Tape Stackers

The method of loading multiple tapes into a single tape drive is known as tape stackers. The stacker dismounts the current tape when it has finished with it and loads the next tape, hence only one tape is available at a time to be accessed. The price and the capabilities may vary, but the common ability is that they can perform unattended backups.

Tape Silos

Tape silos provide large store capacities. Tape silos can store and manage thousands of tapes. They can integrate multiple tape drives. They have the software and hardware to label and store the tapes they store. It is very common for the silo to be connected remotely over a network or a dedicated link. We should ensure that the bandwidth of the connection is up to the job.

Disk Backups

Methods of disk backups are −

• Disk-to-disk backups
• Mirror breaking

These methods are used in the OLTP system. These methods minimize the database downtime and maximize the availability.

Disk-to-Disk Backups

Here backup is taken on the disk rather on the tape. Disk-to-disk backups are done for the following reasons −

• Speed of initial backups
• Speed of restore

Backing up the data from disk to disk is much faster than to the tape. However it is the intermediate step of backup. Later the data is backed up on the tape. The other advantage of disk-to-disk backups is that it gives you an online copy of the latest backup.

Mirror Breaking

The idea is to have disks mirrored for resilience during the working day. When backup is required, one of the mirror sets can be broken out. This technique is a variant of disk-to-disk backups.

Note − The database may need to be shutdown to guarantee consistency of the backup.

Optical Jukeboxes

Optical jukeboxes allow the data to be stored near line. This technique allows a large number of optical disks to be managed in the same way as a tape stacker or a tape silo. The drawback of this technique is that it has slow write speed than disks. But the optical media provides long-life and reliability that makes them a good choice of medium for archiving.

Software Backups

There are software tools available that help in the backup process. These software tools come as a package. These tools not only take backup, they can effectively manage and control the backup strategies. There are many software packages available in the market. Some of them are listed in the following table −

Criteria for Choosing Software Packages

The criteria for choosing the best software package are listed below −

• How scalable is the product as tape drives are added?
• Does the package have client-server option, or must it run on the database server itself?
• Will it work in cluster and MPP environments?
• What degree of parallelism is required?
• What platforms are supported by the package?
• Does the package support easy access to information about tape contents?
• Is the package database aware?
• What tape drive and tape media are supported by the package?