Among the legion of SQL database products out there these days, Sybase SQL Anywhere holds a unique position. From its PC LAN roots as Watcom SQL comes a small, efficient SQL engine that is used in workgroup/departmental business applications as well as embedded applications such as information kiosks. From its synthesis with the Sybase enterprise product line comes a powerful client/server DBMS that supports applications that can be scaled all the way up to SQL Server running on big iron.
I reviewed Watcom SQL for Windows in the October 1993 issue of DBMS (page 89) and found "a feature-rich database server at an attractive price" that ran only on MS-DOS and QNX. My review concluded with these words: "To gain larger acceptance, Watcom needs to get its server out on other operating systems, such as NetWare, OS/2, Unix, and Windows NT. Forming alliances such as that with Powersoft Corp., however, will certainly give the product extra means of exposure."
Now fast forward to 1996 -- Watcom has been purchased by Powersoft, Powersoft has been purchased by Sybase, and the latest version of Watcom SQL has been renamed Sybase SQL Anywhere. Not only does it have a new parent, but the server now runs on MS-DOS, NetWare, OS/2, Windows NT, Windows 95, and Windows 3.x. (OK, so I may have missed the boat on Unix, but three out of four's not too bad.)
But much has been added to the product besides support for additional operating systems. Some of the new features in SQL Anywhere include a message-based replication system named SQL Remote, a graphical database administration tool named SQL Central, support for Transact-SQL, and a variety of performance enhancements.
SQL Remote is aimed at what Sybase calls "occasionally connected" users -- people who take laptop computers out on the road, away from the company LAN, for jobs such as sales and marketing. These users want access to the corporate database, but they don't want a static copy. Instead, they want their local copy refreshed every day and they may even want to update the database themselves. The goal of SQL Remote is to offer automatic synchronization of SQL Anywhere databases, enabling deployment of distributed systems with complete data integrity.
How is this goal achieved? SQL Remote uses a bidirectional, asynchronous replication scheme that does not require a direct connection; an email connection is sufficient. Each message carries a destination address so that no direct connection is needed between the sites exchanging data. All replication administration functions are carried out at the consolidated database site and are transparent to the remote user.
SQL Remote is designed for businesses in which there is a consolidated database that contains data relevant to many remote sites. For example, the consolidated database might contain company sales information. The remote databases are on laptop computers containing each sales representative's own subset of that data. In this model, information exchange between or involving remote computers is achieved through the consolidated database.
Remote databases are updated by replicating information stored in the transaction log. To ensure database consistency, only committed transactions are replicated. SQL Remote maintains a "loose consistency" among remote database sites, meaning that all changes are replicated to each site, but because of the time lag inherent in email, different sites may have different copies of the data at any point in time.
Two-way replication has the potential for data conflicts between remote sites, so SQL Remote includes facilities for reporting and resolving conflicts. Typical conflicts fall into two categories: SQL statement errors such as two remote users inserting rows with the same primary key, and update conflicts such as two remote users deleting the same row. These two categories are handled differently. SQL statement errors cause the operation to fail and generate an error message, although proper design of the replication system can prevent most of these errors from occurring. update conflicts can be resolved by SQL Remote without generating an error message by writing a resolve update trigger that will fire whenever an update conflict occurs. The trigger can perform actions such as comparing dates to determine which update is the most recent.
You can also integrate SQL Anywhere with Sybase Replication Server to form an enterprisewide replication system. By using two other Sybase products named Open Server and the Replication Agent, SQL Anywhere can replicate to SQL Server or other DBMSs.
If you've used the Windows 3.1 File Manager or the Windows 95 Explorer, you'll have no trouble navigating in SQL Central. It uses a split window in which the left panel displays a tree-structured hierarchy of database objects and the right panel displays the contents of the currently selected object. (See Figure 1, page 31.) By navigating down through the hierarchy, you can view and modify database objects such as columns in a table. (See Figure 2.)
It's important to note that SQL Central requires Windows 95 or Windows NT 3.51 or later. For users not running Windows 95 or Windows NT 3.51 or later, all of the tasks you can carry out with SQL Central can be carried out using ISQL (the Interactive SQL utility, see Figure 3) to send SQL statements to the database engine or server, and by using the command-line versions of the SQL Anywhere utilities.
The current version of SQL Central has a few limitations. For example, there is no support for creating temporary tables, pre-extending the transaction log file, or setting or viewing database options, and the rebuild, dbcollat, and dbinfo utilities are not available. These tasks must be performed in ISQL or by running the command-line version of the appropriate utility.
In the earlier Watcom SQL product, the dialect of SQL supported was based on entry-level ANSI SQL-92 with some extensions. SQL Anywhere retains the Watcom SQL dialect and adds a Transact-SQL dialect that allows integration with Sybase SQL Server. SQL Anywhere supports a near-complete subset of SQL Server's Transact-SQL, so applications written using the dialect are compatible with SQL Server. In practice, this means that existing Transact-SQL code may need modification to run with SQL Anywhere, but new code will run seamlessly on both DBMSs.
The major areas of difference between the two versions of Transact-SQL tend to focus on the enterprise file architecture of SQL Server vs. the PC-centric architecture of SQL Anywhere. For example, SQL Server uses a master database and SQL Anywhere stores everything in the database. Some of the system procedures from SQL Server aren't supported in SQL Anywhere. SQL Anywhere will dynamically grow the database as required; SQL Server requires explicit resizing. Naming conventions differ slightly (for example, double quotes are used more loosely in SQL Server).
According to Product Manager Chris Kleisath, the approach taken in SQL Anywhere has been to support Transact-SQL to the extent that it is easy to write an application that will run on both DBMSs. Approximately 80 percent of the Transact-SQL features used regularly are supported in both.
Having a powerful database server is all well and good, but if you're a developer you'll need a programming tool in order to build client applications. SQL Anywhere doesn't come with a built-in programming tool, but it does offer four different interfaces that let you use the tool of your choice.
For C and C++ programmers, SQL Anywhere includes an ANSI-standard Embedded SQL preprocessor that translates SQL statements embedded in the source code into calls to a low-level interface. The interface is implemented as a DLL for Windows, Windows NT, and OS/2, and as a library on other operating systems.
SQL Anywhere supports the Microsoft Open Database Connectivity (ODBC) interface for developers using PowerBuilder, Visual Basic, and other 4GL application development tools, and for programmers who want to call the ODBC API directly. ODBC is the most widely used interface to SQL Anywhere, although it is slightly slower than Embedded SQL and has fewer functions. SQL Anywhere supports all of the ODBC version 2.1 API functions (Core, Level 1, and Level 2).
Two higher-level programming interfaces are also supported: WSQL DDE on Windows and Windows NT, and WSQL HLI on Windows, Windows NT, and OS/2. You can use DDE (Dynamic Data Exchange) in applications such as Excel and Word and in development tools such as Access and Visual Basic. WSQL DDE supports cold links but not warm or hot links. HLI can be used from any application or tool, such as Visual Basic or REXX, that can call DLLs. WSQL HLI is simpler to use than ODBC, but it is also slower and supports less functionality.
Kleisath says that the majority of SQL Anywhere developers use a 4GL tool, although performance-driven applications tend to use Embedded SQL. The three most popular development tools are PowerBuilder, Visual Basic, and C++. There is no OCX or OLE interface, and this is primarily because those interfaces are usually part of the application development tool (such as PowerBuilder or Visual Basic) used with SQL Anywhere.
In addition to the normal pricing scale described at the beginning of this review, there are a couple of special license offerings available. First is a bundle of SQL Anywhere standalone with the upcoming version 5.0 of PowerBuilder. (Previous versions of PowerBuilder were bundled with Watcom SQL.) Second is the royalty-free runtime version of SQL Anywhere that can be used in embedded applications for $99. The runtime version has some limitations -- for example, you can't make any changes to the database schema.
With the release of SQL Anywhere, a good DBMS has just gotten better. Watcom SQL was a pioneering product for its time, and SQL Anywhere has continued that trend with innovative features such as SQL Remote replication for occasionally connected users. The new SQL Central database administration tool will be a big help for busy DBAs, and the support for Transact-SQL will appeal to businesses that want application scalability from the workgroup to the enterprise level.