Pervasive.SQL is the newest SQL-based, networkable database engine for Windows NT Server. Pervasive.SQL claims to offer all the features of a high-end database solution, without the need for an expensive database administrator to install and support it. Like its competitors, Sybase SQL and Oracle Workgroup Server, Pervasive.SQL offers all the features you expect to find in an enterprise-level database engine product. The software is fully Year 2000 compliant and offers standard database features such as stored procedure support, triggers and declarative referential integrity.

Expandable Resources

Pervasive.SQL is very simply, a client/server database engine. Although you use the product to manage your information resources, you can't use it to develop programs; you still need some type of development environment (e.g., Microsoft Access, Visual Basic—VB) to design the application that will manipulate information within the Pervasive.SQL database.

Unlike other SQL-based database engines for NT, Pervasive.SQL has very modest system requirements because of several unique technologies that Pervasive Software uses to deliver the product. These technologies include the company's MicroKernel Database Engine and dynamic file allocation. Pervasive.SQL's microkernel database architecture uses a modular design to deliver a highly flexible database. The database engine requires as little as 5MB of RAM, although the published specifications call for a minimum of 16MB of RAM. As demands on your database environment increase, the product automatically tunes itself to meet the increased demand and allocates more resources as required.

Furthermore, you can configure the software to release resources it no longer needs. This last feature is helpful in environments where you have short periods of heavy processing. For example, assume you are in a manufacturing environment where you run shop-floor and materials requirements planning (MRP) jobs at night, and process orders during the day. During the night, your database engine requires more RAM to accommodate the large bill-of-material jobs you must run before you can schedule your production environment the next day. In a traditional environment, you would allocate a set amount of memory to the server to accommodate this requirement, and you wouldn't be able to recoup the lost memory when it's not in use. Pervasive.SQL lets you do just that, so you can use scarce resources such as RAM where they are most needed.

Another useful, but potentially dangerous feature of Pervasive.SQL, is its dynamic file allocation capabilities. Products such as SQL Server require you to pre-allocate a set amount of disk space for your database. However, when you create a database with Pervasive.SQL, the product allocates the minimal amount of space it needs for its system tables and indexes. This minimalist approach lets you create a database with as little as 50KB. Then, as you add data to your database, Pervasive.SQL expands the database to accommodate the increased storage requirements. This capability lets you maximise your disk space, but it has a potentially dangerous side effect: If you have little available disk space, and Pervasive.SQL needs to expand its files to accommodate your data requirements, you might run out of disk space in a production setting.

Features

Pervasive.SQL software consists of the database engine and six GUI applications to manage your database environment. These applications include Data Decryption Field (DDF) Ease, SQLScope, Rebuild, Monitor, User Count Administrator and SmartScout. The two applications you'll probably use the most are DDF Ease and SQLScope. DDF Ease is a database definition utility. The utility uses a Windows Explorer-like interface, with left and right panes within the program window. The left pane contains a list of your Pervasive.SQL databases. You can drill down through the databases to expose each table's field definitions and index definitions. The right pane contains information about the item you select in the left pane.

SQLScope is an interactive SQL execution utility, similar to ISQL/W in SQL Server. After connecting to a database, you enter a SQL statement in the window and click Execute. The program sends the request to the Pervasive.SQL database engine, which returns the results and displays them below the request. Using this utility, I was able to create complex queries, including queries that used aggregate functions and different joins.

The other utilities accompanying the software perform basic administrative functions. The Rebuild utility is useful only if you are running a pre-7.0 version of Pervasive.SQL. This utility automatically rebuilds data files for use with the version 7.0 file format. This utility can enhance your system’s performance if you are upgrading from a prior release of the software. Monitor is similar to SQL Server’s Performance Monitor. Using this utility, you can monitor Pervasive.SQL’s performance in several categories.

You can examine various microkernel parameters, specifically the current, peak, and maximum values in a range of parameters. The software monitors the number of files open, number of handles in use, clients connected to the system, worker threads currently in use by the database engine, locks in place, transactions executed, and the number of licenses in use. The User Count Administrator lets you increase your license count. When you purchase more licenses, Pervasive Software supplies you with either an electronic key code or a license disk. This utility either reads the disk or prompts you for the electronic code, validates it, and increases your license count appropriately. And finally, the SmartScout utility tests your system’s connectivity to the Pervasive.SQL database server.

Installation and testing

My test copy of Pervasive.SQL came on one distribution CD-ROM and one license-count disk. Overall, installation was rather painless, but I did encounter some unexpected problems. For installation and testing, I used a Digital Prioris HX590 dual-processor Pentium system with 4GB of stripped disk storage and 96MB of RAM, running Windows NT 4.0 Server and the latest service packs. I also installed SQL Server 6.5 with the latest service packs on this machine set for manual start.

I used a different Windows client machine to properly test the database product. Although I could have tested the software from the host system, I felt this configuration would have been an unfair test because SQL Server would consume more memory and affect system performance. For my second test machine, I used an American MegaTrends-based dual-Pentium II system running NT Workstation 4.0, the complete Office 97 suite, and Visual Studio 97. I connected the client and server systems using a thin-net TCP/IP link. I installed Pervasive.SQL in two phases. In the first phase, I set up my NT server with the Pervasive.SQL database engine. In the second phase, I installed the client software on my NT workstation. The server-side installation proceeded smoothly (so I thought). After launching the setup program, I chose to perform a custom installation. I went through several screens of prompts asking me which components to install, after which the installation process ran unattended. When the installation finished, a series of stress tests ran indicating the database engine was online and operating correctly. I rebooted my system and began installing the software on my workstation.

On my workstation, I chose to perform a typical installation, and the install program ran uninterrupted for 5 minutes. However, after completing the installation, I was unable to establish a connection to my Pervasive.SQL server. Upon further investigation, I found the Pervasive.SQL database engine had failed to start when I rebooted my NT server. I couldn't start the server by manually starting the task, and I couldn't find the specific failure code in the online documentation. I uninstalled the software and started the installation from scratch, but this time I chose to perform a typical installation on the server. After I reinstalled the software and rebooted the server, I was able to access the Pervasive.SQL database engine from my client system.

The software definitely needs a database-savvy person, especially if you intend to do any new development. Database administration involves more than monitoring and tuning the database (i.e., only a database-savvy person will be familiar with problems associated with proper creation of database tables, constraints, stored procedures, triggers, and more).