Case Study: TV Ontario - Re-building years of databases

TV Ontario (TVO) is a public broadcaster and educational television network in Ontario, Canada.

PROBLEM:

TVO's existing FileMaker installation and infrastructure was becoming unstable due to age. The databases and infrastructure had to be updated in order to maintain data integrity.

TVO runs many of their network operations on FileMaker Pro. They had close to 100 databases spread over four FileMaker Server machines. The databases were in FileMaker Pro 4 and FileMaker Pro 6, running on Mac OS 9. Some of the databases went back as far as 1991.

The databases ran the gamut from simple contact management systems to incredibly complex scheduling systems that interacted with Oracle databases.

The project's goal was to update the databases to FileMaker Pro 7 (current at the time). TVO could no longer purchase Macintosh computers that ran OS 9. FileMaker Pro was no longer available for OS 9 and ran slowly in Apple's compatibility mode. Updates to hardware and software were forcing TVO to upgrade in order to keep their systems running.

The FileMaker servers were also running on Mac OS 9. OS 9 has a maximum file size limitation of 2 gigabytes. Several of the databases were approaching this limit and action had to be taken so that the databases could continue to operate.

THE PROJECT:

Analyze the databases, define a plan for moving forward to FileMaker Pro 7, build the databases and migrate the data forward, all within a live production environment. TVO did not maintain a separate development and production set of servers for FileMaker.

ANALYSIS:

The first step was to analyze all of the database. Of the near 100 databases we knew that some were obsolete and would be retired. They were easy to handle; they were given a simple conversion to the FileMaker Pro 7 file format and archived. This way they could be retrieved in the future if necessary.

Some of the databases dated back to the early 1990's. There was practically no documentation for any of the databases. I was going in to this project blind and without any kind of safety net.

Other databases represented duplicated information. These databases were merged into a new single database.

The area that represented the highest level of work were the databases containing complex business logic. The coding was intrinsically tied to the version of FileMaker the database was originally created in. After looking at these files, we discovered that simply converting the files from the old format to the new format broke a lot of the coding as the modern equivalent had changed. These databases were rewritten from scratch to take advantage of new functionality within FileMaker and to eliminate obsolete coding.

PRODUCTION:

After the analysis was complete we put together a plan on how to tackle the databases. The first database tackled was a relatively simple system which was done as a proof-of-concept to be shown to management to validate the entire project. This was done quickly and successfully.

The second database was a very complex docket management and scheduling system. These two databases represented extremes for the project; simple vs. very complex. This was also completed successfully.

The first two databases served as a test to develop a common structure for the databases. This was done to improve the usability and to develop a standard, modular interface which would work with the entire library of databases.

REQUIREMENTS:

Before a database was moved into production the department stakeholders were interviewed to determine which features needed to be moved over from the old system, which were obsolete, which didn't work and what features were missing. The goal was not only to move the data to a modern platform, but to improve the quality of the data and the usability of the databases.

TESTING:

During the production of each database it would be shown to the key users and tested for functionality, completeness and usability. Any revisions would be folded back into the system before launch.

RESULTS:

The revised databases were deployed on a single FileMaker Pro server running FileMaker Pro 7 Server. The library of databases went from 100 databases to 40. Several systems were dropped, others merged and two left in the previous version of FileMaker as they were to be moved to Oracle at a later date.

The project took nearly 18 months and was completed by 1 person with almost no assistance from the staff at TVO.

Never underestimate the benefit of planning before beginning any production.