Data Warehousing Backup Strategies

Data Warehousing Backup

• A data warehouse is a complex system that requires a backup strategy to ensure data availability for recovery in the future.
• Backup terminologies:
  • Complete backup: backs up the entire database at once, including all files, control files, and journal files.
  • Partial backup: does not create a complete backup of the database, useful for large databases.
  • Cold backup: taken when the database is completely shut down.
  • Hot backup: taken when the database engine is up and running, with varying requirements depending on the RDBMS.
  • Online backup: similar to hot backup.

Hardware Backup

• The choice of hardware affects the speed of processing backups and restores.
• Hardware options:
  • Tape technology
  • Disk backups

Tape Technology

• Tape media options:
  • Capacity and I/O rates:
    • DLT: 40 GB, 3 MB/s
    • 3490e: 1.6 GB, 3 MB/s
    • 8 mm: 14 GB, 1 MB/s
  • Other factors to consider:
    • Reliability of 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:
  • Can be connected directly to the server, as network available devices, or remotely to other machines.
  • Issues with connecting tape drives to a data warehouse, including node selection and optimal performance.
• Tape stackers:
  • Load multiple tapes into a single tape drive, allowing for unattended backups.
  • Prices and capabilities vary.
• Tape silos:
  • Provide large storage capacities, storing and managing thousands of tapes.
  • Can integrate multiple tape drives, with software and hardware for labeling and storing tapes.
  • Often connected remotely over a network or dedicated link.

Disk Backups

• Methods:
  • Disk-to-disk backups
  • Mirror breaking
• Used in OLTP systems to minimize database downtime and maximize availability.
• Disk-to-disk backups:
  • Faster than tape backups
  • Advantage of having an online copy of the latest backup
• Mirror breaking:
  • Have disks mirrored for resilience during the working day.
  • Break out one of the mirror sets for backup.
  • May require database shutdown to ensure consistency.

Optical Jukeboxes

• Allow data to be stored near line.
• Manage a large number of optical disks like a tape stacker or tape silo.
• Drawback: slow write speed compared to disks.
• Advantages: long-life and reliability, making them suitable for archiving.

Software Backups

• Software tools are available to aid in the backup process.
• These tools come as a package.