[AccessD] Memo field corruption

[James Button]

Sorry - earlier post of this went to Bill rather than the list

If the large mamo or blobs are within the database than changing one means the
table will be 'dirty' and need backing up in it's entirety 
If the blobs and memo fields are separated out from the database holding the
majority of the data fields then 
Backups will only need to be taken of the main database when data within that is
changed, 
And backups needs will be limited to the database (table) containing the memo
fields that changed, or of specific files that contain the blob
The main data field store not needing to be updated when a blob content is
changed as the file name (and link) remains the same 
And - if your processing puts updated versions of memo fields in a "today's"
storage location - table or database then the mass of the data being historic
will not need a new backup 
The access to the memo field being via a process that first looks in "today's"
for the entry, and if not found there goes to the mass store of older 'historic'
entries.

Yes - it does complicate access but reduces backup volume and the sheer amount
of data that will need parsing by the DBMS on the basis that a small amount of
data will be updated in any day/week, and frequently a field to be updated will
already have been updated a short while ago - as in   successively  
report was.. action required is . allocated person is . action proposed is.
Action agreed by . Action initiated by .. action followed-up by . Action deemed
complete by .  

The need or actual implementation of such processes will depend on the actual
data to be stored and the anticipated access patterns 

Now, I would expect any backup process to include all the associated data
storage locations, with the actual backups being conditioned for the minimal
output needed to recreate the system on a replacement facility.
And to conform to any legal requirements.

So - minimal needs - at least 1 copy of all data onsite, replicated to at least
one other offsite location, with a 'copy' including historical entries and
access records. 

If all is in 1 database then all will need backing up each time
If split with the (mostly) unaltered blobs and memo sets be handled separately,
or even as individual entities (files) then backup volume will be vastly reduced
as will the system loading needed to take the backups, and the associated
storage costs 

Then again with storage being so much more byte/buck and I/O per GB being so
much less of the systems throughput capability  -
The need for constraining processed volume becomes less of on overhead than the
staff time to setup the less  resource hungry and hardware costly process.

'Orses for courses and.. if playing waterpolo why 'ave an 'orse ?

JimB


From: Bill Benson <bensonforums at gmail.com> 
Sent: Thursday, January 31, 2019 2:35 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Cc: James Button <jamesbutton at blueyonder.co.uk>
Subject: Re: [AccessD] Memo field corruption

RE:  >>>backup requirements if the entries are held in separate files  

I don't get this remark either. If the entries are in "separate files" then that
implies they are not in the database. In which case, what good would backing up
the database do you?

On Sat, Jan 26, 2019 at 12:43 PM James Button via AccessD
<accessd at databaseadvisors.com> wrote:
Add to the avoiding corruption of masses of data

Allowing for easier changes of associated apps when the memo contains RTF or
other things such as images
Also reduces the time taken for the DBMS to load up the data in the table for a
SELECT statement, or just a scan through the file for backups etc.
If you have 10,000 rows, and each has 1000 bytes of data that's a 10MB table
And maybe 20MB of Temp files 
With a Memo of - say 4000 bytes in each that means any process of the table will
have to read 100MB and possibly use 200MB of RAM for that process
Complete with the flushing of memory to pagefile and the flushing of other data
from the storage cache, and   the RAM allocated by the OS for I/O buffering.

So - separate out the memo and blobs and you avoid massive processing overheads
as well as backup requirements if the entries are held in separate files
And that is without even considering the increasing size of images and
commentary that may be in memo fields
Cut and paste of text from reports and filled-in forms - as in as well as
describing the actual incident, detail your understanding of the cause, and the
effect of the incident on yourself.


JimB