ASE Backup Strategy

[Richard]

We have an openserver application that does the scheduling off all
full and transaction-log backups. The openserver was written in 1993
(4.9.2 ASE), and I assume it was a good alternative to what was
available at the time. We are now considering replacing it with a new
approach that takes advantage of 11.x,12.x, and15.x features.

Here's how the current openserver application works. There's a
configuration file for each database which contains the following
information:

full dump location <---------- directory path
full dump time <-------------- time of day for a full database dump
log dump location <----------- directory path
log dump threshold <---------- only dump the log if it's x% full
log dump frequency <--------- check the threshold every x minutes
log dump enabled flag <------- enable/disable log dumping

The configuration files are read at openserver startup, and timers are
set.

Log dumps would be disabled when a minimally-logged transaction was
going to happen in our batch processing, and re-enabled after a full
dump was completed. The frequency and threshold control when
transaction log dumps are performed. If threshold were set at 15% and
the frequency at 5, then the openserver would check every 5 minutes to
see if the transaction log was >= 15% full. If it is a log dump
occurs.

RPC calls within the openserver allow you to switch the 'log dump
enabled flag' and perform full and log dumps on demand.

What's a better strategy, using features in ASE 15?

Should all log dumps be done by thresholds?

Is there freeware out there that handles backups elegantly?

Is there 3rd-party utilities that do this well?

Thanks.

[A. M.]

Post by Richard
We have an openserver application that does the scheduling off all
full and transaction-log backups. The openserver was written in 1993
(4.9.2 ASE), and I assume it was a good alternative to what was
available at the time.

The usual approach is to write a script or a program. These can
be as simple or as complicated as you wish. I don't think I would
consider an openserver application to be a good alternative. All
it seems to add is unnecessary complexity.

Post by Richard
We are now considering replacing it with a new
approach that takes advantage of 11.x,12.x, and15.x features.

Well, the new features work as extensions to the syntax
of the older backup commands.

Post by Richard
Here's how the current openserver application works. There's a
configuration file for each database which contains the following
full dump location <---------- directory path
full dump time <-------------- time of day for a full database dump
log dump location <----------- directory path
log dump threshold <---------- only dump the log if it's x% full
log dump frequency <--------- check the threshold every x minutes
log dump enabled flag <------- enable/disable log dumping
The configuration files are read at openserver startup, and timers are
set.

Could have been done more easily via cron (on Unix) or its
Windows equivalent. Thresholds should be handled by the
sp_thresholdaction routine.

Post by Richard
Log dumps would be disabled when a minimally-logged transaction was
going to happen in our batch processing, and re-enabled after a full
dump was completed.

Since a log dump would fail if there was a minimally logged
transaction, I don't see what this really buys you (except
some alerts perhaps).

Post by Richard
The frequency and threshold control when
transaction log dumps are performed. If threshold were set at 15% and
the frequency at 5, then the openserver would check every 5 minutes to
see if the transaction log was >= 15% full. If it is a log dump
occurs.
RPC calls within the openserver allow you to switch the 'log dump
enabled flag' and perform full and log dumps on demand.
What's a better strategy, using features in ASE 15?

For a start, an openserver app isn't the way to go. A direct
client fired from cron (or its Window's equivalent) is less
complex and easier to maintain. There's also a new scheduler
feature in ASE but I don't use it.

Post by Richard
Should all log dumps be done by thresholds?

This really depends. If you have a small log, thresholds
are the most sensible approach. For larger logs, thresholds
are unlikely to fire unless set really low (or if the log
fills more quickly than expected). Business requirements
(or clueless or paranoid managers) might mandate regular
log dumps throughout the day. Otherwise daily is fine.
The point in time recovery feature helps eliminate the
need for excessive log dumps.

Post by Richard
Is there freeware out there that handles backups elegantly?

None that I'm aware of. There might be one but I've not
heard of anything.

Post by Richard
Is there 3rd-party utilities that do this well?

Define "well". There are 3rd party apps. SQL BackTrack
is probably the most well known. But there are others.

When I researched this years ago, we had a Perl script
that was a nightmare to maintain. I went looking for
partner solutions at one stage. I didn't find anything
that I considered suitable. You can search here now
http://www.sybase.com/partner/find but a quick look
doesn't seem to find anything useful. I ended up
designing a scripting language that suited my needs.

-am © 2007

[Richard]

Post by A. M.

Post by Richard
Should all log dumps be done by thresholds?

This really depends. If you have a small log, thresholds
are the most sensible approach. For larger logs, thresholds
are unlikely to fire unless set really low (or if the log
fills more quickly than expected). Business requirements
(or clueless or paranoid managers) might mandate regular
log dumps throughout the day. Otherwise daily is fine.
The point in time recovery feature helps eliminate the
need for excessive log dumps.

I think we need to do frequent log dumps, but only because we want to
ship the logs to our backup site and have them loaded on the backup
servers.

[Mark A. Parsons]

There's nothing to keep you from using a script *AND* thresholds to manage tran log dumps.

You can then make your life easier by insuring both processes use the same dump file naming standards and dump
parameters (eg, have both processes call a common sp_dump stored proc?).

So let's say you perform regular log dumps on the hour, but you want to make sure that if any 'extra' activity comes
along that said activity is dumped ASAP ... so set a threshold to fire when the 'extra' activity crosses a specific
threshold (ie, # of used log pages).

Post by Richard

Post by A. M.

Post by Richard
Should all log dumps be done by thresholds?

This really depends. If you have a small log, thresholds
are the most sensible approach. For larger logs, thresholds
are unlikely to fire unless set really low (or if the log
fills more quickly than expected). Business requirements
(or clueless or paranoid managers) might mandate regular
log dumps throughout the day. Otherwise daily is fine.
The point in time recovery feature helps eliminate the
need for excessive log dumps.

I think we need to do frequent log dumps, but only because we want to
ship the logs to our backup site and have them loaded on the backup
servers.

[A. M.]

Post by Richard
I think we need to do frequent log dumps, but only because we want to
ship the logs to our backup site and have them loaded on the backup
servers.

It might be better to consider using ASE Replicator or Replication
Server then. There's nothing wrong with log shipping. Its just
that replicating is easier in the long term.

-am © 2007

[Derek Asirvadem]

Post by Richard
What's a better strategy, using features in ASE 15?

It's simple really, it's about the way you go about it.

cron to Nightly DB dumps plus ...

Post by Richard
Should all log dumps be done by thresholds?

1 Well you definitely need a threshold so that if the log fills up
unexpectedly, it dumps
2 And you definitely need cron scripts to dump the log every hour for shipping

Post by Richard
Is there freeware out there that handles backups elegantly?

Sybase (10, 11, 12, 15) handle backups elegantly, and with a huge range
of options. You need to do a little scripting to administer the
specific form of backups that you choose for your particular site. If
you check the web, you will get a few sites with sybase scriptssprocs
but not the bit that administers backup.

Post by Richard
Is there 3rd-party utilities that do this well?

We have a set of server/database admin scripts that automate a whole
range of DBA tasks. It includes backup with full logging (of
executions) to files and where the dump files was sent, configuration,
thresholds (with error checking and escalation). Interfaces with
SQLBackTrack, NetBackup. Unix (BSD) only. They are integrated: so
that you can (eg) do a demand Db dump: it logs the execution, and it
places the dump file(s) in the right place; (1) and (2) co-exist in teh
same location (for pickup/delivery) with proper names. It includes
the requirements for server rebuild (bcp out master db and server
description) so that that is picked up by the unix backup. It is
licensed, pls write me an email directly.