Shared databases and improving reliability

This message is not archived
 >> Stay informed about: Shared databases and improving reliability 

On 2008-08-26 04:08:38 -0700, Martin Trautmann <t-use.RemoveThis@gmx.net> said:

> 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?

Here's your problem right here. Remote hosting of a peer-to-peer shared
database is a sure path to corruption.

The workstation that physically holds the database should always open
it first, close it last, and become the host of the files. The other
workstations that become guests should use the Open/Remote FM sharing
and find the already open database that way, NOT through the OS sharing.

Do not allow the other machines to open the files remotely.
Imagine...The files live on Machine A. Machine B opens and hosts them
remotely. Then the user of Machine A tries to open the files too, and
must become a guest of files that actually are on the same machine, but
hosted over there -->. Follow the dizzying circle of packets on your
network from B to A to B to A...yipes! FM will use a mix of OS sharing
and FM sharing when the files are opened improperly, and the conflicts
and collisions will crash machines and corrupt the files.

About the time it takes to back up the files, well, you can always set
up a routine to run on closing that exports all the data alone. Since
all guests will have to be out of the files at that point, you could
even do a Save As of both structure and data. I guarantee you, though,
that the FASTEST way to back up such files is to close them, and
through the OS make a copy or a Zip archive. Zip archive is safer, as
you particularly do not want extra copies of files with the same names
on a shared network. Somebody is going to find the wrong set of files
and do a whole day's work in one of the backup sets of files.

Or, depending on the importance of this data to your business, you
could invest in an installation of FM Server and give it a dedicated
machine. Then your backups are done FOR you, done properly, and done
without disrupting work. There will be no conflicts, no crashes, and no
corruption, or at least far less chance of any of them. If your data
isn't important or is easily recreated from other sources, then you can
be less rigorous about hosting and backups.

As for not liking the required space and time for backups, it's a
condition of the tool you've chosen. If you don't like it that much,
choose another tool. I'm pretty sure Wikipedia backs up daily, if not
more often. I have a client who has 20GB of data...he backs up 3 times
daily. Memory is cheap. Downtime is expensive.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA
 >> Stay informed about: Shared databases and improving reliability 

On Aug 26, 4:08 am, Martin Trautmann <t-... DeleteThis @gmx.net> wrote:
> On Tue, 26 Aug 2008 01:06:54 -0700 (PDT), d-42 wrote:
> >  On Aug 25, 11:14 pm, Martin Trautmann <t-... DeleteThis @gmx.net> 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 spreadsheet, 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
 >> Stay informed about: Shared databases and improving reliability 

On Aug 26, 8:50 am, Lynn Allen <l... DeleteThis @NOT-semiotics.com> wrote:
> On 2008-08-26 04:08:38 -0700, Martin Trautmann <t-... DeleteThis @gmx.net> said:
>
> > 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?
>
> Here's your problem right here. Remote hosting of a peer-to-peer shared
> database is a sure path to corruption.

I -think- he's using it correctly. He has a small opener database to
open the remotely hosted fmnet shared database, which is the correct
way to do it. (Provided the network is reliable; doing this over a WAN
can be problematic if the link is flaky.)

But in any case I don't think he's opening the database for sharing
from a remote file share, which as you describe is a FM 'worst
practice'. If he is though, that would certainly explain why he's
having massive problems.

-cheers,
Dave
 >> Stay informed about: Shared databases and improving reliability 

On Aug 26, 5:08 am, Martin Trautmann <t-... DeleteThis @gmx.net> wrote:
> On Tue, 26 Aug 2008 01:06:54 -0700 (PDT), d-42 wrote:
> > On Aug 25, 11:14 pm, Martin Trautmann <t-... DeleteThis @gmx.net> wrote:
>
> > > 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.
>
> Could you imagine that Wikipedia would require a daily copy just for
> backups? 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.
>
> 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.

My minimum backup strategy is to do daily backups once a day, they go
into folders named for the day of the week. When a new Monday rolls
around, the old Monday gets overwritten. I also do weekly backups for
the past month, and monthly backups for the past year. That's about
20 files I'm saving. For some clients I double that, so at most, it's
40 files. And for those clients, it's usually a mix of compacted and
clones. The clones take up an insignificant amount of space, usually
<1MB.

If you don't use Server, you do need to close all the files, but you
don't need to shut down FMP. Script a Save As....

Dave alluded to it already, but maybe you don't know...the general
recommendation is to never use a file that's been Recovered. I happen
to believe you can in some cases, though you won't know if it was a
good decision or not until later. It's clear from the continual
problems you're having that your db is hopelessly corrupted. Recovery
won't help. If you don't have a backup structure to import data into,
you'll need to rebuild from scratch.

I would never teach a user how to Recover a database.
 >> Stay informed about: Shared databases and improving reliability 

On Aug 26, 1:49 pm, Grip <g... RemoveThis @cybermesa.com> wrote:
> On Aug 26, 5:08 am, Martin Trautmann <t-... RemoveThis @gmx.net> wrote:
> > On Tue, 26 Aug 2008 01:06:54 -0700 (PDT), d-42 wrote:
> > > On Aug 25, 11:14 pm, Martin Trautmann <t-... RemoveThis @gmx.net> wrote:
>
> If you don't use Server, you do need to close all the files, but you
> don't need to shut down FMP. Script a Save As....
>

Oops, I meant you need to kick everyone off, but you don't need to
close the files.
 >> Stay informed about: Shared databases and improving reliability 

This message is not archived
 >> Stay informed about: Shared databases and improving reliability 

On 2008-08-26 09:44:42 -0700, Martin Trautmann <t-use.RemoveThis@gmx.net> said:

> It's the question of a reasonable database design. FMP is made neither
> for versionning nor for efficient backup. This might be ok if mechanisms
> would prevent database corruptions as far as possible. Backups would be
> sufficient for real hardware failures of the physical storage (or
> burning the full building). But corruption just by ordinary application
> (and random behavior, who openend the file first) is a no-go to me.

I think you're going to find that with ANY tool, if you don't follow
Best Practices and proper configuration and deployment, you're going to
get less than nominal performance. It's not that FM isn't behaving
properly, or that it's not designed properly. You're basically saying
"I should be able to do anything I want, even in defiance of proper
practices, and it should Just Work." Well, that isn't any software in
the real world, anywhere.

No software is designed for infinite deployment configurations, or
upteen different access methods. The fact that FM *permits" non-optimal
hosting and connection doesn't mean it's okay to work with it that way.
Just because you can do something doesn't mean you should...and that's
true for ANY software.

FM has many drawbacks, but some of its best features are possible
BECAUSE of the proprietary format and combination of data and
structure. Skilled developers can minimize versioning problems with the
Separation Approach, and those who take the trouble to optimize
hardware and network deployment don't experience the problems you're
having. It's a poor workman who blames his tools.

Other databases also experience corruption, just in the normal course
of use. Ask any SQL DBA. Once again I suggest that if FM isn't a
satisfactory tool for you, you should seek out and use another software.

--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA
 >> Stay informed about: Shared databases and improving reliability