Database Choices for MAS 200 - Native C-ISAM or Microsoft SQL
It has been a long time since we addressed this subject on our website. We posted a database white paper after Sage MAS 200 for SQL was first introduced. There had been much interest in a SQL version of MAS 200 ever since SQL had become a popular technical buzzword and a "de facto" requirement for modern software. But few prospective users understood the trade-offs between SQL and the C-ISAM database intrinsic to the standard version.
The MAS 200 SQL version never gained much popularity. The managed database benefits came at a high cost in performace that could only be overcome by hardware with higher processor speeds and much greater memory capacity. Due to low demand, Sage decided not to include the SQL version in the version 4.0 Business Framework rewrite of MAS 90 and MAS 200. Although it is still available and still supported, the develpment of the SQL version has been frozen at the 3.74 level.
There has been some talk of revitalizing the SQL version. I am not sure how it will be implemented but this information should still be a good reference for those trying to figure out the pros and cons of the two database options. Here is the orginal white paper posted on our website. It was written by our founder, Phil Martin.
This is primarily directed to the IT types, but if you're looking for buzzwords to drop in cocktail conversation, read on...
First, a short discussion about the Providex language and why there are two choices of database...
Providex shares the same Dartmouth Basic heritage with all of the other Basic languages, including Microsoft's Visual Basic. However, Providex is a member of the Business Basic family; a branch of Basic that differentiates itself by supporting decimal arithmetic and containing an intrinsic keyed database access method. Traditionally, the Basic language only supports rudimentary relative access to fixed sequential records. The Providex language includes a native C-ISAM database access method, which is the fundamental indexed sequential access method supported by the C programming language. It comes by this capability quite naturally because Providex itself is written in C and C++.
Now, why is this important? The answer is that versions of Basic that don't contain an intrinsic access method must rely on an external file access method for keyed access. It isn't an option; keyed record access isn't possible otherwise. But like these other Basic languages, Providex also has the ability to use external file access methods. Consequently, MAS 200 offers two choices for a database: either the native C-ISAM file system or Microsoft SQL. Only the native file system is available for MAS 90.
Importantly, MAS 90 for Windows, MAS 200 for NT Client/Server and MAS 200 for SQL all share the same common code base. While marketed under different names, implemented on different platforms, and based on different file access methods - there is only one set of application programs. There is not a unique version of each program for each implementation. Consequently, all versions have the same feature set - except where a feature is due to an exclusive attribute of the operating system or database. The only reason to prefer one over the others would be an attribute of the implementation.
Native C-ISAM file access method
The intrinsic database is exceptionally fast. It is part of the language and consequently, there is none of the overhead associated with linking to an external database access method. It has a multi-keyed balanced binary tree architecture using variable length records that is very efficient. Windows applications, such as Crystal Reports, access data through the Providex ODBC driver provided that executes the transactional SQL commands as C-ISAM instructions.
The native file access method is designed for maximum performance with Providex programs. However, the trade-off for performance is a lean machine that doesn't include high overhead features such as journaling and auto-recovery. While native access from MAS 200 is direct, external access from other Windows applications is via the ODBC interface.
This is the preferred choice where transactional throughput is the primary consideration. With the native C-ISAM access method, there should be only a moderate requirement for ODBC access and database integrity should be assured by a stable network and server.
Native C-ISAM - database access doesn't get any faster.
Microsoft SQL Database
Microsoft's SQL database is well known as a popular relational database for mid-range transactional based systems. Implemented as an external file access method to the Providex language, it receives transactional SQL commands from MAS 200 through a SQL ODBC interface. SQL is more than a file access method; it is a managed database. As such, it adds features to MAS 200 such as journaling and utilities for database management. Crystal Reports access is more efficient because access is via a native SQL ODBC connection.
The most significant advantage of using the SQL database is the automatic ability to recover from partial updates. MAS 200 implements SQL's commit-and-roll-back feature available from the use of database journaling. Transactions from a partially posted update are automatically removed upon restart to maintain database integrity at the application level.
This is the preferred choice where database integrity is the primary consideration. With SQL, there should be a greater reliance on Crystal Reports and transactional performance should be assured by fast processors, generous memory, and distributed servers.
Microsoft SQL - reliability doesn't get any better.
