Business Logic

More and more, we will see a move to a three-tiered client-server model. Three-tiered products are currently new, expensive, and proprietary. This presentation's architecture leverages some of the advantages of a true three-tiered model with a mainstream DBMS product, SQL Server.

All access to the server should be in the form of stored procedure calls. These functions are elementary business processes: a process which leaves the database in a consistent, valid state before and after execution (i.e. a "unit-of-work").

A single business function will be encapsulated in a single stored procedure. All commit/rollback is handled on the server. That is, a transaction will not span multiple client calls. However, one call may implement multiple SQL statements. For example, a "create customer" call may involve a series of validations not defined in the database schema.

The stored procedure should implement as much data validation logic as is possible, so that data validation rules are secured and managed on the server.

Complicated stored procedures can make use of new SQL Server specific functionality: T-SQL cursors, macro stored procedures, etc.

The stored procedure should call BEGIN TRAN. After executing every SQL statement, the stored procedure should check for an error with the global @@ERROR variable. If an error occurred, the stored procedure should ROLLBACK the transaction and RETURN. If the stored procedure completes successfully, it should COMMIT the transaction.

The advantages of using stored procedures exclusively for data access include:

• Better performance and reduced server load, since all SQL is precompiled.

• Easier change management: as long as the stored procedure function prototypes don't change, back-end database changes will be transparent to client applications and will not require client-side software updates.

• Better control over database access, since only "approved" SQL is permitted to run against the server. System administrators have the opportunity to audit all database access methods.

• Easier/tighter security administration, since the only entry point to the database is through stored procedures (see below).

• Less network chat, since fewer calls are made by the client and any required cursor processing takes place on the server exclusively.

• More opportunity for asynchronous server processing, since the client does not have to be involved in complicated transactions.

• A solid foundation for migration to emerging three-tiered client-server products.

This model works well in a DSS environment, but sometimes complex OLTP functions can't be implemented this way due to limitations in server functionality. For example, it may not be possible to create a complex "create order" transaction that involves many line items with a single stored procedure call.