Building Database Applications with Remote Support in Mind
There's an old adage that states, "Build a better mousetrap, and the world will beat a path to your door." In the software development industry, this "mousetrap" may often take the form of an "embedded" application designed to support a specific business process or function for remote customers. An embedded application is one in which the entire technical architecture (hardware, application software, and DBMS) is installed completely at the customer site. Although the challenges of designing and developing an embedded software package are obvious, the effort of providing remote support for the deployed application is often underestimated. Careful planning and preparation prior to installing the application can significantly reduce the headaches associated with supporting a remote embedded application suite. If the world beats a path to your door, they will expect the door to be answered promptly....
Video Communications, Inc. (VCI), is an independent software vendor based in Springfield, Mass, and provides an industry-leading Sales, Traffic, and Billing software suite for television stations and cable networks. In mid-1995, it began a project to completely rewrite and redesign its flagship software product, STARS IITM. The goal of the project was two-fold: to replace the existing character-mode DOS interface with a Windows-compatible graphical UI, and to replace the existing MUMPS database with the SQL Anywhere relational DBMS from Sybase iAnywhere. STARS II (and its successor, STARS II+) is a fully "embedded" application. No components of our software are hosted at VCI or with a third-party service provider.
The seeds of any successful approach to customer support should be planted long before the product is released to any client sites. Our experience has pointed out two critical areas that can be considered "pre-release" action items:
- Preparing the Site: Develop policies and procedures that govern how you can gain access to the remote machine(s) at the client site. Select and standardize on a remote access software package.
- Automating Routine Tasks: Construct software routines that automate routine administration tasks such as backups, disk and database defragmentation, as well as proactive tasks such as monitoring free disk drive space.
The next two areas deal with the identification and diagnosis of problems, once they have been fielded by the first-line support personnel. What appears as a "slow" or "stopped" system can actually have many different root causes, and a critical success factor of any support process is the reduction in the time spent researching these root causes.
- Problem Diagnosis: This section will present tips and techniques to assist in the diagnosis of specific problems with an embedded SQL Anywhere database. These may be executed directly in the customer's on-site database with little or no impact to production performance.
- Retrieving a Local Copy of the Database: This stage represents an "escalation" of any problem that couldn't be resolved directly in the customer's on-site database. For cases of this nature, it may be necessary to retrieve a copy of the database into your software labs for further analysis.
Pre-Release Action Items
Preparing the Site
Establish Internet Connectivity via Remote Control Software
The most important issue, and certainly the most politically sensitive, is one of connectivity and access to the remote database server. If your organization is contemplating an embedded database solution, it is absolutely critical to establish a policy that allows your support and engineering staff to access and administer the database server remotely. Basic support tasks such as configuring the server command line parameters, zipping up and transferring backup copies of database files, or diagnosing slow-performing queries simply cannot be conducted over the phone.
In the mid 90s, this meant giving the server a static IP address, and placing it outside the firewall or in the "DMZ," which exposed your clients' data to the open Internet. Understandably, this is neither an effective nor secure solution. Our first implementation of remote control software was with Symantec pcAnywhereTM, over a 56 Kb modem dial-up line. This was a secure, but aggravatingly slow and ineffective option for us.
Today, there are several highly secure and low-cost (even free!) solutions for remote desktop control that allow servers to stay behind NAT layers, routers, and firewalls. Some important additional features to look for in a remote control package are:
- Access to machines behind NAT, router, and firewalls
- File transfer
- Reboot and reconnect
- Screen scaling
- SSL encryption
- Access audit trail (an often-overlooked feature, now more important than ever with the advent of Sarbanes-Oxley)
VCI now has standardized on the SmartTechTM software from Webex, and we now have instant high-speed access to the database server at each of our installed client sites.
Automate the Administration and Maintenance Tasks
Once you turn your embedded software package over to the customer, it's foolish to assume that the customer will A) understand administration and maintenance tasks; B) agree to do them rigorously; and C) perform them correctly. Consequently, the responsibility of identifying these tasks, automating them, and making them as foolproof as possible, is up to you. Fortunately, SQL Anywhere version 9.0.2 offers many features to assist in the development of "hands-off" administration and even proactive maintenance.
SQL Anywhere provides a full set of command line utilities, such as DBBACKUP and DBVALID, that can be scripted and scheduled for periodic execution in BAT files or Unix shell scripts. One possible downside to BAT files is that the connection parameters (UID and PWD) can be entered in open text. This potential security hole can be avoided in a couple of ways. The first way makes use of the dbfhide utility to provide simple encryption of the contents of a configuration file that stores the command-line options. The configuration file can then be used with the "@" parameter; for example, dbbackup @config_file. Another approach is to create a separate database account specifically for backup operations. If you GRANT REMOTE DBA and nothing else to this account, there's very little that a malicious user can do, even if they do gain access.
For the more adventurous, the DBTOOLS API libraries offer programmatic control over these utilities on the Windows platform. Writing C/C++ utilities that invoke these APIs would also provide a measure of internal control over their execution.
The feature that we've found to be the most powerful and flexible is the integrated Event Scheduler. SQL Anywhere comes with an internal scheduler, and events can be programmed to run at regularly scheduled intervals, or linked to specific conditions that SQL Anywhere can detect, or execute by request. Think of them as triggers that aren't associated with any specific table in the database. There are several clear benefits to using events over BAT files or custom written application wrappers:
- Event handler scripts are written in SQL, not in C/C++ or BAT file syntax
- Event handlers are stored in the database, not out on the file system
- External authentication is not required—no passwords written into BAT files
Events and the Event Scheduler can become the foundation of your application's self-diagnostic capabilities.
Page 1 of 3