Data Management

SQL Anywhere Studio 9.0 proves to be a capable DBMS

SQL Anywhere Studio Version 9.0 from Sybase provides a feature-rich DBMS with a small footprint suitable for mobile devices. Find out more about what this DBMS can do for your next database project.


SQL Anywhere Studio 9.0 is Sybase's latest revision of its relational database management system. It's focused on providing database services to small- and medium-size businesses, mobile database applications, and embedded databases. For those of you familiar with Version 8, this release introduces XML and Web services features and tight integration with Microsoft's Visual Studio .NET.

Because SQL Anywhere is targeted to small- to medium-size businesses (SMBs) and developers, I focused much of my time for this review on ease of use. As the manual for the product states, "Workgroup databases must provide high performance out of the box without the aid of experienced database administrators." So with this in mind, I began my install.

Specifications:
SQL Anywhere Studio Version 9:
  • Developer Sybase Inc., through its subsidiary iAnywhere Solutions
  • Windows 95/98/Me, NT, 2000, XP, 2003
  • Mac OS X
  • 64-bit Itanium platforms (Windows Server 2003, Linux)
  • Windows CE (Pocket PC/Handheld PC)
  • Novell NetWare
  • Solaris/SPARC
  • Linux
  • Deployment options for HP-UX, IBM AIX, Compaq Tru-64
  • Requires a minimum of 8-MB RAM and 4 KB per client connection (8 KB for UNIX)
  • Suggested retail price $399


Installation
For review purposes, I installed a late beta of SQL Anywhere Studio Version 9 on a single processor Windows 2000 server machine with a GB of RAM. Installation was extremely easy with a wizard guiding me throughout the entire process. The entire process took less than 15 minutes and that included the time to scan the manual and the README files for any install notes.

Creating and starting a database with Sybase Central
Creating a database in SQL Anywhere can be done via the command line in Windows using a Windows NT command script, via a SQL script in Interactive SQL, or through Sybase Central. Sybase Central is SQL Anywhere's control center GUI that lets you perform database administration functions. Because many SMB users will not have previous relational database experience, most will likely gravitate towards Sybase Central, shown in Figure A.

Figure A
Sybase Central


Since I have had experience with other database systems such as Microsoft SQL Server, Oracle 8i, and MySQL, I jumped right in and tried to do things based on my experience with the other products. My initial reaction to the control center was that it is a little sparse and slightly less intuitive than the ones I have used in the past. This is not to say that there is anything wrong with the control center, but that things aren't necessarily in the places I expected them to be. Once familiarized with the GUI, creating a database, tables etc. was very easy. I imagine that someone with no experience will need to read the manual to give them a kick start, but the wizards included in Sybase Central will ease much of their pain. The manual is well written and easy to follow. Both beginners and experienced users will find it to be an excellent reference.

There are a couple of notable items in Sybase Central that deserve attention. The first is a very good thing known as the ASA Index Consultant (See Figure B). The index consultant is a wizard-driven query optimizer that will analyze your queries and recommend indexes that will improve the performance of your database. The results of the consultant are rich in detail and give you a plethora of information regarding your query plans.

Figure B
ASA Index Consultant


The other item is not necessarily part of Sybase Central but is accessible through it—the Online Books help system. While the information in it is good, the layout and navigation through the topics leaves something to be desired.

As for the rest of the traditional database functionality of SQL Anywhere, it is comparable to the other DBMS in the market.

Bread and butter
The reasons that most developers will gravitate towards SQL Anywhere are for its replication abilities and small footprint, which allow synchronization with remote databases. Whether they are in a data center down the street, on a laptop across the globe, or in a hand held device out in the field, you can synchronize with the databases.

Sybase SQL Anywhere has three replication technologies. MobiLink and SQL Remote are designed for replication between a central database and a large number of remote databases. Replication Server is intended for near-real-time replication between a relatively small number of databases.

The main difference conceptually between Replication Server and the other two methods is that it requires that a particular piece of data can be modified at only one location. Both SQL Remote and MobiLink allow the same data to be changed simultaneously at multiple locations and then provide a means of resolving any conflicts.

The three replication technologies mentioned above correspond with three distinct methods for moving data between databases. The first, MobiLink, is a session-based propagation method.

In a session-based method, synchronization occurs when a remote site connects back to a central server via a direct communication link such as a WAN connection, a traditional modem, or a radio modem. The remote sites connect at regular specific intervals ranging between minutes or weeks depending on how fresh the data needs to be. The process for synchronization in the session-based method goes as follows:
  • The remote server establishes a communication link with the synchronization server.
  • It uploads its changes.
  • It waits for the synchronization server to consolidate the changes.
  • It receives all relevant changes.
  • After it receives the changes, it sends back a confirmation to the synchronization server that it successfully incorporated the changes and then the connection is released.

The second method of propagation is message based using SQL Remote. Simply put, databases move updates and changes between themselves via messages. These messages are files placed in a particular directory through some transfer method, such as FTP, or they are specially formatted e-mail messages. A message agent, attached to each database, sends messages regarding changes to its own data. The same agent also receives messages from one or more other databases and modifies the database, according to the contents of the received messages. This method is designed to be used for databases that have only an occasional connection.

Lastly, there is connection-based replication using Replication Server. This method is designed for high-speed, real time replication between a small number of servers utilizing a continuous high-speed connection. In this method, changes from one database server are almost immediately transferred to a second server, with very little latency.

Clearly, the method you choose depends on your connection methods, the allowable latency between updates, the number of databases you have, and so on. SQL Anywhere's documentation provides a concise table to help you determine which method is best for your use.

Knowing the propagation method is one thing, but setting it up is another. For my review, I set up replication using SQL Remote. My central database resided on my Windows 2000 server and my remote database on my laptop running Windows 2000 Professional. While I cannot go into all the detail of the setup due to space constraints, the following is a summary of the steps involved and what is encompassed in them.

First, I created my central database and added the appropriate rights to allow replication. This involved granting publish permissions to a user ID to identify the source of outgoing messages and then granting remote permissions to all user IDs that were to receive messages. I then had to choose a message type. I could choose between e-mail or have files placed somewhere on disk to be exchanged. I chose to use the file sharing method and placed the databases exchange files in a directory located on the server.

SQL Anywhere uses a publication and subscription process in the SQL Remote method, so I created a publication to describe the data that was to be replicated and a subscriber, who was to receive the data. I then set up the remote database on my laptop using the database extraction utility within SQL Anywhere, which enabled me to carry out all the steps needed to create a remote database complete with subscriptions and required user IDs.

To test the replication, I made changes to the central database data and then created the replication file by running the Message Agent against the consolidated database. This is done at a command prompt. I then ran the Message Agent on the laptop to receive the data and then verify that indeed the changes had been replicated. I then reversed the process to send changes to the server.

The process worked well and was not overly complicated. I found it to be no more difficult than setting up replication on Microsoft SQL Server. While I don't think that someone without database experience could perform this process straight out of the box, I do believe that most developers should be able to follow the excellent instructions and tutorial provided with the software to create their own replication.

Couldn't test every component
I was unable to test the embeddable database features of the software or the Web services and the .NET integration due to equipment and time constraints. All three components are important selling points to developers and, based on the sheer number of components and features in the documentation, iAnywhere Solutions is well aware of this. Support for SOAP requests, XML in the database, inclusion of an ADO.NET provider and ActiveX components that enable development from Microsoft embedded Visual Basic and Pocket IE using Java Script are just a few of the developer-specific goodies to be found in the software.

Share your experience
Have you used the embedded features of SQL Anywhere, or perhaps its .NET integration capabilities? Help the Builder community flesh out additional details for this software by sharing your knowledge of this or previous versions of SQL Anywhere.

Overall, I found SQL Anywhere Version 9.0 to be an improved version of a DBMS that has been proven capable in the niche in which it has been placed. Developers looking for a database to support mobile computing or place into a client environment that doesn't have an experienced DBA should definitely pay attention to Sybase's latest offering.

Editor's Picks