Database Decision

[Roshawn]

Hi,

I am contemplating the development of a non-enterprise database application
that can be used by small businesses (used for subscription services). I
would like to make extensive use of ADO.NET in the app. However, I am
undecided on which database to use.

The database would have to meet some specific guidelines. It would have to
be easy to set up and deploy on a client machine, be lightweight, scalable,
have great performance, allow for multiple users in a client/server
environment, and allow for proper security settings. I would also like to
have the ability to connect to the database without going through a database
server (IOW, I'd prefer a file-based database)

Most people would suggest that I use MSDE 2000 because it meets the
guidelines set forth above, but I know that it installs the database server
(that users may hate). Others would suggest MS Access 2000/2002, but I am
aware of the issues regarding it (not highly scalable, poor in a
client/server environment if more that 5 connections, performance limitation
based on file size, etc.).

Of the two, MSDE seems most appropriate. On the other hand, I don't want to
upset the user by installing a server on their machine.

Can someone point me to some other databases that may meet my needs?

Thanks,
Roshawn

 

[Cezary Nolewajka]

IMHO, you should choose MSDE. I know solutions where MSDE installs nicely
with the whole application setup without users being aware that this is a
database server.

There is nothing to be upset about with database servers. Even contrary -
they give you reliability and stability you would never have with a shared
file access. Add to that scalability, security, ease of maintenance and you
have your answer.

In my opinion MS released MSDE for such small solutions because Access was
giving a lot of headaches to everyone.

Still, you can use Access for reporting, browsing data, forms, etc. with
MSDE at the backend.

 

[Sébastien]

I f you don't want to use MSDE' SQL Server, Access or mySQL, maybe should
you
write your own little database or stock your informations in a binary file ?

But MSDE should install easily, you should check on MSDN web site about how
to build your setup application.

That is my suggestion but probably not the best.

Sébastien

 

[DalePres]

On Microsoft's MDAC pages, they state that support for the Jet engine is
going away except for maintenance mode. I would be surprised if the next
version of Access doesn't have MSDE as the default database engine instead
of just being an option. In my humble opinion, creating a new production
application using Jet would be akin to creating a new VB6 application. You
can do it and get a few years life, but it's probably not a great idea.

Dale

 

[Bob Grommes]

Roshawn,

I understand that MSDE or its successor will be part of the standard
operating system install in future versions and even now, it can be
installed such that the user doesn't know or care that it's there. I would
go with MSDE; it's fast, and standard.

If you really insist on something else, you might consider Microsoft Visual
FoxPro ... it is somewhere between MSDE and Access in general stability and
scalability -- in other words, arguably better than Access, not quite as
good as MSDE. Actually for local or client/server access to the data files
with a moderate number of users it is probably faster than MSDE and just as
stable. Indeed, Fox's query optimizer technology was one of the main
reasons MSFT acquired it in the early 90's and that technology has found its
way into SQL Server, the local engine of ADO and (less directly) ADO.NET,
and (with less success due to fundamental architecture limitations) JET and
Access. It used to be said that Fox had a reputation for 3 things: speed,
speed and speed. Much of that has rubbed off on better-known MSFT products
since.

You can communicate with the current version (8.0) via an OLE-DB driver. It
supports relations; stored procedures and triggers (though they must be
written in FoxPro's proprietary extension of Xbase, not in SQL) as well as
transactions, blobs, etc. The main downside is that it is a product with a
lot of historic, backward-compatibility baggage and language bloat that can
get in the way of someone totally new to it. It is a COM application that
MSFT has made a strategic decision not to move to or interface to via
managed code; however, if used strictly as a DB engine it is hard to beat,
it's a mature product (5 major releases over 10 years under Windows alone,
with another in the works), and in my judgment likely to be fully supported
for about as long as Access. Plus, it's a more developer-oriented platform
than Access, which always was targeted more towards casual developers.
There is a thriving user community. And Fox has objects -- late-bound and
loosely-typed, but full inheritance nevertheless. It's kind of like dBase
meets Ruby.

Although as a tactical matter I have given up on desktop databases and avoid
them when I can, if I were going to use something other than MSDE I would
choose VFP in favor of Access unless there was some overriding requirement
such as heavy use of legacy Access within the organization.

--Bob

 

[Roshawn]

I would like to thank everyone for your insight. After doing some research,
it appears that MSDE 2000 is the way to go. As a second option I chose
Visual FoxPro.

I will be glad when the "Yukon" version of MSDE is available, though.

Thanks,
Roshawn

 

[Miha Markic [MVP C#]]

Hi Roshawn,

If you really want to avoid client-side install you might look into Firebird
or mySql - they both have an embedded module.
It is useful for single client solution.

 

[William \(Bill\) Vaughn]

I think that MSDE will work out. I understand that the next release of MSDE
will have a far more user-friendly installation paradigm.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Simon Morris]

I'm faced with a smiliar decision and am going to wait to look at VistaDB
(http://www.vistadb.net) before making my final choice.

- Simon

 

[Roshawn]

Hi Simon,

I've been looking a this database too. But I'm worried that it doesn't
support certain features that MSDE 2000 has (i.e. stored procedures,
timestamp, batch queries, etc.)

There appears to be no availability of a trial version either.

Roshawn

 

[Simon Morris]

Hi Roshawn

I've been looking a this database too. But I'm worried that it doesn't
support certain features that MSDE 2000 has (i.e. stored procedures,
timestamp, batch queries, etc.)

These aren't items that would worry me as my app has very simple
requirements. Two of my main things are stabilty/reliabilty and speed, which
I'd hope are a given. The other is security, which is not mentioned on the
VistaDB site.

I'd still be happy to use Jet which has good speed and acceptable
reliability (in single-user mode with a low number of updates), but I've not
been able to find a method for securing the data we sell that doesn't have
an over-the-counter way to be hacked.

There appears to be no availability of a trial version either.

I think this is because they're not at the beta stage yet. Early February
has been mentioned, but I think the developers are working on two projects
at the moment so the VistaDB one might slip back a bit.

- Simon

 

[Roshawn]

Thanks for the info, Simon.

Roshawn

 

[William \(Bill\) Vaughn]

Other factors that haven't been mentioned: the skills you acquire learning
MSDE/SQL Server are far more useful in a job search than those learned
implementing an obscure (albeit otherwise suitable) DBMS. I would also be
concerned about support. Look for books, articles, and tools for any
solution you consider--before you settle on any DBMS.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Roshawn]

You are absolutely right, Bill! Just about every job I've come across with
regard to programming requires some knowledge of MSDE/SQL, Oracle, MySQL,
and other widely supported databases. Funny how I never even considered
this, though.

Thanks,
Roshawn

 

[Anthony Carrabino]

Hi Roshawn,

We are the makers of VistaDB. Let me give you some insider info...

1. TimeStamp: 100% supported.
In addition to a set of rich data types (BLOB, Boolean, Character,
Currency, Date, DateTime, Double, Int32, Int64, Memo, Picture, VarChar),
we let you set "dynamic default values" using functions. E.g. GUID(),
Now(), Time(), Day() etc. can be set inside the database. So, yes,
TimeStamp is supported.

2. Stored Procs: we will have something better! The upcoming VistaDB
Server (due in July) will host the CLR. The new VistaDB Server will let
you write C#/VB.NET code, store that native code into a VistaDB database
as "Stored Code", and run it on the fly (runtime compiling), exactly
like you would Stored Procs. The differences is a much more powerful and
flexible way to run code on a server, much more managable source code,
code doesn't have to be related to database, and more efficient
debugging. This is a HUGE feature slated for Yukon -- VistaDB Server
will have it by the summer!

3. Batch queries: supported. Not sure how extensive your batches are,
but yes, you can run numerous queries as long as they are separated by a
";".

4. Trial Version: coming after 2.0 ships (June), or sooner.
VistaDB 2.0 (Beta 3) is nearly out and as soon as it is, we can focus on
making a 30-day trial available.

I highly recommned reading the info on our website. We have been working
on VistaDB for 2+ years _specifically_ to replace JET and MSDE. The MSDE
is 70MB+ in size, VistaDB is 1MB+ in size. That alone is a huge feature.
Furthermore, VistaDB includes both a fast 100% managed ADO.NET Provider,
and our own Direct Data Access objects which let give you high-speed
live data cursors into a database.

Please feel free to contact me direction with more qs.

Anthony Carrabino
www.vistadb.net
anthonyc(at)vistadb.net