By Susan Sales Harkins and Martin W. P. Reid
IT professionals have been looking for an alternative to Microsoft Jet (Access) for some time. As a desktop database, it’s the top of the line, but in a busy client/server environment, it becomes a bit unstable. A Jet expert can make Access run as efficiently as anything else, but most IT departments simply don’t have that kind of devoted expertise.
Office 2000 included a free alternative to Jet, Microsoft Data Engine (MSDE), but it went largely unnoticed. An updated version, known as SQL Server Desktop Engine, was released with Office XP, and that product is finally getting the respect it deserves for taking Access where Jet never could. If you need stability and power, but want it with Access, then SQL Server Desktop is your answer.
SQL Server Desktop outperforms Jet
Access has been the front end of choice for many SQL Server developers for a long time, and SQL Server Desktop is the natural progression of that relationship. Using SQL Server Desktop, you can now store data in SQL Server tables and use the Access interface tools to manipulate that data. Graphically and visually, you’ll think you’re using Access, but you’ll have the power and stability of SQL Server behind your applications, not Jet. The best part is that it’s free. If you have Office XP Developer or Access XP (or Office 2000 or Access 2000), you have SQL Server Desktop.
SQL Server Desktop has the look and feel of Access, but the power of SQL Server, and that in itself makes it a unique and viable product. There are a number of ways SQL Server Desktop outperforms Access. For example:
- · SQL Server Desktop uses Windows 2000 Integrated Security, which is easier and more efficient than Access’ built-in security.
- · SQL Server Desktop maintains a transactions history, so the system can be restored to a specific point in time, if necessary. In addition, transaction processing is built right in.
- · SQL Server Desktop objects are immediately compatible with SQL Server and can be transferred to SQL Server at any time without modifications.
- · SQL Server Desktop chooses the appropriate and most efficient locking method.
Despite SQL Server Desktop’s numerous selling points, not everyone will want to jump on the bandwagon. This product services a very specific niche. SQL Server Desktop works best for:
- · Any developer or administrator who needs more power and flexibility than Jet allows, but wants to retain the Access interface tools for development purposes, such as creating a front end.
- · Any business that has limited funds and personnel and simply isn’t ready to invest the time and money required to maintain SQL Server.
- · Any SQL Server developer who needs to distribute runtime applications to clients that can’t afford SQL Server. SQL Server Desktop embeds its database engine into an application, making it possible for you to install a runtime on a non-SQL Server system.
- · Any business or developer looking for a more stable and better performing database, but who has few users.
- · Remote users such as travelers who need the SQL Server structure, but can’t access their network. Simply install SQL Server Desktop on a laptop and go.
- · Anyone wanting to learn SQL Server. SQL Server Desktop is a great training tool for anyone wanting to learn SQL Server but lacking access to the full-blown version.
- · Developers moving Access applications to SQL Server. Upsizing an Access database can be an exhaustive task. More often than not, extensive rewrites are necessary before an Access (Jet) application can make its way onto the SQL Server. In contrast, SQL Server Desktop creates a file known as an Access project (.adp). These files can be quickly and easily upsized to SQL Server. Consequently, developers can build SQL Server Desktop applications that are structurally compatible with SQL Server 2000. Access projects will move right up with the business.
- · Freelance developers distributing small applications that require storage space. SQL Server Desktop gives you local data storage at no cost. That means you can distribute (sell) an application without also forcing the client to purchase Access or use some other means of storing data. Just distribute the application as a SQL Server Desktop runtime application.
- · Anyone interested in the Web. Publishing a site on the Web can be an expensive venture. Fortunately, SQL Server Desktop is Web-enabled and, as I’ve mentioned, free. Combine SQL Server Desktop with Microsoft’s freeware product, Web Matrix, and you’ll have the latest Microsoft Web technologies. Web Matrix is a scaled-down version of Visual Studio .NET that allows you to build .NET-based applications with database support. Together, the two provide a stable solution. Keep in mind that this solution is for low-traffic sites only. Web Matrix can be downloaded from the ASP.NET Web Matrix Project Web site. Web Matrix Developer’s Guide by John Paul Mueller (published in 2002 by Apress) is an excellent guide to using the product.
The fly in the ointment
You may be thinking that this all sounds too good to be true. You didn’t really expect Microsoft to give you SQL Server for free, did you? SQL Server Desktop has limitations, but most of them are acceptable to the right user. Most importantly, performance is rigged—you are limited to only five concurrent processes. SQL Server Desktop is designed to put the skids on performance at five processes operating on the server. The more active threads you have, the slower the engine responds. It’s designed that way, and there’s nothing you can do about it. Each additional thread—after you've hit the five-thread threshold—is delayed until you fall back within the set limits. This is what’s known as target benchmark users (TBU). (There is some mystery as to the real limit. Microsoft says it’s five, but some sources say that it’s actually eight.)
Take note: I’m not discussing active users; active users aren’t limited. You can have 100 users connected to the database engine, and, provided 95 of them are out to lunch or otherwise preoccupied, the application will perform well.
Keep in mind that active users aren’t the only way to create active threads. Using database engine resources can also run you over the TBU limit before a user makes a single keystroke. For example, retrieving records to populate a combo box list consumes an active thread. The user may only be viewing the list contents and not doing a thing, but the list itself has already consumed at least one of the five threads.
The second issue is that database size is restricted to 2 GB, which is comparable to an Access .mdb file. If size is an issue, SQL Server is limited only by the available physical storage space.
Developers might find the absence of a graphical interface management tool a deal-breaker. SQL Server Desktop doesn’t offer a graphical interface for server management, such as Enterprise Manager, the tool that comes with SQL Server. All you get with SQL Server Desktop is OSQL—a command line utility. You can view details on using this command tool at "HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility."
If you have a SQL Server 2000 license, you can also use Enterprise Manager to manage SQL Server Desktop. (For more information on SQL Server licensing as it pertains to SQL Server Desktop, see the note on licensing.) If not, you can build your own interface using Microsoft Access and SQL distributed management object (SQLDMO). There are also several third-party tools available on the Internet. For example, this MSDE Web site provides a download that enables you to manage a SQL Server Desktop application named MSDE 2000. This product isn’t a direct replacement for the Enterprise Manager, but at $19, it’s a bargain.
Other tools that come with SQL Server but aren’t available with SQL Server Desktop are Query Analyzer and Books Online. In addition, SQL Server offers a number of powerful data management tools that SQL Server Desktop doesn’t. Don’t look for the following in SQL Server Desktop: English query, analytical services, or data mining.
Finally, SQL Server allows up to 16 instances. In comparison, SQL Server Desktop supports just one.
You’ve nothing to lose
Despite the limitations I just shared, SQL Server Desktop is probably the best database solution for any business that’s not ready for the full-blown version of SQL Server—regardless of the reason. In addition, even if you already have SQL Server, the desktop version can prove invaluable for travelers, personnel that need training in SQL Server, and even those smaller projects with just a few users. Install it and take a look; you might find just the tool you’ve been looking for—for free.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.