SQL-BackTrack for Sybase
Download

SQL-BackTrack for Sybase

Advertisement
Download Presentation
Comments
Leo
From:
|  
(3850) |   (0) |   (0)
Views: 58 | Added: 22-05-2013
Rate Presentation: 0 0
Description:
Agenda. ?????????????????????????. ????????. Severity of Database Downtime. Planned. Unplanned. Catastrophic. Latency of Database Recovery. No Downtime. High Availability. Continuous Availability. Disaster Recovery. Online Maintenance. Offline Maintenance. High Availability Clusters. Switching and Warm Standby Replication.
SQL-BackTrack for Sybase

An Image/Link below is provided (as is) to

Download Policy: Content on the Website is provided to you AS IS for your information and personal use only and may not be sold or licensed nor shared on other sites. SlideServe reserves the right to change this policy at anytime. While downloading, If for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.











- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -




1. SQL-BackTrack for Sybase ??? System Consultant Manager ??? System Consultant

2. Agenda ??????? ???? ?????? ???? ????

3. ???????? Severity of Database Downtime Planned Unplanned Catastrophic Latency of Database Recovery No Downtime High Availability Continuous Availability Disaster Recovery Online Maintenance Offline Maintenance High Availability Clusters Switching and Warm Standby Replication Cold Standby Here is how Sybase provides solutions that prevent downtime for your organization. As you will note, there is a Sybase solution to answer every high availability need.Here is how Sybase provides solutions that prevent downtime for your organization. As you will note, there is a Sybase solution to answer every high availability need.

4. 80% of all unplanned downtime is caused by software or human error* 70% of recovery is "think time"! *Source: Gartner, ?Aftermath: Disaster Recovery?, Vic Wheatman, September 21, 2001 ???????

5. SQL-BackTrack automates the entire process! ?????? The recovery process is more then just restoring files to disk from tape. The recovery process is the total time from the time it takes to realize something is wrong, until the database is backup up and running in a stable state. Traditional Recoveries includes these 6 steps: Analysis ? determine what is needed to recover the database Source ? determining what files are needed to recovery and where they are. Are they on tape, if so which tapes Preparation ? does the database require special preparation to do the recovery. If so, what are the commands to issue to the database? Restore ? this is the step that many vendors use in benchmarks. Of course this is the easiest step, it is simply the copy of files from tape to disk. Beware of benchmarks that just show the speed of this step and tout it as a recovery. Recovery ? A database must go through a sometimes complex set of steps to do a total recovery. Logs may need to be applied. If so, the order and the commands issued to recovery them are important. Post-op ? A database may need some clean up or post recovery operations like backing up the database immediately so that future recoveries are not jeopardized. All of these steps are important and when done manually are prone to error. A human error can jeopardize the entire operation and can force the recovery to start over from the very beginning losing valuable time during crititcal database down situation. SQL-BackTrack automates these steps, so that human error is minimized or eliminated. This ensures that recoveries are correctly and therefore in the minimum amount of time each and every time.The recovery process is more then just restoring files to disk from tape. The recovery process is the total time from the time it takes to realize something is wrong, until the database is backup up and running in a stable state. Traditional Recoveries includes these 6 steps: Analysis ? determine what is needed to recover the database Source ? determining what files are needed to recovery and where they are. Are they on tape, if so which tapes Preparation ? does the database require special preparation to do the recovery. If so, what are the commands to issue to the database? Restore ? this is the step that many vendors use in benchmarks. Of course this is the easiest step, it is simply the copy of files from tape to disk. Beware of benchmarks that just show the speed of this step and tout it as a recovery. Recovery ? A database must go through a sometimes complex set of steps to do a total recovery. Logs may need to be applied. If so, the order and the commands issued to recovery them are important. Post-op ? A database may need some clean up or post recovery operations like backing up the database immediately so that future recoveries are not jeopardized. All of these steps are important and when done manually are prone to error. A human error can jeopardize the entire operation and can force the recovery to start over from the very beginning losing valuable time during crititcal database down situation. SQL-BackTrack automates these steps, so that human error is minimized or eliminated. This ensures that recoveries are correctly and therefore in the minimum amount of time each and every time.

6. Why SQL-BackTrack? An example of a recovery of a Sybase database using the native utility Determine which database to recover Determine where backups are located If the backups are striped locate all the stripes Order the backups in the required sequence to be applied. Determine the recovery type: physical, transaction log , is there a specific point in time for the recovery If it is a point in time recovery determine the transaction log backups to be applied. Start an isql session Issue load database Issue load transaction for all the transaction log backups If the recovery is PIT issue load transaction with until_time Issue online database or online for standby access Determine if the database and application are ready for production use. Scenario 1: Recovery of a database from a backup (Native utility) Determine which database to recover Determine where backups are located If the backups are striped locate all the stripes Order the backups in the required sequence to be applied. Determine the recovery type: physical, transaction log , is there a specific point in time for the recovery If it is a point in time recovery determine the transaction log backups to be applied. Start an isql session Issue load database Issue load transaction for all the transaction log backups If the recovery is PIT issue load transaction with until_time Issue online database or online for standby access Determine if the database and application are ready for production use. Steps Required for SQL BackTrack: 1. Determine which database to recover 2. Execute dtsrecover with the control file for the database giving the necessary options for PIT and database status after recovery if any or none. ********************************************************************* Scenario 2: Recreate a production database on a different server (Native utility) Determine which database to recover Determine where backups are located If the backups are striped locate all the stripes Order the backups in the required sequence to be applied. Determine the recovery type: physical, transaction log , is there a specific point in time for the recovery If it is a point in time recovery determine the transaction log backups to be applied. Start an isql session to connect to the new server where new database has to be recovered Determine the size of the original database. Create the new database for load. Find out the database options for the original database Issue sp_dboption for each and every database option Issue load database Issue load transaction for all the transaction log backups If the recovery is PIT issue load transaction with until_time Issue online database or online for standby access Determine if the database and application are ready for production use. Steps Required for SQL BackTrack: 1. Determine which database to recover 2. Execute dtsrecover with the control file for the database giving the necessary options for PIT, database status after recovery and the new database name to be created . Scenario 1: Recovery of a database from a backup (Native utility) Determine which database to recover Determine where backups are located If the backups are striped locate all the stripes Order the backups in the required sequence to be applied. Determine the recovery type: physical, transaction log , is there a specific point in time for the recovery If it is a point in time recovery determine the transaction log backups to be applied. Start an isql session Issue load database Issue load transaction for all the transaction log backups If the recovery is PIT issue load transaction with until_time Issue online database or online for standby access Determine if the database and application are ready for production use. Steps Required for SQL BackTrack: 1. Determine which database to recover 2. Execute dtsrecover with the control file for the database giving the necessary options for PIT and database status after recovery if any or none. ********************************************************************* Scenario 2: Recreate a production database on a different server (Native utility) Determine which database to recover Determine where backups are located If the backups are striped locate all the stripes Order the backups in the required sequence to be applied. Determine the recovery type: physical, transaction log , is there a specific point in time for the recovery If it is a point in time recovery determine the transaction log backups to be applied. Start an isql session to connect to the new server where new database has to be recovered Determine the size of the original database. Create the new database for load. Find out the database options for the original database Issue sp_dboption for each and every database option Issue load database Issue load transaction for all the transaction log backups If the recovery is PIT issue load transaction with until_time Issue online database or online for standby access Determine if the database and application are ready for production use. Steps Required for SQL BackTrack: 1. Determine which database to recover 2. Execute dtsrecover with the control file for the database giving the necessary options for PIT, database status after recovery and the new database name to be created .

7. Why SQL-BackTrack? An example of a recovery of a Sybase database using the native utility Determine which database to recover Determine where backups are located If the backups are striped locate all the stripes Order the backups in the required sequence to be applied. Determine the recovery type: physical, transaction log , is there a specific point in time for the recovery If it is a point in time recovery determine the transaction log backups to be applied. Start an isql session Issue load database Issue load transaction for all the transaction log backups If the recovery is PIT issue load transaction with until_time Issue online database or online for standby access Determine if the database and application are ready for production use. SQL-BackTrack reduces this effort to 2 STEPS!! Scenario 1: Recovery of a database from a backup (Native utility) Determine which database to recover Determine where backups are located If the backups are striped locate all the stripes Order the backups in the required sequence to be applied. Determine the recovery type: physical, transaction log , is there a specific point in time for the recovery If it is a point in time recovery determine the transaction log backups to be applied. Start an isql session Issue load database Issue load transaction for all the transaction log backups If the recovery is PIT issue load transaction with until_time Issue online database or online for standby access Determine if the database and application are ready for production use. Steps Required for SQL BackTrack: 1. Determine which database to recover 2. Execute dtsrecover with the control file for the database giving the necessary options for PIT and database status after recovery if any or none. ********************************************************************* Scenario 2: Recreate a production database on a different server (Native utility) Determine which database to recover Determine where backups are located If the backups are striped locate all the stripes Order the backups in the required sequence to be applied. Determine the recovery type: physical, transaction log , is there a specific point in time for the recovery If it is a point in time recovery determine the transaction log backups to be applied. Start an isql session to connect to the new server where new database has to be recovered Determine the size of the original database. Create the new database for load. Find out the database options for the original database Issue sp_dboption for each and every database option Issue load database Issue load transaction for all the transaction log backups If the recovery is PIT issue load transaction with until_time Issue online database or online for standby access Determine if the database and application are ready for production use. Steps Required for SQL BackTrack: 1. Determine which database to recover 2. Execute dtsrecover with the control file for the database giving the necessary options for PIT, database status after recovery and the new database name to be created . Scenario 1: Recovery of a database from a backup (Native utility) Determine which database to recover Determine where backups are located If the backups are striped locate all the stripes Order the backups in the required sequence to be applied. Determine the recovery type: physical, transaction log , is there a specific point in time for the recovery If it is a point in time recovery determine the transaction log backups to be applied. Start an isql session Issue load database Issue load transaction for all the transaction log backups If the recovery is PIT issue load transaction with until_time Issue online database or online for standby access Determine if the database and application are ready for production use. Steps Required for SQL BackTrack: 1. Determine which database to recover 2. Execute dtsrecover with the control file for the database giving the necessary options for PIT and database status after recovery if any or none. ********************************************************************* Scenario 2: Recreate a production database on a different server (Native utility) Determine which database to recover Determine where backups are located If the backups are striped locate all the stripes Order the backups in the required sequence to be applied. Determine the recovery type: physical, transaction log , is there a specific point in time for the recovery If it is a point in time recovery determine the transaction log backups to be applied. Start an isql session to connect to the new server where new database has to be recovered Determine the size of the original database. Create the new database for load. Find out the database options for the original database Issue sp_dboption for each and every database option Issue load database Issue load transaction for all the transaction log backups If the recovery is PIT issue load transaction with until_time Issue online database or online for standby access Determine if the database and application are ready for production use. Steps Required for SQL BackTrack: 1. Determine which database to recover 2. Execute dtsrecover with the control file for the database giving the necessary options for PIT, database status after recovery and the new database name to be created .

8. Agenda ??????? ???? ?????? ???? ????

9. ???? The SQL-BackTrack programs The SQL-BackTrack programs are a collection of executables that perform the backup and recovery tasks as well as some administration tasks. The main SQL-BackTrack program is sbacktrack. Sbacktrack calls an interactive menu interface that allows the user to access other SQL-BackTrack programs in an easy to use manner. Key programs that are part of SQL-BackTrack include dtsbackup, dtsrecover, dtsdump, dtsload, dtsanalyze and dtscheck. The SQL-BackTrack programs The SQL-BackTrack programs are a collection of executables that perform the backup and recovery tasks as well as some administration tasks. The main SQL-BackTrack program is sbacktrack. Sbacktrack calls an interactive menu interface that allows the user to access other SQL-BackTrack programs in an easy to use manner. Key programs that are part of SQL-BackTrack include dtsbackup, dtsrecover, dtsdump, dtsload, dtsanalyze and dtscheck.

10. ????

11. ASE SQL-BackTrack BT Module Server Program Media/ Client Program Tape Devices Juke Boxes IBM Tivoli Storage Manager VERITAS NetBackup DataCenter Legato NetWorker OBSI???? The tape and disk modules are shipped with SQL-BackTrack. The following modules are sold separately:ADSM, Networker, and VERITAS. There are other modules developed by partners eg. SLDT OBSI by Spectra Logic and Netbackup OBSI by Openvision. BMC does not test the modules written by our partners. These Modules were called OBSIs (Open Backup Stream Interface) but BMC is moving away from the name OBSI and using SQL-BT Modules.The tape and disk modules are shipped with SQL-BackTrack. The following modules are sold separately:ADSM, Networker, and VERITAS. There are other modules developed by partners eg. SLDT OBSI by Spectra Logic and Netbackup OBSI by Openvision. BMC does not test the modules written by our partners. These Modules were called OBSIs (Open Backup Stream Interface) but BMC is moving away from the name OBSI and using SQL-BT Modules.

12. OBSI????

13. Agenda ??????? ???? ?????? ???? ????

14. Common SQL-BackTrack Features Dry run backup and recovery Table recovery from physical backup Unattended on-line and off-line backup Incremental backups Compression and encryption Storage management integration Guided recovery

15. Unique SQL-BackTrack Features for Sybase Master database recovery Needs to be rebuilt, if lost or damaged Generates Master Database Recovery template script to recover Remote administration Warm stand-by server support Table level recovery includes recovery of dependent objects (triggers, etc.) SBT will automatically identify dependent objects such as triggers when performing a table-level recovery and recover these dependent objects as well. With SQL-BackTrack you can generate a Master Database Recovery template that contains the device creation steps necessary to reconstruct the Master Database. This template can be used for re-creating the Master Database. Online Database consistency checking during the backup process is provided by DBV. This supplements the function provided by dbcc. (technical info taken from DBV User?s Manual is for reference only) Sybase provides the Database Consistency Checker (dbcc) utility for checking and repairing database consistency problems. Regular usage of dbcc is recommended for ensuring and maintaining database consistency. However, running dbcc can significantly degrade database performance. As database sizes grow, the time required to run dbcc also grows, often prohibitively. Database Verifier enables automatic and regular consistency checking without incurring a performance penalty or requiring massive disk storage. It does this by analyzing the SQL-BackTrack backup data either during backup or offline. As Database Verifier analyzes the backup data, it detects any consistency problems that can prevent a backup from being reloaded. It also detects most of the errors that cause consistency problems with the data. The purpose of Database Verifier is not to replace dbcc, but to provide a fast screening for database consistency. If Database Verifier reports a database inconsistency, you should then run dbcc to verify and possibly fix the problem. If a backup passes the Database Verifier screening with no errors, it can be recovered. SBT will automatically identify dependent objects such as triggers when performing a table-level recovery and recover these dependent objects as well. With SQL-BackTrack you can generate a Master Database Recovery template that contains the device creation steps necessary to reconstruct the Master Database. This template can be used for re-creating the Master Database. Online Database consistency checking during the backup process is provided by DBV. This supplements the function provided by dbcc. (technical info taken from DBV User?s Manual is for reference only) Sybase provides the Database Consistency Checker (dbcc) utility for checking and repairing database consistency problems. Regular usage of dbcc is recommended for ensuring and maintaining database consistency. However, running dbcc can significantly degrade database performance. As database sizes grow, the time required to run dbcc also grows, often prohibitively. Database Verifier enables automatic and regular consistency checking without incurring a performance penalty or requiring massive disk storage. It does this by analyzing the SQL-BackTrack backup data either during backup or offline. As Database Verifier analyzes the backup data, it detects any consistency problems that can prevent a backup from being reloaded. It also detects most of the errors that cause consistency problems with the data. The purpose of Database Verifier is not to replace dbcc, but to provide a fast screening for database consistency. If Database Verifier reports a database inconsistency, you should then run dbcc to verify and possibly fix the problem. If a backup passes the Database Verifier screening with no errors, it can be recovered.

16. Unique SQL-BackTrack Features over Sybase Native Utilities Logical Object extraction features: DDL Only DDL + Data Specific objects or object types like: stored procedures only or tables only ? Will add objects to database in dependency order. For instance, will compile stored procedures into database in dependency order. Can exclude specific objects like: exclude sysusers, sysalternates tables when copying database to another server. Or you can exclude a type of object like exclude all triggers. Extract/restore database object and all of it?s dependent objects Default is fast bcp in, unless told to do otherwise. Automatically takes care of rebuilding indexes.

17. Unique SQL-BackTrack Features over Sybase Native Utilities Logical restore options make shrinking your database much easier. 1 step - do a logical restore to a smaller database. Warm Stand-by Server Support Master database info, writes a text file of useful information about the master database such as device, configuration, database, sysusages, and syslogin information taken from a physical backup. Very useful in recovering the master database.

18. Unique SQL-BackTrack Features over Sybase Native Utilities Can restore data to a database using full sql insert statements -- useful if ?select into/bulk copy? option is turned off Can restore data to a different segment. Useful if you want to eliminate segments or move data from one segment to another Prints database allocation info. (sysusages) from a physical backup. Useful if you have to manually recreate the database. Supports calling SQL-BackTrack log dump commands from a stored procedure. Useful for thresholds.

19. Agenda ??????? ???? ?????? ???? ????

20. SQL-BackTrack Components SQL-BackTrack Control Directory (with Control Files) sbacktrack Executables (main BT program) .dtoptions file .dtoptions file .dtoptions file

21. SQL-BackTrack Executables (lower-level SQL-BackTrack programs) sbacktrack (main BT program) dtsbackup dtsrecover dtsload dtsdump dtscheck dtscreate The SQL-BackTrack programs are a collection of executables that perform the backup and recovery tasks as well as some administration tasks. The main SQL-BackTrack program is sbacktrack. Sbacktrack calls an interactive menu interface that allows the user to access other SQL-BackTrack programs in an easy to use manner. Key programs that are part of SQL-BackTrack include dtsbackup, dtsrecover, dtsdump, dtsload, dtsanalyze and dtscheck. The SQL-BackTrack programs are a collection of executables that perform the backup and recovery tasks as well as some administration tasks. The main SQL-BackTrack program is sbacktrack. Sbacktrack calls an interactive menu interface that allows the user to access other SQL-BackTrack programs in an easy to use manner. Key programs that are part of SQL-BackTrack include dtsbackup, dtsrecover, dtsdump, dtsload, dtsanalyze and dtscheck.

22. SQL-BackTrack Control Directory Figure 1-6 in the student manual, shows a Unix server Control Directory structure. SQL-BackTrack expects the hierarchy as viewed in figure Figure 1-6. SQL-BackTrack will create at least one of sbackups.physical and/or sbackups.logical control directories as requested by the user. Within these directories, directories representing each individual Sybase SQL-Server are created. The control files exist within the Server Directories. Control files are created for specific databases. For example, if you will always be backing up information in Publications you could create a Control File called pubs. Figure 1-6 in the student manual, shows a Unix server Control Directory structure. SQL-BackTrack expects the hierarchy as viewed in figure Figure 1-6. SQL-BackTrack will create at least one of sbackups.physical and/or sbackups.logical control directories as requested by the user. Within these directories, directories representing each individual Sybase SQL-Server are created. The control files exist within the Server Directories. Control files are created for specific databases. For example, if you will always be backing up information in Publications you could create a Control File called pubs.

23. Special Recovery Situations Copying a database to a different machine / Migrating a database Object extraction from physical backups Resizing a database Generating recovery templates(Master)

24. Agenda ??????? ???? ?????? ???? ????

25. SQL-BackTrack for Sybase vs Competition ASE and extension are limited to scripts. SBT obtains relevant logs at backup time automatically. Limited Limited YES? Transaction Log Backup Automation SBT?s Logical Extraction feature can recover tables, stored procedures, triggers, etc. directly from a physical backup NO NO YES? Logical object recovery from a physical backup NO NO YES? Logical database backup ASE and extensions will only migrate data. SBT backups up table schema, dependencies, and data. (SBT unique) Limited Limited YES? Logical object backup NO YES Extensions (VERITAS & Legato) YES YES Physical database backup (full) NO ASE 12.0/ 12.5 SBT?s intelligent incremental backup feature writes only physical data blocks that have changed since last backup YES? Physical database backup (Incremental) Comments SBT Function ? Unique/noteworthy feature This table summarizes the way in which the BackTrack product enhances the basic backup capabilities of System 10. Many of these points we?ve already talked about.This table summarizes the way in which the BackTrack product enhances the basic backup capabilities of System 10. Many of these points we?ve already talked about.

26. SQL-BackTrack for Sybase vs Competition SBT allows users to recover information to alternate locations (Powerful DR support tool) NO NO YES? Recover to alternate host, database NO NO YES? Dry Run Recovery ASE (BCP utility) and extensions will logically backup data but not schema and is limited to environments with same OS and ASE levels. SBT will migrate data between differing ASE and OS versions as well as between varying database page sizes. Limited Limited YES? Migration Limited Extensions (VERITAS & Legato) ASE automation limited to commands included in scripts created by DBA. Extensions allow ASE scripts to be scheduled for execution. SBT provides guided recovery to automate backup & recovery as well as generate scripts automatically for use in scheduler. Limited YES? Automation ASE 12.0/ 12.5 Comments SBT Function ? Unique/noteworthy feature This table summarizes the way in which the BackTrack product enhances the basic backup capabilities of System 10. Many of these points we?ve already talked about.This table summarizes the way in which the BackTrack product enhances the basic backup capabilities of System 10. Many of these points we?ve already talked about.

27. SQL-BackTrack for Sybase vs Competition Master Database recovery is a tedious, manual process. SBT greatly simplifies process by generating template with relevant information in support of recovery. Limited Limited YES? Master Database Recovery Template Extensions (VERITAS & Legato) ASE 12.0/ 12.5 Comments SBT Function ? Unique/noteworthy feature Limited NO Extensions provide vendor specific support. SBT Modules provide seamless integration with IBM Tivoli Storage Manager (TSM), VERITAS NetBackup DataCenter, and Legato NetWorker NO YES? Storage Manager Integration NO SBT can recover tables, stored procedures, triggers, etc. directly from a physical backup YES? Encryption This table summarizes the way in which the BackTrack product enhances the basic backup capabilities of System 10. Many of these points we?ve already talked about.This table summarizes the way in which the BackTrack product enhances the basic backup capabilities of System 10. Many of these points we?ve already talked about.

28. Questions and Discussion


Other Related Presentations

Copyright © 2014 SlideServe. All rights reserved | Powered By DigitalOfficePro