Arulkumar Elumalai
Microsoft Corporation

December 2001

Applies to:
     Microsoft® SQL Server™ 2000 Desktop Engine

Summary: Many software developers want to embed data storage within their custom applications. Microsoft SQL Server 2000 Desktop Engine (also known as MSDE 2000) enables developers to do this. This document describes how to distribute MSDE 2000 with a custom application by embedding MSDE 2000 Setup within the setup program of the custom application. (8 printed pages)

Introduction

Microsoft® SQL Server™ 2000 Desktop Engine (also known as MSDE 2000) enables software developers to embed data storage within their custom applications. This document describes how to distribute MSDE 2000 with a custom application by embedding MSDE 2000 Setup within the setup program of the custom application.

MSDE 2000 technology provides local data storage that is compatible with SQL Server. You can think of MSDE 2000 as a client/server alternative to the file server Microsoft Jet database engine. MSDE 2000 is designed and optimized for use on smaller computer systems, such as a single computer or a server used for a small workgroup.

MSDE 2000 is distributed as a set of 25 merge modules. Stand-alone installation of MSDE 2000 is possible by using the MSI supplied by SQL Server Setup. (See the next section for an explanation of MSI.) You can also embed MSDE 2000 Setup into any custom setup by consuming these merge modules. Embedding MSDE 2000 Setup into a custom MSI setup program, in essence, merges the merge modules into an MSI package.

Creating a custom application with MSDE 2000 embedded in the setup is a three-phase process consisting of the following tasks:

1. Creating the MSI package to install the custom application.
2. Merging MSDE 2000 merge modules into the custom application.
3. Running Setup to install the custom application and MSDE 2000.

MSDE 2000 features a Microsoft Windows® Installer-based installation. The Windows Installer and Merge Modules section of this document provides a high-level overview of Windows Installer and MSDE 2000 merge modules. You can also skim through the resources listed in Windows Installer and Merge Modules to learn more about MSDE 2000 features and the capabilities of Windows Installer. Creating an MSI Package outlines the steps necessary to create an MSI package. The following sections describe merge modules and the actual merge process—the prerequisites, core tasks, and validation. Appendix A contains answers to frequently asked questions.

Windows Installer and Merge Modules

Windows Installer, which is included with the Microsoft Platform SDK, is a powerful tool that is used to set up software products in Windows environments. The installer stores setup information as a set of tables and creates a bundle, which is known as an MSI package. Executing the MSI package installs the product.

Merge modules are files with an .msm extension. A merge module cannot be installed by itself because it lacks critical database tables that are present in the installation database of an .msi file. Merge modules also contain additional tables that are specific to merge modules. To install the information delivered by a merge module with an application, the module must first be merged into the application's .msi file.

Each merge module has a module signature table. This table contains a single entry that defines the signature of the module. Each time a merge module is merged into an MSI, an entry is added to the module signature table of the MSI if the merge is suclearcase/" target="_blank" >ccessful.

The best alternatives for developers who want to use merge modules are to obtain a freely distributed merge tool, such as the library of functions in Mergemod.dll, or to purchase one of the merge tools available from independent software vendors. Mergemod.dll provides a COM object that implements merge operations and source image generation for merge modules. Orca, a database editor included in the Windows Installer SDK, also uses a COM object to implement the merge operations. Orca is a robust tool for merging MSMs.

The main advantage of merge modules is that they can be easily used with an MSI. You can embed MSDE 2000 merge modules into a custom MSI, allowing the application and MSDE 2000 to be installed in a single process. Understanding Windows Installer and MSDE 2000 makes this task easier.

For more information about MSDE 2000, see SQL Server 2000 Desktop Engine (MSDE 2000).

Creating an MSI Package

The basic steps to create a simple MSI package are as follows:

1. Plan the installation
   List the general installation layout, namely the files to install, the source path, and the target path. In addition, list all registry-related operations. Place the .exe file to install and all supporting files in a specific directory. You can also store supporting files in a hierarchy of subdirectories.
2. Import a blank database
   To create an MSI package you need to copy, or create with a software tool, a Windows Installer database file. A blank installation database, Schema.msi, is provided with the Microsoft Platform SDK components for Windows Installer developers. The SDK also provides a partially blank database, Uisample.msi, which contains the suggested sequence tables and data required for a simple user interface. Copy Uisample.msi into the directory containing the .exe file to be installed. The installation database file and the source files must be located at the root of the same directory (multiple files can be arranged in a hierarchy branching from the root directory); otherwise, you will receive setup errors.
3. Specify the directory structure
   The installer stores information about the installation directory structure in the Directory table. Use the database editor, Orca, or another editor, to add information to the Directory table.
4. List the constituent components
   List all components that are part of the installation. A component can be a set of files or resources that are added to the component table of the database.
5. Specify files and file attributes
   Add all relevant files to the Files table.
6. Enter source media information in the Media table
   The Media table describes the set of disks that comprise the source media for the installation.
7. Define features
   Add product features to the Feature table. The installer enables users to install and remove portions of an application's functionality, which are called Windows Installer features. When merging MSDE 2000 with your application, you can define MSDE 2000 as a separate feature or tie it to one of the other existing features. Create a dummy feature for MSDE 2000 if you want to install MSDE 2000 as a separate feature.
8. Define feature-component relationships
   Use the FeatureComponents table to define the relationships between features and components. Each Windows Installer feature uses one or more Windows Installer components, and features can share components.
9. Add registry information and shortcut properties
   The Registry table and related tables of the installation database hold the registry information that must be written in the system registry for the application. The Shortcut table and related tables of the installation database hold information necessary to install shortcuts.
10. Specify properties
    Windows Installer properties are global variables that the installer uses during an installation. You do not have to define all properties in every package; however, a small set of properties is required. The installer sets the values of properties in a particular order of precedence.
11. Populate sequence tables
    A variety of sequence tables must be populated for the setup to run: InstallExecuteSequence, InstallUISequence, AdminExecuteSequence, AdminUISequence, and AdvtExecuteSequence.
12. Add summary information
    Summary information is not critical to run Setup, but summary information is essential for the package to pass validation. You can use the tool MsiInfo.exe, which is provided with the Windows Installer SDK, to set these properties.

Prerequisites to a Successful Merge Operation

Before merging the MSDE 2000 merge modules into your custom application, you must run validation, make necessary modifications to the Media table, and plan for merge conflict resolution.

Validation

Validation scans the database for errors that may cause incorrect behavior in the context of the entire database. Attempting to install a package that fails validation can damage the user's system. Always run validation on packages before attempting to install them, and rerun validation after making any changes to a package.

You can validate the sample package using Orca or Msival2.exe (both are provided with the Windows Installer SDK). Validate both the merge module and the MSI before merging. The validation tables of the MSM and the MSI must be consistent for the merge to be successful.

Media Table Change

The Media table has a column called LastSequence that contains the file sequence number of the last file in the source media. This number must be altered (increased) to include the files from the MSMs. Type a value that is the maximum of all the file sequence numbers of the files (included in the File table) of all the MSMs to indicate that all of them must be included.

Merge Conflict Resolution

Sometimes entries might overlap in one of the main MSI packages with that of a MSM package. In such cases the rows must be identical. A merge conflict occurs when rows differ in value. For example, in the validation table of the MSI an entry may have been declared as string while in the validation table of the MSM, the same entry may be declared as formatted. In such cases merge conflicts occur, and the values in both rows must be synchronized.

Checking each row for consistency is cumbersome. The best method is to run the merge and correct inconsistencies when errors occur.

Merging Techniques

This section describes how to use the Orca editor for merging.

UI-Based Merging

Orca 1.5 beta and later allows merging through the UI. The functionality of Mergemod.dll can be invoked from the Tools menu of the current version of Orca. Alternatively, script-based merging can be used with earlier versions of Orca.

A merge dialog box can be invoked from the Tools menu of Orca. The dialog box is shown in the following illustration.

Figure 1. Orca Merge dialog box

Use the following steps to merge a given MSM.

1. Click the Browse button and select the MSM to be merged.
2. From the root directory, select a target directory for the installation. By default, Windows Installer uses TARGETDIR to indicate the directory for the installation. In case of wrong selections, merge conflict errors are raised (when you click OK). You can use this as an indicator to correct the root directory.
3. Select the feature to which the MSM is to be attached. Each MSM must be tied to a feature in the MSI. A new feature can be created or an existing feature can be reused to attach the merge module. The merge module will be installed only if the particular feature is installed. It is a good practice to create a single feature that is specific to MSDE 2000 and tie all MSDE 2000 MSMs to it.
4. Select the Image Source option, which creates the same source image. Point to the directory containing the custom application and the MSI.

   Note   The MSI and the custom application must reside in the same location.
   Subdirectories are created in the install directory of the installation media and source files of the MSMs are copied to them.

5. Click OK. If no error messages are displayed, the merge is successful.

To verify that the merge was successful, make sure that:

1. No merge conflict errors are raised.
2. The MSM signature is added to the Modulesignature table of the MSI.
3. The components of the MSM are added to the Components table of the MSI.

After all MSMs are merged, rerun validation to make sure that setup will run as expected.

Script-Based Merging

The process described in the previous section can be scripted. You can use script-based merging with earlier versions of Orca, which has no UI.

The main command is:

c:\Progra~1\Orca\orca.exe /q /c /l %LogFile% /f %Feature% /m %MergeModule% 
c:\myAppPath\myApp.msi

The switches denote the following options:

• q—quit mode.
• c—commit merge to database if no errors occur.
• l—logging.
• f—feature to which the merge module is attached.
• m—merge module name.

Each module must be merged to the MSI using the command-line format shown earlier in this section. By default, this command-line execution does not create a log or raise errors. It is important to use the /l switch and generate a log file to view errors. Merge changes are not committed to the database if a fatal error occurs. You can also perform steps 2 and 3 of the verification process outlined in Merging Techniques to test the results of merge.

Running Setup

Clicking the MSI package causes Setup to run. A successful installation causes the application and MSDE 2000 to be installed.

When you merge MSDE 2000 modules into the MSI of the custom application and run Setup, a shortcut to Service Manager is not created automatically and you cannot see the Service Manager icon in the system tray. These activities are not defined in the MSMs of MSDE 2000 and must be defined in the main MSI package. Task Manager will show that the service is running, which means that the installation was successful and the service is started. Service Manager can be invoked by clicking Sqlmangr.exe in the Binn directory of SQL Server tools.

Sometimes tables must be created and populated with data during setup. You can do this by invoking the MSI package through scripting and running a series of osql commands with a trusted connection (one that uses Windows Authentication and not SQL Server authentication) to do the database manipulations.

Conclusion

Merging MSDE 2000 into a custom application setup is a simple task, due to enhancements in Orca and refinements in MSDE 2000 setup. Using the tools described in this white paper and applying recent service packs can help you achieve this objective.

Appendix A: Frequently Asked Questions

I get an error message that says that merge conflict occurred. How do I resolve the error?

Merge conflict error messages provide the table name and row indicator that caused the conflict. Compare the table or row of the MSI with that of the MSM and synchronize the values. Reattempt the merge and it will be successful.

Do I have to merge all the modules of MSDE 2000? I've heard that some of them are optional.

You can reduce the disk footprint of your application by customizing the SQL Server 2000 Desktop Engine Setup so that it does not install SQL Server 2000 components that are not used by your application. You can leave out DMO*.msm, Repl*.msm, or both. These are merge modules for SQL-DMO and replication, respectively.

I run the merge unattended by using a script, but I do not see an error log.

Use the /l switch to generate an error log. For more information, see Script-Based Merging earlier in this paper.

Setup is complete but I don't know if the service has been started.

The service is started by default. Look in Task Manager and you should see the service running. You can use any application that connects to the service or you can issue osql commands to work with the service.

When I install MSDE 2000 from the CD-ROM I see the service icon in the system tray, but I don't see the service icon in my system tray when MSDE 2000 is embedded with my application. Why?

Creating shortcuts is an activity of the MSI (Sqlrun01.msi) from the CD-ROM. This must be incorporated in a custom MSI. See Creating an MSI Package for more details. The shortcut creation entries are not in any of the merge modules and hence must be done in the main MSI package. To see the icon in the system tray, double-click Sqlmgr.exe in the Binn directory of SQL Server Tools.

My application is unable to connect to MSDE 2000. I receive an error message that says that the connection attempted is not a trusted connection. How can I fix that?

This occurs because MSDE 2000 uses Windows authentication by default. Windows authentication is more secure than SQL Server authentication. You must alter code within your custom application to use the secure login to overcome this error rather than using SQL Server authentication.

I am unable to specify an instance name or change the security mode while using the merge modules. Is there a way to do that? If so, how can I change internal properties?

You can add an entry in the Property table for INSTANCENAME and specify a name. You can add an entry for SECURITYMODE the same way. However, you should make these changes in the main install package and not the SQL Server 2000 MSDE merge modules. You can map each of these properties to an internal property in the merge module by creating custom actions in the main MSI. For more information, see PRB: Cannot Specify Instance Name Using SQL Server 2000 Merge Modules (Q281983).

Sometimes errors are displayed in the log but setup runs fine. Why?

Errors are generated in the log if identical rows are found in MSI and MSM tables while merging. While these are listed as errors in the log, they are not fatal errors and can be allowed. As long as the changes are committed to the database, you can ignore these errors.

I receive the following error during setup: "Cannot pick package id." Why does this happen?

This can occur with the RTM version of MSDE 2000. The error may be caused by the existence of Sqlboot.dll from the prior version installations on the local computer. A workaround is to rename every occurrence of Sqlboot.dll, run Setup again, and change the name of the DLL back. If this occurs during a clean install, embedding the SP1 merge modules will resolve the problem. SQL Server 2000 SP 1 can be downloaded from http://www.microsoft.com/sql/downloads/default.asp.

Sometimes my installation rolls back at the end of the progress bar without providing an error message.

Refer to the log. This might have to do with the installation of performance monitors (Installperfmon). Embedding SP1 level merge modules of MSDE will resolve this problem.

I have installed my application and need to upgrade it. How do I do that?

This is an activity external to merging. You can upgrade your application files only by running an upgrade .msi package. For more information, see the Windows Installer SDK. Your MSDE installation can remain untouched.

I try to merge MSMs, but nothing happens: no errors, no messages, no reaction. Why does this occur?

Possibly Mergemod.dll registration did not occur. Register Mergemod.dll using regsrvr32 and reattempt merging. The merge should be successful.