Is your ASE system being used for SAP applications, or for other uses? If it isn't being used for SAP applications, the "SAP Sybase ASE for Custom Applications" group is the place for general ASE questions. http://scn.sap.com/community/sybase-ase-custom-applications SAP has specific methods they want you to use with their applications, see the following notes:
- 1585981 SYB: Ensuring Recoverability for Sybase ASE
- 1588316 SYB: Configure automatic database and log backups
- 1611715 SYB: How to restore a Sybase ASE database server (Windows)
- 1618817 SYB: How to restore a Sybase ASE database server (UNIX)
Speaking from a generic ASE perspective, there are two main methods for backup:
1) DUMP DATABASE [and DUMP TRAN]
2) QUIESCE DATABASE [or ASE shutdown] with direct device copy.
Both are documented in the ASE System Administration Guide, particularly Chapter 12.
http://infocenter.sybase.com/help/index.jsp?docset=/com.sybase.infocenter.help.ase.15.7.2/title.htm&...
One of the first things you should think about when deciding on a backup strategy for ASE is how much data loss you can accept in the case of a disaster, as well as how much down time you can accept. I expect that SAP has already made these decisions for their own applications. There are actually factors beyond the dump strategy that can factor into this planning – for instance disk mirroring to guard against a hard drive freezing up and bursting into flame, the “high availability” option that uses two hosts sharing a disk system to guard against failure of a host, etc.
An ASE server has multiple databases on it, each database can be on multiple devices (and can share devices). Each database has a transaction log, which is itself a table within the database. At any given time, the contents of tables written to disk may contain a mix of committed and uncommitted data. ASE uses a write-ahead transaction log, meaning that when a client issues a commit transaction all the log records for that transaction are written to disk before success is returned to the client. However, the changes to data pages are not flushed to disk by the commit command (though they are flushed by other events, such as checkpoints or aging out of cache.) If the server goes down abruptly, a recovery process applies the transaction log records to the rest of the data stored on disk to return it to a transactionally consistent state.
As a general overview, there are three main methods for doing backups (in order of common usage): the DUMP and LOAD commands, the QUIESCE DATABASE command coupled with disk copy, and Replication.
The DUMP DATABASE command copies the entire database to an archive device. It does not prune (truncate) the transaction log (which is itself a table in the database). DUMP DATABASE is typically run on a daily or weekly basis. If your loss tolerance is 24 hours, then a single DUMP DATABASE every 24 hours is a reasonable choice for backup strategy and the transaction log can be kept pruned by either setting the database option “truncate log on checkpoint” or by regular use of the DUMP TRANSACTION command with the TRUNCATE_ONLY option.
If your loss tolerance is less than 24 hours, down to about 15 minutes, then you would add in transaction dumps. The DUMP TRANSACTION command copies the contents of the transaction log to an archive device and truncates the log from the beginning to the start of the oldest open transaction. DUMP TRAN can be run off a cron (or Job Scheduler) job, or triggered automatically when the log grows to a certain size using thresholds. Note that the more frequently transaction log dumps are taken, the more transaction log dump files there are to LOAD, and the longer it will take to load them all. These dumps can be kept to be reloaded on the host in case of a problem, or used to maintain a "warm" backupserver by promptly loading the dumps as they are taken onto a second host; in case of a failure the warm system can be brought online relatively quickly by starting the recovery process by issuing the ONLINE DATABASE command (the time to load the dumps is avoided, but the last phase of recovery still needs to be done).
DUMPs can be made either natively (directly to file or tape device) or to an interface written using the Backupserver API. DUMPS made to a file look like “dump database mydatabase to ‘/work/mydatabase.dmp’”, DUMPS made to an API look like “dump database mydatabase to ‘myapimodule::arbitrary_api_parameters’”. Sybase provides one API module with ASE, which is the compression module, which takes a compression level and file name as a parameter. “dump database mydatabse to ‘compress::4::/work/mydatabase.cmp.dmp’”. There are other third party vendors such BMC’s SQL Backtrack and various automated tape library manufacturers that create and provide API modules. ASE DUMPs also now have a native compression parameter, there are two methods for dump compression because the compression API was developed first and is still supported.
The second major backup method is the use of QUIESCE DATABASE coupled with a device copy of the underlying devices of the database, usually using a very fast copying method. QUIESCE prevents ASE from performing any writes to the devices until it is released; this allows a consistent “snapshot” of multiple devices to be copied. Activity can continue on the server in this state until the data caches fill completely with dirty buffers – although any process that has to flush log records will be blocked at that point until the log records can be written. This approach is typically used for very large databases where the time taken to dump or load the full database becomes very long with the traditional DUMP and LOAD commands. This method can also be combined with DUMP TRANSACTION.
A third method, using Replication, is used when the tolerance for data loss or down time is very low. The method maintains a hot backup system on another host by constantly shipping and applying transaction log records to the hot backup system using a Replication Server. If the primary fails, the warm backup can quickly be substituted.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.