Streamline Database Applications

Balance client resources against server resources

Even the best-designed database cannot overcome the limitations of poorlydesigned or underpowered business applications. Database-oriented applicationsoften suffer when you place too much load on the client, or at the other end ofthe spectrum, when you put too much demand on the database server.

The trick to implementing a robust database application, then, is tobalance the resources of the clients against the resources of the servers. Twopopular approaches to obtaining this balance are multitiered businessapplications and Open Database Connectivity (ODBC) servers.

Multitier Architectures
Desktop applications gain power by moving some processing to a server, or "partitioning"the application. Application partitioning uses middleware to distribute logicacross processes on multiple computers. In the database world, the transitionfrom a file server architecture to a client/server architecture was an earlyexample of moving logic to the server. A file server has no intelligence beyondthe ability to read and write files, so applications using file servers executequery logic at the client level.

SQL Database Management Systems (DBMSs), in contrast, often use aclient/server architecture that executes query logic at the server. Many SQLproducts have additional partitioning capability because they can execute storedprocedures at the server. Stored procedures (or persistent stored modulesin SQL3 terms) are business rules or other logic stored at the server. For SQLdatabases, stored procedures are typically optimized,

precompiled SQL scripts. Stored procedures are also a useful tool forversion control because changing an application's logic requires only one changeat the server instead of changes to multiple client PCs.

Traditional client/server SQL applications follow a two-tier architecture:A server hosts the databases and SQL engine, and clients host application andpresentation logic. As the number of users increases, that architecture canexhibit performance problems.

To overcome these problems, some developers balance workloads bypartitioning logic and adding tiers. A three-tier architecture augments thedatabase server with a separate application server that handles business rulesand related logic. For example, in a desktop accounting application, the salestax calculations are accessible only to code on the PC running the application.Moving the logic to an application server lets multiple client computers accessthe procedure or object that does sales tax calculations.

To implement multitier architectures, some developers use formalapplication partitioning products. These products include Dynasty Technologies'sDynasty, and Oracle's Developer/2000. But other developers want to continueusing familiar programming tools such as Visual Basic (VB) and PowerBuilder.

One way to scale up with traditional two-tier programming languages is tobuild application servers and layer in remote procedure calls (RPCs). You canuse tools such as Greenbrier & Russel's RPCpainter or NobleNet's EZ-RPC.RPCpainter lets you call RPCs from PowerBuilder, and EZ-RPC works with VB,SQLWindows, PowerBuilder, Delphi, C++, and other languages.

Microsoft sees Object Linking and Embedding (OLE) as an important tool forbuilding applications from components. This approach inherently providesapplication partitioning and logic redistribution capabilities. OLE is based onthe Component Object Model (COM), which uses RPCs to access local or remoteobjects.

The version of COM that supports remote objects is Distributed COM (DCOM),formerly Network OLE. DCOM was not available when Microsoft shipped VB 4.0, butthe VB developers included support for Remote Automation Servers so that VBEnterprise Edition users can develop three-tier applications by means of remoteOLE automation. The release of VB 4.0 means that developers can use VB andVisual C++ to develop OLE-automation servers that can service multiple clients,although servers developed with VB are single-threaded.

ODBC Servers
ODBC is a widely used programming interface with SQL databases. ODBCinstalls an infrastructure that includes a driver manager Dynamic Link Library(DLL), loadable database drivers, translation libraries, and other supportingfiles.

You typically configure an ODBC client machine for each database productthat the client accesses. As a result, a given ODBC client sometimes has to runmultiple protocol stacks and maintain separate configuration files for eachdatabase product that the client accesses. The consequence is a client-sideapplication environment that can consume a significant amount of clientresources and administrative overhead.

Software companies have recognized that administering ODBC in organizationswith dozens, hundreds, or thousands of PCs is a problem in need of a solution.The term ODBC server describes a genre of products that simplifies ODBCadministration by implementing a generic ODBC server to manage the interactionsbetween the ODBC clients and the database servers. The ODBC server products letan organization create a thin client architecture by moving drivers, networklibraries, client libraries, and protocol stacks off the client and onto theODBC server. Figure 1 illustrates the classic fat ODBC client architecture.Figure 2 shows the thin ODBC server architecture.

ODBC servers produce a thin client by offloading components such as clientlibraries, network libraries, protocol stacks, and the ODBC Cursor Library. Theresulting client has a single ODBC driver and network transport.

In future issues, Windows NT Magazine will look at ODBCservers that provide connectivity to multiple databases. Some ODBC servers aretargeted at Web developers and use a multithreaded architecture to supportmultiple simultaneous users. Other ODBC servers emphasize the development ofrobust applications. These ODBC servers integrate with Distributed ComputingEnvironment (DCE) or use RSA Data Security public key encryption to providesecure processing for the Internet and other applications that require security.

A Multiple Choice Question?
Both multitiered application architectures and ODBC servers streamline theoverall operation of client/server database applications, but in different ways.Multitiered applications provide relief by distributing the applicationprocessing over a broad set of hardware resources, so you can easily scalemultitiered applications. ODBC servers, in contrast, streamline the operation ofdatabase applications by adding a protective component (i.e., the ODBC serversystem) between the clients and servers.

The ODBC server reduces both the client and server resource requirements bymanaging the bulk of the end-to-end client/server communications. Best of all,you don't have to make a choice between these approaches. You can leverage bothin the same environment if the need arises.