Advantage Database Server A High level overview Chris franz Systems consultant / Advantage Evangelist May 2011
What is Advantage ? Advantage database server a robust, high-performance and fully scalable database system that enables you to create powerful business applications faster and easier. 17 years of continuous development Easy to embed with your application Low administration – no DBA required Full-featured but lightweight Small footprint, less than 8MB
What makes Advantage Unique? • Provides both navigational/ISAM data access as well as SQL data access. • Provides a free “local server” DLL for embedded data access when a client/server installation is not available or desired. • Supports legacy DBF data as well as a proprietary file format with more data types and more efficient performance. • Can share data with non-Advantage applications while converting to client/server. Allows a gradual conversion path. • Provides an XBase expression engine for use in filters and index expressions. • Low Cost
Server Types • Advantage Local Server (ALS) • DLL loaded by an application in place of connecting to a database server • Provides data access when a server is not available, or is not desired • Free, including royalty free distribution • Advantage Database Server (ADS, or “remote” server) • True client/server database • Advantage Internet Server (AIS) a different type of connection to an Advantage Database Server. • Support for Windows, Linux and Netware 5+
footprint • Advantage is an embedded database with a small footprint: • If using local server: • Advantage Client Engine (ACE): ~1MB • Advantage Local Server DLL: ~1.2MB • If using remote server: • Advantage Client Engine (ACE): ~1MB • Advantage Comm Layer: ~170KB • Advantage Database Server: ~2.1MB • If using Unicode columns: • ICU Library (aicu.dll) ~2MB • ICU Data File (icudt40l.dat) ~13MB
Dynamic Configuration • Settings for number of Connections, Work Areas, Tables, Indexes and Locks are used as an initial allocation values • If all resources are in use, Advantage will allocate more • As resource usage dwindles, resources will be released • Automatically configured worker thread count • Number of cores multiplied by 8 • Not supported on NetWare
Supported Languages/Environments Advantage supports many development environments, languages, and data access paradigms including, but not limited to: • OLE DB (ADO) (32 & 64-bit) • ODBC (32 & 64-bit) • Perl DBD • JDBC • PHP (32 & 64-bit for Linux) • C/C++ API (32 & 64-bit) • Visual FoxPro • Delphi/C++Builder • Visual Studio (32 & 64-bit) • Visual Basic • Visual Objects • CA Clipper • Crystal Reports • .NET Data Provider (32 & 64-bit)
Supported File Formats • Advantage supports four file formats. Three of the formats are Xbase formats, and the fourth is an Advantage proprietary format. • CA-Clipper: DBF, NTX, DBT • Foxpro: DBF, IDX/CDX, FPT • Visual Foxpro: DBF, IDX/CDX, FPT • Advantage Proprietary: ADT, ADI, ADM • Unicode fields supported in all file formats • NCHAR, NVARCHAR, NMEMO • Stored using UTF-16 encoding • No NetWare support for Unicode
Side-by-side installs • Install multiple versions of ADS on a single server • Server’s identified by IP port and in Windows a unique service name • Avoid licensing issues if multiple vendors use ADS on the same server • Avoid version conflicts if multiple vendors use ADS on the same server • Useful for testing
DBF Format Details • XBase • Useful if Sharing Data with non-Advantage Applications • Useful if you prefer “lazy” record deletion • Useful if you prefer traditional XBase unique index behavior
Advantage ADT Format • Long Field Names and Long Index Names • More Characters in Field Names and Index Names • Additional Field Data Types • True Unique Indexes for Primary Key Support • Deleted Records are Invisible and Automatically Reused • More Efficient Memo Block Size • Better Performance for Certain Operations • More Suited for Future Enhancements • Support for NULL Values
Single Application Scalability • Do not need separate applications for different environments. Only one application is needed for: • NetWare, Windows, and Linux networks • IP and IPX communication • Access to data on a file server running ADS • Access to data on a file server not running ADS • Access to data on a local workstation • Access to data located at a remote site via an Internet connection
Database Stability • Non-client/server solutions are prone to index corruption • Index data is read over to the client to search for the old data to remove, and to search for where to write the new data • All table and index data is updated on the client • If the application, workstation, or network goes down, physical or logical index corruption may result • Advantage eliminates physical and logical index corruption that occurs with non-client/server solutions • Tables and index files are only updated if the Advantage Database Server receives all of the data necessary to perform the entire update • Status of the application, workstation, or network does not affect Advantage updates
Anatomy of a record update first index update second index update table update client network Non Client/Server server Read Old Pages Lock Index Lock Index Write Old Page Write Root Page Write Root Page Read Old Pages Read New Pages Write New Page Write New Record Read New Pages Write Old Page Write New Page client network Client/Server server
ISAM/Navigational Data Access Advantage uses Indexed Sequential Access Method (ISAM) data files. Records are stored sequentially Indexes contain pointers into the tables, allowing quick record retrieval Indexes are small and can be searched quickly Modifications to data do not require modifications to other non-related records or index entries Direct access to tables and indexes eliminates the overhead of SQL parsing, optimization and execution
SQL Data Access • Consists of most of the SQL-92 standard with ODBC extensions. • The Advantage SQL engine supports a subset of the ANSI SQL 2003 PSM (Persistent Stored Module) scripting language standard including variables, cursors, branches, loops, and exception handling. • The SQL engine also supports many features that are not in the current SQL standard but are customary available in other RDBMS, such as Views, Query Execution Plan, Query Logging, User Defined Functions, and Temporary Tables.
Record and Index Locking • Due to its ISAM foundation, Advantage supports record-level locking. A page of records (a concept that doesn’t apply to an ISAM table) is never locked. • Advantage locks index tags on an individual basis. A single index file can have up to 50 individual index tags. Although these tags reside in the same physical file, they are locked as individual units, increasing multi-user performance.
Advantage Proprietary Locking • Files are opened in an exclusive mode and cannot be opened by other applications or non-Advantage users • High performance, intelligent locking system • Lock management done in internal data structures, as opposed to using the operating system • No index locking retries ever • Allows for read-through index locking • ADTs always use proprietary locking • Proprietary locking is an Advantage Database Feature it is not available with Local Server
Non-Exclusive Proprietary Locking • Lock management can be done internally as files are opened “deny write”, preventing non-Advantage applications from updating them • Non-Advantage applications (such as report generators) that need access to DBF tables can still open them for read-only use
Advantage Compatibility Locking • Share DBF data with non-Advantage applications that need to write to the tables • Data files are opened in the mode specified • Locks end up going through the operating system • No read-through index locking • Provides growth path to full Advantage system • Database stability not guaranteed because non-Advantage applications can write to and corrupt the database
Full Text Search (FTS) • Full Text Search provides very fast content searches of character, memo, and BLOB fields • Available in table filters and SQL statements • Uses FTS indexes created on desired search fields • Like AOFs, FTS indexes are fully dynamic and reflect table updates • Only supported with ADI and CDX/IDX indexes • FTS discussed in detail in its own session
Advantage Expression Engine • The Advantage Expression engine can be used to evaluate expressions in filters or index definitions. • The Expression Engine can parse and evaluate operators, fields (columns), literal values, and many functions within the expression. A full list of functions can be found in the help file. • Indexes can be built with an expression that matches a common filter expression, allowing the resulting Advantage Optimized Filter (AOF) to be fully optimized.
Advantage Optimized Filters (AOFs) • High performance record filters • Speeds performance by using index keys rather than table records to determine if record passes filter condition • Creates bitmap with one bit for each record • AOFs are used to optimize WHERE clauses in SQL statements • Best performance vs. traditional record filters results when a small percentage of records pass the filter condition • Custom AOF functionality available to specifically add or remove individual records to or from an AOF
AOF Optimization Levels • Fully optimized filters • Fastest AOFs • Individual indexes or a single multi-segmented index must exist for all fields in filter expression • Should attempt to have all AOFs fully optimized • Non-optimized filters • Indexes exist for no fields in filter expression • Perform like traditional record filters on first pass through data and like fully optimized filters from then on • Filters on DBF tables with NTX indexes will all be non-optimized
Aof optimization levels • Partially optimized filters • Indexes exist for some of the fields in filter expression • Perform somewhere between fully optimized filters and traditional record filters on first pass through data and like fully optimized filters from then on
AOF Behavior • Updates to data affect AOFs depending on AOF types setting, ADS vs. ALS, and compatibility locking (w/ DBFs) • Dynamic AOFs: all updates by all users reflected in AOFs • Keyset-driven: only updates made by AOF owner reflected in AOF • Fixed: no updates made by any user reflected in AOF • Advantage Database Server with ADTs, or Advantage Database Server with DBFs and proprietary locking • all three AOF types available; default is dynamic • Advantage Database Server with DBFs and compatibility locking • all three AOF types available, but behaves like keyset-driven when a non-ADS user updates affected data • Advantage Local Server • keyset-driven and fixed AOF types available; default is keyset
Transaction Processing System • Transactions allow you to define a logical unit of work or “business transaction” • Updates, inserts, and deletes to multiple records in one or more tables on a single server can be in a single transaction • Either all changes occur or none occur • Automatic rollback upon workstation or network failure • Read Committed transaction isolation level • Updates, inserts, and deletes are invisible until they are committed
Transaction processing system Order processing example with three parts ordered: begin transaction OrderTable->insert CustomerTable->update PartsTable->insert InventoryTable->Update Automatic rollback upon workstation or network failure. PartsTable->Insert InventoryTable->Update PartsTable->Insert InventoryTable->Update commit transaction
Transaction Processing System • Transaction savepoints for partial rollbacks • Illegal operations within a transaction • Pack, zap/empty table, deleting index order, reindex, transactions spanning servers • Behavioral differences of operations within a transaction • File closes and unlocks in ACE-based clients will not actually occur until the transaction has completed
Transaction Processing System • Failed transactions • If server goes down during a transaction, transactions that were active are handled and the database is put back into a stable state when the server is brought back up • Appended records will still exist after a rollback of DBF tables • Use with Advantage C/S applications only • Non-Advantage DBF-based applications may see data as corrupt
Transaction Processing System Version 10 Enhancements Nested Transactions Transaction Free Tables Updates to these tables always performed outside of transaction Useful for audit tables, key generation tables, debug log tables Transaction Performance Improvements in v10 Lock list and visibility list improvements, improves transactions that involve a relatively large number of updates Temporary File Handle Pool, improves performance of shorter repeated transactions
Server-side Processing • Stored Procedures and Triggers • A piece of code or an SQL script that runs on the server in response to an update, insert, or delete operation • Can be written as SQL scripts, WIN32 DLLs, COM Objects, Linux shared objects, or .NET Assemblies. • Events • Mechanism that allows an action at the server to proactively notify clients that an event they are interested in has occurred. • Attend Server-Side Processing for more details
Data Dictionary Functionality • Views • "virtual table" in a database whose contents are defined by an SQL SELECT statement. • User Defined Functions • Reusable functions that can be used in an SQL statement or SQL script just like existing system scalar functions. • Functions are implemented as SQL scripts. • Referential Integrity • Advantage supports cascading updates and deletes. Delete and update rules can be: Restrict, Cascade, Set NULL, or Set Default. • Constraints • Field-level minimum and maximum constraints ensure the data entered for each individual field in a record is logically valid. • Record-level constraints ensure that the data entered for related fields in a record is logically valid before posting the record to the table.
Backup and Restore • Allow you to capture and save a snapshot of your database. • Users can be connected and making modifications at the time of the backup. • Differential backups are supported, which only backup changes since the last successful backup. • Advantage Database Server (remote server) only.
Replication • Replication distributes changes from tables in one database to another. • Replication can be 1-way, 2-way, and higher. • Chaining (forwarding) is supported. • Transactional and Referential Integrity is maintained. • Conflict Resolution is handled by the CONFLICT trigger type. • Advantage implements “push” replication. • Not supported with Local Server
Encryption • Data on the network wire is encrypted • All transmissions can be encrypted over the wire in v8.0 • Data stored on the server is encrypted • Table, Memo, and BLOB data is encrypted • Index data is encrypted with ADI indexes in v8.0 • Encryption with ADT tables that are bound to a Data Dictionary includes table header encryption
Encryption • Data displayed on the client will be decrypted if the user has the valid encryption password or has an authenticated database connection. • Encryption • Standard uses 160-bit, RC4-compatible algorithm. • Enhanced encryption is FIPS compliant can use 128 or 256 AES Encryption • With DBFs, the identifier byte in the DBF header is encrypted if the entire table is encrypted so that non-Advantage applications cannot open the DBF.
Database security • Database security is the ability to define what users can access and/or make updates to the database. • Advantage provides two methods of database security for free connections (i.e., those connections not involving a data dictionary database) • Checking user’s network OS rights • Ignoring user’s network OS rights • Security is a per-table setting for free connections • Advantage provides User Account security for database connections (i.e., those connections to a data dictionary database).
User Accounts Database Connections User accounts provide control of access to database tables, columns, views, stored procedures, etc. To effectively guard against unauthorized access to the database, it is recommended that all database files are stored in a secured server directory to which no workstations have rights (similar to Ignore Rights security with free connections). Two levels of control Login authentication Access rights verification
authentication Database Connections Requires the user connecting to the database to have a valid combination of user name and password. Once authenticated, a user has access to tables, columns, views, stored procedures, etc. By default, no login authentication is required when a database is created. In this state, a user can connect to the database with no user name and no password and have full access to all tables, views, and stored procedures.
Access Rights Database Connections User access to dictionary objects are verified to make sure that proper rights have been granted to that user. The data dictionary administrator, or a user who has been granted permissions to do so, is responsible for setting up individual rights. User groups can be defined in the database to ease the task of setting up users with similar access rights. By default, access to database objects are not restricted when a database is created.
Security Settings and Tips • DISABLE_FREE_CONNECTIONS – server configuration parameter • Configure ADS service or daemon to run as a restricted user • Use server-side aliases to hide data path from clients • Always use parameterized queries to avoid SQL injection attacks
summary • The Advantage Database Server provides: • Database stability by eliminating index corruption and via Transaction Processing System, Referential Integrity, and constraints • Database security by allowing only Advantage applications to access your database, and by specifying which users have access to which tables, columns, views, stored procedures, etc. • An easy to use RDBMS that runs on multiple platforms, does not require a DBA, is easily installed and configured, and allows for easily scalable applications