Data Management

Decision Support: Choose the right Microsoft database for your development needs

Choose the right Microsoft database

By Mike Gunderloy

Microsoft has produced a dizzying array of software over the years. Think, for just a moment, about all of the varieties of Windows; if you go back as far as I do, you've probably worked with at least a dozen versions. Similar choices exist in other areas of the Microsoft universe, including the one that I'm writing about today: databases. Microsoft offers developers four databases for their application needs:
  • Microsoft Jet and Microsoft Access
  • Microsoft Visual FoxPro
  • Microsoft Database Engine (MSDE)
  • Microsoft SQL Server

How do you decide which of these databases to use for a given application and client? Although hard-and-fast rules are few and far between, a basic understanding of the differences (and similarities) between these databases will help you choose intelligently.

Please note: I'm aware there are many fine database products from companies other than Microsoft. Depending on your own client and application constraints, some of these other products will be worthy of consideration. But this article is geared more toward consultants whose clients are already committed to Microsoft software.

Microsoft Jet 4.0 and Microsoft Access 2002
First, I need to clear up some confusion. Although "Jet" and "Access" are often used interchangeably, they are two different components. Microsoft Jet 4.0 is the actual database engine, which handles the work of reading and writing data from a database file. Microsoft Access 2002 is the layer that sits on top of Jet to provide the user interface. In an Access database, the tables and queries are handled by Jet, while the forms and reports are handled by Access.

It's important to understand this difference, because the two pieces can be shipped separately. The Jet engine itself is freely distributable and is easy to use from Visual Basic, Visual C++, Visual Basic .NET, or C#, among other languages. But to distribute Access, you need to purchase a runtime license (available by buying the Office XP Developer Edition).

Whether used as part of Access or as the backing store for another application, Jet offers a number of advantages in its current incarnation. It's a relatively low-cost alternative, even with an Access runtime license. Access is one of the easier development environments to learn, making this an attractive choice for novice developers or in-house development by non-IT departments.

Because the Jet engine is more than a decade old, an immense amount of sample code is available for it, and several excellent reference books are on the market. Jet also supports some high-end capabilities, such as replication, which can make it a reasonable choice for distributed applications.

But many developers will hit the wall with the Jet engine at some point. Although current versions do not have the same problems with large numbers of concurrent users as early Jet releases, you'll still find that the engine runs out of steam somewhere between 30 and 200 users, depending on the application's pattern of database usage. Jet is a file-server database, rather than a true client-server database (like SQL Server), which leads to increased network traffic when many users are trying to share a database.

Finally, as Microsoft Knowledge Base article 300216 states, "Microsoft Jet is not intended to be used with high-stress, high-concurrency, 24x7 server applications, such as Web, commerce, transactional, and messaging servers. For these type of applications, the best solution is to switch to a true client/server-based database system such as Microsoft Data Engine (MSDE) or Microsoft SQL Server."

Bottom line: For departmental and low-volume applications, particularly those that must be developed rapidly and maintained by power users, Jet (whether through Access or otherwise) is an excellent choice. But make a realistic estimate of the application's growth potential before settling on this option.

Microsoft Visual FoxPro 8.0
Sometimes it seems like Visual FoxPro is the database that Microsoft forgets it owns. Microsoft bought FoxPro in the mid-1990s, rolled some of its performance features into the Jet engine, and then continued developing FoxPro as an independent product. But FoxPro has never benefited from the sort of full-press marketing that the company has applied to Access and SQL Server. That's a shame, because Visual FoxPro has grown into an amazingly powerful system for building database applications.

Although originally a dBASE clone, FoxPro has evolved far beyond its xBASE roots. The latest version, released in early 2003, sports a number of high-end features, including:
  • Deep and consistent object orientation.
  • The ability to use SQL Server, OLE DB, and ODBC data sources on a par with native FoxPro tables.
  • Advanced user interface controls.
  • XML support, including Web Service compatibility via the Microsoft SOAP Toolkit.
  • A redistributable runtime library.

The FoxPro engine has a well-deserved reputation for speed. Coupled with the true object-orientation of the language, FoxPro is an excellent choice for dedicated desktop database developers who have performance and rapid development as their goals. But FoxPro remains a desktop, file-server database, not suited for high-volume transactional applications. (FoxPro can use SQL Server as a database, but in that case, you're using the SQL Server engine, not the FoxPro one.)

FoxPro is also the only one of the Microsoft database products to run well on Linux, using the WINE libraries—although there has been some question as to whether this is legal under the FoxPro license.

Perhaps the largest drawback of Visual FoxPro is its niche status within Microsoft. The FoxPro IDE doesn't look or work like the other Microsoft IDEs and the FoxPro language is unique. And although FoxPro does interoperate with other Microsoft products, sometimes the implementation of this interoperability lags.

If you can stake your entire career on a single database product, Visual FoxPro will fit the bill. But if you move back and forth frequently between multiple products, you may find that switching to and from FoxPro is somewhat jarring.

Microsoft Data Engine 2000
To some extent, the Microsoft Data Engine (more often referred to as MSDE) is an exercise in marketing. Rather than a completely separate product, MSDE is actually a slightly stripped down version of Microsoft's flagship SQL Server database. MSDE is available with such development environments as Access, FoxPro, and Visual Studio .NET, and you can freely redistribute it to your customers. Thus, its positioning is somewhat similar to the Jet engine: It's a component you incorporate into full applications.

Like SQL Server, MSDE is a client-server database. That means that an intelligent program actually runs on the server computer, making it possible to distribute processing and cut down network traffic—a big win for distributed applications. MSDE is fully compatible with the SQL Server version of the SQL language, so the learning curve for SQL Server developers is negligible. MSDE also offers high-end features such as true transactional processing with recovery, fault tolerance, and replication.

So what's missing with MSDE? Well, for starters, some artificial limits are imposed on this version of the code base to keep it from competing with the full SQL Server product. MSDE databases are limited to 2 GB of data, and the product implements a "query governor" when there are more than five concurrent batches. This means that for each active user after the fifth one, the performance of MSDE is artificially slowed down. In some applications (like ASP.NET applications where there's only a single user, the ASPNET process), this hardly matters. But for a transactional application with dozens of users (or more), the query governor can rapidly make MSDE unusable.

Also lacking is any sort of client user interface or management tools. MSDE makes no provision for creating users, backing up databases, managing security, or a host of other administrative tasks. You'll either need to write your own code for these tasks or locate (and support) a third-party product to do so.

MSDE works well for client-server applications with relatively low numbers of users. It's also a great database for applications that may grow; you can swap out MSDE and swap in SQL Server with no change to the application. As long as the client can afford the upgraded database server, moving up from MSDE is painless.

Microsoft SQL Server 2000
Microsoft SQL Server is Microsoft's flagship high-end database, with a price to match. Expect to pay at least $5,000 to install a SQL Server. For a large installation, a six-figure price tag (hardware included) isn't out of the question. You also need to budget for serious administration if you're setting up a large SQL Server database. Plan on a full-time database administrator (DBA) to keep the server humming, the data backed up, and the security patches applied.

For the price, though, you get one of the world's leading databases. SQL Server is designed to support the most demanding database applications out there. The current version sports some impressive numbers:
  • Maximum database size: Roughly 1 million terabytes
  • Maximum databases per server: 32,767
  • Maximum locks: 2 billion
  • Maximum open objects: 2 billion
  • Supported CPUs: 32
  • Supported RAM: 64 GB

Beyond sheer size, SQL Server has many advanced capabilities. It supports full-text searching, XML (including XML Web Services via the SQLXML add-on), data warehousing and OLAP cubes (using SQL Server Analysis Services), flexible replication, and natural-language queries. SQL Server comes with a full suite of graphical administration tools, as well as command line batch administrative tools.

SQL Server is also compliant with a variety of standards, including ANSI SQL, ODBC, and OLE DB. It's easy to get data in and out of SQL Server using dozens of different APIs and clients.

If your data is so large that SQL Server won't handle it, you probably don't need any guidance from me in the first place.

Making the choice
Any guidance I can provide on making the actual choice among these products is bound to upset someone. Each of the Microsoft databases has its own fierce proponents, and they're often quick to complain if they feel their own product has been slighted. Nevertheless, I think that it's possible to come up with some sensible guidelines:
  • For a departmental-level application that needs data storage, and for which you've decided on one of the visual programming tools for the user interface, use the Jet engine. It's there, it will take a reasonable load without complaining, and it's easy to deploy and to use.
  • If databases are your bread and butter and you pride yourself on performance and craftsmanship, invest the time to learn Visual FoxPro. You may not be able to do everything with this tool, but the things that you can do will be done superbly well.
  • For client-server or Internet applications with a light to moderate load, start with MSDE, knowing that you can upgrade to SQL Server later.
  • If you know from the start that your application will be extremely demanding, plan on SQL Server to begin with. You can use the relatively inexpensive Developer Edition to build your application while you convince the client to purchase and set up the full product.

Remember, software decisions are never set in stone. Don't be afraid to reevaluate your choices as you learn more and as Microsoft releases new versions and new products.

Editor's Picks

Free Newsletters, In your Inbox