I.T. Discussion Community!
-Collapse +Expand
DBA
Wes Peterson
-Collapse +Expand Profile
Member Info
Member Narration

POSTS:
MB Posts (158)
KB Posts (163)
KB Comments (34)
Blog (20 Topics)

KBPOSTSBYTYPE:
Tech Articles
FAQ Page (6)
Tips Page (6)
Definitions Page (4)
Resource Links (17)
File Library (11)

Search wes:

Advanced
   ► KBRole-Based T...DBA & Data   Print This     
 
DBA & Data:
Databases: Which are Best?
 
Posted 14 years ago on 10/16/2006 and updated 12/5/2008
Take Away: Which databases Prestwood Software prefers - and why.
 A blog topic from wes's Blog

KB100406

[Updated 06/06/2007] 

Talk about opening a can of worms! If you ask (or answer) that question - be prepared for heated replies.

So, to save my own skin and, hopefully, keep things down to a low roar, let me first state emphatically that, in the world of software development, there is seldom one, clear "best" way to accomplish a given task.  In reality, there are always scores of wrong ways, probably dozens of ways that would work adequately, and two or three really nice solutions.  Picking which of those final candidates is "best" is seldom straightforward - with good arguments available for each option.

In my experience, this principle applies when it comes to choosing the database for a project, too.  Rejecting the "wrong" options is usually pretty easy.  If we're talking about a single-user, desktop program, something like Oracle is almost certainly overkill.  At the other end of the spectrum, a high-traffic, application, serving thousands of concurrent users, clearly won't be do well with Paradox or Access tables as its back end. Picking the "rightest" database is a little trickier.

I think it's easiest to begin the selection of a database by first understanding two essentials:

  1. The "scale" of the application (db size, and concurrency requirements), and
  2. Whether or not there's an existing, populated database that must be considered.

In many Prestwood projects, we're called upon to enhance or maintain legacy applications with an existing database.  Those cases typically involve Paradox, Access, or one of the "xBase" variants (dBASE, Clipper, FoxPro, dBXL/Quicksilver). If we were tasked with creating an entirely new application from the ground up, none of those databases would be very high on our list of potential candidates, regardless the scale of the application.

But for existing applications, clients may have good reasons for wanting to stick with the existing data store.  Those reasons usually include familiarity with existing reporting tools, resistance to having to learn new tools, fear of losing crucial data during a migration, and the expense of data migration.

In these cases, provided the existing database has the ability to scale well to the clients' expected needs, we'll happily work with those databases, but not without advising the client of the benefits of updating to a newer database - and the risks of retaining the existing one.

One other factor often argues for switching to a newer, better database: Sometimes the "legacy" applications we're asked to support have databases cobbled together by people not particularly skilled in database design.  When we encounter a patently horrible database design, we'll often recommend that the client allow us to redesign the database.  Since that, alone, can involve significant expense, the added expense of switching to a newer database becomes a smaller part of the overall picture - and makes more and more sense.

"Scale" encompasses the issues of anticipated database size (kilobytes, megabytes, gigabytes, or larger), concurrency (number of anticipated concurrent users), and load (anticipated database access "traffic.").

Working our way through common categories of scale, then, the following are Prestwood's favored databases - and some of the reasons why.

Single user, desktop (or laptop) database applications. 

Issues we take into consideration are:

  • Database stability
  • Database performance
  • Can it be embedded in the application?
  • Vendor stability
  • Vendor support
  • Cost per end user
  • Can it run, standalone, from CD?

Our primary candidates, here, are DBISAM, Advantage Database, and Microsoft SQL Server 2005 Express. But, before we talk about the reasons for choosing these, let's talk a bit about the candidates we've rejected:

xBASE: (dBASE, Fox, Clipper, etc.).  This is technology from the early 1980's, and its age is definitely showing.  As databases go, it's probably the most primitive still hanging on.  "Out of the box," xBASE databases don't strictly embed in their applications - they rely on DLLs that must be installed. Third-party solutions, like Apollo, do allow an xBASE engine to be truly embedded in an application.

Paradox: Like xBASE, we're talking old technology.  While Paradox provides several features missing in xBASE, it always requires the installation of supporting DLLs.  I'm not aware of a product that allows Paradox to be completely embedded.

Paradox also carries one unwelcome albatross around its neck: The "BDE."

In its day, the Borland Database Engine was an amazing development.  It allowed several different databases to be "exposed" identically in development tools.  But life moves on, and Borland eventually sold Paradox, and the BDE, to Corel.  There are cases in which developers' BDE-based applications are "stepped on" by subsequent installation of Corel's Office Suite - which install's Corel's own incarnation of the BDE. This can lead to a lot of support overhead.

Finally, Corel, themselves, don't seem to think much of Paradox.  Just click the link above and try finding anything useful about Paradox.

Access: Here, we're actually talking about the "JET" database engine that's "under the covers" of Access.

JET is actually a pretty nice, reasonably modern database. It has a rich set of data types, built-in support for referential integrity, user-friendly reporting tools, excellent performance, and good stability. 

What JET lacks is an ongoing commitment from Microsoft.  The JET database engine is, essentially, deprecated.  At some point in the probably-not-distant future, Microsoft is going to pull the plug on JET, and Prestwood doesn't want to be holding the bag when that happens.

Now - about our favorite candidates for single-user applications. One thing each of these has in common is zero cost per end user. This can be extremely important to a client that wants to market the final product. 

DBISAM: Stable as a rock, and fast.

The DBISAM "engine" compiles directly into its host application's executable, making it automatically, fully embedded. Not having to install DLLs or other supporting files makes for very simple installation programs.  We've created several DBISAM projects in which  the setup program installs only two or three files: The program executable, and help files.  The first time the program is run, it can create its own database.  This makes for a very "clean" and trouble-free setup program.

DBISAM itself isn't free, but licensed developers (of which Prestwood is one), can distribute DBISAM applications without further expense to end users.

Elevate Software, makers of DBISAM, have been around for many years, are strongly committed to its growth, release updates several times each year, and provide the best support of any vendor I've had the pleasure of using.

DBISAM also integrates well with our development tools, and is a sheer pleasure to work with.

Another neat DBISAM feature is that it "plays nice" with CD-only usage.  Say, for instance, that you want to publish an electronic catalog on CD.  With DBISAM as the supporting database, there is nothing at all to be installed on the end user's machine.  Your program will simply run from the CD.

Finally, DBISAM scales nicely from single-user desktop applications, through large sized shared-file multi-user solutions, to full-blown client/server projects.

Since first writing this post, Elevate Software have released ElevateDB, a worthy successor to DBISAM. I've posted a brief introduction here.

Advantage Database: Recently acquired by Sybase, the Advantage Database is one of the few that have leveraged the old xBASE table format into a modern incarnation. Advantage has been around for years and years, and support has been good.  Speed and stability are also good.

Advantage, to my knowledge, is not completely embeddable: It requires the installation of DLLs.

SQL Server 2005 Express: Though not precisely intended for single user applications, SSE can certainly do the job.  It's about as modern as a database can get, so there's some justification for the hassles of installing the (required) .NET framework and SSE to support a single-user application.

Small-to-medium scale, multi-user applications:

These applications can treat their database in one of three ways:

  • Shared-file, multi-user (SFMU), or
  • Client/Server (CS)
  • N-Tier

Traditionally, these applications have used the SFMU model. With databases like xBASE, Paradox, and Access, this is the only way to address multi-user requirements. Among the reasons the SFMU design remains prevalent are:

  • The traditional high cost of CS databases (that's changed),
  • Limitations of popular "desktop" databases (Access, Paradox)
  • Developer familiarity with SFMU
  • The expense of a database server machine (now quite low)

Today, at least two CS solutions are virtually free: DBISAM, and SQL Server 2005 Express.  Others, like Advantage Database, are reasonably priced.

At Prestwood, we always stress the advantages of CS over SFMU. No matter how small the project, if it involves multi-user database access, we feel strongly that SFMU is to be avoided whenever possible. The two major reasons are:

  1. Application complexity: All business rules have to be implemented and enforced in the application code.  This complicates development, and limits the value of the database to other applications that might need it.
  2. Potential data corruption: Any number of unfortunate events can cause data corruption in SFMU.  A workstation crashes while attempting to update the database. A network card gets flaky. The cat chewed through a network cable.

CS solutions are virtually immune to corruption issues, and they allow much of the business logic to be handled by the database itself - rather than application code.

Modern development tools, like Delphi and Visual Studio .NET, make it just about as easy for developers to work in CS mode as it is/was to work with SFMU.  The CS model, can, in fact, simplify development. Per-user costs can be as low as zero, and the prices of hardware have fallen to the point that the cost of a database server machine is almost always a non-issue.

If we must use the shared-file, multi-user model on a new project, our number one candidate is DBISAM.  It works exceedingly well in that mode, remains fast and stable, and doesn't suffer from the "left over lock files" problem that afflicted misused Paradox applications.  As long as the network is stable, data corruption in DBISAM is virtually unheard-of.  If/when corruption does occur, DBISAM includes the utilities necessary to validate and repair its tables.  The success rate for table repair is unusually high with DBISAM.

When we can use CS for small to medium sized multi-user applications, our number one choice is SQL Server Express 2005. It's advantages are many. Here are a few:

  • It's free.
  • It's from Microsoft and is, essentially, a "de-tuned" version of their flagship heavy-duty database. Being from Microsoft doesn't necessarily make it great, but many clients, especially those intending to market their project, feel that SQL Server will produce the fewest customer objections.
  • It provides a clean path to scale up to full-blown SQL Server 2005 if/when the client's business has the good fortune to grow to the point that a more "heavy duty" solution is in order.
  • It supports (and requires) the .NET framework.  This is the future of Windows operating systems, so a system that utilizes SQL Server 2005 Express is positioned nicely for the next versions of Windows.
  • Stored procedures and triggers can be written in .NET compiled languages.  This not only eases the task of coding complex procedures/triggers, it makes them exceedingly fast.

Our second choice would be either DBISAM Client Server, or Advantage Database Server.  Both are solid CS databases, but neither is (yet) as feature-rich as SQL Server Express 2005. The downside of Advantage Database Server is that there are per-seat licensing costs.

An N-Tier architecture is rarely called for in small to medium multi-user scenarios.  The exception might be when the database is intended to simultaneously support both desktop applications and web applications.

Large Scale Multi-User systems (Client Server and N-Tier):

Prestwood's choices, in order of preference are:

Microsoft SQL Server 2005: Like it's "little brother," SQL Server Express 2005, SS 2005 supports (and requires the .NET framework), allows stored procedures and triggers to be written in compiled .NET code, and positions projects nicely for the next versions of Windows.  This is a solid database with a long pedigree, and a solid reputation.

Interbase: This is Borland's "big gun" database. It's been around for years; and proven its stability and performance well enough to be adopted by the U.S. Navy. One of its claims to fame is that it needs little ongoing attention from a database administrator.

Oracle: In the opinion of many, Oracle is the large scale database. While it offers a number of compelling features, my personal hunch is that demand for Oracle is driven more by name recognition (and, perhaps, the antics of the company's colorful founder) than actual technical supremacy. Oracle does not come cheap!

Other possible candidates are IBM's DB/2, Informix, and MySQL.

One Size Fits All:

What if you could have a single database that could be embedded right into your executable for local, single-user access and shared -file, multi-user access and it could also scale to a full-blown client-server solution with a lot of compelling features?  The new ElevateDB may be just the ticket.  I encourage you to check it out.

Also Rans:

Naturally there are many, many other databases we haven't yet mentioned.  We haven't excluded them because there's necessarily anything wrong with them.  Some, in fact, are excellent. Practical factors, though, dictate that, as a development shop, we focus on being really good at a handful of databases, rather than having all our developers learn a little about all of them.

But it wouldn't be fair to conclude without mentioning them:

MSDE: This is the Microsoft predecessor to SQL Server 2005 Express.  MSDE is now deprecated, and word is that it won't run on Vista and later versions of Windows. Which is why I haven't bothered to include a link to MSDE.  

Firebird: For a brief period, Inprise (now Borland again) released the InterBase source code to the open source community.  That launched the Firebird project.  For a while, Firebird leapfrogged InterBase in the features department.  Meanwhile, Borland withdrew their own further enhancements from open source, so there is now an "official" InterBase, and a Firebird "fork" in open source.

I imagine Borland regrets releasing InterBase to open source, and I'm pretty sure the open source community is disappointed that Borland withdrew from that initiative. 

At Prestwood, we favor the official InterBase over Firebird.  We'll readily admit to a certain level of bias in favor of Borland but, more important, we're more comfortable with the Borland resources behind InterBase than the odds of getting solid support for Firebird.

AbsoluteDB: This is a relatively new database that seems most suitably targeted at small, single-user applications. I've not yet used it, but I hear good things about it from other developers who's opinions I respect.

NexusDB: I used NexusDB's predecessor, Turbo Power's Flash Filer, with good success on a couple of projects.  When Turbo Power withdrew from the software market, they were good enough to release their products to the open source community.  NexusDB is a commercial fork in the Flash Filer code base, and is supported by some of the most avid Flash Filer users.  I hear NexusDB is a good product, but haven't had occasion to give it a whirl.

TurboDB: This is an interesting-looking database from Germany. I haven't tried it, either.

VistaDB: This is a new offering from the makers of the Apollo xBASE library. (Apollo is a BDE replacement library for use with xBASE databases.  It has a long - and checkered pedigree).  VistaDB is not based on either Apollo or xBASE. It is said to be written in 100% managed .NET code. This is yet another database I haven't tried.

Talk about opening a can of worms! If you ask (or answer) that question - be prepared for heated replies.

So, to save my own skin and, hopefully, keep things down to a low roar, let me first state emphatically that, in the world of software development, there is seldom one, clear "best" way to accomplish a given task.  In reality, there are always scores of wrong ways, probably dozens of ways that would work adequately, and two or three really nice solutions.  Picking which of those final candidates is "best" is seldom straightforward - with good arguments available for each option.

In my experience, this principle applies when it comes to choosing the database for a project, too.  Rejecting the "wrong" options is usually pretty easy.

More Info

News:  ElevateDb - The New Kid on the DB Block

Blog Entries!

1 Comments.
 Contribute to this open topic blog!
Comment 1 of 1

Elevate now has a full-blown MySQL competitor.

You could also update, if you like, to include MySQL, Elevate's DB 2.0 and SQLite.

Posted 12 years ago
 
Comment on this blog topic...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1135A1
Enter key:
Blog Contributed By Wes Peterson:

Wes Peterson is a Senior Programmer Analyst with Prestwood IT Solutions where he develops custom Windows software and custom websites using .Net and Delphi. When Wes is not coding for clients, he participates in this online community. Prior to his 10-year love-affair with Delphi, he worked with several other tools and databases. Currently he specializes in VS.Net using C# and VB.Net. To Wes, the .NET revolution is as exciting as the birth of Delphi.

Visit Profile

 KB Article #100406 Counter
14358
Since 4/2/2008

Follow PrestwoodBoards on: 


©1995-2020 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech