The MSDE Deployment Toolkit (RC) in Action

  • Article

 

Mario Szpuszta

March 2004

Applies to:
   Microsoft® SQL Server™ 2000 Desktop Engine

Summary: Learn how to use the MSDE Deployment Toolkit (Release Candidate) to integrate MSDE deployment functionality into your application's installation package with minimal effort. Additionally, get some insight into building a reusable installer class to make this process even faster and easier. (24 printed pages)

Contents

Introduction
MSDE Usage Scenarios
Deployment Options
MSDE Deployment Toolkit
A Reusable Installer Class
Creating the Final Setup Package
Conclusion

Introduction

Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is the free downloadable, redistributable version of SQL Server that can be used in client applications or small web applications up to about 25 concurrent users. For information about using MSDE optimally with Web applications, see Using MSDE 2000 in a Web Application.

Although it has some restrictions, like a maximum database size of 2 GB and a limitation on the number of statements processed in parallel, it is fully compatible with Microsoft SQL Server 2000. It even supports things like creation of stored procedures and triggers, and offers the same security options.

In mid-December 2003, Microsoft released the MSDE Deployment Toolkit Release Candidate, an add-on for Visual Studio .NET for creating setup packages to allow you to deploy MSDE 2000 with your own application. In this article, I want to show you how you can use this version of the MSDE Deployment Toolkit for creating such setup packages, and explain many of the details for developing the necessary parts of these packages.

But before going into detail about the deployment toolkit itself, let us take a quick look at the primary usage scenarios for MSDE, as well as options that are available for deploying MSDE.

MSDE Usage Scenarios

The two primary usage scenarios for MSDE 2000 are end-user desktop applications and small Web sites.

Desktop Applications – MSDE as an offline store for Smart Clients

Why are desktop applications becoming interesting again nowadays, when we have worked so hard on developing Web applications during the last few years? Well, let's answer this question by asking some other interesting questions!

Have you ever had to work with a browser-based application that tries to mimic a traditional rich desktop application? Or have you ever tried to use a browser-based application when you were offline, perhaps during a plane flight? Of course, browser-based applications do have advantages, especially in terms of deployment and reach. But browser-based applications are, in most cases, not as user-friendly as rich client applications, and they definitely do not work well offline. And these are the key drivers for the return of the client – the Smart Client.

Smart Clients are rich client applications, which combine the advantages of Web and Windows application. A Smart Client with offline capabilities needs to keep data on the client and requires a store for doing so. This is where MSDE 2000 fits in. As soon as you need rich querying or report creation functionality offline, MSDE 2000 becomes a very good candidate for your offline store. Of course, if your application uses MSDE 2000 as an offline store, the application's installation program must install the database engine, if it is not present before installing the application itself.

Small Websites and MSDE

Small websites can also use MSDE 2000 as their database engine. Since MSDE can be deployed royalty-free with your applications, it has some restrictions:

  • Maximum database size of 2GB
  • Maximum of 8 statements processed in parallel.
  • Maximum of 16 instances per machine.
  • No GUI administration tools included.

If the requirements of the website go beyond these restrictions, you should think about using SQL Server 2000 Standard or Enterprise Edition, depending on your requirements.

Deployment Options

In either case – Smart Clients or websites – MSDE 2000 must be deployed to the target machine. The setup program requirements are more extensive for Smart Client applications, since they are used by end users, and probably will be installed by them, too. However, a Web site administrator will definitely appreciate a good setup program for web applications that requires as few steps as possible.

In general, we have three possibilities for deploying MSDE 2000 to the target machine:

  • Manual installation of MSDE
  • Deployment in the application's setup package with merge modules.
  • Add a setup boot-strapper to your setup application which installs MSDE prior to the setup of your own application.

Each of these possibilities has definite advantages, but none of them is perfect. Let's take a quick look at each possibility with its advantages and limits.

Installing MSDE manually

Because Smart Client applications are running on end user machines in many cases, the installation procedure must be as simple as possible and not require the user to have any special knowledge. Therefore, manual installation of MSDE 2000 is not a good option for Smart Client applications.

Since manual installation is not a good option, we should look at integrating MSDE into the setup package of the application, since that is easier for the end user. The only two valid options for creating such setup packages are merge modules and setup boot-strappers.

Deployment via Merge Modules

The most user-friendly way for deploying applications with MSDE 2000 is to integrate the MSDE setup into the application's setup program, so that it will be automatically installed with the application without any additional steps.

Windows Installer supports integration of reusable setup parts in your own setup programs through merge modules. Some earlier versions of MSDE 2000 included merge modules for redistribution of MSDE with your own applications. However, MSDE Release A – the most recent version, and the recommended one to use for any new projects – does not include merge modules.

In addition to causing you to use an older version of MSDE (not recommended), the other reason we do not recommend using merge modules is due to the fact that when MSDE is installed through a merge module, it is not registered as a separate product on the target machine. This means that installing hotfixes for such an instance is not possible, and applying service packs is only possible if the application developer creates an updater to update the app and its MSDE instance as necessary. This is not a good solution for security or maintenance.

Deployment via setup boot-strapper

The last option is the creation of a setup boot-strapper. This setup boot-strapper installs MSDE 2000 before it launches the setup for your application. Writing such a setup boot-strapper is not easy, because it is doing a chained .msi installation, which is not natively supported by the Windows Installer service. Think of all the scenarios that must be supported when writing such an application: necessary reboots, handling errors that occur during setup, passing the right parameters for unattended setup, and so forth. So, this option is possible, but not easy.

MSDE Deployment Toolkit

Introduction

Looking at all the options above, none of them is really satisfying, and the only valid option for integrating MSDE 2000 in setups – creating a setup boot-strapper – cannot be easily implemented. But if you look more closely at it, writing the boot-strapper is the only real challenge in this case. That's where MSDE Deployment Toolkit hits the spot.

The MSDE Deployment Toolkit is a package that consists of a ready-to-use bootstrap loader and an add-in for Visual Studio .NET 2003 to assist in laying out your setup package. It also includes very detailed documentation and a bunch of samples demonstrating how to create setup packages that include MSDE 2000 as well as your application.

The Toolkit allows you to create Visual Studio .NET-like setup programs. These setup programs conform to the Microsoft standard of installing prerequisites for an application first and afterwards the application itself:

Figure 1. Office Launch Proof of Concept Setup

The following figure shows what the setup boot-strapper is doing for you (conceptually):

Figure 2. Setup boot-strapper flow-chart

This way, the setup package can not only install and configure your application, but also create and configure the application database on the previously installed MSDE instance.

Setup packages for redistributing MSDE are created based on a setup template that is part of the toolkit. This template contains the setup boot-strapper, configuration files for the boot-strapper, and subdirectories reserved for the setup files of MSDE 2000 as well as your application.

Figure 3. MSDE Deployment Toolkit template directory

You use the add-in that will be installed with the toolkit to create setup images based on this template, your application's setup, and the setup files of MSDE 2000. The resulting package can be burned onto CD or placed on a network share for distribution.

The template used for setup package creation is located in the template subdirectory of the MSDE Deployment Toolkit program files folder (see figure). When taking a look at this directory, you will see that the boot-strapper consists of two separate applications, Setup.exe and MSDEBootStrap.exe. The first one, Setup.exe, is written in unmanaged code, and is the primary boot strapper application that installs the .NET Framework if it is not present on the target machine. The second part of the boot-strapper – MSDEBootStrap.exe – is a managed application which installs MSDE 2000 if it is not yet installed on the target machine. Both have two separate configuration files – the unmanaged part uses the setup.ini configuration file located in the same directory, whereas the managed part uses its app.config file. The application configuration file will be written and modified by the toolkit's Visual Studio .NET add-in during the setup creation process (the setup.ini file is not touched).

The above-mentioned add-in is essentially a wizard that guides you through the steps for configuring the boot-strapper, and then copying the template files, your application setup files, and the MSDE 2000 setup files into a final setup image. After MSDE Deployment Toolkit has been installed, you have to manually activate the add-in through Visual Studio's add-in manager.

Setup Creation – Outline

Creating setup applications using the MSDE Deployment Toolkit is very similar to creating normal Windows Installer packages. The boot-strapper application installs the .NET Framework as well as MSDE 2000 for you. Creating the setup package for the application itself, as well as writing code for the database installation, are left to the developer, and will not automatically be done by the toolkit. Overall, the following steps are required for creating a complete setup package:

  1. Create a setup project with Visual Studio .NET.
  2. Include all required parts of your application into this setup project – these are the application project files, all resource DLLs, and any additional files required for using the application.
  3. Add two custom actions to your setup project that will handle installing and uninstalling the application database on the MSDE instance.
  4. Complete the steps of the MSDE Bootstrap Loader Configurator in Visual Studio .NET. Remember to open the setup project before starting the wizard. When going through the steps of this wizard, you will have to specify MSDE configuration options as well as the path to MSDE 2000 setup files and the output path.
  5. Copy the resulting image from the previously selected output path to a network directory or burn it on CD.

The most interesting parts of this process are definitely the custom actions required for installing and uninstalling the database. Custom actions can be added in many different ways to setup packages – from simple script files to executables to batch files. With the .NET Framework, another way of adding custom actions to Windows Installer packages has become available – installer classes. Installer classes are simple .NET Framework classes that enable the developer to encapsulate custom actions for setup packages in .NET assemblies. These .NET assemblies can be added to the .msi package and then be registered as custom actions for this package.

Creating installer classes is very easy and straight forward. First, you have to create a new class library project and add an installer class to this project. Afterwards you can catch installer events like AfterInstall or BeforeUninstall and use these to access to the context of the installation. This will give you access to all of the parameters passed to the installer service, as well as information about the environment (machine name, operating system, etc…).

All the MSDE-specific installation tasks will be completed by the toolkit's boot-strapper, as configured through the add-in, while the creation of the installer class for managing the database installation part is the task of the developer. I will illustrate the creation of such an installer class in the next section, before discussing the final steps of creating a setup package using MSDE Deployment Toolkit.

A Reusable Installer Class

When writing my first installer class, I quickly recognized that the actions available in such an installer are pretty standard across products, and in many cases have little to do with the particular application. During installation you have to create the database - the tables and relationships, as well as the database users, roles and logins. Uninstalling the database means, in most cases, creating a backup, then removing the database and any associated files.

With these facts in mind, I started thinking about creating a more reusable installer class that can be included in several projects with just a small set of modifications. Let's take a look at what is required to create such a custom installer class.

To create a new installer, add a new class library project to your solution, then add a new item of type Installer Class.

Figure 4. Adding a new Installer Class

The installer class inherits from System.Configuration.Install.Installer, and enables us to catch several events that occur during the installation of a Windows Installer package, as well as access context information (parameters, operating system version, machine name…).

In our case, two events are of interest – AfterInstall and BeforeUninstall. We will use the first event if we need to install the database on the MSDE instance (which has been previously installed by the boot-strapper), and the second event if we need to remove the database(you should always explicitly remove the database when uninstalling the application). The following code snippet shows the skeleton of the installer class:

Imports System.ComponentModel
Imports System.Configuration.Install

<RunInstaller(True)> Public Class DatabaseInstaller
    Inherits System.Configuration.Install.Installer

Component Designer generated code

  Private Sub DatabaseInstaller_AfterInstall(ByVal sender As Object, _
        ByVal e As System.Configuration.Install.InstallEventArgs) 
                   Handles MyBase.AfterInstall

      'Installation of the database

  End Sub

  Private Sub DatabaseInstaller_BeforeUninstall(ByVal sender As Object, _
        ByVal e As System.Configuration.Install.InstallEventArgs) 
                   Handles MyBase.BeforeUninstall

      'Uninstall actions for backing up and / or removing the database

  End Sub

End Class

Here are the steps that must be completed during the installation of the database:

  • Validate whether the MSDE instance is started.
  • Create the database and its objects (tables, relationships…).
  • Create logins and database users, as well as roles (optional).
  • Fill the database with initial data (optional).

These steps can be independently reused from application to application. Thus we can encapsulate them in a more generic installer class. The setup variables would be:

  • The name of the MSDE instance
  • The name of the database
  • The database connection strings
  • The scripts for database creation as well as table creation
  • The scripts for creating the logins, users and roles

The plumbing code around these actions is always the same – from validation of the MSDE instance service to error logging, as well as putting the parts together in the correct order.

When taking a look at the uninstall process, we will be faced with a very similar situation. Uninstalling the database requires the following actions:

  • Validate whether the MSDE instance is started.
  • Prompt the user to see if a backup is desired, and perform the backup if necessary (optional).
  • Delete the database from the MSDE instance.
  • Delete users, roles and logins from the instance.

Uninstalling the database requires the same variables as installing the database: the name of the instance, the name of the database, the connection strings, and the scripts.

The Installer Base Class

So, we will have our installer class act as an abstract base class, which is just a template for the installation process. The plug-in points for this template are specified through abstract methods that must be provided by a concrete implementation of the installer. Just take a look at the following code snippet:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.ServiceProcess
Imports System.Configuration.Install


<RunInstaller(True)> _
Public MustInherit Class SqlDbInstaller
    Inherits System.Configuration.Install.Installer

"Component Designer generated code "

    'Abstract parts of the class --------------------------
    Public MustOverride ReadOnly Property DatabaseName() As String
    Public MustOverride ReadOnly Property InstanceName() As String

    Protected MustOverride Function GetDatabaseCreationScript() As String()
    Protected MustOverride Function GetDatabaseInstallScript() As String()
    Protected MustOverride Function GetDatabaseDropScript() As String()
    Protected MustOverride Function GetDatabaseBackupScript() As String()

    Protected Overridable ReadOnly Property ServiceWaitTime() As Integer
        Get
            Return 60
        End Get
    End Property
    '------------------------------------------------------


    Private Sub DatabaseInstaller_AfterInstall(ByVal sender As Object, _
                ByVal e As System.Configuration.Install.InstallEventArgs) _
                      Handles MyBase.AfterInstall

        'Template code for installing the database

    End Sub

    Private Sub DatabaseInstaller_BeforeUninstall(ByVal sender As Object, _
                ByVal e As System.Configuration.Install.InstallEventArgs) 
                      Handles MyBase.BeforeUninstall

        'Template code for removing the database

    End Sub

"Private Methods"

End Class

The following tables show how the base class uses the different abstract members that must be provided by a concrete implementation:

Property Description
DatabaseName Name of the application's database.
InstanceName Name of the MSDE instance. This name must match the name that will be used when completing the steps of the MSDE Bootstrap Loader Configurator.
Method Description
GetDatabaseCreationScript Returns an array of strings. Each element in the array represents a SQL statement that will be executed for the creation of the database. While executing this script, the installer is connected to the master database.
GetDatabaseInstallScript Returns an array of strings. Each element in the array is a SQL statement. These statements are used for creating database objects as well as logins, users and roles. While executing this script, the installer is connected to the application database. If this method returns nothing, no actions will be executed.
GetDatabaseBackupScript Returns an array of strings. Each element is one SQL statement of a script that will be executed immediately before the database drop statements (see next method) will be executed. If this method returns nothing, no actions will be executed.
GetDatabaseDropScript Returns an array of strings, each one of which is a SQL statement. These statements will be executed when the database will be deleted. While executing these statements the installer is connected to the master database.
ServiceWaitTime This property returns an integer that specifies the time to wait for the MSDE service when starting the service.

All methods and properties described above must be provided by a concrete implementation of the installer class. The base class assembles all these methods to define the necessary actions for database installation.

First of all, let's take a look at the database installation part. The database is installed in the AfterInstall event of the installer class, as shown in the following code snippet:

Private Sub DatabaseInstaller_AfterInstall(ByVal sender As Object, _
            ByVal e As System.Configuration.Install.InstallEventArgs) _
                  Handles MyBase.AfterInstall

    Dim cmds() As String
    Dim cmd As SqlCommand
    Dim conn As SqlConnection

    'Installing the database
    Try

        'Verify whether MSDE is running on the target machine
        ValidateService()

        'Load the database installation scripts
        Dim sqlCreate() As String = Me.GetDatabaseCreationScript()
        Dim sqlInstall() As String = Me.GetDatabaseInstallScript()

        'Create the database
        conn = New SqlConnection(Me.MasterConnectionString)
        conn.Open()

        Try

            For Each sql As String In sqlCreate
                cmd = New SqlCommand(sql.Trim(), conn)
                cmd.ExecuteNonQuery()
            Next

        Finally
            conn.Close()
        End Try

        'Create database content (users, roles, tables, initial data...)
        conn = New SqlConnection(Me.ConnectionString)
        conn.Open()

        Try

            For Each sql As String In sqlInstall
                cmd = New SqlCommand(sql, conn)
                cmd.ExecuteNonQuery()
            Next

        Finally
            conn.Close()
        End Try


    Catch ex As Exception

        Throw

    End Try

End Sub

Before starting with the installation of the database itself, the installer must validate whether the MSDE target instance is running. If the application's setup package is launched immediately after the MSDE setup has completed (typical behavior), the MSDE service is not yet started. The ValidateService() function verifies whether the MSDE service is running, and starts it if it isn't.

Afterwards, the function retrieves the necessary SQL commands for creating the database as well as the tables, relationships, constraints, users and roles. It isn't of key importance how the concrete implementation gets these statements (in the sample, I used embedded resources). You could also add code to do things like displaying dialogs to get user input during the different steps of the installation process.

For the database creation step, the installer connects to the master database and executes the creation statements, whatever they are (CREATE DATABASE, RESTORE DATABASE, sp_attach_single_file_db). Afterwards, the connection to master is closed and a connection to the application database will be opened for setting up security and adding content (users, roles, initial data…).

That's it! With this code, we have provided all the steps that are necessary for installing the database. Let's take a look at the uninstall process at next:

Private Sub DatabaseInstaller_BeforeUninstall(ByVal sender As Object, _
            ByVal e As System.Configuration.Install.InstallEventArgs) _
                  Handles MyBase.BeforeUninstall

    Dim cmds() As String
    Dim cmd As SqlCommand
    Dim conn As SqlConnection

    Try

        'Verify whether MSDE is running
        ValidateService()

        'Load the uninstall database scripts
        Dim sqlDrop() As String = Me.GetDatabaseDropScript()
        Dim sqlUninstall() As String = Me.GetDatabaseBackupScript()

        If Not IsNothing(sqlUninstall) Then

            conn = New SqlConnection(Me.ConnectionString)
            conn.Open()

            Try

                For Each sql As String In sqlUninstall
                    cmd = New SqlCommand(sql, conn)
                    cmd.ExecuteNonQuery()
                Next sql

            Finally
                conn.Close()
                conn.Dispose()
            End Try

        End If

        'Connect to the master database 
        conn = New SqlConnection(Me.MasterConnectionString)
        conn.Open()

        Try

            For Each sql As String In sqlDrop
                cmd = New SqlCommand(sql, conn)
                cmd.ExecuteNonQuery()
            Next

        Finally

            conn.Close()

        End Try

    Catch ex As Exception

        Throw

    End Try

End Sub

Again, the code looks very familiar, as its basic structure is similar to the database installation code. First of all it validates whether MSDE is running, and if it isn't it starts the service. Then it retrieves the SQL statements for uninstalling and dropping the database and executes them in two separate steps.

The statements for uninstalling the database will be executed through a connection to the application database itself. These statements can contain things like database backup statements. After these statements have been executed successfully, the application connects to the master database and executes the statements for dropping the database and associated logins.

In my first implementation, sometimes the installer was not able to drop the database. The reason for that was quite simple! Unfortunately, I had forgotten to keep the connection pooling behavior of ADO.NET in mind. When you look at the function above, in the first step the installer connects to the application database. After calling Connection.Close(), the connection to the database will not actually be closed but only returned to the ADO.NET connection pool. When the installer tries to drop the database in the next step (when connected to master), an exception occurs because the first connection still exists in the connection pool.

Therefore, I had to change the connection properties for the database to disable connection pooling as you can see in the following code snippet:

Public Overridable ReadOnly Property ConnectionString() As String
    Get
        Return String.Format("data source=""{0}\{1}"";" & _
                             "Trusted_Connection=yes;database={2};" & _
                             "Pooling='false'", _
                             Environment.MachineName, Me.InstanceName, _
                             Me.DatabaseName)
    End Get
End Property

Public Overridable ReadOnly Property MasterConnectionString() As String
    Get
        Return String.Format("data source=""{0}\{1}"";" & _
                             "Trusted_Connection=yes;database=master;" & _
                             "Pooling='false'", Environment.MachineName, _
                             Me.InstanceName)
    End Get
End Property

Last but not least, the secrets of ValidateService() must be unveiled. This function verifies whether the MSDE service is running and if not, it starts the service.

Protected Sub ValidateService()

    Try

        Dim svc As New ServiceController("MSSQL$" & Me.InstanceName)

        If Not IsNothing(svc) Then
            If svc.Status <> ServiceControllerStatus.Running Then

                svc.Start()

                Dim startDate As DateTime = DateTime.Now
                While DateTime.Now.Subtract(startDate).Seconds _
                                                 <= Me.ServiceWaitTime
                    svc.Refresh()
                    System.Threading.Thread.Sleep(2000)

                    If svc.Status = ServiceControllerStatus.Running Then
                        Exit While
                    End If
                End While

                svc.Refresh()
                If svc.Status <> ServiceControllerStatus.Running Then
                    Throw New Exception("Unable to start instance 
service!")
                End If

            End If
        End If

    Catch ex As Exception

        Throw New Exception("Unable to access service: " & ex.Message, ex)

    End Try

End Sub

For all SQL Server instances that are not the machine's default instance, a unique name must be assigned. SQL Server setup will always adds the prefix MSSQL$ to these instance names.

ValidateService() retrieves a ServiceController() for the corresponding MSDE instance and verifies whether this services exists. If it does, it examines the service's state; if the service is not running, it will start it. It will then check the service's state again – if it still isn't running, it throws an exception to abort the installation.

That's it for the installer class. Using a class like this, we can simplify coding for most of the usual installation processes.

A concrete implementation

Let's tale a quick look at a sample illustrating the concrete implementation of the installer base class. This sample provides the necessary information like the database name, the instance name, and all the scripts that are necessary for installing and removing the database. Take a look at the following code snippet:

Public Class CustomInstaller
    Inherits SqlDatabaseInstaller.SqlDbInstaller


    Public Overrides ReadOnly Property DatabaseName() As String
        Get
            Return "MyOfficePoc"
        End Get
    End Property

    Public Overrides ReadOnly Property InstanceName() As String
        Get
            Return "MyInstance"
        End Get
    End Property

    Protected Overrides Function GetDatabaseInstallScript() As String()
        Dim statements As String = _
               LoadManifestResource("DBInstaller.DBInstall.sql")
        statements = statements.Replace("GO", ";")

        Return statements.Split(";")
    End Function

    Protected Overrides Function GetDatabaseCreationScript() As String()
        Dim statements As String = _
               MyBase.LoadManifestResource("DBInstaller.DBCreate.sql")
        statements = String.Format(statements, Me.DatabaseName)
        statements = statements.Replace("GO", ";")

        Return statements.Split(";")
    End Function

    Protected Overrides Function GetDatabaseBackupScript() As String()
        Dim ret() As String = Nothing

        'Load the uninstall database commands
        Dim fBackup As New BackupForm
        If fBackup.ShowDialog() = Windows.Forms.DialogResult.OK Then
            ReDim ret(0)

            ret(0) = 
MyBase.LoadManifestResource("DBInstaller.DBUninstall.sql")
            ret(0) = String.Format(ret(0), Me.DatabaseName, 
fBackup.BackupFile)
        End If

        Return ret
    End Function

    Protected Overrides Function GetDatabaseDropScript() As String()
        Dim statements As String = _
               MyBase.LoadManifestResource("DBInstaller.DBDrop.sql")
        statements = String.Format(statements, Me.DatabaseName)
        statements = statements.Replace("GO", ";")

        Return statements.Split(";")
    End Function

End Class

First, the concrete implementation overrides the properties DatabaseName and InstanceName. The DatabaseName specifies the name of the application's database that will be created during the installation and will be removed when uninstalling the application.

Then there's the instance name. MSDE enables installation of more than one instance on target machines. An instance consists of its own services, configuration, and system databases. Thus, instances are complete separate units working independently from each other. A single machine can have exactly one default instance that has no name. Any further instance must have a unique name on the target machine.

All the other functions are used to retrieve SQL scripts for the specific task in the setup process. A commonality of all these functions is that they are using the base class's LoadManifestResource() function in this concrete sample for loading SQL scripts from the assembly's manifest resource stream (that's just one possible way – the advantage here is that you only need to replace these embedded resource files when changing SQL statements).

The following code snipped shows the content of the function:

Dim ret As String
Dim s As System.IO.StreamReader = New System.IO.StreamReader( _

System.Reflection.Assembly.GetCallingAssembly().
                            GetManifestResourceStream(name))

ret = s.ReadToEnd()
s.Close()

Return ret

It just reads the content of an embedded resource through GetManifestResourceStream(). An embedded resource is just content that will be compiled directly into the executable or DLL. That enables integration of things like images, strings or other things into the application without separately deploying them.

Embedding resources can be accomplished by adding a file to the project and setting its Build Action property to Embedded Resource. The name of the resource consists of the project name as well as the filename in the format <project name>.filename.

The figure below shows the option in the Visual Studio .NET IDE where the file DBCreate.SQL of the Project DBInstaller is selected in the solution explorer.

Figure 5. DBCreate.sql file properties

The selected build action for this item is Embedded Resource thus the name of this resource used in LoadManifestResource() would be DBInstaller.DBCreate.sql.

All necessary SQL scripts must be included as embedded resources in the project. I generated the SQL script by modeling my tables in SQL Server using Enterprise Manager, then using the Generate Script functionality. You can also write the scripts manually or create them using case tools like Visio.

The concrete implementation includes four text files as embedded resources, DBCreate.sql, DBInstall.sql, DBUninstall.sql and DBDrop.sql. These files contain the SQL statements necessary for performing the corresponding step during the installation phase. The content of each of these files will be returned as an array of SQL statements in the corresponding functions of the concrete implementation. Look at the following table to get a description for each of these files.

File Description
DBCreate.sql This file contains the necessary statements for creating the database. Typically this is a CREATE DATABASE statement as well as statements to create some logins. While executing these statements the installer should be connected to the master database. As we will see later on you have some more elegant solutions than CREATE DATABASE available, too!
DBInstall.sql After the database has been created, database tables, users and logins can be created. This SQL script file contains all the necessary statements for creating tables, constraints, relationships, users, and roles.
DBUninstall.sql Before the database is dropped during the uninstall process, the user might create backups or perform some other actions. The necessary SQL commands for these actions are included in this script file. For the sample we will only add statements for database backup.
DBDrop.sql The last action during the uninstall process is dropping the database. You should always drop the database when removing the application (after backup if required).

Although creating and using DDL statements is easy and straightforward, I want to introduce some more elegant possibilities for creating the database during the installation process. In general you have three options for database creation:

  • As I introduced in the sample above, you can use CREATE DATABASE and CREATE TABLE statements to create the whole database.
  • Instead of executing DDL statements during the setup process, you can ship an existing MDF file with your setup, detached from your developer machine using sp_detach_db, and re-attach it using the system stored procedure sp_attach_single_file_db. In this case you don't need to execute any additional DDL statements for creating tables, relationships and constraints, as they should already exist in the database.
  • Another option is restoring a database from a backup created on your developer machine and shipped with the setup package. That has the same advantages as re-attaching a database file. In addition, you can include transaction log files created on the development machine when using backups.

In either case, you have to manually create logins, users, and optionally database roles after the database has been created.

In the next section I will describe how to include this installer class in a custom setup, and introduce the MSDE Bootstrap Loader Configurator.

Creating the Final Setup Package

Now it's time for creating the setup package for our application in Visual Studio .NET. In order to do so, we have to add a new setup project to our solution and add all files necessary for using our application, plus the assembly that contains the installer classes introduced in the previous section.

When we have added the files to the setup project, we need to switch to the Custom Actions View by right clicking the project and selecting View – Custom Actions.

Figure 6. Custom Actions view

In this view we can specify all custom actions that should be executed during the different cycles of the installation process. The interesting parts in our case are the Install and Uninstall cycles of the installation process, where we want to create or drop the database from the MSDE instance. To add our custom actions, we have to right click on the corresponding folder (Install or Uninstall) and select Add Custom Action. A dialog for selecting the custom action pops up.

Figure 7. Add Custom Action dialog

In this dialog we have the possibility of selecting executables, DLLs, VB script files or Jscript files that encapsulate the custom action. Only files that are included into the setup project can be selected because they must of course be deployed to the target machine, too (they are executed during the installation process on the target machine).

As we have created a class library with a custom installer, we are going to select the assembly DLL with the concrete implementation of our installer class. When clicking on the new custom action you can see that the InstallerClass property is set to true in the properties window. Visual Studio .NET detects that the assembly DLL contains a custom installer class that should be executed during the setup process. Any further connections with the corresponding events will be done by the installer service automatically.

When we have finished the creation of our setup package, we need to rebuild the solution and the setup package by right clicking the setup project and selecting Rebuild (or Build).

Creating the setup package using the MSDE Bootstrap Loader Configurator

The steps so far are not specific to the MSDE Deployment Toolkit. The same tasks would have to be completed in a similar way to create a setup package for an application that assumes MSDE is pre-installed. In this last step, we must bundle our application setup, the MSDE 2000 setup, and the boot-strapper of the toolkit. This bundling can be accomplished by configuring the boot-strapper and copying all necessary files into a final setup image.

These steps can easily be completed by using the add-in delivered with MSDE Deployment Toolkit – the MSDE Bootstrap Loader Configurator.

If the toolkit's add-in is loaded, you'll find a new menu entry in the Tools menu of Visual Studio .NET called MSDE Bootstrap Loader Configurator. If you select this entry, a wizard starts with the screen shown as follows.

Figure 8. MSDE Bootsrap Loader Configurator

In this first step, you have to specify the instance name for the MSDE installation on the target machine, as well as a product name and a vendor name. The product ID will automatically be retrieved from the application's setup project as MSDE will act as a part of your product. The instance name must exactly match the name selected during the creation step of the installer class. Otherwise, the installer will not be able to find the MSDE instance for the database creation steps.

In the next few steps, we specify the different text parts that will be displayed in the boot-strapper application.

Figure 9. Bootstrap Loader Configurator page 2

Then, we have to specify several directories that will be used by the configurator for collecting all the necessary files and directories that should be included in the final setup image.

Figure 10. Bootstrap Loader Configurator page 3

These directories are:

  • Bootstrap Loader Files
    Directory with the setup boot-strapper files shipped with MSDE Deployment Toolkit.
  • MSDE Files
    Path to the extracted setup files of MSDE 2000.
  • Your Setup Files
    Path to the MSI setup package of your own application. This path will automatically be retrieved by the setup project included in the solution.
  • Output Directory
    Points to the folder where the wizard will copy the entire collected file. This directory contains the final image that can be burned on CD or published on a network share.

The options in the last two steps are more interesting again, because they allow you to specify the default options for the MSDE target installation.

Figure 11. Bootstrap Loader Configurator page 4

First of all, MSDE 2000 requires an sa password. Thus you have the option to automatically generate a strong password or to select your own password. If you chose the generate option, the password will be created dynamically by the bootstrap loader at MSDE installation time.

If you want to use SQL Server logins, you have to enable the mixed-mode security option, otherwise Windows authentication will be used.

Very important is the Disable Network Protocols option, which is set to true by default. When network protocols are disabled, the instance is not reachable through TCP or other network protocols, and thus can only be accessed from applications running on the local machine. This is very important from a security perspective, and should be kept as the setting for MSDE instances used on client machines.

In the next step, you specify things like the path to the database files as well as a hard-coded path for the MSDE installation. You can also change the default collation as well as the logging modes.

The very last page of the wizard is a summary of the options you have selected while completing the previous steps, which you can use to verify your settings. When you complete this last step of the wizard, it starts copying the files from the selected source destinations to the selected output directory and creates the corresponding configuration files for the setup package. If you want to include the .NET Framework on your final setup image, you have to copy the framework redistributable (dotnetfx.exe) to the target destination, too.

Conclusion

SQL Server is Microsoft's main relational database engine, no matter whether for large scale enterprise applications, desktop applications or even applications running on smart devices like Pocket PC or SmartPhone (SQL CE).

MSDE 2000 is the light weight version of SQL Server which is the best choice for small websites as well as Smart Clients running on end-user desktop systems. Using MSDE 2000 for such scenarios requires easy installation and deployment which was a very challenging task in the past. The MSDE Deployment Toolkit addresses this issue by simplifying the creation of setup packages that install both MSDE 2000 and your application. The most challenging task left is the creation of the installer class that creates the database during the installation of the application. But even the creation of the installer is just a piece of cake when using a generic installer class like the one introduced in this article.

Altogether, the MSDE Deployment Toolkit is something that we have been waiting for for a very long time. Using it is a great way to easily include MSDE in your setup packages.