Backing up large files

I mentioned the other week that I would go into more detail regarding backing up, so here goes.

We had a scenario lately where a single FileMaker 9 file was around 1.2Gb and initially it was taking around four minutes to back up, which admittedly is quicker than a FileMaker 6 file. The problem was that we wanted to back the file up every hour and, of course, the users weren’t happy to wait for four minutes every hour throughout the day.

The scenario was a Windows Server 2003, running FileMaker Server 9, with clients running FileMaker Pro 9 on Windows XP. The server was a state-of-the-art HP box running a high-speed striped RAID array. The net result was that the users would be locked out of the file whilst it was backing up. During testing in our office we found that running this on a Mac Server with Mac clients helped improve the situation, in that the clients could still work, albeit it slowly, but still not ideal.

By optimising the server configuration, through cache and various other settings and performing compact save on the file, we managed to get the backup time down to around 2 minutes. This still wasn’t ideal as, the users were often on the phone to their clients and two minutes during a phone call is unacceptable.

After trying various methods, we finally hit on the perfect solution by utilising a 3rd party plug-in that was already in use on this system. The plug-in is called fmDataGuard, by WorldSync, as is predominately designed to record data changes within your system and create an audit log.  This was how we were using the plug-in already in the clients system. Whenever a user created or changed data, a new record would be created in an audit log table, in a separate file, showing what the data was before and after the change and who carried out the change and when.

Another feature of the plug-in is to Roll-Forward or Roll-Back the data from an audit log file to the primary file after a loss of data or a corruption.  Of course one issue is that the audit log file can get quite large (in fact after only 6 months it was already larger than the primary file).  To solve this we created a duplicate of the audit log file so we could archive the audit log every night, thus keeping the daily audit log file extremely small (less than 20Mb).

Putting this all together meant that we only needed to back the primary file up every night and the main audit log file was backed up hourly, but as it is so small, it backs up almost instantly.  The user experience is that they don’t even notice the backup taking place.

If a corruption happens to the main file, the administrator can just restore the primary file from the previous nights backup, the audit log from the previous hourly backup and click the button to Roll-Forward all the data form the audit log back into the primary file.

This solution was also helped by the ability to run an import script directly on the server from a schedule in FileMaker Server 10.  So after we upgraded the client to FileMaker Server 10, the audit log could transfer to the archive audit log automatically every night, after the main nightly backup.

So in conclusion fmDataGuard worked wonders, it’s an amazing product that really helped us to deliver the solution the client required.

Paul de Hallé

Paul de Hallé

Paul de Hallé is the CEO and founder of Linear Blue, having set up the company in 1999 to focus on intuitive database solutions and web integration. Paul is a FileMaker Certified developer; and has also been a key player in the implementation of the FileMaker Certification program since it's inception with FileMaker 7. A regular guest speaker at FileMaker Business Alliance & Technet meetings in the UK and the FileMaker Developers Conference in the USA, along with contributing to various technical publications around the world.

More Posts - Website - LinkedIn

1 thought on “Backing up large files”

  1. A word of warning relating to exporting & importing date & timestamp fields in a server script where your dates are not in MM/DD/YYYY format. There seems to be a bug in FM Server 10 where it interprets dates & timestamps as MM/DD/YYYY when importing even though your server and file are using UK date formats (DD/MM/YYYY). This means that, for example, 1/12/2009 (1st December) becomes 12/1/2009 (12th January).

Leave a Reply