I see everyone's point of view. What I would like is an option, on
SAV/RST commands, to include Data Link items with the backup. So if you
configure the data link file manager, DLFM server, define the field in
your database as a data link to the attachments, you should be able to
backup and restore the attachments with the database. They are linked
are they not? If you back them up you should have the option to restore
them or not. The system also knows if the attachments has been backed
up so if you are running a differential backup, you would pick up the
changed database but only the changed attachments. This could be a
nightmare to restore, but not impossible. What I like is options and
automation.
Christopher Bipes
Information Services Director
CrossCheck, Inc.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Walden H. Leverich
Sent: Monday, May 14, 2007 10:58 AM
To: Midrange Systems Technical Discussion
Subject: RE: Saving and restoring LOB data
While the initial reaction might be that all related data should be
saved at the same time, it seems that the very
nature
of much LOB data (large, essentially immutable streams) means that the
very
archival medium (much less things like retention rules) should perhaps
be
different for this sort of data.
I think the difference is less of a theoretical one and more of a
practical one, specifically for backup/recovery reasons.
We have an application where users can add "attachments" to rows in our
database. An attachment can be anything, in fact we describe it in our
sales presentations as "if you can save it on your PC you can save it in
our system." The typical attachment is a word document or pdf, but we've
seen AVIs, MP3s, JPGs, and multi-hundred megabyte zip files. In our
case, as you suggest, the bits are immutable. If you want to upload a
new version go right ahead, but we're keeping the old version too (don't
blame us, it's OHRP and the FDAs fault <G>).
From a backup/restore point of view we're torn on what to do. If we
stick it in the database we have a "complete" backup. However, since the
majority of the storage space used is used for attachments our backups
get larger and larger each time (nightly, plus 15-minute log-files) when
the backed-up bits can't have changed since last time. Even worse than
backup size and time though is restore time. The bigger the backup the
longer the restore, and for the most part, if attachments come back
online "later" than the rest of the application it's not a big deal. I'd
rather say, "hey, you'll have everything but attachments in 20 minutes,
and attachments 2 hours later" than have to say "you won't get anything
for 2 hours". [1]
In our case we've chosen not to store the attachments in the database
but instead store them as files in the file system, and simply store the
filename in the database. We have to backup and restore the db and the
attachment-space separately, but now backups are small and the restore
process is more straightforward. I've run this past several people and
the general consensus is "Databases are for data and file systems are
for files, don't mix the two."
Having said all that, I think you also have to look at the file sizes.
If you're taking about a 20KB image of a product I'd stick it in the
database, but when you get to multi-megabyte files, well, use a file
system.
[1] Yes, I'm leaving HA out of this equation.
midrange.com
