Re: Architecture ideas for n-tier app with diff backends

Hi Peter!

Peter Daniels wrote:

> We are in the proccess of converting our MS Access 97 based application
> to a 3+ tiered app that will be able to use either an Access 97, Access
> 2000, SQLServer7, or Oracle 8.0.5 backend. I would like to open a
> discussion about the caveats, tricks, and tips involved in such an
> architecture. Currently, we are only concerned with the data tier. I
> have a few ideas to fuel the fire:
>
> 1) separating the tiers into presentaion, business rules, data access,
> dbms

  You are venturing directly into the area application servers are designed for. You can concentrate on your value added if you use a ready-built infrastructure that will support enterprise-class applications instead of the man years to roll your own, and maintain and extend it as requirements grow. I'd also recommend Java and the Java Enterprise Standards which most major software and hardware vendors are supporting. A Java application server can provide pooled, managed JDBC access to DBMSes. It can support high performance multi-threaded business logic on the server in a portable way via EJBs, servlets, or other server classes. It can serve static or dynamic HTML as well as providing DBMS connectivity to browser clients, over several protocols like HTTPS for security over third-party firewalls.   A good application server provides inter-client communication, lookup and event services. Ours, for instance, has a zero-administration-client feature to automatically or by request maintain client state by downloading any client- side executables, data files etc from a central site to keep clients up-to-date.

> 2) breaking the data access tier into a single interface for the
> presentation layer and possible multiple child interfaces for actually
> talking to the different dbms.

Not a problem. JDBC and connection pools can be used generically regardless of the DBMS to which a given pool connects. To the extent that the communication is generic SQL, if desired, clients can be switched seamlessly or failed over between DBMSes (instances and/or brands). The application server can asynchronously query DBMSes and maintain FAD (frequently accessed data) to supply to clients, avoiding massive requerying of the DBMS by every client. The server can notify and/or update any interested clients when any FAD changes.

> 3) The compromise between leveraging dbms specific functionality and
> maintaining easy revision control for conceptually similar data access.

JDBC helps maintain commonality of application code base, and DBMS-specific features can be abstracted out to just above the JDBC layer to make the appropriate JDBC/SQL/DBMS-specific calls.

> 4) (a continuation of 3) using straight ANSI SQL in data tier components
> vs dbms specific stored procedure languages.

This depends on how much code is currently invested in stored procedures. The main performance benefit is to ensure you do your DBMS logic where the data is, in the DBMS, not in a client. The biggest mistake is shipping raw data from the DBMS to be processed row-by-row in the client, even if that client is on the same machine as the DBMS. If you can architect using good set-based SQL operations, you will largely have this main advantage in a DBMS-neutral way.
On the otherhand, to the extent that you do not want application-specific SQL exposed, and to the extent that you can install procedures in your target DBMS(es) and handle the differences in the features stored procedures provide in the various DBMSes, they can help to centralize the appropriate business object logic. Business _rules_ should operate in a middle tier, but they should operate on business objects, not raw data. Business objects are manifested in the lower tiers, close to or in the DBMS, and stored procedures (like triggers as a special case) live where they operate, at the data, so no application needs to worry about or is able to circumvent the integrities the stored procedures (especially triggers or other procedurally defined RI) maintain.

> I hope this thread will provide insight for the armies of developers
> required to design for multiple dbms.

> -Peter

Joe Weinstein at BEA, the home of WebLogic

--
PS: Hey folks, we're hiring Java engineers for our WebLogic

    Engineering group in downtown S.F. Send me your resume.

                    The Weblogic Application Server from BEA
         JavaWorld Editor's Choice Award: Best Web Application Server
  Java Developer's Journal Editor's Choice Award: Best Web Application Server
     Crossroads A-List Award: Rapid Application Development Tools for Java
Intelligent Enterprise RealWare: Best Application Using a Component Architecture
               http://weblogic.beasys.com/press/awards/index.htm