From its headquarters location in Hurst, Berkshire, and from the historic Trafalgar Square section of London, Linear Blue, a FileMaker Business Alliance Platinum Member, provides advanced database design and web technologies development to clients throughout the United Kingdom. The company serves the needs of companies who routinely manage large amounts of valuable information.

One such company’s main database file was 1.2 GB in size. While not exceptionally large a such files go, it nevertheless was taking four to five minutes to backup each hour. And during that backup it was essentially unavailable to the busy company who relied on its accessibility to meet the demands of their customers. While subsequent hardware and software tweaking cut that backup time to two minutes per hour, the company still was not satisfied.

An innovative use of fmDataGuard solved this backup problem for Linear Blue and its client. CEO Paul de Halle employed the native capability of fmDataGuard to update a backup by invoking fmDataGuard’s roll-forward feature. "We hit on the perfect solution. We only needed to back up the primary file once every night and then the main audit log file was backed up hourly. It is so small, it backs up almost instantly," said de Halle.

"If a problem occurs in the primary file, the administrator can just restore the primary file from the previous night’s backup, employ the audit log from the previous hourly backup and click the button to Roll-Forward all the data from the audit log back into the primary file. fmDataGuard worked wonders, it’s an amazing product that really helped us to deliver the solution the client required," de Halle noted.

How does such a system work? As users commit add, change, and delete operations, fmDataGuard writes the information about what they did to a separate table including both the before-change and after-change values.  The roll-forward mechanism in fmDataGuard allows an administrator to find all entries added to the log after the date and time the last backup was made. The administrator can then apply those changes to that backup with a single function call. This re-performs each add, change, or delete operation for any table throughout the database. The backup is thus updated and ready for use.

This left management of the audit log itself as an issue. Again, Paul de Halle: "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. This kept the daily audit log file extremely small, less than 20Mb. 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."