SQL Server/Office Integration: What Is MSDE?

The Microsoft Data Engine (MSDE) makes SQL Server technology available in development contexts that were previously restricted to the Jet database engine. This new database engine, which ships with all versions of Office 2000 that contain Access 2000, falls between SQL Server and Jet in size and power as a compelling new option. MSDE appeared initially during the Office 2000 beta testing, but Microsoft announced its availability to Visual Studio 6.0 developers during Tech Ed 99. Developers can distribute MSDE-based solutions royalty-free. This article focuses on how MSDE compares with and works with the other Microsoft database engines.

Overview of MSDE Technologies

Microsoft refers to MSDE as a technology rather than a product. Two versions ship with Microsoft Office 2000: One has a user interface through Access projects, and the other is a redistributable version for custom solutions. The developer interface of MSDE for Visual Studio 6.0 looks different from that of the MSDE in Office 2000. And, Visual Studio lets you deploy custom solutions with a redistributable MSDE version. Also, Microsoft released Service Pack 1 (SP1) for SQL Server 7.0 and MSDE 1.0, a bug fix that resolves many problems related to core database operation—including OLAP Services—associated with the Office 2000 and Visual Studio MSDE offerings. (For more on the bug fixes in SP1, see Andrey Kruchkov, "What's in SQL Server 7.0 Service Pack 1," August 1999.) Although it isn't technically a different implementation, the service pack improves the operation of MSDE.

All versions of Office 2000, Professional Edition and above, contain the MSDE version that works with Access projects and is appropriate for developing and using custom database solutions. The rapid application-development interface for Access projects resembles the one for .mdb files. This version's admin- istration features for backing up and restoring databases and for administering database security also make it well suited for designing applications.

For developing and using solutions, client workstations must have Office 2000 Professional Edition (or higher) installed and must have a connection to an MSDE server, either on the workstation or over a LAN. And you must install a database on the server that supports the custom application.

The redistributable MSDE version is available exclusively through the Microsoft Office 2000 Developer Edition (MOD). This version permits royalty-free distribution of MSDE as part of custom solutions. The redistributable version doesn't have hooks into the Access Project user interface; developers determine this version's look and feel in custom solutions through their own forms. And the redistributable MSDE version has a different installation procedure from the version that has special hooks for the Access Project interface.

MSDE for Visual Studio 6.0 includes three products: a SQL Server 7.0 Developer Edition, the redistributable MSDE, and Windows NT SP4. MSDE runs as is on Windows 9x computers, but it requires SP4 or later to operate on NT computers. MSDE components don't ship with Visual Studio 6.0 (or standalone versions of any of its constituent packages, such as the Professional edition of Visual C++). The SQL Server 7.0 Developer Edition offers the Enterprise Manager interface for administering databases and creating database objects, but this edition's license doesn't permit its distribution with custom solutions. Visual Studio's Package and Deployment Wizard supports distribution of the redistributable MSDE.

The SQL Server 7.0 SP1 works for SQL Server 7.0 and MSDE 1.0. It implements 56 fixes related to core database engine operation features and OLAP Services. The service pack setup program automatically detects whether MSDE or SQL Server is running on the local computer and applies the appropriate fixes.

Comparing MSDE to Jet and SQL Server

Table 1 compares the MSDE, Jet, and SQL Server engines on selected features that relate to scalability, cost, and ease of use. Note that the three database options differ on many more features not covered here.

MSDE can replace Jet in many contexts. (An example of an exception is that machines with few system resources wouldn't support MSDE well because of its greater resource requirements. Also, Jet 4.0, the version shipping with Access 2000, has superior backward compatibility compared with MSDE.) Four features in particular differentiate MSDE from Jet. First, MSDE eliminates the need to use the Access Upsizing Wizard for transferring custom applications to SQL Server; MSDE databases are already fully compliant with SQL Server technology. Second, MSDE supports NT and Windows 2000 (Win2K) integrated security, but Jet offers only password or user-level security. Third, MSDE scales to two concurrent processors on NT and Win2K computers, but Jet doesn't scale with the number of processors on a server. Fourth, MSDE is a client/server database with a true database engine, but Jet is a file database without an engine.

You can also compare MSDE and SQL Server to decide which database engine to choose for a project. Two considerations that have an impact on the cost of deploying solutions are the requirement for a Client Access License (CAL) and any related server license fee. A CAL is necessary only if your application connects to a SQL Server directly or through MSDE. SQL Server, however, offers the potential for greater scalability through its support for up to 32 processors and multiple terabytes of data. MSDE shares with Jet a database file size limit of 2GB, and MSDE's processor scalability is substantially less than SQL Server's.

Setup, Installation, Operation, and Deployment

For both Office and Visual Studio developers, developer and redistributable versions of MSDE are available. Office developers can deploy solutions with either version (if the client workstations have a Professional Office license or higher). Because many organizations deploy Office generally to the workforce, Office is also available for use as a client in custom applications. Microsoft recommends that Visual Studio developers build custom solutions with the SQL Server 7.0 Developer Edition and then deploy the solutions with the redistributable MSDE.

MSDE doesn't install with the standard Office setup. You install the version with special hooks for Access Projects by running setupsql.exe in the SQLX86SETUP folder from the first Office installation CD. This program installs MSDE on Windows 9x, NT, and Win2K computers. However, watch out for some subtle differences between the installation processes for NT and non-NT computers. NT SP4 doesn't ship with Office 2000, but you can download SP4 from http://support.microsoft.com/Support/ NTServer/TSE/ServicePacks/Default.asp and apply it to NT before attempting to install MSDE. After installation on an NT or Win2K computer, MSDE starts automatically as a computer service. On a Win9x machine, you need to manually start the service the first time. After initially launching MSDE on a Win9x computer, use the SQL Server Service Manager (which comes with MSDE) to set MSDE to start automatically whenever the computer boots. Also, you'll need to update the network library for computers connecting to MSDE when MSDE runs on a Win9x computer. The default is Named Pipes, but Win9x doesn't support this option. Use the Client Network Utility program to update the library connection to TCP/IP or another protocol appropriate for your network. Open the utility program from either the MSDE or the SQL Server command on the Start menu.

Although you can install the redistributable MSDE version for development, this installation isn't ideal because it doesn't support the Access Project interface. The Access Project interface, which resembles the traditional Access Database window, can substantially reduce the development time for custom solutions. You can deploy a solution you build with an Access Project to other workstations that enable Access Projects. First, you need to attach your custom database to the server that your clients will use. Then you can distribute the Access Projects (.adp) file to the client workstation.

When you're building a commercial product with Access or deploying solutions to workstations that don't support Access Projects, use the redistributable MSDE. To create a custom application for this scenario, you need MOD because it contains a license to use the redistributable MSDE and the Package and Deployment Wizard, which you need in order to prepare your solution for automatic deployment by end users. To deploy solutions with the redistributable MSDE, Office developers need to install MSDE on a client's workstation, start MSDE, then load the custom database into MSDE.

The installation program for the redistributable MSDE is msdex86.exe (available on the MOD CD). This program typically runs silently and requires a setup initialization file (.iss). This file specifies the options for an MSDE or SQL Server installation. You can use SQL-Distributed Management Objects (SQL-DMO) to start MSDE and to load a database into it on a workstation. A Microsoft white paper, which you can find at http://msdn.microsoft.com/ library/techart/msdedeploy.htm, illustrates how to perform the custom installation.

The MSDE for Visual Studio 6.0 package ships with a redistributable MSDE suitable for Alpha processors (msdealpha.exe) and one for Intel computers (msdex86.exe). (See Michael Otey, Editorial, "From Alpha to Omega," November 1999, for information about Microsoft support for the Alpha chip.) MSDE for Visual Studio comes with a default version of the setup initialization file.

Migrating Solutions from MSDE to SQL Server

SQL-DMO programming is one key to migrating MSDE solutions from a development environment to a production environment on either a SQL Server machine or another MSDE machine. SQL-DMO lets you attach a database file to a server, such as the MSDE database engine. And the SQL-DMO object model lets developers programmatically administer SQL Server or MSDE. The power of this object model is immense: Microsoft built Enterprise Manager with SQL-DMO programming.

Deploying your custom solutions from development to production environments often requires you to detach an MSDE database and attach it to another MSDE or SQL Server. One advantage of using SQL-DMO programming is that you don't have to shut the servers down to achieve the transfer.

Listing 1 shows the procedures for detaching a database from an MSDE. So that the second procedure can run, your module needs a reference to the Microsoft SQL-DMO Object Library. Choose Tools, References from any Office application file, including Access projects, to create the reference. The first procedure passes the name of a database to another database. The second procedure, detachMyDB, frees a database file (.mdf) so that you can reattach it to another server.

The procedure to detach a database is simple. It begins by instantiating the oSvr pointer to reference an MSDE. The code sample creates a connection to the local MSDE. If you use the server name (local), the procedure assumes that MSDE resides on the workstation running it. If that isn't the case, use the remote server name instead. Also, you must use a login with sufficient administrative authority to detach a database from a server. After instantiating a pointer to the MSDE and connecting to it, the procedure invokes the DetachDB method to release the database file from the current server. A message box provides feedback to the user about the outcome of the attempt to detach the database. The procedure closes by disconnecting from the server and releasing the resources for the server pointer.

The listing "Procedures to Attach a Database File to a Server," which you can download at the top of this article, shows a second pair of procedures, which attach the detached database files to another server (either MSDE or SQL Server). The first procedure passes the path of the original MSDE and the filenames for both the database file and its log file. The second procedure takes this information and performs two essential tasks. First, it uses the File System Object in the Microsoft Scripting Runtime library to copy the detached file from the old server to the current local server. (Update the path statements and server names to reflect your computing environment.) The CopyFile method for the File System Object fails if a log file from a previous version exists, so the procedure tests for the existence of such a file and deletes it if it exists.

The second part of the procedure parallels the detachMyDB procedure. First, it creates a connection to a local server. You must again replace (local) with the name of a remote server if your workstation doesn't operate on the server. The AttachDBWithSingleFile method attaches the copied database file from the first part of the procedure to the new database server. The procedure closes by releasing resources for the pointers.

MSDE is an exciting new database engine option. It has many interesting features, but its price ($0) and royalty-free distribution, along with its SQL Server technology, make it particularly noteworthy. Microsoft doesn't hard-code a maximum number of users, but it recommends MSDE for a handful of concurrent users. I've already heard about MSDE being used with tens of users, though. Building solutions with MSDE is attractive because it simplifies their eventual migration from MSDE to SQL Server.