The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

A database for ISVs writing shrinkwrap software

I develop shrinkwrap, document-centric software that uses the Jet 4.0 database engine for my backend document storage.  The user never sees ".mdb", but instead sees a file extension associated with my app.  My application has "File|New" and "File|Open" for creating or opening new "documents".  Technically, these documents are just password-protected Jet/Access database files with my custom file extension.

Users can email these documents around.  In the vast majority of cases, these documents are only used by one user at a time, but when a two or more people want to share the document, they just place it on a shared file server.  I've been quite pleased with the Jet solution, although I'm not saying that Jet is perfect.  I've had to work around a bug here and there.  I've added an auto-backup feature to my app, mostly to keep the user from losing all their data due to corruptions (which are rare for most of my users, but do happen occasionally).

But now, I want to take my application in a certain direction, and I'm not sure if Jet is up to the job.  I’m highly inspired by the Outlook 2003 cached Exchange feature.  If you’re not familiar with this:  Basically you’re always working on an offline copy of your data, and as long as you have a connection to the Exchange server, you are synchronizing your changes.  You can pull the network cord on your computer at anytime, and Outlook doesn’t skip a beat.  Plug the network card back in, and it starts to synchronize changes as needed.  Having the ability to do this for my app would be ideal, to say the least.

Why?

1.    This would provide a constant/current backup copy of each user’s database on a well managed server.
2.    This would facilitate easy sharing of a document (database) between more than one user, without requiring a dedicated network connection.  (Think of roaming users with laptops as opposed to stationary users in an office with desktop workstations.)
3.    This would allow me to build a Web version of my application (think Outlook Web Access) to provide my users with remote access to their database when they are away from their computer.

I also want to develop a Pocket PC version of my application, and would require for it to synchronize either with the desktop database, or with the online database (on the web), or both!

I’ve looked into Jet’s replication features, although I don’t really have much experience with it.  This just might be the ticket to what I’m trying to do between the desktop database and web database via http(s?).  Although it seems a bit of a black box to me.  And what pre-built options would I have for the Pocket PC?  I’m leaning towards building my own merge replication solution.  Maybe I’m crazy!  But I’m a bit nervous about the solution provided by Jet.  As I consider building my own replication solution, I know I could really benefit from triggers, which, of course, Jet doesn’t have.  If I decide to stay with Jet, and don’t have triggers, I’d have to catch all data changes in my code, and update appropriate replication tables.  In other words, I couldn’t just “UPDATE TABLE SET … WHERE…” anymore.  :^(  Because that would bypass my code.

Am I right to be afraid of Jet replication?  What are some alternatives to Jet that meet these minimum requirements:

1.  The database file is stored as ONE file only (not two or more).  The database file includes *everything* needed and has no dependencies outside of itself (ie. lookup "orphaned users" re: SQL Server).

2.  Multiple computers can share a database just by placing it on a shared file server (no need to install a dedicated database server or open special TCP/IP ports).

3.  The engine is “embedded”.  Preferably it runs in-process (ala Jet dlls).  If it must run as its own process, it’s transparent to the user (and to my app).  (It would be nice if the engine files were installed next to my app so I have complete control.)

4.  It has decent support for OLEDB/ADO or ADO.NET drivers.

5.  Triggers (or a very good replication model)!

Thanks for taking the time to read this far!
Troy, MicroISV
Saturday, February 25, 2006
 
 
slartibartfast Send private email
Saturday, February 25, 2006
 
 
slartibartfast Send private email
Saturday, February 25, 2006
 
 
By "close to my criteria", do you mean that these database engines only meet some of my criteria?  I need suggestions that meet all of them.  Jet is already close to my criteria, and I don't have to change a line of code.
Troy, MicroISV
Saturday, February 25, 2006
 
 
SQLite:
From their FAQ:  "SQLite lets me insert a string into a database column of type integer!  This is a feature, not a bug. SQLite does not enforce data type constraints. Any data can be inserted into any column."  Moving on!

Firebird:
Looks nice, but it doesn't allow multi-user via the file system.

Mimer, Kirix:
Hard to find answers on their website.  :^(  Anyone else know?

VistaDB:
This might be perfect!  From their website:
"All data is stored in a single .VDB database file."
"VistaDB 2.0 is a "file server" type database. VistaDB 2.0 supports single-user and multi-user access to database files."
"VistaDB engine VistaDB20.DLL (500KB) in 'embedded mode'"

Plus it has the option to use their server (tcp/ip) product to scale up to larger multi-user scenarios.  Looks like all you have to do is change the connection config.
Troy, MicroISV Send private email
Saturday, February 25, 2006
 
 
About SQLite. I use it for my file format and I also moved on from Jet although for different reasons. I am not sure if it is a good fit for your problem but it is used in Spotlight (in the Macs) with very good results. The fact that it stores data in a different way may be immaterial to your problem. It was for me.

I also looked at the other options when I was doing the migration and SQLite seemed the better supported and the livelier project. It is also binary compatible across platforms, which is a must for me.
JSD Send private email
Sunday, February 26, 2006
 
 
I don't know much about it but SQLite looks great: single file (like your MDB), small footprint (better than Jet), no dependencies. I would not discount it for its type system unless you are sure it is wrong for you. The only potential problem I see is its limited support for concurrency. It seems to depend on OS file system locking which is a concern, especially on Windows and across the LAN. As I recall Access used an LDB file that would appear next to your MDB file in multi-user situations to do locking in a non-FS dependent way, but may be this was a long time ago. Best wishes.
Ben Bryant
Sunday, February 26, 2006
 
 
"I would not discount it for its type system unless you are sure it is wrong for you."

I'm sure.  Thanks for the suggestion, anyway.
Troy, MicroISV Send private email
Sunday, February 26, 2006
 
 
Am I right to be afraid of Jet replication?  Also, what kind of synchronization options do I have with the Pocket PC and Jet?
Troy, MicroISV Send private email
Sunday, February 26, 2006
 
 
Mimer has a similar structure to Sql Server. Installs as a service and has seperate database files. It has a substantial feature list and is decent quality.
Not what your after though if you want to send the database files about. Same issues as sql server.

I'd look at sqllite which is a single stand alone file like jet. I think it is one also one dll instead of the MDAC quagmire.
Kim
Sunday, February 26, 2006
 
 
Did anyone else read your requirements?  SQLite, for example, doesn't have any form of replication.  It has this ( http://www.sqlite.org/sharedcache.html ) but that's as close as it gets.

I guess the lack of an answer indicates there is no alternative?
pds Send private email
Sunday, February 26, 2006
 
 
Could you use some kind of message queueing system (MSMQ?) between your application and database?
devuser Send private email
Monday, February 27, 2006
 
 
"doesn't have any form of replication."

I don't necessarily need replication (would be nice), but triggers would enable me to more comfortably roll my own.

"I guess the lack of an answer indicates there is no alternative?"

I think so.  Too bad, because it seems like this would be a killer app (a database engine that allows developers to build an end to end solution allowing smart clients to work online/offline seamlessly).

"Could you use some kind of message queueing system (MSMQ?) between your application and database?"

I hadn't thought of that, but this type of thing might be something to consider if I end up rolling my own replication.

So far VistaDB is looking to be the best fit for my needs.  I'm looking forward to reading the docs on their 3.0 engine with it's ability to host the CLR, and store native .NET data types.

One aspect of VistaDB 3.0 that should also be mentioned is that it is supposed to work on the Pocket PC (and Mono) as well, since it's rewritten in completely managed code.  Thus the Pocket PC requirement that I mentioned would also have some help.
Troy, MicroISV Send private email
Monday, February 27, 2006
 
 
The "Advantage Database Server" satisfies all your requirements, I think.

They have a freely available (i.e., no cost) fileserver version that could be used at all your local installs.  Then there is a db-server version (not free) that could run at the main site that your local sites are replicating to.  Replication is an add-on that you pay a per-instance licensing fee for, I think.  Advantage has been around for a long time and is robust, pretty fast, and fairly feature-rich with SQL (e.g., includes triggers).

Advantage's creator, Extended Systems, was bought out by Sybase last year. The new site seems a bit confusing to me, but here's a link to get you started:

http://www.advantagedatabase.com/web/content.aspx?key=4290F5759B1F5D69F34AD94713D236EF
Herbert Sitz Send private email
Monday, February 27, 2006
 
 
Whoops, don't think Advantage satisifed the single file db requirement.
Herbert Sitz Send private email
Monday, February 27, 2006
 
 
I've never actually used it like this (and you probably arn't using .NET either) but the .NET DataSet supports a disconnected mode. The theory is that you can continue working on the dataset whilst disconnected and serialize it to a local XML file until you reconnect where it is automatically syncronised.
Adrian
Monday, February 27, 2006
 
 
"you can continue working on the dataset whilst disconnected and serialize it to a local XML file until you reconnect where it is automatically syncronised."

I've thought about this.  But I see a few problems:
1.  Doesn't support multi-users sharing the XML file.  This is a show-stopper for me.
2.  Hard to enable 3rd-party report apps.  All the reporting has to be done through your app.
3.  I'm guessing that it would be slow to load/save the XML to/from memory.
Troy, MicroISV Send private email
Monday, February 27, 2006
 
 
I think that Enterprise Architect files are mdb files (with an "ea") extension. EA supports "create replica from master" and "synch replica with master" operations (during which you can't do anything else), which is useful for working with local replicas.
Christopher Wells Send private email
Monday, February 27, 2006
 
 
> I've thought about this.  But I see a few problems:

I think with a disconnected dataset, you can continue using your existing Access database as the main database held on the server. All users share this, and any existing reporting you have will continue to work. If you pull the plug on the network, you still have an in-memory copy of your database stored within your dataset. Each user can perform selects/inserts/updates and they'll all get synchronised when the network is re-established. If a user wants to shut down the application *before* the network is re-connected, you have the option of serializing the dataset to a local XML file. The only problems I see with this are:

1) If you have a very large database then obtaining an in-memory copy every time the app starts is impractical (maybe you could just grab the most relevant data?)

2) If several users are likely to be updating the same records whilst in disconnected mode, then you might need manual intervention during the synchronisation.
Adrian
Tuesday, February 28, 2006
 
 
Adrian
Tuesday, February 28, 2006
 
 
DataSnap also supports a disconnected mode and is part of the Borland Developer Studio we use.  Like Adrian mentioned, DataSnap caches to a local XML or flat file until the connection is resumed.  And you can share the cached local file.

Here's a blurb that kind of touches the surface of what you can do:
http://bdn.borland.com/article/borcon/files/2106/paper/2106.html

Not sure if Jet is supported directly but for sure possible through an ODBC layer.

Hope this helps...
Eric (another ISV guy with his company)
Tuesday, February 28, 2006
 
 
and here's another link on the briefcase model:

http://www.drbob42.com/examine/examin64.htm

NOTE: They talk about Delphi (object pascal) but the article applies to C++ Builder as well.
Eric (another ISV guy with his company)
Tuesday, February 28, 2006
 
 
"And you can share the cached local file."

At the same time in a concurrent way?  Maybe I'm missing something, but it doesn't look like a multi-user database engine to me.

Also, I see a few problems:
1.  Hard to enable 3rd-party report apps.  All the reporting has to be done through your app.
2.  I'm guessing that it would be slow to load/save the XML to/from memory.
3.  I'm looking for a much more granular way to keep two databases in sync.  The briefcase model keeps the server up to date, sine the client keeps a change log.  But it doesn't address how to keep the client up to date in an efficient way.  I don't want to download the whole 50 MB file every time!
Troy, MicroISV Send private email
Tuesday, February 28, 2006
 
 
If you're still thinking about this, really look into JET replication.  As far as I know it works, and if you need actual expert advice, comp.databases.ms-access has a lot of discussions about it.  Anyway I think it would be easier to just start using Replication with your current system than to completely migrate away to other database systems, and will probably work better than the others.

I'm pretty sure that replication is exactly what you're looking for, with more complexity than you're willing to handle (but it's probably as simple as it can get).
pds Send private email
Wednesday, March 01, 2006
 
 
"It seems like this would be a killer app (a database engine that allows developers to build an end to end solution allowing smart clients to work online/offline seamlessly)."

I agree -- I've been looking for a solution like this for a long time, haven't found anything that fits the bill.  Perhaps VistaDB 3.0 will; I've written to them encouraging them to build in some kind of synchronization for this release, since it's supposed to run on PDAs.

The only thing I've found that comes close is db4o ( http://www.db4o.com ).  It's an object database, which I've resisted a bit because of the obscure querying syntax and so on.  In the recent versions, they've got "natural language queries," which make things a lot easier.  You can synchronize various instances of db4o (it runs on PocektPCs), and they use a Hibernate-based system to synchronize with SQL Server and so on.  Caveat: I've just started using it in a project, so I can't report on robustness and so on.  But it's easy to use, and I like it so far.

  -- Vikram

Tuesday, March 07, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz