In-Depth
A Case for Migration
ADC Legal Systems recently faced a problem that is increasingly shared by softwarepublishers — how to scale a client-based back-end to the client/server. Founded in1985, ADC Legal Systems publishes Perfect Practice, a 32-bit information managementprogram for law firms. Originally a DOS product, we successfully ported the program toWindows with the longer-term goal of making it a robust, client/server application.
Perfect Practice tracks information for an unlimited number of cases, parties, jobs orsupport calls, and necessitates the sharing and updating of information among multipleusers. Activities for each case or party include notes, to-dos, appointments and timetransactions. All activities are viewable by case or staff member. The system includesshared calendaring and docketing functions that allow users to color-code to setpriorities. Time transactions integrate to Perfect Practice billing/accounting systems, aswell as other standard billing programs.
The application must support complex management requirements and information sharing atmultiple sites, while enabling rapid data entry. To ensure flexibility in the design, thedatabase was normalized, resulting in many complex data relationships. Since the designwas not flat file, avoiding index and data corruption was a major priority.
Our DOS product was not client/server, so we believed we could start with a desktopdatabase for our Windows product, then scale to client/server at our leisure. We quicklyfound out that the complexity and instability of the networked Windows environment madethe desktop database unacceptable.
There were several problems inherent to using a desktop database within the networkedWindows environment. One of the biggest problems was that for many reasons, Windowsclients crash frequently. Any one of a host of variables can cause these crashes:hardware, network, Windows client software, network operating system, database,applications and users. In fact, we’ve seen hard disk drive problems, Windowsreporting a "general protection fault," applications shutting down abruptly,network operating systems not allowing clients access to shared data files and usersturning off their computers at the end of the day while running the database application.Each crash typically corrupted the shared network data, because the client was in themidst of writing its changes. The usual extent of the corruption was index corruption, buton occasion data files were rendered useless. In one case we found part of a Word documentwritten into a database file!
A related problem created by components of the network is they did not cause clients tocrash, but insidiously corrupted data or indexes. Such things as "non-certified"NT or Novell Network Interface Cards (NIC), "bad" coaxial cable, non-Category 5twisted-pair cable on a 100 Mbit network and network hubs with bad ports could cause, orbe, a factor in the problem.
Another was slow network speed caused by the increased network traffic, since largeamounts of data were being sent back and forth across the network. Problems with harddisks, network cabling, Network Interface Cards and network hubs could magnify the impactof this issue.
Due to the sheer number of variables, when a crash or other problem occurred, justfinding out what was wrong was an overwhelming task. The only way to address the number ofvariables was to eliminate as many as possible, and then painstakingly check and replacethe remaining components in the system. Throughout the process we would look for recurringpatterns to help us narrow the scope. We might bring each suspect client up one at a time,to see which was causing the problem, then further eliminate the components of that clientsystem.
In addition to instabilities in the operating system and other applications crashing onclient PCs and network hardware problems, later releases of Windows 95 had redirectionDLLs that Microsoft reported could cause data corruption. So with any given crash, indexcorruption (or slow network speeds) could have been caused by a network utility, a generaloperating system problem, other client applications, specific clients, network cards oreven hubs.
When using a desktop database solution for Perfect Practice, we could count on at leastone of the above problems occurring to at least one client each day, resulting inthree-to-eight hours worth of problem solving, per problem; although some of theseproblems could stretch over several days.
Using a client/server solution, many of these problems disappeared. With client/server,the system only makes changes to data files on the server rather than on each client doingthe work, greatly reducing the chance of data corruption. With a server-centric database,clients only receive the specific data they need, rather than entire blocks of data. Thisreduces the opportunity for corruption and increases network speed. Finally, aclient/server database insulates data from corruption caused by client crashes. If anissue occurs, you know where the problem is — at the file server or related networkhardware.
One of the challenges we had as a vertical market developer was finding a vendor whounderstood our business and was willing to give us the level of support necessary for aneffective implementation. Major database vendors seem more comfortable with developerswithin large corporations which typically have just a few client/server engines atcorporate offices and a database staff at each office to support them. The embeddeddatabase developer needs a database that can be shipped to and installed by clients, withminimal technical skills, at hundreds of sites. Most vendor databases that we reviewed,were not ISAM-based and therefore needed more ongoing modifications than our clients wereprepared to handle.
Our original path to client/server was to develop using INPRISE’s "GoldenGate" model — developing the application to the Borland Database Engine (BDE).The promise was that if we wrote to the BDE, then we could easily scale from a single- ormulti-user database to a client/server database. We developed the product with Paradox, anative database for the BDE.
Believing that the BDE isolated us from most implementation details of particularINPRISE databases, we decided to enhance our software to support INPRISE’s InterBase.Unfortunately, we found that even though we had written to the BDE, we were faced with asubstantial rewrite anyway. Even some of our basic utilities would not work correctly withInterBase. Surprisingly, InterBase did not support all the field name conventions ofParadox.
We use SQL scripts to develop utilities to do such things as reindexing, doingconversions between databases and for general housekeeping. We quickly discovered that ourSQL scripts would have to account for significant syntax differences between Paradox andInterBase, increasing our development effort.
We concluded that INPRISE didn’t address the needs of a vertical market/embeddeddatabase developer. It did not provide us an easy migration path from desktop toclient/server, forcing us to rewrite code and scripts, did not provide us the level ofsupport we expected, and required too much ongoing tuning for our non-technical clients.We needed a database vendor that wanted to treat us as a partner.
That’s when we decided to look for a company that specializes in embeddeddatabases. Btrieve was well-known to us and used by one of our strategic partners. It costless than InterBase, and their staff seemed attuned to the needs of vertical marketdevelopers. However, during our research, we found that Btrieve/Scalable SQL does nothandle blob/memo fields well. In any given table only one blob or memo field is allowed,and it has to be the last field in the table! One-third of our tables had more than oneblob or memo field; none had one as the last field. Implementing Btrieve/Scalable SQLwould have required significant modification to our database design and required ourdevelopers to write "housekeeping" routines that should be handled by thedatabase engine.
Additionally, when we researched Btrieve, we found that its Pervasive SQL did not havea single/multi user version. This would have required us to support two databases: Paradoxfor single/multi user and Btrieve for client/server, again significantly increasing ourdevelopment effort and complicating future support.
When we found Extended Systems’ Advantage, we were very impressed with itscapabilities. Advantage is a database that scales from single/multi user to trueclient/server. With Advantage, we only needed to write one database interface for bothlocal server (desktop) and client/server. Its Data Architect utility was able to convertour data files the first time out of the chute with no modifications. Although we did haveto make field name changes, we understand that the latest version of the product supportslonger field names.
With the new Advantage Data Type (ADT) and tools that Extended Systems offers,conversion from Paradox to Advantage is a smooth process. For those requiring SQL support,the Advantage ODBC driver, in our experience, provides excellent performance. Displayingcomplex data relationships through SQL joins is central to our program design. TheAdvantage ODBC driver gave us the tool to accomplish that, with no compromise in speed.
The other factor that we were looking for was solutions-oriented support. When we raninto issues, response from the Extended Systems team was very fast and always included agood workaround. For example, early after our initial conversion, the system was reportinga "5086 error" whenever the program did a DbiForceReread (from TBDEDataSetInternalRefresh).
Support people came back within hours with a solution of forcing a reread to thedatabase, using an Advantage database function, which solved the problem. Even whenproblems were not the fault of the Advantage database, Extended Systems personnel pointedus to the right solution. Additionally they offer developers a forum for learning theproduct through developer conferences.
Developing in the current software environment necessitates learning several languagesand/or Application Programming Interfaces (API). Currently, there are four major Windowsenvironments, two major network environments, software installation languages and shiftingprogramming standards, in addition to the development languages used by any particularsystem. Extended Systems provides components to the Delphi, Clipper and FoxPro developer,which encapsulate most of the database API calls into a native format, reducing the levelof development required of the end-user developer. In this environment, it is unusual butappreciated when a vendor goes out of their way to help their customer quickly becomeproductive.
After having installed Advantage at 40+ sites, we have found its performance to beexcellent. The improvement in the speed of SQL queries has been the most dramatic. A 15-30second join query with the Paradox Local SQL plummeted to 2 seconds using the AdvantageODBC driver. Overall, Advantage runs one and one-half to two times faster than Paradox ina multi-user configuration.
Most importantly, even Windows sites with unstable networks and multiple applicationsaccessing the BDE require less support with the installation of the Advantageclient/server. Advantage insulates the application from potential index, data or BDEconfiguration file corruption. From our perspective, the change to client/server wasclearly worthwhile. And it was made easier by using a product that lends itself to theneeds of a vertical developer — by focusing on ease of use, high performance and lowmaintenance.
About the Author:
Rich Woodman is Senior Programmer/Development Manager with responsibility for newproduct development at ADC Legal Systems.