With its recently released Adaptive Server Enterprise 11.5, Sybase is now playing in a different league. The newly extended Sybase database server, positioned in the context of a multitier architecture, aims to address all OLTP transaction performance, DSS query performance, mobile and mass deployment, processing of complex datatypes, multitier database application architectures, component-based rapid application de-velopment, and of course, extended support of Internet technology. In Adaptive Server Enterprise 11.5, Sybase is promising unmatched scalability, high performance for simple and complex transactions in dynamic environments, and extensibility for distributed applications and specialized datatypes. This month, Iıll explore how Sybase Adaptive Server fits into Sybaseıs overall architecture. Then Iıll look at whatıs new with Adaptive Server and determine whether it meets the high expectations Sybase has set for it.
The new playing field for the Sybase Adaptive Server is the Adaptive Component Architecture. This is a typical three-tier architecture, with client, application, and server layers. It was developed to support multiple database servers by offering common languages and services, component-based development tools, and open component interfaces across all three tiers. The Adaptive Component Architecture is made up of so-called optimized data stores, the Jaguar Component Transaction Server (CTS), data movement and connectivity products, and rapid application development tools. (See Figure 1.)
In Sybase nomenclature, "optimized data stores" refers to a single database server layer with specialized DBMSs for specific types of applications and support for specialized datatypes within these markets, such as OLTP with mixed workloads, mobile and departmental databases, data warehouses, and application-specific data marts. These DBMSs will evolve from Sybaseıs various existing database servers. Sybase SQL Server 11 has already evolved into the Sybase Adaptive Server Enterprise 11.5. In subsequent releases, Sybaseıs mobile database server, SQL Anywhere, will be renamed Sybase Adaptive Server Anywhere. Sybaseıs DSS server, Sybase IQ, will be renamed Sybase Adaptive Server IQ. The integration of Sybase SQL Anywhere and Sybase IQ as data-store components should occur as soon as these products have been extended with the additional APIs to support Java and all the required common services. (Sybase SQL Anywhere was briefly described in my November 1997 column, and I reviewed Sybase IQ in my August 1997 column.)
The Jaguar CTS, which began shipping in September 1997, is a middle-tier execution environment that supports various component models, including ActiveX, Java and JavaBeans, native C and C++, and CORBA. In essence, it combines the features of object request brokers (ORBs) and transaction-processing (TP) monitors in a component-based application environment. It can funnel a large number of browser sessions through to a small number of synchronous or asynchronous database connections, which is a useful feature when you have large numbers of client applications accessing the database from the Web. CTS also includes a system administration and monitoring tool that can run in a browser or as a standalone Java application.
The data movement and connectivity products (again Sybase nomenclature) within the Adaptive Component Architecture provide back-end APIs for remote database connectivity and replication in multitier, heterogeneous, and distributed environments. These products include Sybaseıs Replication Server, SQL Remote, DirectConnect, OmniConnect, and the recently released jConnect and dbQ message-queuing software.
Within the rapid application development tools layer, Sybase has added the "Power" family of application development tools, including SQL Modeler (the CASE tool previously known as PowerDesigner, and before that known as S-Designor), PowerBuilder, Power++ (previously known as Optima++), and the recently released PowerJ. These tools have all been extended to support multitier distributed computing architectures and the standard component models supported by the Adaptive Component Architecture.
Sybase Adaptive Server Enterprise 11.5 is the key element in the Adaptive Component Architecture. Although not widely advertised as such, it is in essence a Universal Server with its so-called snap-in services (described further on) that evolved from Sybase SQL Server 11. There are four major components to Sybase Adaptive Server: a common language processor, a component integration layer, the Sybase optimized data stores, and common services. (See Figure 2.)
The common language processor offers a consistent language interface across all the data stores in the Adaptive Component Architecture. The current release supports Transact-SQL, and future releases will also support Java. An internal JDBC driver running as part of the database server will enable JavaBeans to access the relational data directly. With the addition of Java as an API, you will be able to create and access Java objects and Java-based abstract datatypes. Application logic can then be processed in a Java Virtual Machine incorporated in the common language processor, while Transact-SQL commands are passed through to the appropriate database server, such as the Sybase Adaptive Server.
The component integration services allow you to connect to remote Sybase and non-Sybase databases. At present this supports distributed query processing across different data stores, with full data location transparency ı your application doesnıt need to know where the data is physically stored. With the Omni services now integrated into the Sybase Adaptive Server, you can transparently access data stored locally in a Sybase Adaptive Server database as well as data stored in DB2, Informix, and Oracle from a single connection to a single Sybase server. For example, you can join tables in these databases, use the select into command to transfer the contents of one table into another, and maintain referential integrity across heterogeneous data sources. Future releases will also support distributed transaction processing to multiple data stores.
The common services are middleware services across the various types of data stores for data movement and operational management. These services include replication, distribution, remote access, and facilities for database administration, performance maintenance, Web-site management, queuing and asynchronous messaging, backup and recovery, and, of course, security control. These common services include the integration of existing facilities such as SQL Remote and the Sybase Replication Server with Sybase Central (described further on). SQL Remote provides message-based data replication between a central consolidated database and a set of databases on laptop or desktop computers at the same or different sites. The remote sites need only be connected occasionally and indirectly to the consolidated database site. The remote computers must be running their own database servers, such as Sybase SQL Anywhere.
Other components on the database server platform include the Backup Server, Monitor Server, Historical Server, and the Server Config utility. The Backup Server runs concurrently with the Sybase Adaptive Server to perform high-speed online database dumps and loads. The Monitor Server and the Historical Server are the server components of a client/server application called the Adaptive Server Monitor. This monitor lets you capture, display, and evaluate performance data as well as tune the Sybase Adaptive Serverıs performance. The Monitor Server captures performance data from the Sybase Adaptive Serverıs shared memory. The Historical Server, in turn, writes the data to files for offline analysis.
There are several Adaptive Server Enterprise components installed on the client platforms. NetImpact Dynamo is a suite of tools for building and managing Web sites driven from Sybase Adaptive Server databases. You can store, manage, and access HTML and database data locally or remotely. NetImpact Dynamo consists of HTML templates, the NetImpact Dynamo application server, and the NetImpact Dynamo personal Web server. HTML templates are created with wizards, stored in a database, and can contain SQL queries, Dynamo scripts, and JavaScript. The NetImpact Dynamo application server processes SQL queries and Dynamo scripts. It provides an interface between common Web server programs and the Sybase Adaptive Server. NetImpact Dynamo includes a CGI application server for CGI-compatible servers, an ISAPI application server for the Microsoft Internet Information Server or other ISAPI-compatible servers, and an NSAPI application server for the Netscape Web Server or other NSAPI-compatible servers.
Sybase Central is a new application for managing Sybase databases and NetImpact Dynamo. It helps DBAs manage database objects and perform common administrative tasks such as managing the Sybase Adaptive Servers (connecting to, disconnecting from, starting and stopping servers, and various troubleshooting functions), managing data caches, managing the Sybase Adaptive Serversı physical resources, managing databases (creating, deleting, backing up, and restoring databases), managing access (creating and deleting logins, and creating and deleting database users, user groups, and roles), and managing object and command permissions. It is also used to monitor the Sybase Adaptive Serverıs performance data in order to tune Adaptive Serverıs performance parameters. Sybase Central replaces the SQL Server Manager that shipped with Sybase SQL Server 11.
InfoMaker is a reporting and data maintenance tool that lets you create forms, reports, graphs, crosstabs, and tables as well as simple applications that use these as building blocks. You can move data between databases using the InfoMaker data pipeline. InfoMaker connects natively to the Sybase Adaptive Server and through ODBC to other databases.
The Open Client API, which includes Client-Library, DB-Library, and CS-Library, enables client applications to interface with the Sybase Adaptive Server. Third-party client applications and application development tools such as PowerBuilder and InfoMaker require the Open Client libraries to communicate with the Sybase Adaptive Server. Open Client also includes isql (an interactive query processor), bcp (a program that copies data to and from a database to an operating system file), and defncopy (a program to copy database object definitions).
Finally, there are several desktop utilities on the client. They include dsedit, an editor for creating and modifying network configuration files; wdllvers, a utility for examining Sybase and Windows DLLs loaded into memory; SQL Advantage, a GUI utility for executing Transact-SQL commands and system procedures; and of course, ODBC drivers.
Now that you understand Sybaseıs overall strategy with the Adaptive Component Architecture and how the Adaptive Server fits into the picture, letıs look more closely at the new functionality Sybase has added to the Adaptive Server. The majority of the new features in Adaptive Server aim to exploit parallel processing platforms. The Sybase Adaptive Serverıs multithreaded architecture supports parallel queries, parallel index creation, parallel data loading, parallel database consistency checks, and parallel backup and recovery.
When executing queries, the Sybase Adaptive Server dispatches multiple internal threads to process a single query. The query optimizer determines the highest degree of parallelism possible given the query, the data distribution, the available resources, and the resource limits the DBA has put on the task. Table scans, index scans, aggregates, unions, sorts, and joins can be parallelized ı this applies to partitioned and unpartitioned tables as well as to clustered and unclustered indexes. Most of the other parallel-aware DBMS products can only apply such operations as parallel table scans to partitioned tables.
You should know, however, that although parallelized operations result in faster query response times, the operations demand more CPU resources to perform. To control this, the Logical Memory Manager and the Logical Process Manager have been extended with additional functions to assign particular CPU resources to specified applications and to assign specified database objects to any number of named caches. Through the Logical Process Manager you control the order in which the Sybase Adaptive Server executes requests. Two new capabilities, engine affinity and execution precedence, let you specify to the server the performance trade-offs you want to make among connections and sessions. For example, you can rank client applications, logins, and stored procedures with respect to execution priorities. The Sybase Adaptive Server will consider your assignments when it places the requests in one of its three priority run queues. Engine affinity is used to suggest how resources should be partitioned among connections and sessions. You can also use the Resource Governor to control the workload sharing among complex queries and critical online transactions, as well as specify resource limits based on I/O costs, elapsed time, or the numbers of rows returned.
These features give DBAs the power to control the Sybase Adaptive Serverıs behavior on parallel processing platforms. However, it also requires that DBAs know exactly what they are doing. Incorrect resource allocations can kill the DBMSıs performance, even on a powerful platform.
The Sybase Adaptive Server Enterprise 11.5 includes an SDK through which Sybase and its partners can develop and integrate so-called snap-in services (or specialized datatypes) into the database server. The SDK is based on the Sybase Open Server and OmniConnect Direct Connect API. The Sybase Adaptive Server ships with snap-ins for text searching, imaging, geospatial, and time-series data, as implemented by various third-party developers.
The Sybase Adaptive Server supports extended stored procedures (ESPs). ESPs provide a method for calling external, procedural, non-Transact SQL functions. They let you implement the equivalent of stored procedures in C, C++, or even operating system shell scripts, and then access them from within the database. These ESPs enable the Sybase Adaptive Server to perform tasks outside the server in response to events occurring within the server. For example, an ESP can send an email message, invoked by a trigger fired when a row in a database table is set to a certain value. The interface to ESPs is similar to the interface to system procedures and user-defined stored procedures. The difference is that an ESP executes procedural language code rather than Transact-SQL statements. Support for Java is scheduled for a subsequent release.
ESPs are implemented by an Open Server application called XP Server, which runs on the same machine as the Sybase Adaptive Server. Running ESPs in a separate process protects the Sybase Adaptive Server from failing when the ESP performs illegal operations. The Sybase Adaptive Server and the XP Server communicate through remote procedure calls (RPCs). The function that implements the ESP is compiled and linked into a dynamic link library (DLL) or a shared library. The Sybase Adaptive Server searches the system tables for the function that has the same name as the requested ESP and passes the function name and the DLL name to XP Server. The XP Server loads the DLL, invokes the function that implements the ESP, and passes the functionıs return status, output parameters, and results back to the Sybase Adaptive Server.
With this feature, Sybase has finally caught up with Microsoft SQL Serverıs external procedures. However, Sybaseıs implementation through the XP Server reduces the risks associated with developers adding procedural code to the database server.
The list prefetch feature of Sybase 11 (where large buffers are physically preread to satisfy many logical reads) has been extended with the asynchronous prefetch operation. This allows the Sybase Adaptive Server to issue multiple asynchronous physical reads of large buffers concurrently. The asynchronous prefetch operation also reduces the times that the Sybase server has to wait for the completion of physical read operations, pushing parallelism right down to the operating systemıs I/O subsystem without requiring the memory and processing power or the Sybase Adaptive Serverıs parallel options. The asynchronous prefetch improves the performance of sequential scans, such as table scans, clustered and nonclustered index scans, update statistics commands, dbcc checks, some recovery tasks, and queries that access large numbers of pages, as long as the I/O subsystems on the machine are not saturated. Physical I/O can be further improved through tunable I/O block sizes, configurable backward table scans, preallocating memory for batch tasks, and caching of crucial metadata, such as user indexes, procedures, triggers, views, rules, and defaults.
A Security Control Layer provides an interface to industry-standard security mechanisms such as DCE 1.1 and CyberSafe Kerberos. It uses this layer for authentication, message integrity, and message confidentiality. In future releases, it will also include public-key encryption and Netscapeıs Secure Socket Layer. These features are necessary for electronic commerce and enterprisewide security enforcement, particularly since the Sybase Adaptive Server is positioned as the cornerstone of the Adaptive Component Architecture.
The Sybase Adaptive Server includes an auditing system that can archive and process audit data without manual intervention and the loss of any audit data. This is achieved by using multiple audit tables and has a user interface to control auditing. You can audit various events, including the creation of database objects, binding and unbinding of rules, defaults, and messages, actions performed by specific users or roles, and various other security-related events.
In Sybase SQL Server 11, the source text of compiled objects was saved in the syscomments system table. With Sybase Adaptive Server 11.5 you can prevent users from seeing this text by encrypting it with the sp_hidetext system procedure.
Other security enhancements include role-based security and proxy authorization. With role-based security, permissions are granted to specific roles instead of individual users. Proxy authorization is best illustrated in the context of a typical three-tier client/server architecture, as is often found in Web-enabled database applications. When an application server connects to a database server using a generic login, you have to code all the required security controls into the application. With proxy authorization, the application server assumes the identity of the user on whose behalf it connects to the database server. The operations issued through the application server are then controlled by the database server as they were issued directly by the actual user.
Sybase Adaptive Server includes various enhancements to the Transact SQL language, including case, coalesce, nullif, and when-then expressions, as well as a new limit of 192 table references per query. The case expression is a concise version of a complex if-then-else statement; nullif is a comparison operator; and coalesce and when-then are replacement functions ı all these can simplify some SQL expressions considerably.
With this latest release, Sybase not only joins the big boys like IBM and Informix with an extensible database server ı it also joins the likes of Oracle, with a database server positioned in the context of a comprehensive multitier architecture. Many new features have been added to the Sybase Adaptive Server. Some of these features, such as support for parallel machine architectures, are essential to maintain Sybaseıs place as a serious player in the database technology league.
However, similar to Oracleıs cartridge-based architecture, large parts of the Sybase Adaptive Component Architecture are still plans, promises, and architectures of future releases ı vaporware that runs very well on overhead projectors and corporate white papers. It will be interesting to see which of the two companies brings its completed architecture to run on real distributed, multitiered, Web-enabled, multiprocessor platforms first. Oracle has had a head start with Oracle8, but Sybase has a less complex architecture to complete. It will be especially interesting to see who gets ahead in this round of the database technology tournament.
Sybase claims to be the only certified ISO 9000 DBMS vendor. The company also claims that the Sybase Adaptive Server was developed from the start to conform to the ISO 9000 specifications. Although this may be a requirement for various government projects, and although it may give some users some peace of mind, when I consider how much time and resources are spent on the certification process, I wonder if those resources would not have been better spent on getting the Sybase Adaptive Component Architecture and the Sybase Adaptive Server completed and out on the playing field sooner.
Figure 1. Adaptive Component Architecture
Figure 2. The Sybase Adaptive Server architecture.
Martin Rennhackkamp is the owner and principal consultant of The Data Base Approach, a corporation specializing in relational and distributed databases, based in Cape Town, South Africa. You can reach Martin via the Internet at mr@dba.co.za.