Adding Database Access to ASTA Servers

ASTA Servers are Application Servers that handle database connections, threads and messaging to allow remote ASTA thin clients applications to be deployed. ASTA has abstracted the complete database process in order to allow any Delphi 3rd Party Database component to be used on ASTA servers and ASTA currently supports 20 different 3rd Party Delphi Database components on ASTA servers. This document will detail the events and properties of the AstaServerSocket that can be used to Database and Thread enable an ASTA Server.

Threading

ASTA uses non-blocking, event driven sockets so that the sockets do not thread but will be very responsive and scale well. ASTA does add threading to the database capabilities. One of ASTA's design goals is "To do all threading internally so that Developers need not be concerned with threading issues". ASTA Servers support 3 different threading models: Single Session, PersistentSessions and Pooled Sessions. The AstaServerSocket provides properties, events and methods that allow you to tune your ASTA server for your needs.

ASTA Server Database Abstraction

For full Client Side SQL access and in support of server side database coding the following Database activities must be supported.

• SQL Selects
• Metadata Requests
• Blob Support
• Execute Parameterized Exec Queries
• Transactions
• Exception Handling
• Connect and Login to Database

We will use the recommended threading model for ASTA servers in this example, the "Pooled Sessions" threading model which makes the most efficient use of the most costly database operation (obtaining a connection to the database) for our explanation of what occurs when client requests are received from ASTA clients.

Walk through of a SQL Select Request

When a remote ASTA client writes some SQL and sets the Active Property to True of an AstaclientDataSet this is what occurs on the server

1. A Database Session is checked out of the Thread Pool and marked as in use. (AstaServerSocket event : ThreadedDBSupplySession)
2. A TAstaThread is created and the Database Session is stored by the thread as a property so that only the thread will be able to access that session.
3. A Component is requested that can perform a Select statement (AstaServerSocket Event : ThreadedDBSupplyQuery)
4. The Thread is launched and the AstaServerSocket OnSubmitSQL Event is called.
5. If the SQL was successful a result set is sent back to the client, otherwise an Exception is raised and streamed back to the remote client
6. The Database Session is put back in the ThreadPool at the earliest possible moment.
7. The thread ends

There are added features that ASTA supports in some of the more advanced Threading abilities of ASTA servers like using Asynchronous database threaded queries and the ability to set Maximum Session and Thread variables to enable some Thread Queuing but that is beyond the scope of this discussion.

AstaServersocket Events that need to be coded to provide full Database Support

	OnSubmitSQL	Passes SQL from AstaclientDataSets to support result sets
	OnExecSQLParamList	Passes Parameterized Queries from AstaclientDataSets and must be able to handle blob and memos in Parameterzied queries. A TAstaParamlist is sent from the client which is used to update the Delphi 3rd Party Database component used in parameterized Queries on the server.
	OnTransactionBegin	Start a Transaction
	OnTransactionEnd	A Success:Boolean variable is passed to this method and if True a Commit shoud be executed, otherwise a call to Rollback is in order
	OnStoredProcedure	A procedure Name is passed in along with any Params and a Boolean that signifies whether the StoredProcedure returns a result set or not
	OnFetchMetaData	Must be able to provide information about the database including tablenames, fieldnames and datatypes of tables, stored procedure names and parameters and optionally indexes, view, and foreign key information
	OnClientDBLogin	Used in PersistentSessions threading model allows remote clients to use Database UserName and password. Requires each client to have their own Database Connection

AstaServersocket Database Threading Related Events

	OnThreadedDBSupplySession	Creates a DataModule that contains a component that can connect to a database. In Pooled Sessions this is called DatabaseSessions number of times at servers startup. With PersistentSessions it is called each time a client connects to get a unique DatabaseSession for each client. Not used if the OnClientDBLogin is coded to enable the use of the Database UserName and password
	OnThreadedDBSupplyQuery	When ASTA internally needs a database component, it will call this event. The event must be able to return components used for selects, stored procedures, transactions, parameterized exec queries. The enumerated type of TThreadDBAction is passed to this event and it must return a Tcomponent based on the request.

AstaServerSocket Threading Related Properties

	DatabaseSessions	The number of initial Database Sessions created for ASTA Pooled Threading. It is recommended that Database Session components be used on DataModules so at server startup ThreadedDBSupplySessions is called DataBaseSessions number of times to create DatabaseSessiosn number of Datamodules. By default the session pool expands on demand unless the MaximumSessions property is set. This will cause Threads to Queue up and not to exceed MaxiumSessions.
	ThreadingModel	SingleSession, PersistentSessions or PooledSessions
	ThreadCacheSize	This is part of the Borland sockets and does not have anything to do with ASTA threading. It is used in blocking sockets which ASTA does NOT support
	MaximumSessions (public not published)	Applies to Pooled Session Threading. By default the Session Pool will expand on demand. So if the DatabaseSessions is set to 5 and all 5 Sessions are busy when a request is received, the DatabaseSessions will increase. When MaximumSessions is set the DatabaseSessions will not increase beyond MaximumSessions and the Thread will be Queued to be launched when a Session becomes available.
	MaximumAsyncSessions (public not published)	If ASTA Asynchronous Database Queries are used (Pooled Session Model only) , this sets a maximum number of concurrent threads for any connected client so that the Session Pool is not claimed by limited number of clients. If a database activity is requested and the request exceeds the concurrent number of threads in use for a specific client, the request is Queued and will be executed when a Database Session becomes available

Exception Handling

ASTA servers can not be enabled to raise exceptions that pop up a Windows Dialog. Server Exceptions must be handled and optionally streamed back to ASTA remote clients. The Application.OnException event must be assigned by a server method and that event should send any exceptions to remote clients by calling the AstaServerSocket SendExceptionToClient.

AstaServerSocket Methods Required for Server Initialization

	RegisterServerMethod	AstaClientDataSets now support Ttable calls like FindKey and SetRange and RegisterServerMethods allows efficient ServerSide Methods to be used for these calls using a TastaBusinessObjectsManager. RegisterServermethod maps the clientSide standard calls to the specfic ones coded for a particular ASTAServer.
	RegisterDataModule	In order for ASTAClientDataSets to show ServerMethods, ServerDataSets, Providers and the possible Parameters they may have, you must call RegisterDataModule. This allows all the components on the DataModule to be inventoried and placed in internal ASTAServerSocket data structures.
	CommandLinePortCheck	If you want to support standard ASTA command line syntax like Port= and Sessions=5 or PersisentSessions
	CreateSessionsforDBThreads	Sets up Pooled Threading by reading the DatabaseSessions property and create DatabaseSessions number of Datamodules by calling ThreadedDBSupplySession DatabaseSessions Number of times. The method takes a Boolean that signifies if the Sessions created are to be disposed of by ASTA or by code supplied by the ASTA developer.

Leverage Your Most Precious Resource

The most costly operation of using a Database is connecting to that database. Users of Database applications spend most of their time NOT using that expensive connection. In order to make better use of resources and allow your application to scale, why not share the Database Connections? That is what ASTA's Pooled Threading Model is all about, sharing Database Connections.

If you require the Database UserName and Password to be used by your Application then you can use the Persisent Session ASTA Threading Model which creates a new connection for each remote user and allows you to pass their Database name and password when attempting to connect to the Database (OnClientDBLogin). Also if you want to have an "open cursor" and use packetized queries so that you can call GetNextPacket each user must have their own Database connection.

Scaling

ASTA PooledSession Threaded Servers were designed to scale but need to be configured depending on which Delphi 3rd Party Database component is used on the server. Usually bandwidth will be the most limiting factor but if you are running an ASTA server on a fast local network the MaximumSessions property may need to be used to limit the number of sessions created.

It is recommended that Pooled Sessions be used on ASTA servers and since the Session Pool will expand on demand it is recommended that you set a conservative value (5-10) for the DatabaseSessions property. Remember, each session may be able to handle 10-25 clients depending on your application design and bandwidth.

Server User Interface

ASTA servers come with a User Interface that consists of a Paged Tab control and some memo's where a server log is written. There is a tremendous performance cost to writing to this memo for the cpu so we expect you to turn the log off or to remove the user interface of your ASTA server for production server if you want to get the maximum performance from your server. Any User Interface control is very costly to update on a threaded server with lots of activity.

It is recommended that you start with a pre-coded ASTA server and remove all the messaging examples and trim it down for production use. If a server needs to be accessed it should be done by a remote client administration program using ASTA messaging.

NT Service

ASTA Servers can run as NT Services' but instead of providing examples of the 20 ASTA servers supported (spring 2000) ASTA has created the AstaServerLauncher that is a normal EXE to launch and bounce back ASTA servers if they go down. There is an NT Service version of this available as AstaServerLauncherNTS.