Title: Arial 28pt'

1
SQL923 Backup and Recovery of SQL Anywhere, Tips
and Techniques
Robert Waywell Technical Services
Manager rwaywell_at_ianywhere.com August, 2004
2
Objectives

• Describe the types of failure an ASA database can
  experience
• Describe how ASA recovers from system failures
• Describe the data loss associated with media
  failure
• Describe how to protect from specific file loss
  (database, transaction log, mirror log)

3
Topics

• Types of Failure
• Protection from System Failure
• Protection from Media Failure
• Validating Databases
• Backups
• Recovery
• Backup Strategies

4
Types of Failure

• System
• Occurs when the computer or operating system goes
  down while there are partially completed
  transactions
• Computer turned off or rebooted
• Operating system crashes
• Power failure
• Database is unavailable but undamaged
• Requires no intervention
• System recovers automatically when restarted
• May take some time to recover

5
Types of Failure

• Media
• Occurs when a system or component failure causes
  the destruction of the database file(s).
• File system becomes unusable
• Physical disk drive fails
• File(s) become corrupted
• Database file and/or transaction log become
  unusable

6
Topics

• Types of Failure
• Protection from System Failure
• Protection from Media Failure
• Validating Databases
• Backups
• Recovery
• Backup Strategies

7
Protection from System Failure

• Integrity of any ASA database is protected by 3
  logs
• Transaction Log
• Stores a record of all changes to the database in
  the order in which they occur
• Checkpoint Log
• Contains before images of all physical data pages
  changed since last checkpoint (dirty pages)
• Rollback Log
• Contains the undo operations required to
  reverse any current transactions

8
Transaction Log

• Separate file from the database
• Records all inserts, deletes, updates, commits,
  rollbacks and database schema changes
• Should be created on a separate device with a
  separate controller from the database file
• Provides better recoverability in case of media
  failure

\mydata.log
9
Checkpoint Log

• Located at the end of the database file
• Checkpoint log pages are added as necessary
  during a session
• Checkpoint log pages are freed when a Checkpoint
  takes place
• Before any database changes are made, the server
• Reads the page into the database cache
• Makes a copy of the original page in the
  Checkpoint log on disk
• Changes are then made to the cached version of
  the page
• Checkpoint
• Flushes all dirty pages (changed pages) from
  cache to the database file
• Removes all entries from the checkpoint log

10
Checkpoint Takes Place When

• Database engine is shut down
• Time since last checkpoint gt CHECKPOINT_TIME
• Estimated time for recovery gt RECOVERY_TIME
• Database engine has been idle long enough
• CHECKPOINT command is issued
• Transaction committed on a database that is
  configured without a transaction log

11
How the Checkpoint Log Works
Empty
12
Rollback Log

• Located in cache
• On a checkpoint, the rollback logs for the active
  transactions are written to the database file
• Contains the undo operations required to
  reverse the current transaction
• One per open transaction
• Released when pending transaction committed or
  rolled back

13
System Recovery and the Logs

• After a system failure occurs
• The database server automatically takes the
  following steps to recover
• Recover to the most recent checkpoint using the
  Checkpoint Log
• Apply changes made since the checkpoint using the
  Transaction log
• Rollback any remaining uncommitted transactions
  using the Rollback logs

14
System Recovery

• I. 02/10 223534. Starting database "test"
  (C\test.db) at Mon Feb 10 2003 2235
• I. 02/10 223534. Database recovery in progress
• I. 02/10 223534. Last checkpoint at Mon Feb
  10 2003 2230
• I. 02/10 223534. Checkpoint log...
• I. 02/10 223535. Database file "C\test.db"
  consists of 9 disk fragments
• I. 02/10 223535. Note The size of 'C\test.db'
  is larger than expected
• I. 02/10 223535. Transaction log
  test.log...
• I. 02/10 223535. Rollback log...
• I. 02/10 223535. Checkpointing...
• I. 02/10 223535. Starting checkpoint of "test"
  (test.db) at Mon Feb 10 2003 2235
• I. 02/10 223535. Finished checkpoint of "test"
  (test.db) at Mon Feb 10 2003 2235
• I. 02/10 223535. Recovery complete
• I. 02/10 223535. Database "test" (test.db)
  started at Mon Feb 10 2003 2235

15
Topics

• Types of Failure
• Protection from System Failure
• Protection from Media Failure
• Validating Databases
• Backups
• Recovery
• Backup Strategies

16
Protection from Media Failure

• Points of persistent data
• Main database file(s) (including dbspaces)
• Transaction log
• Mirror log

\mydata.log
17
Potential Data Loss Scenarios

• Scenario 1
• Main database file(s) is corrupted
• Transaction log is intact
• No Mirror log
• Data Loss
• Incomplete transactions not yet committed

\mydata.log
X
18
Potential Data Loss Scenarios

• Scenario 2
• Transaction log corrupted
• Main database file(s) are intact
• No Mirror log
• Data Loss
• Cached data not yet written to the database at
  the time of the failure
• Any changes since the last checkpoint
• Incomplete transactions not yet committed

\mydata.log
X
19
Potential Data Loss Scenarios

• Scenario 3
• Mirror log corrupted
• Main database file(s) are intact
• Transaction log is intact
• Data Loss
• Incomplete transactions not yet committed

\mydata.log
X
20
Potential Data Loss Scenarios

• Most important strategies for handling media
  failure
• Place the transaction log on a separate drive
  from the database using a different disk
  controller
• Use a transaction log mirror that is also on a
  separate drive with a unique disk controller
• Perform regular backups
• A recent backup of the database and a set of
  valid log(s) (or log mirrors) are critical for
  recovering from a media failure unscathed

21
Topics

• Types of Failure
• Protection from System Failure
• Protection from Media Failure
• Validating Databases
• Backups
• Recovery
• Backup Strategies

22
Validating Databases

• Purpose of Validation
• Validation Tools
• Default and Express Validation
• Importance of Exclusive Access
• With Data Check
• With Index Check
• Checksum Validation

23
Purpose of Validation

• Verify structural integrity of the database and
  transaction log
• Can database pages be read?
• Do index contents properly match table contents?
• Confirm declared entity integrity and referential
  integrity constraints
• Can the transaction log be read?
• Have database pages been modified while on disk?
• Proactive maintenance
• Detect potential problems before the users find
  out the hard way
• Peace of Mind
• Integral part of the Backup and Recovery process
• Need valid files in order to perform an effective
  recovery

24
Validation Tools

• Unload/Reload of the database
• Creates a fresh copy of the database
• Will detect minor inconsistencies that wont be
  detected by other means
• eg Views that reference tables which no longer
  exist
• Will force some data type validation (eg
  date/time values)
• Most suitable as part of an upgrade process
• DBValid Utility
• Command line utility
• Particularly suited to validating a backup copy
  of the database in an offline mode
• Validate Statement
• Provides the same functionality as the command
  line utility
• Can be used within a scheduled Event for regular
  validation of a production database
• Sybase Central
• Provides a GUI interface to the validation tools

25
Default and Express Validation Actions

• By default the validation process (DBValid or
  VALIDATE statement)
• Scans every record in every table and tracks the
  row id of the records
• A row id consists of a page and an offset
  within the page
• Scans every index entry and verifies that the row
  id in the index matches the row id of an existing
  record
• Checks that the total of index entries does not
  exceed the total of records
• In the case of a foreign key which allows NULL
  values, there may be records in the table without
  a corresponding index entry
• Does an ordered traversal of the index to confirm
  structural integrity of the index
• The process does NOT
• Walk the page chains for extended records or
  columns
• Compare the hashed value in the index to the
  actual data in the record

26
Express Validation

• Express validation
• Is more efficient than the historical algorithm
  at checking only the items described above
• Prior to 9.0.1 was less thorough than the Default
  algorithm
• As of 9.0.1 the default validation process is the
  Express validation process

27
Importance of Exclusive Access

• In order to minimize the impact on users of the
  database, the validation processes do not
  exclusively lock tables or indexes
• Contents of a table or index may properly change
  while the validation process is executing
• Can result in spurious errors if a validation is
  run against an active database
• Be aware of this
• Should you receive errors when validating an
  active production database you should
• Re-run the validation, preferrably with exclusive
  access
• Validate your most recent backup
• Any errors reported when validating a database
  with exclusive access are definite

28
WITH DATA CHECK (-fd)

• Default behavior finds the start of each and
  every record in a table
• It does not read all of the columns within each
  record
• WITH DATA CHECK causes the validation process to
  read each column in its entirety
• Follows continued records that are stored across
  multiple database pages
• Follows continued columns (eg LONG VARCHAR, LONG
  BINARY) that are stored across multiple database
  pages
• Confirms that each column starts at the expected
  offset within a page
• Verifies that preceding columns were the expected
  of bytes
• Remember that many data types are stored as
  variable length values (eg CHAR, VARCHAR,
  NUMERIC)
• Does not perform ISDATE, ISNUMERIC or other data
  type validation functions

29
WITH INDEX CHECK (-fi)

• Default validation compares row ids between the
  table records and index entries, but does not
  compare the data values
• WITH INDEX CHECK causes the validation process
  to
• Read the database record
• Hash the column value(s) for the index
• Compare the value to the hashed value stored in
  the index
• Ensures that the record being pointed to by the
  index is the right record

30
Checksum Validation

• New Feature in version 9.0.1
• Used to detect if database pages have been
  changed outside of the database while on disk
• Requires that the database be initialized with
  checksum support (dbinit s )
• Must be run separately from other validation
  processes
• VALIDATE CHECKSUM (-s)
• Reads a database page from disk
• Calculates a checksum based on the current
  contents of the page
• Compares the value to the checksum value that was
  recorded at the time the page was last saved to
  disk

31
Topics

• Types of Failure
• Protection from System Failure
• Protection from Media Failure
• Validating Databases
• Backups
• Recovery
• Backup Strategies

32
Online vs Offline Backups

• Online Backup
• Performed without stopping database engine
• Provides snapshot of consistent database
• Useful for databases with high availability
  requirements
• Can be part of full backup or incremental backup
  strategy
• Offline Backup
• Copy database file(s) to disk or tape directly
• Performed after shutting down database engine
• Useful when database engine can be taken down on
  a regular basis
• Used in addition to an incremental backup strategy

33
Full Backup

• Makes a copy of database and transaction log
  files
• Simplest backup strategy
• Useful for relatively small databases
• Impractical for large databases

34
Incremental Backup

• Uses a repeated cycle of steps
• Full backup of database and transaction log files
• Subsequent backups of transaction log only
• Cycle should be restarted periodically
• Longer cycles increase risk of data loss due to
  possible transaction log backup loss or
  corruption
• Important to store log backups on reliable media
• Useful for large databases

35
Image versus Archive Backups

• Image Backup
• Makes a copy of the database and/or log file
• Typical backup approach
• Archive Backup
• Instead of backing up the database file and
  transaction log to another directory, all
  database files can be backed up into a single
  file that can be written directly to tape
• Only one file can be stored on each tape
• Meant for backing up very large databases
  directly to tape

36
Backup Tools

• System level file copy
• Suitable for offline backups
• Can be used for full or incremental backups
• DBBackup Utility
• Command line utility
• Can be used for online or offline backups
• Works as a client application retrieving each
  database or transaction log page and then writing
  it to disk
• Can be made to use the BACKUP statement by
  specifying the s switch
• BACKUP Statement
• Runs within the database
• More efficient than the DBBackup client
• Can be used in a scheduled EVENT to automate the
  backup process
• Sybase Central
• Provides a GUI interface to the backup
• NOTE There are no 3rd party backup tools that
  currently support backing up a live ASA database

37
Backup Options

• DBFILE ONLY (-d)
• Used to backup the database file only
• WAIT BEFORE START
• Ensures that the backup database file generated
  does not require any recovery
• Only supported through the BACKUP statement
• Allows the backup copy of the database to be
  started up in Read Only mode allowing it to be
  validated offline
• WAIT AFTER END
• Ensures that all transactions are completed
  before the log file is renamed or truncated
• Only supported through the BACKUP statement

38
Transaction Log Validation

• Transaction log is required for up to the second
  recovery
• Transaction log can be validated by translating
  it
• DBTran command line utility
• Sybase Central
• Translation will fail if the log is corrupted

39
Controlling the Transaction Log Size

• Control how fast the Transaction Log grows by
  ensuring that all database tables have primary
  keys
• Updates or deletes on tables without primary keys
  results in the entire row being stored in the
  transaction log
• A UNIQUE NOT NULL index can be used in place of a
  primary key
• The Transaction Log should be periodically
  restarted as part of the back process

40
Transaction Log Backup Options

• Continue to use the same Transaction Log
• Default behaviour
• Simplest backup
• Used when disk space is plentiful
• Delete the original Transaction Log
• TRANSACTION LOG TRUNCATE (-x)
• Used when disk space is limited
• Log file is truncated
• Requires all incremental log files to recover
  from media failure on the database
• Rename the original Transaction Log
• TRANSACTION LOG RENAME (-r)
• Used with replication systems
• Transaction log is renamed to an offline log
• New log is started

41
Backup Considerations for SQL Remote or MobiLink

• Ask the iAnywhere Experts on the Technology
  Boardwalk
• Drop in during exhibit hall hours and have all
  your questions answered by our technical experts!
• Appointments outside of exhibit hall hours are
  also available to speak one-on-one with our
  Senior Engineers. Ask questions or get your
  yearly technical review ask us for details!

42
Topics

• Types of Failure
• Protection from System Failure
• Protection from Media Failure
• Validating Databases
• Backups
• Recovery
• Backup Strategies

43
Recovering From Media Failure

• Repair failed device
• Repair database
• Method depends on whether database or log device
  was lost

44
Media Failure on Database File

• One transaction log
• Log has not been backed up since last full
  database backup
• Multiple transaction logs
• Log has been backed up since last database backup

45
Recovery with One Log

• Make a file system copy of the current
  transaction log
• Restore most recent full backup of database file
• Start database engine with -a switch and
  transaction log name to apply log to database
  backup
• Back up recovered database
• Start database engine with a new transaction log
• Example
• dbsrv9 mydata.db -a mydata.log

46
Recovery with Multiple Logs

• Make a file system copy of the current
  transaction log
• Restore most recent full backup of database file
• Apply logs by starting database engine with -a
  switch and transaction log name for each
  transaction log starting with earliest log
• Back up recovered database
• Start database engine with a new transaction log
• Alternately, rename the last log applied to the
  correct log name and restart database engine

47
Recovery with Multiple Logs

• Example
• copy sales.log d\backup\sales.log
• dbeng9 sales.db -a d\oldlogs\mon.log
• dbeng9 sales.db -a d\oldlogs\tue.log
• dbeng9 sales.db -a d\oldlogs\wed.log
• dbeng9 sales.db -a d\backup\sales.log

48
Media Failure on Transaction Log

• High potential for data loss
• System failure after media failure on transaction
  log causes lost transactions
• Use mirror on separate device

49
Media Failure on Transaction Log

• Back up good database file
• Move or delete transaction log
• Restart good database with -f (no log) switch
• Restores database to most recent checkpoint
• Rolls back any transactions not committed up to
  this checkpoint
• Starts a new transaction log
• Back up recovered database
• Restart database with new transaction log

50
Media Failure on Mirrored Transaction Log

• Make an extra copy of the backup of the database
  file
• Identify which of the two log files is corrupt
• Run the Log Translation utility on both logs
• The intact log will be properly converted to SQL
  while the corrupt log will generate an error
  message during translation
• Copy the valid log over the corrupt file
• Restart the server

51
Topics

• Types of Failure
• Protection from System Failure
• Protection from Media Failure
• Validating Databases
• Backups
• Recovery
• Backup Strategies

52
Designing a Backup Strategy

• Design Physical Database Setup
• Database file(s), Transaction log, Mirror log on
  separate devices?
• Should each device use a separate controller from
  a different manufacturer?

53
Designing a Backup Strategy

• Design Backup and Recovery Procedures
• How often should the database be validated?
• How often should a full backup be performed?
• How often should an incremental backup be
  performed?
• How often should backups be moved off-site?
• How often should the recovery procedure be tested?

54
Backup Strategies

• There will be different levels of paranoia based
  on how you are using ASA
• Unconcerned
• Concerned
• Paranoid
• Job depends on backup

55
Unconcerned Backup Strategy

• Physical Database Setup
• Database File and Log File reside on same
  physical device, probably in the same directory
• Backup Procedure
• No need to do anything
• You could possibly take a full system backup to
  tape every now and then

56
Unconcerned Backup Strategy

• Pros
• Easy setup, no maintenance required
• Protected from system failures
• Cons
• No protection from media failure
• Recovery of database from media failure depends
  on system backup
• Changes since last system backup are lost even if
  system backup exists

57
Concerned Backup Strategy

• Physical Database Setup
• Database file and transaction log reside on
  different physical devices
• Backup Procedure
• Take a full backup every week and rename and
  restart the transaction log
• Either have the backup placed on another device
  (network mapped drive), or spin the backed up
  database and transaction log to alternate media
  (tape, jaz drive, ) after the backup completes
• Test your recovery procedure after having defined
  your procedure

58
Concerned Backup Strategy

• Pros
• Protection from both system and media failure on
  a single device
• No data loss should a media failure occur on a
  single device
• Cons
• You might be overwriting your only good backup,
  so a backup failure may result in you not having
  any backup at all
• Media failure on device with transaction log
  could result in data loss since the last
  checkpoint

59
Paranoid Backup Strategy

• Physical Database Setup
• Database file, transaction log and transaction
  log mirror all reside on separate physical
  devices
• Backup Procedure
• Run dbvalid on database and check for errors
• Take a full backup once a week and an incremental
  backup every day, and rename and restart the
  transaction log
• Copy backed up database file and transaction logs
  to another machine or alternate media once backup
  completes
• Test your recovery procedure once a month

60
Paranoid Backup Strategy

• Pros
• Protection from system and media failures
• Running dbvalid will help you from backing up a
  corrupt database
• Placing backed up files on alternate media will
  give you a point to recover to should the entire
  machine be destroyed
• Cons
• Running dbvalid on large database is time
  consuming
• Problem with disk controller could destroy all
  hard drives
• Lots of backed up files to manage
• Database may become corrupt during backup

61
Job Security Backup Strategy

• Physical Database Setup
• Database file, transaction log and transaction
  log mirror all reside on separate physical
  devices
• Each physical device is controlled by a separate
  disk controller from a different manufacturer

62
Job Security Backup Strategy

• Backup Procedure
• Run dbvalid on database and check for errors
• Take a full backup once a week and an incremental
  backup every day, and rename and restart the
  transaction log
• Copy backed up database file and transaction logs
  to alternate media once backup completes
• Move alternate media off-site as soon as possible
• Run dbvalid on the backed up database with the
  engine in read only mode to ensure that backed up
  image is valid
• Test your recovery procedure at least once a week
• If running dbremote, use the -u switch
• Also consider running dbbackup -l (live backup)
  to keep an up-to-date version of your log file on
  a separate machine
• Document your backup and recovery procedures so
  they can continue in case you are out of the
  office for some reason

63
Job Security Backup Strategy

• Pros
• Protection from system and media failures
• Running dbvalid twice ensures that backed up
  image is also valid
• Taking alternate media off-site protects you from
  a site disaster
• Running dbremote with the -u switch ensures that
  in the case of a site disaster or system and
  media failure, no remote users will be affected
• A bug in a disk controller can not destroy all
  your hard drives
• You get to keep your job

64
Job Security Backup Strategy

• Cons
• In the case of a site disaster or system and
  media failure, there will still be data loss
  since the last backup
• Very time consuming
• Lots of backed up files need to be managed

65
Summary

• System failure occurs when the computer or
  operating system goes down while there are
  partially completed transactions
• Media failure occurs when a system or component
  failure causes the destruction of the database
  file(s)
• Integrity of any ASA database is protected by the
  transaction log, checkpoint log and rollback log
• In an ASA system the points of persistent data
  are the main database file(s) (including
  dbspaces), the transaction log and the mirror log

66
Summary

• In order to protect data from media failure, back
  up the database and log files regularly, store
  the transaction log on a separate device, and use
  a transaction log mirror
• Database validation is important since corruption
  may not be apparent until applications try to
  access the affected part of the database
• A full backup is the simplest backup strategy and
  is suitable for small databases. An incremental
  backup is more efficient and suited for larger
  databases

67
iAnywhere at TechWave2004

• Ask the iAnywhere Experts on the Technology
  Boardwalk
• Drop in during exhibit hall hours and have all
  your questions answered by our technical experts!
• Appointments outside of exhibit hall hours are
  also available to speak one-on-one with our
  Senior Engineers. Ask questions or get your
  yearly technical review ask us for details!
• TechWave ToGo Channel
• TechWave To Go, an AvantGo channel providing
  up-to-date information about TechWave classes,
  events, maps and more also, keep up to date
  with the TechWave Newsletter now available via
  your handheld device!
• Mobile and Wireless Email using Pylon Anywhere
• iAnywhere has provided access to your corporate
  email at the show using Pylon Anywhere. You can
  keep up-to-date with your latest email, calendar,
  cotnacts, and tasks from your PDA or any
  Web-client! Visit the iAnywhere pedestal in the
  exhibit hall or the Ask the Experts room for
  details on how you can evaluate Pylon Anywhere
  yourself!

68
iAnywhere at TechWave2004

• Wi-Fi Hotspots brought to you by Intel
  iAnywhere Solutions
• You can enjoy wireless internet access via a
  Wi-Fi hotspot provided by Intel. Using either a
  laptop or PDA that is Wi-Fi 802.11b
  wirelessly-enabled, visitors can access personal
  email, the internet ,and TechWave ToGo
• Developer Community
• A one-stop source for technical information!
• Access to newsgroups,new betas and code samples
• Monthly technical newsletters
• Technical whitepapers,tips and online product
  documentation
• Current webcast,class,conference and seminar
  listings
• Excellent resources for commonly asked questions
• All available express bug fixes and patches
• Network with thousands of industry experts
• http//www.ianywhere.com/developer/