Display but don't back up

[LaRetta]

Hi everyone,

I have an attitude problem. We have 80,000 invoices with 500,000 LineItems that are posted and frozen and will NEVER change. This represents 750MB+ of 850MB file (our entire solution is currently in this one file). But every day ... every single day ... our office is brought to standstill because it backs up this file on our FM Server. And it bothers me. We get the dreaded coffee cup and it brings our office to a standstill. We will hit 800,000 LineItems in another 6 months.

I'm beginning restructure (on paper) for re-write in vs. 8. I had planned to place all data (including Invoices/LineItems) in a separate file (separation model). But that doesn't solve our problem. I am displeased with Server in that we can't selectively back up only certain tables/files. The only way (that I can tell) that Server won't back up posted Invoices/LineItems is if they are in different file and NOT in datadatabases. Is this correct?

I realize I'll have two tables/files (posted and unposted). I realize I'll have to move invoices/lineitems each month from unposted to posted and that I'll need to group them again for some displays (upper portal unposted; lower portal posted, etc). I am willing to pay this price but this goes against normalization doesn't it? Some tables need backed up frequently (customers, activities) and some only need to be backed up once a month or so and many static lookup tables don't need backed up at all. I'm torn. Invoice/LineItem data is constantly displayed in various ways ... data-modeling for production, customer trends - I can't archive this data because it's too new (5 years).

I am even considering stopping full server backups and replacing with export of raw data based on modification timestamps. I haven't tried this yet but it MUST be faster than server backups. I would like to back up several times a day but even once is too much! If I added a second backup, half our sales team would walk out. Surely I'm not the only one facing this kind of problem. Do I need to change my attitude or is there anything I can do? Am I nuts to consider two files (posted and unposted)?

LaRetta :wink2:

[Brian C]

I dont have much experience with server 8 yet but since you say you cannot selectively backup files perhaps you may want to consider moving your solution to be on separate servers?

Providing you want to consider the separation model, see the following:

Server 1: User Interface File, Current Data File

Server 2: Archive Data File, Reference Table File

In this way, you can keep your data that does not change on one server that does not need to backed up as often. Your data that does change more often and is needed to be accessed more readily by your users can then reside on its own server and will take less time to backup.

Dont know if this helps or if a 2nd server is a solution you want to consider, but it is a possible answer at least.

[LaRetta]

Thank you, Brian. I appreciate the input. I’ve been chewing on possibilities - that's why it took so long to respond.

Archive is a relative term - where would I split? How valuable are trends within the past 5 years? 3 years? I've been considering the way our data is currently displayed because I don't want to split the data only to discover problems displaying it (like newbies that start their design with repeating fields and don't recognize their problem until they attempt to report). In reviewing our display needs, the file location (resides on another server or in data) really wouldn't matter. Invoices/lineItems will STILL be split at a date-point and it would effect our entire solution and structure. Many reports would be easy - use different TOs (with portals or multiple windows with list views) to compare month-to-month or week-to-week ... but envision this: displaying last year (all from 'posted' file) to this year (most in 'posted' file and this month in main file)? I need to consider this VERY carefully. I've even been considering a join file between them (containing only ID) with one calculation - if there is related record in unposted then display that; else, display the related from Archive and create reports in this Join table. Event trigger would create a new join record containing only the unique ID.

My other concern (and I'm in the wrong forum to ask but it's related) is whether Server can serve a file from another directory (such as data) or am I opening up other unforeseen problems? I'm also considering performing full back up at night and script incremental exports during day on specific active tables (and only modified records). GTRR to ModifiedDate and export and all because every file in datadatabases gets backed up, then 750mb (and quickly climbing) of data that will NEVER BE ALLOWED to change, drags us down. It makes me feel insane. I want speed. I want safety. And I want free data-modeling and reporting back at least 5 years. Is that asking too much? It reminds me of one of my favorite sayings, "Dear Santa. I want it all."

So do I split by date and if so where? How then will I display the pieces? If I split, do both files NEED to be Served? Or do I leave Invoices/LineItems intact and script exports? Doesn't it bother others that they are backing up static data over and over? Once static data is backed up, do we think we'll get a better copy if it's backed up again?

This decision will affect the base of my structure. I can't wait to make this decision until I start adding the reporting features. Brian, as our business continues to alarmingly expand (size x 8 since FM 2 years ago), we will continue to face this issue (just on larger scale). Unless Server will allow selective backups, I refuse to sacrifice speed and waste resources backing up LineItems that were posted 3 years ago. If I'm the only one that has/is struggling with these issues then maybe I'm missing something quite obvious. I can't start my base until I make this decision and I feel like I'm approaching it blindfolded. Am I the only one?

ps. FM also needs to allow selective Save Copy As with and without data per TABLE. But that is another issue ...

LaRetta

[Genx]

Ummm.. I don't want to read all of this right now, but you might consider doing something such as the following in terms of your desperate raw data attempts --> It will require VB Script and i only know it's possible / only have the idea because i know someone who does this with other files...

Point is, you would do a series of exports and incremental updates on your raw data files:

First Export would be your modified records only (using mod timestamp comparison) and you would update matching records in your text files using that.

Second Export would be only the new records (using creation timestamp comparison) and would just be tacked on to the end of the files.

I might be looking into the same thing because i need to run remote backups over a live net connection so the smaller the file size the better.

Anyway, even though there's probably not much use to this, i'm just subscribing to the topic lol.

~Genx

[Brian C]

I need to spend a little more time reading your post - but the ideas you both have come up with have me really liking the idea of use exporting as your means of backing up incrementally, and your normal nightly backup be your snapshot of the day...

You could have a single field in each table that is needing to be exported that would date/time stamp each record. This would allow you to see when a record was last backed up (exported) or if it was ever backed up (exported) at all.

In this way you can have an incremental backup of your data as it changes throughout the day without causing too much down time.

You may want to make sure that prior to your doing the export, you have a mechanism to prevent record creation/editing while you are performing the action so that a record will not be busy/in use at the time of export.

[Brian C]

Quick Note:

Your edit scripts could wipe out the time/date stamp for records that have been modified, and new records would naturally begin with no modification time/date stamp.

This way you could search for empty date/time stamp records to get your set of records that need to be backed up incrementally.

Following your export process you would then just populate the date/time stamp field.

I suppost that you could just alternately use a binary result to conserve space as well. 0 = Not backed up, 1 = backed up. Just depends on if you want to keep track of the last date/time a record was backed up. This would make it easier to locate the specific incremental backup for that record if needed, in addition, if you needed to reconstruct your data you could do a comparison to grab only the most recent version of a record.

Edited June 22, 2006 by Guest

[Brian C]

Regarding your question about server being able to serve data from another directory, it is possible to designate 1 additional directory that exists outside of Server's default directory of served files.

Using the admin tool connect and click on the Default Folders tab. You will see an option to check: Use additional database folder

This could exist either on the server itself or on another file server altogether. Serving a file from a shared volume... I suspect you will see a signficant speed hit in this situation since Server's speed has a lot to do with it's ability to crunch the data and send it back to the client quickly. You would need to have at least a gigabit of bandwidth - but even that is no match for normal file i/o from a hard drive.

[Brian C]

Should you split? If your issue just has to do with speedy backup of data, I think the answer would be no. I think that it does not make a lot of sense to change your solution too much just to try and solve that issue.

I think that the approach of exporting data from active tables should suffice and solve your down-time issues that would be caused by a normal server backup of all data.

Server backups were always designed to be more of a nightly backup (snapshot) of the data anyway that you could then pump out to tapes or some other media, or send off to a remote ftp server in case a disaster hits and you need to get access to your data because the building burned down or some other nasty thing.

If it were to somehow make sense to split the data in your solution, reporting on split data is not too hard, but it does require more work, is slower in getting your results and it is not as dynamic as just doing normal searching and displaying a layout.

Where would you split? The only way to answer that question would be to determine how often older data is queried... Do you always get a 5 year span? Will you always need a 5 year span? Maybe the majority of searches only go as far back as 3?

The real trick is making sure the system knows when you cross that line so that it can warn the user so they know it may take a little while, and then begin building your results in a separate table so that you can have a unified look at all the data together.

[Genx]

Just regarding the backups again with the active records... These would be done at midnight, downtime for two hours general... but how do you kick a user without having to manually close the file, or could we schedule this on server?

~Genx

[Brian C]

If you keep track of what users are doing in a user actions table - i.e. creating a record, editing a record, etc, you can at any time look to see who is doing what and if they are still creating a record or editing a record at that moment and in what table that are doing it in.

If you have a process setup to prevent users from beginning an edit or create process, and you combine that with your user actions table, you then have a way to let users know that when they try to edit or create a record, that a backup is in progress via a message that you can change on the fly if needed. For users that are dragging their feet on something, you can boot them at the server level if you cannot reach them. From there you should then be able to easily backup your active table records via an export, and then "UNLOCK" everything so that the users can then get back into the system. You should be able to use Server to send messages out to the users to tell them when things are going down and are back up. Or you can use email.

Not too hard really when you consider the alternative is rewriting your entire system...

[Genx]

True, but i run a whole series of scripts at midnight anyway so users are kicked at 5 minutes to midnight through server then one re-connects as per a windows schedule to run necessary scripts. Anyway where's Wim, he's good at all this VB scripting stuff, maybe he can suggest something? :)

[Genx]

Actually, a problem comes to mind if a record is deleted..

[Brian C]

We are responding to LaRetta's issue yes? Her problem is how to backup data during business hours with as little impact to her users as possible. Backing up after hours is not what is being discussed at this point at least as I understand it...

As for deleting records - just as you lock access for editing and creating, it follows that you would lock access for being able to delete as well. Though I suppose it doesnt hurt to mention it so it is not overlooked. Thanks for bringing that up.

[LaRetta]

Thank you both for responding. I simply can't get away from the fact that, if I split my LineItems on date-point, I will be spending a lot of time putting Humpty Dumpty back together for reporting. Currently, full migration to new clone takes 5 hours. If we go down, I don't want to hold the business up while I migrate us and attempt to re-capture possibly trashed data from a Recovered file. But if a good full backup is available (from midnight) and I have scripted process to grab the latest day's incremental backup, it would not only be fast but could also be triggered from a client (which would absorb most of the burdeon).

A few comments before I begin some heavy testing and please feel free to disagree. 1) Deletions are not a problem - we don't allow them ever since a few employees deleted records they shouldn't have and I had to pull data again from backup. 2) I would not feel comfortable removing the modification date and see no need to do so. I may change my mind but, the modification date will be what I GTRR to in each active table (quicker than a Find). 3) I don't need to track creation date because, if a record is new, the modification date would also set itself anyway.

And in fact, I probably won't export at all. I will probably pull (import) into an empty clone (NOT performing auto-enter upon import). Creating an empty clone whenever I modify my files will be simple and Backup script can be easily modified. It is my understanding that record locking wouldn't be an issue - I thought that working with a found set (and even exporting) is User-specific, just as Copy All Records and so on. Although a Restore from another FM file will work well, record-locking CAN occur during the Restore phase (importing with Update and Add To back into the live files). I may kick all Users off the system during this (hopefully) brief time-period. Otherwise I will need to be very careful here (performing auto-enter, resetting serials and so on). We have RAID. But I don't trust it. What if it mirrors the host file the moment it crashes? The mirrored file will be trash as well (and I may not know it). I don't trust it (I realize I said that twice but I felt it twice).

A good scripted incremental backup process (properly structured) should be fairly quick and inconspicuous. I'll find out when I run full tests but preliminary test (pulling in 4,500 LineItems into clone) only took 1 second today. Sure ... auto-enter was off. We'll see how long it takes import/updating back into live files with auto-enter performed. I KNOW that's a whole 'nother ballgame. :smirk:

[Genx]

Just tell us how you get on, i'm actually really interested in this.

Cheers,

~Genx

[Brian C]

In reference to the "modification date" I was not referring to the use of an existing modification date, I was suggesting the addition of a new date field which could be used specifically to track when a record was backed up. Very much like how you have a backup date marker for files at the operating system level.

As for the use of a RAID array, your comments are correct. If there is a hardware failure a raid is great for keeping a system alive and running since a good raid employs multiple drives(mirrored and striped), redundant power supplies, redundant cache modules, batteries, etc. But if your data on the raid is bad, you are in a world of hurt if you dont have good backups. I also cannot help but strees shooting for a good disaster recovery plan as well. This is something that we all tend to put off, but trust me, it is worth taking the time to do. Nuff said.

What you have in mind sounds like it should work well. I too look forward to how things turn out for you.

Another possibility you could consider, though I don't think it will give you the speed that you are looking for in terms of fast recovery:

My backup strategy employs the nightly snapshot of the data via FMP Server's built in backup capability. It also employs a very detailed audit trail that occurs whenever any user creates, edits, or deletes records. The data is not stored freeform in a single field as many solutions tend to do, instead it stores the data in a separate audit trail table which tracks the field name, table of origin, date/time of backup, what the data was, and what it was changed to, and what process triggered the audit record creation in the first place. This solves the incremental backup issues, since it occurs on the fly while users are performing actions. This audit trail can then be used to roll data back to match a state at any moment in time thru the use of a scripted restoral. This is a very common type of data restoral used in the world of mainframes. Make no mistake though, this is not a fast way to recover anything - at least in the world of FMP.