In the Real WorldThe (Almost) Free Lunch

In the Real World The (Almost) Free Lunch

Controversy over the future of conventional shared-file multiuser Jet applications continues unabated among Access developers and Microsoft marketers. The shared-file, "Jet is alive and well" axis insists that Jet is a viable back end for workgroup-size online transaction processing (OLTP) applications. The "Jet is dead" cabal, whose membership is dominated by SQL Server marketing folks, consider MSDE to be Microsoft's "strategic database" for Office applications and SQL Server 2000 to be the natural back-end choice for everyone else, including users of hand-held devices running Windows CE. Regardless of the rhetoric, you can't beat the price of MSDE; it's free.

Shared-file proponents tend to favor traditional Data Access Objects (DAO), a mature technology now in its final version (3.6). Members of the MSDE/SQL Server clan justifiably promote OLE DB and ActiveX Data Objects (ADO) as being where the action is for database connectivity. OLE DB and ADO are designed to support Web-based applications; DAO isn't. ADP use OLE DB and ADO to connect to SQL Server. Microsoft's .NET framework is centered on SQL Server and an enhanced version of ADO called ADO.NET, and you can expect major changes to Access when Microsoft finally gets around to releasing the next version of Office. Access versus Next undoubtedly will toll the death knell for new Jet-based applications and DAO.

The reality is that multiuser Jet does run out of steam in heavy-duty OLTP applications having many simultaneous users. The point at which concurrency and file corruption problems begin to appear in Jet back-end databases depends on a variety of factors. Each upgrade to Jet has improved multiuser reliability, but many developers still consider 20 to 30 simultaneous updating users to be the practical limit for Jet 4.0. The absolute maximum number of concurrent user connections to any Jet database is 255. Thus Jet isn't a serious contender for an e-commerce orders database on a highly trafficked Web site. The 1GB maximum table size and 32-index limit (including indexes created by relationships) makes Jet impractical for use in data marts and warehouses of medium or larger scope.

Jet offers the advantage of easy conversion from single-user, single-file mode to shared-file multiuser mode. The Database Splitter utility makes the transition automatic. Descriptions of the Jet security system range from Byzantine to Machiavellian, but Jet security is easier to manage than SQL Server security, especially when using OSQL. You would probably be surprised to learn how many production SQL Server databases installed with mixed (Windows authentication and SQL Server security) have run unsecured for months or years with the default sa as the username and no password. Access's User-Level Security Wizard takes most of the pain out of securing front-end and back-end .mdb files.

If you seek multiuser simplicity in a small Windows XP workgroup environment, shared-file Jet probably is your best bet. For more sizable projects, linking Jet front ends to MSDE under Windows 2000+ Server followed ultimately by a transition to a full version of SQL Server is the natural choice. Jet security applies to SQL Server links, but doesn't secure the back-end database itself against marauding members of the local Administrators group that have a copy of SQL Server Enterprise Manager.

MSDE is substantially more robust than Jet, especially for OLTP. You don't need to periodically compact SQL Server files as users edit and delete records. MSDE offers automated backup and restore operations and provides a transaction log that you can use to return restored tables to their exact state at the time of a crash. Although Microsoft claims to "tune" MSDE for five simultaneous users, tests indicate that MSDE running under Windows 2000 Server can support many more than five clients.

If you're starting an Access project from scratch, and you expect more than about 20 users to update database tables simultaneously, seriously consider starting directly with ADP. Although you can use the Access Upsizing Wizard to convert a conventional Access application to ADP, the upsizing process isn't bulletproof. You must rewrite Jet queries that contain Access-specific reserved words and functions missing from the Wizard's bag of tricks. You save time in the short and long run by conforming to SQL Server's T-SQL dialect when you design your queries. Chapter 22, "Upsizing Jet Applications to Access Data Projects," describes typical workarounds for Jet queries that won't upsize to T-SQL.

Your free lunch ticket expires when you must upgrade your server from MSDE 2000 to the $4,995 SQL Server 2000 Standard Edition for a single processor. To mitigate the pain of SQL Server license costs, Microsoft gives you a free copy of SQL Server Analysis (formerly OLAP) Services. Of course, the SQL Server folks hope you'll build data marts so large that they require their own dedicated server cluster (and thus pairs of even more costly SQL Server 2000 Enterprise Edition licenses).

Tip

If you're thinking about upgrading from MSDE 2000 and will have less than 50 users for a year or two, consider Microsoft's $1,299 Small Business Server (SBS) with five Client Access Licenses (CALs). SBS includes Windows 2000 Server, SQL Server 2000, Exchange 2000 Server, and the Internet Security and Acceleration (ISA) Server 2000. Additional CALs are $44 each in groups of five. Make sure your server has at least 512MB RAM because all services must run on the same box. Go to http://www.microsoft.com/sbserver/ for more details. When this book was written, Microsoft hadn't announced licensing terms or pricing for the Windows Server 2003 SBS version.