Going Mobile: Data Management Solutions for Mobile Devices
July 23, 2010
Crunched by the economy and squeezed by competition, businesses and consumers alike are turning to mobile computing. This report wraps up our overview of the top contenders in the mobile database market with a look at Sybase SQL Anywhere and SQLite.
Sybase, makers of SQL Anywhere, just announced version 12 of their mobile database. Some of the new features and improvements with this version are support for spatial data, language extensions, performance improvements, support for the iPhone, read-only scale-out (read-only query isolation), central administration of remote databases, and much more. See a complete list here.
This sophisticated embedded database can support thousands of users. The database can hold hundreds of gigabytes of data and process up to 20,000 transactions per minute. It has robust capabilities including xml support, more advanced data types such as spatial (as previously mentioned), OLAP, and materialized views to name a few. Support for stored procedures and triggers is also included as is transaction support and full-text search. ODBC, JDBC, ADO.NET, OLE DB, and other connection protocols are supported. Special drivers are available for PERL and PHP.
Developers can take advantage of SQL Anywhere support for the most popular languages and development tools including Visual Studio (and the .NET languages), Eclipse, PowerBuilder, Java, PHP, and PERL. SQL Anywhere also comes with its own graphical tools to facilitate design, development, testing, debugging and monitoring of the database and synchronization. Similarly, administrators can use the tools to help them remotely support databases including auditing, trace logging, and database movement.
A deployment of SQL Anywhere requires 10MB of hard drive space and the engine will need 4MB of memory. A lighter engine, UltraLite, is available for small footprint devices. This engine only requires 75KB of memory. The UltraLite database runs in-memory (also an option for SQL Anywhere). UltraLiteJ is a version of the in-memory database for use on Blackberry devices and with J2SE.
Bi-directional replication is accomplished using MobiLink with support for both relational and non-relational data stores (such as JMS-based messaging systems and application servers). MobiLink has support for server-based synchronization, conflict detection and customizable resolution, row and column subsets for synchronization, push-notification, file transfer, and prioritization. RSA and ECC encryption protocols for data encryption are provided as optional purchases.
Operating systems supported for an installation of SQL Anywhere are Windows, Windows Mobile, Linux, embedded Linux, Mac OS X on Intel, Sun Solaris, IBM AIX, and HP-UX. The MobiLink synchronization server can be installed on Windows, Linux, Mac OS X, Sun Solaris, IBM AIX, and HP-UX. Database mirroring is available for fail-over support and SQL Anywhere has a Veritas Cluster Server (VCS) agent to allow for SQL Anywhere management within a Veritas Cluster.
SQL Anywhere comes with a Deployment Wizard to handle packaging and deployment. Either a Windows Installer Package or Windows Merge Module file can be created from the wizard. Additionally, database files can be pre-configured prior to deployment to reduce or eliminate install time configuration.
There are multiple editions of SQL Anywhere with different included and optional features. The following table was taken from Sybase's SQL Anywhere 11.0.1 Editions page. A similar comparison is not yet available for SQL Anywhere 12 that I could find.
SQL Anywhere 11.0.1 Editions
* The software will expire after 60 days if a free registration key is not provided during installation or before the 60-day limit.
** Please consult this matrix for a comprehensive listing of platforms supported for specific SQL Anywhere components: http://www.sybase.com/detail?id=1002288.
*** This option is free for development and evaluation use. Installation requires a registration key.
Licensing for SQL Anywhere is highly dependent on what you need to do with it, who will be using it, how many will be using it, and what platform you are targeting. For instance, a single CPU license of SQL Anywhere Advanced for Sun Solaris SPARC is $21,999. That doesn't include an Upgrade Subscription Plan (USP) or a support level. A SQL Anywhere Advanced 25 user license (only available for internal use) on the same platform is $11,549. In contrast, a SQL Server Workgroup Single CPU license on Windows is $2725. However, the developer edition is free. This information is for SQL Anywhere 11.0.1 as pricing is not yet available for SQL Anywhere 12 at the time of this writing.
SQLite is an embedded database engine, which is public domain. It consists of a single cross-platform file and a library that can be called dynamically or integrated into the application. Additionally, it can be configured to run in-memory. Popular applications like Apple's iTunes, Google Gear, Mozilla Firefox, and Adobe AIR utilize this database engine.
In its current revision, 18.104.22.168, SQLite supports most of the SQL-92 standard. It has limitations in areas such as ALTER TABLE, trigger support, and writes to views (see http://www.sqlite.org/omitted.html for a complete list). The user community has contributed to the product to increase the feature set such as Google's contribution to full-text search. The SQLite Consortium supports the continued growth of the product. This includes companies such as Symbian, Adobe, Bloomberg, Oracle, and Mozilla.
A fully transactional database engine, SQLite has the drawback of needing to lock the entire database for a write operation. Because of the database being a single file, it can allow multiple concurrent reads, but only one write operation at a time. Write operations will fail if any other operation prevents the locking, but retry behavior can be configured.
SQLite does not provide for replication or synchronization of its data with another database. It is less suited for sales force automation, for instance, than it would be for a consumer application on a smart phone. Its tiny footprint of less than 275K and maximum database size of a tera- or even peta-byte make it a compelling choice where simplicity is key.
SQLite deployment is also simplified. No installation is required and no server is created. The SQLite executable can simply be copied to the target device and executed. The database file itself can exist anywhere on the file system.
An interesting aspect of SQLite is dynamically typed columns, called manifest typing. In this database engine, the columns have a declared type, but any type of data can go in any column (with the exception of integer primary keys). This means a string value can go into a datetime column. The engine will attempt to turn the string into a datetime, but if it fails, the string is still stored in that datetime column.
Sqlite3 is an interface for creating databases and tables, querying, and data manipulation in SQLite. It runs as a Tool Command Language (TCL) command. Multiple commands are implemented as options for sqlite3 such as eval or collate.
Documentation for the database engine is provided on the official SQLite site, http://www.SQLite.org . In addition to the general documentation, the supporters have also provided sample code, upgrade guidance, and API references.
Whereas SQLite is free to develop with and deploy, technical support will cost. There are also extensions to SQLite that are not public domain such as the encryption library. This library has support for reading and writing encrypted databases using these algorithms: RC4, AES-128 in OFB mode, AES-128 in CCM mode, and AES-256 in OFB mode.
SQLite is by far the simplest and smallest of the mobile database and is already heavily used by many well-known companies and devices. However, it should only be used in situations where a complex engine is not needed and data synchronization is not a concern. Concurrency should also be a top consideration due to the database locking on writes.
Microsoft SQL Server is another free database and is more complex and capable than SQLite, however it is limited in its capabilities such as a lack of support for stored procedures and XML data types. Its integration with other products from Microsoft (such as SQL Server Management Studio, SQL Server Analysis Services, and Visual Studio) makes it a strong contender depending on your needs. Plus you can't really beat free.
DB2 Everyplace has one definite advantage over SQL Server Compact with its cross-platform support. There is a slight cost for a license of either of its two editions. Database edition is good for solutions not requiring synchronization or frequent updates. Otherwise, the Enterprise edition should be used.
The most robust and therefore most expensive choices for embedded databases are Oracle and SQL Anywhere. Both vendors supply a scaled-down lighter engine as well as the robust offering. However, cost could be prohibitive. The needs of the system would have to justify the increased footprint and cost.
As always, the devil is in the details. This is just an overview of the capabilities of each of these mobile database offerings. Requirements of your system should drive further examination of a hopefully narrower set of possible mobile solutions. For instance, if your system needed to work with spatial data, you could narrow the list of possible mobile databases and research those further to determine the extent of the support for spatial data.
For More Information