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 \SQL\X86\SETUP 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 link to this article at http://www .sqlmag.com, 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.