HomeFeatures

Going Mobile: Data Management Solutions for Mobile Devices

Deanna Dicken
By Deanna Dicken


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.

SQL Anywhere

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

Feature

Included Feature

*** This option is free for
development and evaluation use. Installation requires a registration
key.

Legend:

Included Feature

– Included feature

O

– Paid option

n/a

– Option not available in this
edition

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

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, 3.6.23.1,
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.

Conclusion

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

SQL Server Compact 3.5

Wikipedia: SQLite

SQLite

Oracle Database Lite 10g

Oracle Database Lite Client

Oracle Database Lite Mobile Server

Sybase UltraLite

Sybase SQL Anywhere

DB2 Everyplace Database Edition

Gain the Competitive Advantage: Data Management Solutions for Mobile Devices

»


See All Articles by Columnist
Deanna Dicken

Deanna Dicken
Deanna Dicken
Deanna Dicken is an architect with approximately 20 years of IT experience. In that time she has worked on many large-scale, mission-critical applications and has been involved at all phases of the lifecycle. She has also contributed to three SQL Server MCSE books, co-authored Learn SQL in a Weekend, and tech edited many other titles. She lives on the outskirts of Indianapolis, Indiana with her husband, Curtis, and children, Kylee and Zach.
Previous articleDatabase Security Threats — How to Stop Them
Next articleSQLDBManager Plus Launced to Help DBAs Diagnose SQL Server Performance

Related Articles

Latest Articles

DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.

Advertisers

Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.

Advertise with Us

Menu

Our Brands

Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.