DM232 Home Grown DBMS Backup and Recovery Systems

1
DM232Home Grown DBMS Backup and Recovery Systems
Brent DeSpain MicronTechnology,
Inc. http\\www.micron.com bdespain_at_micron.com
2
Home Grown DBMS Backup and Recovery Systems

• Overview
• Microns Environment
• Where We Started
• Where We are Currently
• Where We are Going
• Sybase Specific Dump Stored Procedures

3
Microns Environment

• Multiple DBMS vendors
• Sybase ASE
• Oracle
• MS SQL Server
• Red Brick (Data Warehousing)
• Nucleus (Compressed Bitmapped Database)

4
Microns Environment

• Multiple Platforms
• AIX
• Solaris
• NT
• Dec UNIX
• Long Term Storage
• IBMs ADSM robotic tape library

5
Microns Environment

• Multiple Physical Sites Around the World
• Boise, ID
• Lehi, UT
• Richardson, TX
• Avezzano, Italy
• Singapore

6
Where We Started

• 6 Years Ago
• 2 Sybase 4.9.2 Servers on VAX/VMS.
• Used VMS AUTOSUB to schedule dump database and
  dump transactions.
• Backed up directly to TK85 tapes.
• Generated about 500 MB daily.

7
Where We Started

• Pros
• Simple small environment.
• Cons
• Manually had to load tapes for backups and
  recoveries.
• Backup to tape was slow.
• VMS was dropped as a supported platform by Sybase.

8
Where We are Now

• DBMS Vendors
• Sybase
• Versions ranging from ASE 11.5.1.x to 11.9.2.1.
• 105 Production servers out of 190 total servers.
• 1,920 user defined databases.
• HW
• 80 AIX
• 19 Solaris
• 1 NT

9
Where We are Now

• DBMS Vendors cont.
• Oracle
• Versions 8.0.5 and 8.1.5.
• 20 Production instances out of 36 total.
• HW
• 44 Solaris
• 34 AIX
• 22 NT

10
Where We are Now

• DBMS Vendors cont.
• NT
• Versions 6.5 and 7.0.
• 4 Production servers out of 8 total servers.
• HW
• 100 NT -- Imagine that.

11
Where We are Now

• DBMS Vendors cont.
• Red Brick
• 1 installation. Moving from Dec UNIX to Solaris.
• Nucleus
• 1 installation on NT.

12
Where We are Now

• Scheduling is done at the OS level (i.e. crontab,
  at, etc.) on each host.
• Database backups are sent directly to disk on
  each host and are kept there for quick recovery.
• Database and transaction backups are copied to
  ADSM and the transaction backups are deleted.
• We backup around 15 TB on a daily basis.

13
Where We are Now

• Pros
• Automated. No human interaction needed once a
  server is set up.
• Quick recovery. Last database backup on disk.
  Dont have to read tapes.
• Reliable long term storage.
• Good error handling once backup stored procedure
  called.

14
Where We are Now

• Cons
• Scheduling nightmare.
• ADSM cant handle all backups at once.
• Staggering dumps involves visiting several hosts.
• No errors generated on some conditions.
• Job fails to run.
• Server is never set up.
• Copy to ADSM fails or is interrupted.

15
Where We are Now

• Cons cont.
• Recovery difficult.
• Have to use ADSMs interface.
• Very difficult to recover files to a different
  host, even if they are in the same cluster.
• Different algorithms and scripts used for each
  DBMS/OS combination.
• ADSM throughput dictates backup windows.
• Manual backups do not go to ADSM.

16
Where We are Going

• Features Needed
• Flexible scheduling
• Across multiple servers and hosts.
• Down to the database and transaction log level.
• ADSM throughput governor.
• Long term storage
• Manual backups need to be copied to ADSM.
• Interrupted and failed copies need to be
  resubmitted.

17
Where We are Going

• Features Needed cont.
• Detection of
• New servers and databases.
• Backups that were missed or not started.
• Backups that hang.
• Errors during backup.
• Success or failure of copy to ADSM.
• Support for multiple sites. No dependency on
  WAN.
• Support for all DBMS/OS with same interface.

18
Where We are Going

• Features Needed cont.
• Support multiple backup types.
• Cold Backup - taken at the OS level while the
  database is down.
• Hot Backup - taken while the database is
  available and active.
• Transactional Backup - transactional activity.
• Object Backup - a given object (i.e. a table).

19
Where We are Going

• Features Needed cont.
• Reduce code maintenance.
• Automated recovery of backup files.
• Recover to previous location.
• Recover to an alternate host.
• Recover based on point-in-time.

20
Where We are Going

• Architecture
• Centralized scheduler
• Maintain all scheduling from one interface.
• Schedule backups according to databases needs.
• Schedule transaction dumps as well as thresholds.
• Manage ADSM throughput.
• DBMS/OS independent.

21
Where We are Going

• Architecture cont.
• Jobs
• Detects backups that hang or exceed time.
• Detects error during copy to ADSM.
• Resubmits failed copies to ADSM.
• DBMS specific, but platform independent.

22
Where We are Going

• Architecture cont.
• DBMS specific code.
• Generates backup files.
• Captures errors during backup.
• Captures manual backup events.
• Reports
• Picks up new servers and databases.
• Backups that were missed on not started.

23
Where We are Going

• Tools
• Java and JDBC
• Scheduler, Jobs, and user interfaces.
• Provides OS independence.
• Can create a plug-in model to support new DBMS.
• Sybase ASE for data repository.
• Sybase Replication Server for redundancy and
  multi-site.

24
Where We are Going

• Tools cont.
• Perl
• DBMS specific code on hosts.
• Provides OS independence.
• Reduce code maintenance.

25
Sybase Specific Dump Stored Procedures

• diskdump_db
• diskdump_tran