Scripting a Custom Database Installation

Packaging a SQL Server database for installation as part of a custom application can be challenging. Your most important task is moving the database's tables, procedures, and related objects cleanly. Other tasks, such as packaging the database instance's backup, carry added peril, preventing you from specifying new user accounts and the primary data file's name. However, you can use Microsoft Windows Installer with a set of custom VBScript and T-SQL scripts to package and custom-install your database with remarkable efficiency. In this article, I use T-SQL scripts generated from SQL Server 2000. You can use the installation to create either a SQL Server 2000 or SQL Server 7.0 database.

I discovered this effective combination when I began to use my company's custom tool for realtime Web site usage analysis. The tool, which integrates with the membership service Active Directory (AD), relies on settings stored in the registry to define a database connection. But because we used the tool in different environments, we needed a way to ensure the consistency of component settings across different machines; allow for custom database names, accounts, and passwords; and let the DBA coordinate components installed across different enterprise machines without having to edit the registry directly. We chose to use Windows Installer, which provides a common configuration framework and encompasses a UI for custom settings. By combining the Installer and custom scripts, you can

• provide a custom database name and installation location
• create custom user accounts and passwords
• create the tables and stored procedures in the new database
• assign account permissions to objects you create in the new database
• import comma-delimited static data from the application

Let's walk through the process for creating an installation package that meets the preceding requirements and look at a simple installation tool that you can use to install SQL Server databases and create your own custom installation packages. You can download the sample tool from the SQL Server Magazine Web site at http://www.sqlmag.com; the installation file contains the SampleDB.msi file and the IKSampleDBFiles subdirectory, which holds the script files that the .msi file needs. Instead of incorporating the source installation files as a .cab file inside the .msi file, I placed the source installation files outside the .msi file in their uncompressed format so that you can replace the .vbs and .sql scripts as necessary to construct your custom databases. For download instructions, see "More on the Web," page 52.

Using the Installer
Windows Installer is a Windows installation service that began shipping with Windows 2000 and is provided as a service pack for Windows NT 4.0 and Windows 9x. The .msi extension represents files that support the Windows Installer format. Scripting a database installation requires two components: the .vbs and .sql scripts that carry out the tasks and the .msi file that transports and coordinates the scripts' actions. Online documentation for Windows Installer is available at http://msdn.microsoft.com/library/psdk/msi/wiport_6gf9.htm.

Microsoft thoroughly documents Windows Installer's programmatic interfaces and capabilities; however, the Installer doesn't provide a typical UI for creating new installation packages. Instead, third-party vendors have developed comprehensive UIs that work with .msi packages. For example, applications from Wise Solutions and InstallShield let you edit the binary .msi file's installation database through a GUI. These products also automatically add a set of wizard windows to your installation package. You can modify these windows by editing the default display or by adding additional windows to the setup process. For the .msi package that I include in this article, I selected Wise for Windows Installer 2.01 because one of my company's customers requires the tool. (Note that Wise Solutions has released Wise for Windows Installer 3.0.)

Demonstrating how to work within a custom environment such as Wise for Windows Installer is beyond the scope of this article, but let's examine the basic modifications that you need to make to support the database installation scripts and run the Installer package. To support script execution, the first change you need to make to the default installation is to add custom properties to the installer database's property table. The .msi environment lets you define global variables by assigning appropriate properties. You can find a useful properties description at http://msdn.microsoft.com/library/en-us/msi/hh/msi/prop_0ylv.asp. The properties that you choose must meet the following two criteria: You must be able to edit the properties in the custom windows that I show in the article, and you must be able to use them in the scripts that I provide. Because of the preceding restrictions, you must assign the custom property definitions to restricted public properties status, which the application designates in uppercase letters (e.g., MASTERDBMACHINE). The property INSTALLDIR, which the Wise tool automatically adds to the Secure Public Properties list, saves your selected installation location for the new database and script files.

In addition to the common windows that an installation builder generates automatically, I created two custom windows. (Running IKSample.msi at this stage in the installation process is helpful because you can review changes as they appear in the windows and during the installation process.) Running the compiled installation package generates an introductory window, followed by a window that lets you select the target directory for the database files. Next, the first of these two custom windows, the Connect to Master Database window, appears. Figure 1 shows this window and the information you need to log in to the database server. The fields on this window are assigned to properties in the installation package. The first field is the server name, which is associated with the property MASTERDBMACHINE. The account information is associated with the custom properties MASTERDBPWD and MASTERDBUSER. When you run the installation package, you provide a database account with administrator privileges to add a new database. The Installer doesn't store this information but needs the data to connect to SQL Server and to execute the commands for creating and populating the new database.