Backup and Recovery of SQL Anywhere, Tips and Techniques Joshua Savill
Backup and Recovery of SQL Anywhere, Tips and Techniques Joshua Savill Product Support Analyst, iAnywhere Solutions Thursday, August 10, 2006 9:30 am - 10:30 am Presenter Joshua Savill Product Support Analyst – Direct Support Team iAnywhere Solutions [email protected] 2 Objectives for Presentation Learn the concepts of how the SQL Anywhere database functions and the components involved in a working system Understanding SQL Anywhere tools used to create database backups Be able to develop an effective backup and recovery strategy suitable to your environment 3 Agenda for Presentation Types of Failure Protection From System Failure Protection From Media Failure Database Validation Backups Database Recovery Designing a Recovery Strategy New Backup Features 4 Agenda for Presentation Types of Failure • System Failure • Media Failure Protection From System Failure Protection From Media Failure Database Validation Backups Database Recovery Designing a Recovery Strategy New Backup Features 5 Types of Failure System Failure Computer or operating system goes down or into an unexpected state while there are partially completed transactions in the database • Computer turned off or rebooted without shutting down database server • Operating system crash • Power failure Database is unavailable to application • No intervention required • Database recovery automatically when restarted • Depending on database size, could take a while to recover 6 Types of Failure Media Failure System or component failure that causes destruction to the database file, transaction log or mirror file • Failure in the file system • Hard drive failure • Files becomes corrupted – Database file or transaction log become usable – Bit flips in database file 7 Agenda for Presentation Types of Failure Protection From System Failure • SQL Anywhere database files • Integrity of the SQL Anywhere database Protection From Media Failure Database Validation Backups Database Recovery Designing a Recovery Strategy New Backup Features 8 Protection From System Failure SQL Anywhere Database Consists of Multiple Files Database file • Holds database tables, indexes, information about data distribution • File suffix .db Transaction log • Contains a record of all operations performed on the database • Files suffix .log and normally same name as database file Mirror log • Mirror copy of the transaction log • Files suffix .mlg and normally same name as transaction log 9 Protection From System Failure Temporary file • Hold temporary information used by the database server for query processing • Files suffix .tmp, and typically named asat0000.tmp 10 Integrity of the SQL Anywhere Database Protected by 3 logs Transaction log • Stores a record of all changes to the database in the order they occur Checkpoint log • List of dirty pages stored in database server cache Rollback log • Contains “undo” operations required for any incomplete transactions 11 Transaction Log Transaction log • Records inserts, deletes, updates, commits, rollbacks, and database schema changes • Key component of backup and recovery • Should be created on a separate physical media with a separate controller from the database file – Provides better recoverability in case of media failure 12 Checkpoint Log Contains the before image of all physical data page changes since last checkpoint ( dirty pages) • Reads a given page into database cache • Copy of the original page is stored in the checkpoint log on disk • Changes are then made to the cached version of the given page Located at the end of the database file • Checkpoint log pages are added to the database file as necessary • Checkpoint log pages are freed when a checkpoint is issued Checkpoint issued • Flushes all pages in cache ( dirty pages ) to the database file • Remove all entries from the checkpoint log 13 When the Database Checkpoints CHECKPOINT statement is issued Database engine shutdown Checkpoint urgency when time since last checkpoint > CHECKPOINT_TIME ( default time is 60 minutes ) Recovery urgency when estimated time for recovery > RECOVERY_TIME ( default time is 2 minutes ) Transaction committed on a database that is configured without a transaction log Writing of dirty pages to disk is carried out by the idle I/O task 14 When the Database Checkpoints 15 Rollback Log Contains all operations for the purpose of undoing changes if a transaction is rolled back ( undo log ) • Explicit ROLLBACK statement issued • Automatic rollback of uncommitted transactions during database recovery Rollback log is deleted after a transaction is committed Separate rollback log for each connection Stored in cache • Open rollback logs that exist at time of checkpoint are written to the database file 16 System Recovery After a System Failure Occurs Database server will take the following steps to recover • Revert to the most recent checkpoint using the checkpoint log • Apply any transactions since the last checkpoint using the transaction log • Rollback any uncommitted transactions using the rollback log 17 System Recovery Database recovery in progress Last checkpoint at Wed Jun 21 2006 10:25 Checkpoint log... Performance warning: Database file "C:\Program Files\Sybase\SQL Anywhere 10\demo.db" consists of 3 disk fragments Transaction log: demo.log... Rollback log... Checkpointing... Starting checkpoint of "demo" (demo.db) at Wed Jun 21 2006 10:25 Finished checkpoint of "demo" (demo.db) at Wed Jun 21 2006 10:25 Recovery complete Database "demo" (demo.db) started at Wed Jun 21 2006 10:25 Database server started at Wed Jun 21 2006 10:25 Trying to start SharedMemory link ... SharedMemory link started successfully Trying to start TDS (TCPIP) link ... TDS (TCPIP) link started successfully Now accepting requests 18 Agenda for Presentation Types of Failure Protection From System Failure Protection From Media Failure • Storage of persistent data • Potential data loss scenarios Database Validation Backups Database Recovery Designing a Recovery Strategy New Backup Features 19 Storage of Persistent Data Recommend database file, transaction log, and mirror file should be stored on a separate media • Separate physical controller for each media • Failure of one media will not have an effect on use of other media • Increase in performance using multiple storage devices Media should be local to the physical machine or needs to be configured correctly to remotely store the database files ( http://www.ianywhere.com/developer/technotes/asa_db_file_stored_remotely.html ) Recommend database files not be stored on network drives • Poor performance reading and writing pages • Files may be corrupted as a result of the OS writing across the network 20 Potential Data Loss Scenarios Media Failure – Scenario # 1 Database file is corrupted • Database server goes into an assertion each time it is started Transaction log is intact No mirror log Data Loss Incomplete transactions not committed 21 Potential Data Loss Scenarios Media Failure – Scenario # 2 Database file is intact Transaction log is corrupted • Database server goes into an assertion each time it is started • Cannot translate transaction log No mirror log Data Loss Data in cache not yet written to database file at time of failure Committed data changes since last checkpoint Incomplete transactions not committed 22 Potential Data Loss Scenarios Media Failure – Scenario # 3 Database file is intact Transaction log is intact Mirror log is corrupted • Database server goes into an assertion each time it is started • Cannot translate mirror log Data Loss Incomplete transactions not committed 23 Potential Data Loss Scenarios Important Strategies for Handling Media Failure 3 files, 3 different manufactured controllers, 3 different manufactured media • Resistance against bugs in controller and media Perform regular backups Recent backup of the database file and a set of valid transaction logs ( or log mirrors ) are critical for recovering from a media failure unscathed 24 Agenda for Presentation Types of Failure Protection From System Failure Protection From Media Failure Database Validation • • • • Purpose of validation The Validation utility VALIDATE statement Transaction log validation Backups Database Recovery Designing a Recovery Strategy New Backup Features 25 Purpose of Database Validation Verify structural integrity of database file and transaction log • • • • Determines if database file is corrupted in any manner Ensures entity integrity and referential integrity constraints Checksums enabled will check the validity of disk pages Confirms database file and transaction log integrity Proactive maintenance of database • Identify problems before the system hits an exception state in production Necessary for a backup and recovery process • Valid database file and transaction logs are necessary for recovery 26 How to Validate the Database Rebuilding the database The Validation utility • Sybase Central using Validate Database wizard VALIDATE statement 27 Rebuilding the Database ( Unload/Reload ) End result is a new fresh copy of the database Removes any minor inconsistencies in the database not detectable by other means • Schema changes, such as a view that references a table that no longer exist All data types will be evaluated and validated Usually done as a part of an upgrade process 28 The Validation Utility Validates indexes, keys, tables, materialized views Validates checksums for the database Scans a database object then validates relations to the object Should only be performed on a database with no connections, otherwise spurious errors can be reported indicating false database corruption Default behaviour is a full database validation Command line execution using dbvalid.exe Sybase Central using Validate Database wizard 29 Validation Utility Examples dbvalid -c "ENG=sample_server;DBN=demo;UID=DBA;PWD=sql“ • Validates the demo database on the sample server dbvalid -i DBA.Customers.IX_customer_name -c "ENG=sample_server;DBN=demo;UID=DBA;PWD=sql“ • Validates the IX_customer_name index on the Customers table 30 VALIDATE Statement Validates indexes, keys, tables, and materialized views in the current database Validates checksums for current database 31 Transaction Log Validation Purpose of Transaction Log Validation Validates the integrity of transaction log Validation Tool The Log Translation Utility • • • • Command line using dbtran.exe Sybase Central using Translate Log File wizard Translation will fail if there is corruption in the transaction log Specifics regarding Log Translation Utility can be answered in the Technical Support Lounge 32 Agenda for Presentation Types of Failure Protection From System Failure Protection From Media Failure Database Validation Backups • • • • Online vs. Offline Full and incremental backups Backup tools and utilities Considerations during backup process Database Recovery Designing a Recovery Strategy New Backup Features 33 Online vs. Offline Backup Online Backup Performed on a running database without stopping the database engine Snapshot of consistent data at time backup is completed Used on databases that require high availability Can be performed as part of an incremental or full backup strategy Offline Backup Performed after shutting down database engine Used on databases that can be taken down on a regular basis Can be performed as part of an incremental or full backup strategy 34 Full Backup Makes a full copy of database file and transaction log files Simplest type of backup strategy Used on relatively small databases Time consuming for large databases 35 Incremental Backup Repeated cycle of steps and files to backup • Regular full backup of database file and transaction log files • Followed by a cycle of backups of only the transaction log – Cycle needs to do a full backup to restart Longer the cycle, more risk of transaction log loss or corruption • Backup needs to be stored on reliable media Recommended for use with large databases • Incremental backup of log cycle does not require as much time as is required for a full backup 36 Image vs. Archive Backup Image Backup Creates a file copy of the database file and transaction log file • Backups up to another directory or machine • Typical backup approach Archive Backup Creates a backup copy of the database file and transaction log in a single file image • File image is written directly to tape • Only one file image can be stored on each tape • Meant to backup large databases directly to tape 37 SQL Anywhere Backup Tools NOTE: 3rd party backup tools are most likely not capable of backing up a live SQL Anywhere database A live SQL Anywhere database has active transactions and connections SQL Anywhere backup tools are capable of handling database transactions during the running of a backup 3rd party backup vendors potentially do not have the proprietary understanding of how the SQL Anywhere database file works or use the correct tools when doing a backup A backup of a database using 3rd party backup tools will most likely not provide a recoverability option ( most likely the backup will be corrupted in some manner ) 38 System Level File Copy Can only be done on an offline database Suitable for both full and incremental backup strategies 39 The Backup Utility Client side backup Used for both online and offline backups Command line using dbbackup.exe Using Sybase Central to backup database • Create Backup Image Wizard • Backup Database Wizard 40 Backup Utility Examples dbbackup -c "ENG=sample_server;DBN=demo;UID=DBA;PWD=sql" C:\SQLAnybackup • Command will backup the sample database running on the server sample_server to the directory SQLAnybackup dbbackup -t -c "ENG=sample_server;DBN=demo;UID=DBA;PWD=sql" C:\SQLAnybackup • Command will delete and restart a new transaction log after backing up the original to the directory SQLAnybackup 41 The BACKUP Statement Server side backup executed within the database • More efficient as a result of running within the database Suitable for both full and incremental backup strategies Ability to create both image and archive backups Can be called from within a scheduled EVENT to automate the backup process 42 Considerations for Replication or Synchronization For information on considerations for replicating and synchronizing environments come to the Technical Support Lounge 43 Agenda for Presentation Types of Failure Protection From System Failure Protection From Media Failure Database Validation Backups Database Recovery • Database corruption • Transaction log corruption Designing a Recovery Strategy New Backup Features 44 Recovery Situations Media Failure Need to repair the failed media Repair the Database Determine what needs to be repaired within the system 45 Database Corruption or Loss Make a file system copy of the current transaction log • Backup to revert to in case of unexpected situation during recovery Restore most recent full backup of database file and transaction log Start database engine with -a option and transaction log name to apply log to database backup • dbsrv10 dbfile.db -a dblog.log • Make sure to start the database on the same engine it was backed up on Backup recovered database and transaction log Start the database engine with a new transaction log 46 Recovery with Single Transaction Log Make a file system copy of the current transaction log Restore most recent full backup of database file and transaction log Apply transaction log to restored database • dbsrv10 dbfile.db -a dblog.db Backup recovered database and transaction log Start the database engine with a new transaction log 47 Recovery with Multiple Transaction Logs Make a file system copy of the current transaction log Restore most recent full backup of database file and transaction log Apply all backed up transaction log in sequential order to the restored database • dbsrv9 dbfile.db -a c:\backup\dblog_Mon.db • dbsrv9 dbfile.db -a c:\backup\dblog_Tues.db • dbsrv9 dbfile.db -a dblog.db Backup recovered database and transaction log Start the database engine with a new transaction log 48 Transaction Log Corruption or Loss High potential for data loss Make a file system copy of the database file and transaction log Restart the database engine using the -f switch • Restores database to most recent checkpoint • Rollback of any transactions not committed since last checkpoint • Starts a new transaction log Backup recovered database and transaction log Start database engine with a new transaction log 49 Transaction Log Recovery with Mirror Log Make a file system copy of the database file, transaction log and mirror log Determine whether the transaction log or mirror log is corrupted or lost • Log Translation Utility or Translate Log File wizard Copy valid log over the corrupt or lost log Restart the database server 50 Agenda for Presentation Types of Failure Protection From System Failure Protection From Media Failure Database Validation Backups Database Recovery Designing a Recovery Strategy • Physical database design and setup • Backup and Recovery process considerations • Levels of backup paranoia New Backup Features 51 Physical Database Design and Setup Location of database file, transaction log and mirror log Hardware and software in the SQL Anywhere environment Components involved in the SQL Anywhere environment • SQL Anywhere database • MobiLink synchronization • SQL Remote replication 52 Considerations 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? 53 Designing a Backup Strategy Different levels of paranoia based on how you use SQL Anywhere Unconcerned • Database file and transaction log ( if using one ) resides on the same media, possible in the same directory • Data loss is not important • No need to make backup; might do a full system backup every now and then • Probably would not be in this presentation if you were unconcerned 54 Designing a Backup Strategy • Pros – Easy to setup, little to no maintenance required – Protected from complete system failure if full system is backed up every now and then • Cons – No protection from media failure – Recovery of database is dependent on the last system backup ( if it was taken properly ) – Data loss from the last system backup to the point when the system failed 55 Designing a Backup Strategy Concerned • Database file and transaction log reside on different physical media • Full database backup every week, then rename and restart the transaction log • Backup is placed on another device ( mapped network drive ), or moved to alternate media ( tape, jaz drive, …) after the backup completes • Tested recovery strategy to ensure it is sufficient 56 Designing a Backup Strategy • Pros – Protection from system failure and single media failure – No data loss should occur as a result of single media failure • Cons – Possibly could be writing over previous backup, so backup failure might result in not having a backup – Media failure where transaction log is stored could result in data loss since last checkpoint – Complete media failure of both devices will result in data loss from the time of last backup 57 Designing a Backup Strategy Paranoid • Database file, transaction log, and mirror log all reside on different physical media • Run dbvalid or VALIDATE statement on database during times of no connections • Full backup of database once a week, incremental backup every day, and then rename and restart the transaction log • Copy backed up database file and transaction logs to another machine or alternate media once backup completes • Recovery strategy is tested once a month 58 Designing a Backup Strategy • Pros – Protection from system failure and media failure – Using dbvalid or VALIDATE statement prevents backing up a corrupt database – Moving backed up file to an alternative media will provide recoverability if the machine is destroyed • Cons – – – – Validating a large database is time consuming Issues or bugs in the disk controller could destroy all media Requires management of backup up files Database may become corruption during the backup process 59 Designing a Backup Strategy Company is Dependent on Recoverability • Database file, transaction log, and mirror log all reside on separate physical media • Each physical media is controlled by a separate controller from a different manufacturer • Each physical media may also be from a different manufacturer • Run dbvalid or VALIDATE statement on database during times of no connections • Full backup of database once a week, incremental backup every day, and then rename and restart the transaction log • Copy backed up database file and transaction logs to another machine or alternate media once backup completes 60 Designing a Backup Strategy • Alternative media is moved off site as quickly as possible • Validation is run on the backed up database in read only mode ( or on a secondary copy ) to ensure the backup is valid • Recovery strategy is tested once a week • In the case of running dbremote, the -u option is used • Consideration running dbbackup -l ( live backup ) to keep an up-todate version of the log file on a separate physical media • Backup and recovery strategy is well documented so it can be run in case you are out of the office for some reason 61 Designing a Backup Strategy • Pros – – – – Protection from system failure and media failure Running validation on backup ensures the validity Moving alternate media off-site protects the data 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 • Cons – Time consuming – Resources costs 62 Agenda for Presentation Types of Failure Protection From System Failure Protection From Media Failure Database Validation Backups Database Recovery Designing a Recovery Strategy New Backup Features 63 New Features Additional functionality added to the Validation Utility and VALIDATE DATABASE statement Additional functionality added to the Backup Utility and BACKUP statement BACKUP authority • Assigns BACKUP authority to a user to perform backups, instead of granting the user DBA authority • GRANT BACKUP TO userid VALIDATE authority • Assigns VALIADATE authority to a user to perform validations, instead of granting the user DBA authority • GRANT VALIDATE TO userid 64 New Features Tracking information on the last backup • LAST_BACKUP column added to the ISYSHISTORY system table to store information regarding the last backup – Includes date, type of backup, and file names – Version of database server that performed the backup Checksums calculated automatically for critical database pages • Database server records checksums for critical database pages regardless of whether checksums are enabled Applying multiple transaction logs at startup recovery • New -ad, -ar, and -as recovery options give the ability to apply multiple transaction logs during startup 65 New Features Support for parallel database backups ( server-side images ) • Advantage of physical I/O to perform read and write information in parallel, instead of sequential – Increased performance ( could be at least double the speed with certain hardware configurations ) 66 SQL Anywhere 10 Documentation Suggested Documentation: SQL Anywhere® Server - Database Administration Backup and Data Recovery SQL Anywhere® Server - Database Administration Database Administration Utilities The Backup utility SQL Anywhere® Server - Database Administration Database Administration Utilities The Validation utility 67 SQL Anywhere 10 Documentation SQL Anywhere® Server - Database Administration Database Administration Utilities The Log Translation utility SQL Anywhere® Server - SQL Reference SQL Statements BACKUP Statement SQL Anywhere® Server - SQL Reference SQL Statements VALIDATE Statement 68 Questions Questions? 69 iAnywhere at TechWave 2006 Tech Support at TechWave 2006 • Meet with technical experts from Sybase iAnywhere and TeamSybase • Bring your technical questions and get answers on the spot! • Located off the Exhibit Hall on the fourth floor, Palace Ballroom Foyer Ask the iAnywhere Experts • Drop in during support hours to have your questions answered by experts! • Appointments are available to speak one-on-one with Senior Engineers • Located across from the Tech Support area TechWave-To-Go AvantGo Channel on your handheld device • Download the TechWave AvantGo channel for up-to-date details on sessions, events, maps and more • www.ianywhere.com/techwavetogo • Visit the AvantGo Kiosk on the 3rd floor 70 iAnywhere at TechWave 2006 Reference Program • Share your vision and innovation with your peers • Come by the Information Desk at the Sybase booth to complete a survey form -- all submissions will receive a gift! iAnywhere Developer Community A one-stop source for technical information! • Access to newsgroups, new betas and code samples • Technical whitepapers, tips and online product documentation • Excellent resources for commonly asked questions • All available express bug fixes and patches • Network with thousands of industry experts http://www.ianywhere.com/developer 71
© Copyright 2024