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.

migrating from jet to ?

I'm at the end of my rope with Jet. 

I've got *very* complex queries and large data sets.  I need to move to something more powerful and I don't have time to mess around.

I don't need network support.  This is a standalone db, located on the same machine as the app.

I do need to convert the db into a file (backup and restore)

Possible options (with the limited information that I think I know):

SQL Server - costs something to distribute; should be easy to convert; big community (books, developers, etc.)

MySQL - low cost; don't know if query optimization is any good; big community; don't know when a stable version with stored procedures is available.

Firebird - free; don't know if query optimization is good; backup files are unknown, etc.

Can anyone fill in some of the many blanks in my knowledge?

My fantasy: a full-featured db (big data, great query speed, backup files) with a big community and 0 cost.

Mike
Bankstrong Send private email
Friday, April 29, 2005
 
 
"My fantasy: a full-featured db (big data, great query speed, backup files) with a big community and 0 cost."

In that case, I'd scratch MySQL off the list and add PostgreSQL.  Query optimization is good, even for large datasets and heinous queries.  Backup/restore is trivial, as well.

The only thing that's likely to be annoying is the periodic vacuuming.  Rather than purge the old versions of rows in real-time, once the db realizes no existing connections need to access the old versions, it does it as a periodic process (which it calls vacuuming); this is typically the only administrative operation besides backup that you'll need to schedule.  If you have a reasonable way of doing that (or even if you don't - they're building more reasonable ways of doing this into the app all the time), look into Postgres.  http://www.postgresql.org
schmoe Send private email
Friday, April 29, 2005
 
 
MSDE gives you the advantages of SQL Server, but without the cost.  The DB size is limited to 2GB, but then you can easily upgrade to full SQL Server and if any of you customers can fill that up they ought to be using SQL Server anyway.
Joel Coehoorn
Friday, April 29, 2005
 
 
Schmoe -

I forgot to add - this will be on WinXP.  The postgres web site says that the windows port is not mature yet.  Do you agree or are they just being extra-careful?

Joel -

I'm including MSDE in "SQLServer".  This definitely reduced cost - I don't need to buy it for smaller customers.

Mike

PS - one more feature in my fantasy list: encryption/security.  No one should be able to tap into the db without a password.
Bankstrong Send private email
Friday, April 29, 2005
 
 
I have no data on the Windows port.  I assumed you were on a Windows system, and I knew they had a Windows port, but I didn't know they didn't consider it mature.

In the absence of further data, I'd be inclined to trust their judgment that it's not ready for prime time.  If you can find someone else with data that it's stable enough for you, that might be good enough, but I'm not that person.  :)
schmoe Send private email
Friday, April 29, 2005
 
 
Maybe Sqlite?

http://www.sqlite.org/
TownDrunk Send private email
Friday, April 29, 2005
 
 
If you are considering the free MSDE then you might also look at its successor, SQL Server 2005 Express Edition:

http://www.microsoft.com/downloads/details.aspx?FamilyId=97383299-E74B-4AF8-90C7-FA0CA20A10C4&displaylang=en

It's the free edition of SQL Server 2005 and, even though it is pre-release, you can use it in production today:

http://www.microsoft.com/presspass/press/2005/apr05/04-18VSSQL2005PR.asp
Mike Green Send private email
Saturday, April 30, 2005
 
 
"The postgres web site says that the windows port is not mature yet.  Do you agree or are they just being extra-careful?"

All previous versions of Postgres were all iffy on Windows.  The latest version is Windows native (no Cygwin) and been heavily tested.  I'd say they are being extra-careful because this essentially the first version for Windows but it honestly should be fine.

I'd consider it first over MySQL and Firebird.  MySQL is great, stable, and whatnot but, from the type of usage you describe, it would drive you insane.  Firebird is alot like Jet in that's very old and has lots of strange inbuilt limitations.
Almost Anonymous Send private email
Saturday, April 30, 2005
 
 
"Firebird is alot like Jet in that's very old and has lots of strange inbuilt limitations."

Yes it is old...but how is that a bad thing?

What are the "strange inbuilt limitations"?
Chris Send private email
Saturday, April 30, 2005
 
 
I would rule out MySQL versions <= 4.1, as other posters suggested.

You could try MySQL 5.0.  It's in development status though -- not release status, but in my experience they fix bugs and anomalies really fast; and even the development versions are very stable (I never recieved a crash or wrong data in a MySQL development version...)

What I would do is download MySQL 5.0, install it, get a snapshot of your data moved over, and then try as many complex queries as you can to test if it will be suitable.

If it doesn't meet your expectations, then try some of the other fine DB's suggested.  If the size of your data will always be less than 2GB, then MSDE Express might be your ticket.  I like PostGres too, but I don't think it's as widely used as MySQL (but it has great transactional abilities).  Also, MSDE and MySQL you can buy support contracts, though there might be some 3rd party offering PostGres support.

What else?  Encryption.  I couldn't tell you.  I can tell you that what you're encrypting and how you're encrypting it could have a huge performance impact regardless of DB. 

For example, if you encrypt last name, and then perform a query where you want all of the last names between 'L' and 'N', inclusive, depending on the encryption method, you might have to read *every* last name, decrypt it, and see if it's the one you're looking for...  So, that's something to consider...

Anyway, I hope you find the DB that is best suited to your needs.

Peter
Peter Sherman Send private email
Saturday, April 30, 2005
 
 
"Yes it is old...but how is that a bad thing?"

Anybody remember Windows 95?  DOS? 

"What are the "strange inbuilt limitations"?"

There are limitations on key size.  Maybe that's not a problem, but this page exists to find out:
http://www.volny.cz/iprenosil/interbase/ip_ib_indexcalculator.htm

It might not be a pratical problem but I find it a bit distasteful.
Almost Anonymous Send private email
Saturday, April 30, 2005
 
 
Mike,

Since you said that the database will be on the same machine as the app, you might want to look at SQLite. The URL is http://www.sqlite.org/
Slartibartfast Send private email
Sunday, May 01, 2005
 
 
It depends largely on what your front end is written in and whether its separated into the three general layers.  If its VBA to Jet then the simplest migration would be to MSDE, then you would get the performance enhancements of the DBMS.

However, you have to bear in mind two things.  With a single machine using VB you're still basically in a single wrapped application rather than client server and if you are using VB you aren't really going to get as big an improvement as you'd hope for as VB isn't data centric.

Personally, and this is a bias from experience and given you don't have the same experience it may well be exactly the wrong thing to do, I would reengineer in Visual Foxpro, not particularly for the database, that could be in MSDE or native VFP, but because VFP is a data centric language and also gives me the N-tier separation that also removes (or at least minimises), the dependance upon any particular DBMS.
Simon Lucy Send private email
Sunday, May 01, 2005
 
 
Oh, and MSDE is being replaced with SQL Server 2005 Express, Emperor's new clothes and all...
Simon Lucy Send private email
Sunday, May 01, 2005
 
 
What about Cloudbase from IBM? It's mature and Open source, but it's Java-based, which you may not like...
c-choox
Sunday, May 01, 2005
 
 
The key limitation in firebird can be anoying.

Version 2.0 fixes this problem though:

http://www.ibase.ru/firebird/fb2alpha01_rn_0200_02.pdf
Chris Send private email
Sunday, May 01, 2005
 
 
A few here have mentioned MySql, but it is not free for commercial use

Also, MySql has very poor support for scripting (is there even a server side language out yet?).

And, triggers etc. requires the inno extensions (at least this is now part of the disto).

The free sql-express edition from MS seems about the best choice. It supports triggers, t-sql (for server side procedures), and even supports the running of .net code on the server side (that is sooo cool!!). The throttle, and user limitations that the old MSDE had are now removed.

Max data database size is now 4 gigs.

So, you can pay to use MySql, or you can get a free sql server with the following features:

Some features from the web site show:

  Stored procedures
  Views
  Triggers
  Cursors
  Extended indexes
  Snapshot isolation level
  Advanced query optimizer
  Transact-SQL support

 XML Support

  Native XML datatype
  Support for structured and semi-structured data
  XQuery support
  Transactional replication subscriber
  Merge replication subscriber
  Support for distributed transactions

Hum, for a free engine (like JET is), the express edtion seems darn good. You could however purchase MySql, and use that also.

Albert D.Kallal
Edmonton, Alberta Canada
Kallal@ msn.com
http://www.members.shaw.ca/AlbertKallal
Albert D. Kallal Send private email
Sunday, May 01, 2005
 
 
Encryption for local databases is best done by the OS itself.
i like i
Monday, May 02, 2005
 
 
Oracle Personal Edition? $500 a seat, but it's the real deal -- full Oracle functionality

http://oraclestore.oracle.com/OA_HTML/ibeCCtpSctDspRte.jsp?section=10168
David Aldridge Send private email
Monday, May 02, 2005
 
 
Thanks everyone for excellent advice.

I went ahead with SQL Server/MSDE (I decided I just don't have any time with a possibly tougher migration).

We've already put in two long days (many complex queries, lots of vestigial stuff in our VB code to move to stored procedures, etc.).  I expect another two.

But this stuff is amazing!  I was an extreme skeptic on the benefits of moving from Access.  So far, SS rocks.

One extra benefit - SS files are a third the size of my humongous Jet files (about the size of the compressed Jet files).  I'm not surprised that the data file works more efficiently, but I did not think about how many more customers I can handle with MSDE.  This path seems much more economical than I thought.

Mike
Bankstrong Send private email
Monday, May 02, 2005
 
 

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

Other recent topics Other recent topics
 
Powered by FogBugz