Offsite backup recommendations for large database +100gB

[m]

We have a database that when dumped with the dbbackup command is a
67gb .db file and a 28gB .log file . It's becoming somewhat difficult
to manage the offsite backups, which are now done weekly via scp. Tar
does not provide any compression on the .db file. The server the db
runs on is windows 2003 and all backup systems are nix based.

1. In sybase is it possible to delta the dumps. I can't find if
dbbackup is a copy of the .db file and .log in use or if it is a sql
dump. Using rsync on a file that large does not work. I would have to
use something that can pre-calculate the difference and then upload.

What recommendations do you have for offsite backups/copies. Our link
is a 10mb symetrical to our off-site server.

2. Our next step if we can't figure this out is to backup our server
at the block level live, but that might still create issues not only
in storage, but if the dbbackup dumps are too different, as it will
still require uploading a significant amount of data. Additionally,
storage costs will skyrocket for us, as we will be backing up more
data than needed.

[Keith]

As the previous poster mentioned, Sybase is a company not a product,
but since we ASE users tend to be the folks who forget this most
often, I'll assume that you're running ASE 12.5.x or newer and that
you can actually compress the dump on the fly. One syntax supported
for this is:

DUMP ... WITH COMPRESSION=n ...

where N is an integer 1 ... 9 I believe. At level 4 (our default) we
tend to get the best ROI for time (lost) and space (gained) until the
database gets quite a bit bigger than yours currently is. On our
(Linux) platform at least, YMMV.

Once you've compressed it, you're right tar's -z or -Z flags won't
squeeze much more out of it since they likely use the same well-known
algorithms.

I don't understand why rsync is not working for you, and I'm not
entirely sure what you expect to get by "delta-ing" the backups, but
you may be able to use the "sum" command in Unix-like environments to
check if the file is identical to its previous iteration.

If you're allowed to use rsync, then the security built into scp will
definitely cost you performance-wise. Not only should rcp or rsync be
faster, but so will ftp.

Finally, for a warm-standby solution (not a replacement for backups,
but it may solve some of the issues you're trying to solve with them
currently) have you tried RepServer or ASE replicator?

Keith

[Leonid Gvirtz]

Hi

From the original poster's explanation I can conclude that Sybase SQL
Anywhere (ASA) and not Sybase ASE is involved. If so, 28 GB of
transaction log for SQL Anywhere database sounds me a lot, you are
probably able to save a lot of space just by truncating it in an
appropriate way. In addition, did you consider incremental transaction
log backups supported by ASA? It may be a good option for you if you
don't perform minimally-logged operations, such as non-logged LOAD
TABLE statements. Replication via SQL Remote is another possible
option.

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

[m]

Thanks for the replies they are all helpful. We are using sql
anywhere. We don't have the option of ftp for a our data, so we have
to take the hit on overhead from ssh or other protocols with
encryption. Looking at the transaction log, you are correct, it should
be truncating and it is not. We are making that change this weekend. I
do not see an option with dbbackup to compress with our setup. As far
as rsync, in my experience, it does not scale well over 25gB. It takes
longer to perform the checksums than to just scp the file. I would
like to delta the backups just to improve the transfer time offsite. I
will look at the other options you mentioned.