SQLite vs. SQL Server 2005 Mobile Edition

[Daniel]

Hi all,

we are gonna have a project with around 1000 mobile Clients in mobile
windows 2003. A am evaluating, which Database is the best for this. I
searched in all forums and now I think the best options are SQLite and
SQL Server 2005 Mobile Edition.

I am not familiar with mobile development neither databases. Can you
give me a general comparison!? Ah, we want to use Compact Framework 2.0
and C#. I am unexperienced in this area so fast development time is
important. Another maybe most important aspect is the license. Is it
free if we get licenses for Visual Studio 2005? Or do we have to pay
for each client?

Also what I didn't understand... can you synchronize a Server Database
with a Client Database in case of SQL Server 2005 Mobile Edition? So
does this mean, you don't have to do the inserts, updates, blablabla on
the client? In fact each of our clients has different data.,

Thanks
Daniel

 

[Guest]

I am not familiar with mobile development neither databases. Can you
give me a general comparison!? Ah, we want to use Compact Framework 2.0
and C#. I am unexperienced in this area so fast development time is
important.

SQL Mobile has an full, supported managed interface, so development is fast.
I think there's one for SQLite as well, but I have no experience with it, so
no idea of the quality. Obviously it comes with no support. You're going
to want to add time and cost for more robust testing if you go that route (I
would anyway).

Another maybe most important aspect is the license. Is it
free if we get licenses for Visual Studio 2005? Or do we have to pay
for each client?

It follows the SQL Server model. If the server has a per-processor license,
then the clients don't need CALs. If it's licensed per client, they do.

Also what I didn't understand... can you synchronize a Server Database
with a Client Database in case of SQL Server 2005 Mobile Edition? So
does this mean, you don't have to do the inserts, updates, blablabla on
the client? In fact each of our clients has different data.,

SQL Mobile has full replication support, so yes synchnonization comes with
it.

-Chris

 

[Robert Simpson]

There are quite a few differences between SQL Mobile and SQLite. I'm not
really qualified to give a full overview of SQL Mobile's capabilities, but
here are some of the features and limitations of SQLite on the Compact
Framework:

- SQLite is tiny. The core DLL is only 261kb and the ADO.NET 2.0 Provider
is 89kb. These are the only two files required for the Compact Framework.
The Full Framework merges the two into a single assembly.

- SQLite implements most of the SQL92 spec. I've had a hard time coming up
with queries SQLite can't handle. Not supported are stored procs and udf's,
but SQLite does support custom collation sequences, scalar and aggregate
functions.

- SQLite's locking mechanism is a little draconic and designed for
simplicity. Only one writer at a time can be writing to the database.
Other writers are blocked until the first writer completes. No readers can
be actively iterating a table while a write is in progress, and a write will
wait until all readers are finished iterating.

- No nested transactions. Again, SQLite is designed to be "light".

- Full portability. Any SQLite database file can be copied from one
platform to another regardless of the CPU's byte-ordering. Build your
database files on the PC and just copy them over to any PDA.

- SQLite is faster than SQL Mobile, and SQLite's database files are smaller.
In a couple of simple tests inserting, selecting and updating an Int64,
SQLite was more than 10x faster. Inserts/updates that took minutes in SQL
Mobile took seconds in SQLite.

- No replication support. If you need to merge the PDA's database file to a
database file on the desktop, just copy the file over to the desktop, use
SQLite's ATTACH statement to attach the PDA's database to your master
database, and then write a few custom queries to merge the changes into the
main database. This should be pretty trivial.

- SQLite is 100% free, and full source code is available in the public
domain. No GPL, LGPL, or any other variation of open source license.

- The ADO.NET 2.0 Provider is well supported (by me at the moment), is also
free (in keeping with the spirit of SQLite) and sources are available on
SourceForge.

- The ADO.NET provider has VS2005 design-time support. You can create
queries, typed datasets, etc with it. I'm working on expanding designer
support to eventually include full database management from within VS2005.
Creating tables, modifying table definitions, indexes, constraints, etc.

For more details on SQLite, what it does and especially how it works in
..NET, head over to http://sqlite.phxsoftware.com

Robert

 

[Sachin Palewar]

We just did an application where we are handling about 25 mobile
clients and we used Ultralite from ianywhere, a sybase company. It
comes with a builtin syncronisation component called mobilink. You can
consider that too. I personally feel its a very good option. Their
developer community is also very supporting and active.

Setting up mobilink is easy as we just need to enter sync scripts in
plain SQL using its GUI interface. It also has option for generating
scripts automatically. You can sync with any ODBC database, we synced
with MS SQL Server in our solution.

Check out for more info:
http://www.ianywhere.com/developer/sql_anywhere.html

Oracle also has their mobile database called OracleLite, though I
haven't used it but I read that its latest version is compatible to
Grid Computing which is now a feature of Oracle Databases. I also don't
know that you can sync it with databases other than oracle or not.

You can get more info for OracleLite for www.oracle.com and goto their
Products > Database section.

Regards,

Sachin Palewar

Palewar Techno Solutions
Pocket PC & Mobile Software Development
Nagpur, India

www.palewar.com

 

[Daniel]

thanks for your suggestions!

I checked the ADO.NET page and found the encryption point:
* "Create and encrypt databases using Win2K+ Encrypting File System
(EFS). Supports creating NTFS compressed database files."
Does this mean, that my db is safe in case of loss of a mobile device,
cause only my application knows how to encrypt the db?

We also want to get a backup of the db on a memory card. Which options
do those different db's offer? I could think of just copying the file
every evening to a memory card. More realtime would of course be, to do
a backup action after every commited transaction. Which options do the
db's offer?

Daniel

 

[Daniel]

one other point:

we have to synchronize assignments between a server-db from a
jboss-webserver to our client. At the moment we think about
transferring xml-files between client and server and viceversa. Two
problems: transaction security and velocity!

With sql mobile or Ultralite would it be possible two handle those two
things? Do you have any experience with that?

Daniel

 

[Robert Simpson]

Windows Mobile doesn't store its files in NTFS format, and the Encrypted
File System that's part of NT is not part of Windows CE.

However, the idea of encrypted SQLite databases isnot unreasonable given the
extensible nature of the underlying file system. Dr. Hipp provides an
encryption addon for a fee already, but I have not seen it personally.

With any file-based database engine, copying the file to a memory card is a
trivial operation.

Robert

 

[Robert Simpson]

Sybase's Ultralite has quite a few limitations, not the least of which is no
ADO.NET-compatible provider, which locks you into their custom API.

As for Oracle's mobile product ... I shudder at all things Oracle. As soon
as they start mentioning things like "java stored procedures" on the
embedded platform, I'm pretty much assured it will run like a 3-legged dog.

Robert

 

[Sachin Palewar]

Robert I think you checked out sybases's ultralite some time ago. As of
now they have a ADO.NET provider.

ianywhere.data.ultralite is the namespace for ADO.NET provider, whereas
ianywhere.ultralite is for their legacy API which is now being phased
out.

Regards,

Sachin Palewar

Palewar Techno Solutions
Pocket PC & Mobile Software Development
Nagpur, India

www.palewar.com

 

[Sachin Palewar]

Daniel,

encryption is also supported by most of the DBs, also you can protect
your database by a password, so even if your device is lost, database
can't be open by others without knowing your software.

SQL Server CE has a single SDF file for database, which you can copy to
take backup. Ultralite also has UDB file for database.

I will say that backup is normally not an issue in device if you are
synching your mobile database with your server database continuously.
Mobile devices are used as temporary storage devices in most cases and
they keep sending data to server periodically.

Backup etc can very well be handled at server.

Sachin Palewar

Palewar Techno Solutions
Pocket PC & Mobile Software Development
Nagpur, India
www.palewar.com

 

[Daniel]

Sachin,

we will not synchronize with a server database cause we use our own
system (application server JBoss), but need to backup the actual data
before the syncronization. But of course with a single file it's no
problem.

Daniel

 

[Thomas Bandt]

Hi Sachin,

encryption is also supported by most of the DBs, also you can protect
your database by a password, so even if your device is lost, database
can't be open by others without knowing your software.

encryption is maybe supported by most of the DBs, but not by SQLite
in the free version.

I am playing around with SQLite and it is more than 5x faster than
SQL Server 2005 Mobile. The great advantage of SQL Server is its
integration in the MS development process with Management Studio
and so on, and of course, the possibility to safe the database file
by setting a password.

But these advantages are killed by the bad performance. I have to
develop an application which comes with ~ 15.000 records per table,
and I've tested it with ~ 1500, so I think I can't use it.

I am now looking for a "encryption or password" solution for
securing the SQLLite database for unauthorized access. It does not
have to be 100% secure, but the user should also not only need to
open the file to get the data.

To come to the point - is there any way to get it secure without
paying 2000 Dollars (this would make the project gainless)?

Regards from Germany,

Thomas
http://blogs.dotnetgerman.com/thomas/

 

[Christian Schwarz]

To come to the point - is there any way to get it secure without

paying 2000 Dollars (this would make the project gainless)?

A few weeks (or months?) ago someone posted a link to a self-made encryption
extension to the SQLite mailing list. Unfortunately I cannot find the link
at the moment...

You could also modify the SQLite's OS layer (sqlite3OsRead and
sqlite3OsWrite functions for instance) to implement your own encryption and
decryption layer.

Greetings, Christian

 

[Thomas Bandt]

Hi Christian,

A few weeks (or months?) ago someone posted a link to a self-made
encryption extension to the SQLite mailing list. Unfortunately I cannot
find the link at the moment...

Thanks for advice, I'll be looking about it.

You could also modify the SQLite's OS layer (sqlite3OsRead and
sqlite3OsWrite functions for instance) to implement your own encryption
and decryption layer.

Yes I could, but it would propably be cheaper to pay the 2000 $ than to
develop it by my self. ;-)

Greetings, Thomas
http://blogs.dotnetgerman.com/thomas/

 

[Christian Schwarz]

A few weeks (or months?) ago someone posted a link to a self-made

Thanks for advice, I'll be looking about it.

You can get the encryption extension here:
http://www.voidbrain.com/sqlitesec.html

Greetings, Christian

 

[Thomas Bandt]

You can get the encryption extension here:
http://www.voidbrain.com/sqlitesec.html

Thank you very much I'll try to combine it with the
ADO.NET 2.0 Provider [1] for SQLite.

[1] http://sourceforge.net/project/showfiles.php?group_id=132486

Regards, Thomas
http://blogs.dotnetgerman.com/thomas/

 

[Thomas Bandt]

Hi again,

You can get the encryption extension here:
http://www.voidbrain.com/sqlitesec.html

I tried to combine it with the ADO.NET 2.0 Provider from
Robert, but with no success - I have not enough experience
in C/C++ to get it running

Does anyone have this experience? I think it could'nt be
a big thing to extend the Interop-Projekt including full
SQLite engine to the security features ...

Greetings, Thomas

 

[Robert Simpson]

I'll be looking into this carefully after the 1st of the year.

Robert

 

[Daniel]

Hi Robert,

we made the decision to use SQLite with your ADO.NET Wrapper in our
project!
Evaluated UltraLite and Oracle Lite and now we think that SQLite offers
us all we need. Also first steps with your wrapper went fast! Not so
with UltraLite.

So, again, to understand that fully I have to ask again:
We are planning to buy the encryption-extension of D. Robert Hipp. Is
it then possible to start the encryption with your wrapper? (Windows
Mobile 2003, Compact Framework 2.0)

Thanks for your help
Daniel

 

[Thomas Bandt]

Hi,

So, again, to understand that fully I have to ask again:
We are planning to buy the encryption-extension of D. Robert Hipp. Is
it then possible to start the encryption with your wrapper? (Windows
Mobile 2003, Compact Framework 2.0)

good question! I evaluated also some databases and decided a few
minutes ago to use SQLite with the ADO.NTE Wrapper of Robert. It's
simply faster and more easy to deploy than other solutions especially
SQL Server Mobile.

My customer decided to do the first version without encryption, but
if the product sells well, I also have to implement a encryption
extension. And if Robert or somebody else won't implement SQLiteSec
in early 2006 I am also planning to buy the extension of Robert Hipp.

That's because I am also interested in the answer

Greetings,

Thomas