Re: Shared databases and improving reliability

On Aug 26, 4:08 am, Martin Trautmann <t-...@xxxxxxx> wrote:
On Tue, 26 Aug 2008 01:06:54 -0700 (PDT), d-42 wrote:
 On Aug 25, 11:14 pm, Martin Trautmann <t-...@xxxxxxx> wrote:
Hi all,

three users do share access to a database.
Typical operations are scripted imports (with heavy auto-enter data
manipulation), which is done mainly by one
user. Other users do search/replace operations.

 To me this is a red flag of possible poor database design. Why do you
 need to do search and replace operations so much?

why not? For me that's one of the major tasks of a database!

If you are doing a lot of "search AND replace" it MIGHT mean that your
database isn't properly normalized. It doesn't necessarily mean your
database isn't normalized. Its a 'red flag' its not conclusive
evidence.

The main operation here is

- import data (from a bank or a company)
- compare data
- find open payments
- trigger actions (mark them as open, payed, further actions)

The mere job of data conversion is rather cumbersome for FMP itself
(import is a single-line file only which has to be split to hundreds of
records and dozens of fields). This job could be done much better e.g.
by a rather basic perl script. Comparison is more comlicated - but still
could be done by perl and simple text files. But FMP comes in for the
major search, replace, display and print tasks, where PDFs can be
created for further manual handlig or where records can be marked
manually, how they where processed.

You want to tell me that FMP is not suited for database operations?

I'm not saying that at all.

Maybe - I could use perl and a rather straight forward cgi/html
approach. This could be used very well for search, edit and replace
operations, especially since FMP does not offer better multi-cell
copy/paste operations. So FMP is not as powerful as a spread***, but
much closer to simple html web forms.

Its a database. Its record oriented, like any other database.

It's the proper printout formatting where I still prefer FMP. Maybe
xhtml or xml will be as suited soon, while I would not like to use a
TeX-like approach here.

I see.

How could I improve reliability? I added some Flush Cache to Disc script
steps after import and replace operations. But I'm very disappointed by
the poor, shared stability which returned e.g. malfunctions on import

 We would need more details on how precisely the database is becoming
 corrupted, when its happening exactly, and what errors you are
 getting.

Unfortunately I do not know any details - I'm not the user who corrupted
it. I guess it was an incomplete import, a killed database process or
network trouble which caused the confusion.

If it was the latter, yes, Filemaker does require a reliable network
in order to reliably work over the network.


as
well as reports that e.g. 65 records could not be updated since they
where blocked by other users. That many records, while just three
members did use it?

 Multiple records can be locked via portals/relationships. Multiple
 records can be locked in multiple windows too.

I'm just aware of locks when you actually edit a field entry - and
there's always just one record, one field per open window that could be
edited at a time.

A replacement on related records might have a lock on multiple records -
but replacements are always done within the local table only, but not on
related data.

A lock should be gone as soon as the database was closed down and opened
again.

Agreed. unless the database is corrupt.

The other table showed zero records and complete
failures on import. After recovery the 700 000 records where available
again.

 You've been around here long enough, that I'm sure I don't need to
 give you the 'lecture' on 'recovery'. If you've been using the recover
 command on your databases, that might well be part of the source of
 your problems.

'recover' always should be something that never should be required.
Database should take care on their own not to become corrupted at all,
neither on interrupted imports, replace operations, broken networks (too
much traffic, WLAN interrupts etc.)

Yeah, it would be nice if FM was journaling, transactional, and
guaranteed integrity. But it doesn't. And even if it did, there would
still be faulty ram, hard drives, and controllers to contend with.

BTW, I was somehow lost how to teach the user how to recover the
database on Win. Is there any problem within the dialogs on
protected files?

The main process is
  1) select the recover menu choice
  2) select the file to be recovered
  3) open the file
  4) define where to save the recovered copy
  5) wait for the recovery begin
  6) wait for the recovery end

At what stage do you have to press the shift key in order to optain the
user/pass dialogue for admin rights? Immediately before or after 3) or
after 4?

I do not have any problems on Mac, using the opt key. But I can not use
the shift key within the ordinary file open dialogs. I always had to go
via the choice to open the last used file from the menu choices. Since
this is available for open only, but not for recover, I do not know yet
how to get the user/pass dialog here.

Sorry I don't know offhand. I would imagine its between 2 and 3.

What's your recommendation for a working backup strategy? The database
is not that large (< 100 MB), but doing a daily security backup
(saving a compressed clone) would be both very time and space consuming.

 Assuming peer networking - once a day, kick the guests, quit FM, and
 just duplicate the database folder. Its a minor hassle, but it only
 takes a few seconds, and then you can use FM again. You can take
 however long you need to deal with the 'duplicate'.

 Ideally, get a server, and use it to schedule backups. Its less
 hassle. But its poor value for just 'backups'.

 Either way, once you've got a backup on the hd, backup it up however
 you want. (Copy it to a CD...to a remote filesystem... a usb stick....
 all of the above...)

ok, so FMP designs do not offer better choices.

Filemaker is primarily a workgroup database. The backup solution
should fit the target audience. Full backups are simple and easy to
understand and perform.

What would be a better choice? A chain of "incremental backups" going
back into the mists of time? Lose just one and you are hosed, and they
might take days to re-integrate.
Or how about a replication server? Except you don't even have one
server, for that you'd need two.

Could you imagine that Wikipedia would require a daily copy just for
backups?

Could you imagine maintaining Wikipedia's infrastructure for your 3
users who only use it a few times per day?

 The database gets 10 KB added every day. That's 3 MB/year,
taking maybe another 10 MB backup space. Within FMP this filtered and
indexed info is up to 50 MB already - that's 1 GB backup per year.

That's not that much, but I feel it's a significant annoyance.
Fortunately, the database ist just used a few times every day. Just
imagine that you would have to do the backup every 30 minutes and you
will have much higher volumes.

Sure, but if I have 48 =complete= backups per day, what would I need
to keep ALL of them indefinitely for? For example, I take twice daily
backups of a GB+ system at one site, but I don't keep all of them. I
keep the miday one only until the end of the day, and after a week I
discard most of the previous weeks end-day ones too. And although I
made ~700 backups of the system in 2007, I only still have 50 of them,
one per week.

Apart from that, you should not duplicate an open database. As you
mentionned, you have to close it first. I feel it's more than a major
hassle when you have to kick all guests, when they have to open the
database remotely again every day, instead of leaving it open all the
time.

FM Server can backup a database without closing it, without kicking
everyone out, and without manual intervention. If you find it this to
be 'more than a major hassle', buy a server.

The Win guy installed a small script already which does the remote open
automatically. I hope that this is not part of the problem (ist it?) -
but the manual remote open of a shared database is a major hassle to me.

How do you use this? Do you have a local, small FMP database which does
the shared remote open?

That is usually exactly how its done. The local fmp "opener database"
can also do some additional checking to ensure network is enabled,
etc, prior to trying to connect.

-Dave
.