Architecture & DesignBuilding Database Applications with Remote Support in Mind

Building Database Applications with Remote Support in Mind

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Introduction

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.

STARS II+ is now installed at over one hundred TV stations and cable networks across the USA and Canada, many of which do not have a full-time IT staff. The customers that purchase an embedded application have a reasonable expectation that your support organization will be able to respond to their support calls, whether or not the defect lies in your own code or that of a third party, such as the DBMS platform. For that reason, it was critical that VCI that select a database platform that could both perform well, and would require little or no on-site support. Routine support tasks must be completed, even in the absence of a paid, full-time administrator. Our support and engineering staff would not have a constant, live connection to these remote databases, nor would we have the time to check each and every site for problems on a regular basis.

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:

  1. 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.
  2. 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.

  1. 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.
  2. 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
  • Chat
  • 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.

Design Unattended Database Backups

The design and implementation of disaster recovery procedures is very specific to the nature of the application being maintained. There’s no “one size fits all” scheme for every possible set of requirements. Applications that are highly transaction intensive may want to take more frequent log-only backups, while others may only require a nightly full backup. For an excellent tutorial on developing robust backup and recovery procedures, see chapter 9 of Breck Carter’s book SQL Anywhere Studio 9 Developer’s Guide, ISBN: 1-55622-506-7.

The simplest example is a nightly event that performs a complete image backup to a folder on the server’s file system, and truncates the active transaction log.

CREATE EVENT "nightlyFullBackup" ENABLE AT ALL
HANDLER
begin
   BACKUP DATABASE
   DIRECTORY 'd:databasebackup'
   TRANSACTION LOG TRUNCATE
end
go
   ALTER EVENT "nightlyFullBackup"
   ADD SCHEDULE "nightlyFullBackup_1"
   START TIME '00:00:01' ON
   ('Tuesday','Wednesday','Thursday','Friday','Saturday')
go

This example could be made more dynamic by retrieving the backup location from a database column from a “settings” table, parsing that value into the BACKUP DATABASE statement, and using EXECUTE IMMEDIATE, as follows:

CREATE EVENT "nightlyFullBackup" ENABLE AT ALL
HANDLER
begin
   declare backupFolder varchar(255);
   declare backupCmd varchar(255);
   set backupFolder = (Select folderName
   From dbOptions );
   set backupCmd = ' BACKUP DATABASE ' ||
                   ' DIRECTORY ' || backupFolder ||
                   ' TRANSACTION LOG TRUNCATE';
   EXECUTE IMMEDIATE WITH ESCAPES OFF backupCmd ;
end
go

In a high-transaction or replicating environment, it might be necessary to perform “log only” backups at regular intervals throughout the business day. The Event Scheduler can be directed to execute events at specific recurring intervals, as follows:

CREATE EVENT "hourlyLogBackup" ENABLE AT ALL
HANDLER
begin
   declare backupFolder varchar(255);
   declare backupCmd varchar(255);
   set backupFolder=(select folderName from dbOptions);
   set backupCmd = ' BACKUP DATABASE ' ||
                   ' DIRECTORY ' || backupFolder ||
                   ' TRANSACTION LOG ONLY ' ||
                   ' TRANSACTION LOG RENAME MATCH ';
   EXECUTE IMMEDIATE WITH ESCAPES OFF backupCmd ;
end
go
   ALTER EVENT "hourlyLogBackup"
   ADD SCHEDULE "hourlyLogBackup_1"
   BETWEEN '10:00:00' AND '18:00:00'
   EVERY 1 HOURS ON ('Monday','Tuesday','Wednesday','Thursday',
                     'Friday')
Go

Event handlers also can be written to monitor critical server resources, such as low disk space or excessive database file fragmentation. When the item that is being monitored is something that can be corrected as well, such as table or index fragmentation, the event itself can be written to repair the condition. Here’s an event that runs at 4:00am on Sunday morning, and detects for indexes that have grown beyond four levels deep. If any are detected, they are reorganized on-the-fly. This event demonstrates another powerful feature of SQL Anywhere—the ability to write SELECT statements across stored procedures.

HANDLER
BEGIN
   declare tName varchar(60);
   declare iName varchar(60);
   declare iType varchar(60);
   declare stmt varchar(60);
   declare err_notFound exception for sqlstate value '02000';
   declare indexLevels no scroll cursor for
SELECT
   tableName,
   indexName,
   indexType
FROM sa_index_levels()
WHERE
   levels >= 4;
OPEN indexLevels;
   indexLoop:
LOOP
FETCH NEXT indexLevels
INTO tName,
   iName,
   iType;
IF sqlstate = err_notFound then
LEAVE indexLoop
END IF;
SET stmt='REORGANIZE TABLE ' || tName;
CASE upper(iType)
WHEN 'PKEY' THEN
SET stmt=stmt || ' PRIMARY KEY;'
WHEN 'FKEY' THEN
SET stmt=stmt || ' FOREIGN KEY ' || iName || ';'
WHEN 'NUI' THEN
SET stmt=stmt || ' INDEX ' || iName || ';'
END CASE ;
MESSAGE string(now) || ' Event indexReorg: ' || stmt to console;
EXECUTE IMMEDIATE stmt ;
END LOOP indexLoop;
CLOSE indexLevels;
   message string(now(*)) || 'Event indexReorg completed.' to console;
END
go
ALTER EVENT "indexReorg"
ADD SCHEDULE "indexReorg_1"
START TIME '04:00:00' ON ('Sunday')
go

When the condition being monitored requires physical access to the server machine, such as a low disk space condition, a message to the console is inadequate. More immediate attention is required. With that in mind, SQL Anywhere has the ability to send an e-mail, either through the MAPI interface, or through an SMTP server.

Note for Microsoft Exchange users: Microsoft, in an effort to cut down on SPAM, has placed specific restrictions in the MAPI interface that prevent the automatic sending of e-mails from non-Outlook clients. This makes the simple MAPI interface very undesirable for use in an unattended server-based application. In these environments, it may be more beneficial to research the use of SMTP for outbound e-mails.

Here’s an example of an Event that detects free disk space on the main database drive. If the database uses multiple dbSpaces, it will examine each of the drive letters referenced by the different dbSpaces. If the free disk space on any drive falls below 10%, this event sends a proactive e-mail to an administrative account stored in the dbOptions table.

CREATE EVENT "lowDiskSpace"
TYPE "DBDiskSpace"
WHERE event_condition('DBFreePercent') <= 10
ENABLE AT ALL
HANDLER
BEGIN
   declare sMailType char(4);
   declare sMailAcct varchar(60);
   declare sMailPwd varchar(60);
   declare sMailServer varchar(60);
   declare iSMTPPort smallint;
   declare sRecip varchar(60);
   declare SiteName varchar(60);
   declare iStatus integer;
SELECT
   sMailType = mailType,
   sMailPwd = mailPwd,
   sMailServer = mailServer,
   iSMTPPort = mailPort,
   sRecip = adminEmail,
   SiteName = site_name
FROM dbOptions ;
CASE sMailType
WHEN 'MAPI' THEN
   iStatus =
CALL xp_startMAIL(
   mail_user = sMailAcct,
   mail_password = sMailPwd );
WHEN 'SMTP' THEN
   iStatus = CALL xp_startSMTP(
      smtp_sender = sMailAcct,
      smtp_server = sMailServer,
      smtp_port = iSMTPPort );
END CASE;
If iStatus <> 0 then
   MESSAGE 'Low Disk Space Warning!! ' ||
           'Cannot start Mail session. ' ||
           'status = ' || string(iStatus)
      to console;
   return
end if;
iStatus = CALL xp_sendMail(
   recipient=adminEmail,
   subject='Free disk space below 10% at: ' || SiteName
) ;
If iStatus <> 0 then
   MESSAGE 'Low Disk Space Warning!! ' ||
           'Cannot send e-mail to: ' || adminEmail ||
           ' via ' || sMailtype || '. status = ' ||
   string(iStatus)
   TO console ;
end if;
CALL xp_stopMail() ;
END

Identification and Diagnosis of Problems

Problem Diagnosis in the On-Site Database

Many times, the support call comes in, and the only information is “the system is slow”… It’s up to you to diagnose exactly what and where the problem is. This section presents some quick tips and non-invasive queries that can be used against a production database that provide excellent visibility into the inner workings of the database engine, helping pinpoint common causes associated with degrading performance.

Setting the “Remember_Last_Statement” engine property

The .zl engine switch enables this feature, which “remembers” the last statement executed by any active connection. Turn this feature on, and leave it on. It costs very little in terms of execution overhead, and lets you see the exact SQL statements that each connection is using. This option can also be enabled and disabled dynamically, with the following command:

call sa_server_option( 'remember_last_statement', 'ON' );
Using “Request_Level_Logging”

Request_Level_Logging is a feature of SQL Anywhere that provides a comprehensive trace of all requests that the database engine processes. This can be a valuable debugging technique when trying to determine exactly which query is performing poorly.

Note: use of this feature can add significant overhead to the database engine, and should be used judiciously. If the output is redirected to a file, the file can grow rather rapidly.

This feature is enabled with the following commands:

call sa_server_option( 'request_level_logging', 'SQL' );
call sa_server_option( 'request_level_log_file', "<output file>' );

After the desired results are captured to the output file, turn request_level_logging off with:

call sa_server_option( 'request_level_logging', 'none' );
call sa_server_option( 'request_level_log_file', '' );

Finally, the output file that is generated by request_level_logging can be analyzed with two system stored procedures:

call sa_get_request_profile( 'request_level_log_file'
[,connection-id ] );

sa_get_request_profile() processes the generated log file, and populates the temporary table SATMP_REQUEST_PROFILE. If an optional connection_id argument is used, the results will be filtered down to that specific connection_id. Once the satmp_request_profile table is populated, it can be queried to identify potentially poor-performing queries.

call sa_get_request_times( 'request_level_log_file'
[,connection-id ] );

sa_get_request_times() is a similar procedure to sa_get_request_profile(), but it populates the satmp_request_times table.

Detecting Blocked Connections

As you’ve seen, SQL Anywhere has the ability to perform SELECT operations across the results of stored procedures. With this capability as a foundation, it’s possible to write queries that can detect all kinds of anomalous conditions.

For example, here’s a query that finds any connections that are blocked on another connection, and the statements that both connections are executing.

Note: This query is much more effective when the “remember_last_statement” engine property has been enabled.

SELECT
   blockedActivity.number blockedConn,
   blockedActivity.userID blockedUser,
   datediff( mi, blockedActivity.lastReqTime, now())
      as blockedInMinutes,
   blockedActivity.lastStatement blockedStatement,
   blockedInfo.nodeAddr blockedIPAddr,
   blockerActivity.number blockingConn,
   blockerActivity.userID blockingUser,
   datediff( mi, blockerActivity.lastReqTime, now()) blockingSince,
   blockerActivity.lastStatement blockingStatement,
   blockerInfo.nodeAddr blockingIPAddr
FROM
   sa_conn_activity() blockedActivity
JOIN
   sa_conn_info() blockedInfo
ON blockedInfo.number = blockedActivity.number
JOIN
   sa_db_info() dbInfo
ON blockedActivity.DBNumber = dbInfo.Number
JOIN
   sa_conn_activity() blockerActivity
ON blockedInfo.blockedOn = blockerActivity.number
JOIN
   sa_conn_info() blockerInfo
ON blockerActivity.number = blockerInfo.number
ORDER BY
   blockedInMinutes DESC
;

This query uses the sa_conn_activity() and sa_conn_info() procedures to report the current elapsed time of all active queries being processed by the SQL Anywhere engine.

SELECT
   Activity.number,
   Activity.userID,
   datediff( ms, Activity.lastReqTime, now()) / 1000.0 as duration,
   Activity.lastStatement,
   Info.nodeAddr IPAddr,
   Info.reqType requestType
FROM
   sa_conn_activity() Activity
JOIN
   sa_conn_info() Info
ON Info.number = Activity.number
WHERE
   requestType not in ('COMMIT', 'CURSOR_CLOSE')
ORDER BY
   Activity.lastReqTime;

This particular query was especially helpful recently at one of our customer sites. The CHUM Television group hosts our application client software on a cluster of CitrixTM servers, and the users were complaining of degrading performance. The diagnostic query above identified the long-running queries, but we noticed that the majority of these had a requestType of ‘CURSOR_FETCH’. This indicated that the database engine had completed the execution of the queries and was trying to send the result sets back to their requestors, which were hosted client sessions on the Citrix servers. An examination of the Citrix servers revealed that they were significantly underpowered, and were trying to service three times more client sessions than originally specified. By adding more Citrix servers into the cluster, they were able to relieve the client-side bottlenecks, and our application performance returned to normal.

Retrieving a Local Copy of the Remote Database

There are some diagnostic tests that simply cannot be executed against a production database with active users. One example is the sa_table_page_usage() stored procedure, which requires exclusive access to the database during its execution, and can take an hour or more to complete. Consequently, there are times when the best recourse is to bring a backup of the production database into your labs, mount it on a local server, and execute the diagnostic tests on that machine. This allows you to stop and restart the database at will, step through procedures, triggers and events in Sybase Central’s integrated debugger, and generally execute any test that might require exclusive access to the database.

SQL Anywhere databases can be transferred and mounted on a different server with no prior setup required because they are completely binary-compatible across platforms. In addition, SQL Anywhere .DB files are highly compressible, and utilities like WinZipTM, WinRarTM, can be used to shrink the .db and .log files down prior to transmission. If the zipped file will exceed 4 Gb in size, tools such as WinRar can partition the archive automatically into multiple 700 Mb slices, which are small enough to be burned onto a CD.

Remember that a SQL Anywhere database may consist of one or more physical .DB files (if multiple DBSPACES are used), and one .LOG file. If “image” backups are used, it will be necessary to transfer all of these files before the database can be mounted on a local server.

The alternative is to use the Archive backup method, which creates a single archive file containing all of the necessary database components. Once this file has been zipped and transferred locally, the individual files can be extracted with a single RESTORE DATABASE operation.

Conclusions

Supporting remote embedded applications can a difficult and time consuming endeavor. The hard work and effort that went into developing the application suite can be all for naught with one lost production database.

So, develop your plan to remotely support customers while still in the development phase. In particular, plan to:

  • Prepare the Site: The first step is careful planning and adequate preparation of the remote site.
  • Automate routine tasks: Build in functionality so routine tasks are completed even when no IT administrator works at the client site.
  • Diagnose the problem: Identify key tools and techniques that could be used to diagnose problems in a production database.
  • Retrieve a local copy of the database: Prepare for those instances where a database may need to brought into the lab to properly diagnose and repair the problem.

The costs associated with software support tasks are not often directly visible, but certainly have a direct impact on the bottom line for companies such as VCI. With now well over one hundred remote customer installations of STARSII+, the four strategies listed above have been critical for us in minimizing these hidden costs, and maintaining a low TCO for our customers.

About the Author

Paul Horan is a senior consultant with Washington, D.C.-based Cynergy Systems. Prior to joining Cynergy, Paul was the Senior Architect at Video Communications, Inc., of Springfield, MA. He has over 15 years of experiencedeveloping client/server and distributed applications. Paul’s areas of expertise include relational database design and implementation, and he has been working with the Watcom/SQL Anywhere database platform since 1993. Paul serves on the iAnywhere Customer Advisory Panel, has been a member of TeamSybase since 2000, and is a frequent speaker at the annual Sybase TechWave User Conference. Currently residing in Buffalo, NY, Paul can be reached via e-mail at paul.horan@cynergysystems.com.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories