Introduction
The word "applet" evokes software code that handles some small, seemingly
trivial task.
But not all applets are created equally. At Airclaims, an applet is a key
tool for improving customer service, enabling faster, cheaper and more accurate
data migration, and even opening up new revenue streams.
Airclaims is a leading international provider of information and consultancy
services for the aviation industry. At the center of Airclaims' services is CASE
(Client Aviation System Enquiry), which the company says is the most
comprehensive database of commercial aircraft, covering histories of more than
90,000 commercial jets, turboprops and business jets. Approximately 310
Airclaims customers worldwide rely on information from CASE.
CASE runs on SQL Server 2000. Until recently, it was updated once a month
through a full snapshot replication. The database was reorganized and
defragmented, and an offline copy was made and built into an InstallShield
package. It took approximately three hours to build the master CD.
After testing, the master CD was duplicated hundreds of times and shipped to
customers by express or regular mail, depending on service terms. It took about
four hours to burn the CDs, with added time for packaging and shipping.
Big savings, new services
In the summer of 2004, Airclaims began work to bring its database updates
online. Since the company already had a limited Internet version of CASE, some
of the framework and methodology for the migration was in place. The first
challenge was finding the key software tool to automate database
comparisons.
"We did an evaluation of Red Gate's SQL Toolkit and, using one of the sample
databases, quickly established that it was the product to do the job," says Paul
Buxton, Airclaims' lead developer and DBA for CASE.
SQL Toolkit provides access to the APIs and command-line utilities used
within Red Gate's SQL database comparison and synchronization tools. It enables
users to build their own specialized programs for automating installation,
replication and verification.
Airclaims created a .NET applet that used SQL Toolkit to automate database
comparisons. This was augmented by a web service to validate end users, and an
import application to scan, download and apply updates on the customers'
machines. It took only three months for a single developer to put all the tools
in place.
Updates now begin with the .NET applet, which compares the master database
maintained by Airclaims to the copy of the database for the end user. The update
generates a small file - approximately 300K on average. The file is then applied
to the destination database in-house. If it is applied correctly, the file is
automatically zipped and uploaded to a web server.
To receive their updates, customers run the small web service applet that
provides authentication and determines how many updates are required. The
individual daily files are then downloaded and applied via SQL-DMO to bring the
end user's database up to date.
"Our biggest savings are in distribution," says Buxton. "We no longer need to
ship 350 CDs every month. I believe our savings over a year could be �15,000 to
�20,000 after all of our customers migrate to the new Internet CASE."
Buxton says that Airclaims' customers reap three major benefits from the new
service: smaller file sizes for updates, faster implementation of changes, and
no shutdown of the CASE system while updating.
"The ability to receive frequent data updates from a secure web site has been
a major factor in influencing our decision to become a new customer for CASE,"
says Sean Lee of Singapore Aircraft and Leasing Enterprise.
A natural offshoot of the new process is that it enables more price
flexibility and service options. Customers can select daily, weekly, fortnightly
and monthly updates without Airclaims having to change anything in its process
but the subscription web service.
"We now have the flexibility to add to the choice of schedules," says Buxton.
"If we want to introduce quarterly update schedules, we don't need to change
anything in the update creation. The more regular the update schedule, the
higher the premium for that service."
Download
your copy of SQL Toolkit from Red Gate Software to see how well it will work
for you.
