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 "Download the Code" icon at the top of the page; 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.
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.
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. 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.
The second custom input window displays the next step in the installation wizard. In the New Database Settings window that Figure 2 shows, you can provide a custom name for the new database (e.g., WHSDBNAME) and designate a user account (e.g., WHSDBUSER) and a password (e.g., WHSDBPWD) that access the new database. If you need to create more than one account for your database, you can adjust this screen or add screens that let you specify additional user account names for the installation.
After you finish entering data into the New Database Settings window, click Next in the confirmation window that follows. Now the installation begins: Windows Installer copies the source directory's script files to the target directory. After you've copied the files, the Installer package runs three custom actions. You can define custom actions that execute custom scripts the same way you added custom properties to the Property table in the Installer database. You can find a good reference for defining custom actions at http://msdn.microsoft.com/library/en-us/msi/hh/msi/prop_0ylv.asp. From within the Wise product, I added a call to each of the three .vbs files that I included in the installation scripts. Each of the CreateDB, GrantPermission, and RunScripts actions executes a .vbs file that, as I describe later in the article, carries out the steps to create a new database.
The custom installation you've created thus far is set up for a simple database, but you can customize the installation's scripts to create your own database. Your first step—modifying the .sql files in the Installer package—lets you specify the database structures that the installation script builds. Note that changing structures might require changing the .vbs files that operate dynamically during installation. You can change the contents of all the files that are in the source directory IKSampleDBFiles. I used external uncompressed source files for this installation so that you can replace the .vbs and .sql scripts as necessary to construct your custom databases.
Creating the Database
Database creation, the heart of the installation, occurs in a series of steps, the first of which is creating the SQL script CreateWhsDB.sql. Listing 1 shows a sample script that the installation uses to create a database on the current server. This script results when the Installer package executes the accompanying CreateWhsDB.vbs file. The installation specifies four parameters for the CREATE DATABASE command: The first parameter is the new database name, and the second parameter designates the .mdf file's location. The script can also specify the log-file location and name; however, the CREATE DATABASE command generates a file with the default name in the same directory as the database's primary data file when you don't specify an explicit log-file location. The third and fourth parameters are the database's initial database size and file-growth setting.
The script executes two stored procedures. The first stored procedure, sp_addlogin, creates a new user account for the database server and assigns a password and default database for that account. Sp_addlogin creates a SQL Server account; to have the script create an NT account or group, replace this stored procedure call with a call to sp_grantlogin. Both sp_addlogin and sp_grantlogin work with the sp_grantdbaccess stored procedure. After the script gives the user access to SQL Server, the sp_grantdbaccess stored procedure assigns the new account as a valid user to a database on the server.
Listing 1 has hard-coded values, which is why it's only a sample. The Installer package initiates the CreateDB action to run the CreateWhsDB.vbs script that Listing 2 shows. Listing 2's code uses a file-system object to open, then replace all the sample text that Listing 1 contains. After Listing 2's script opens the CreateWhsDB.sql file, the script simply writes out the same commands in the same format by using a series of session values from the properties that the Installer defined. The first installation property, Session.Property("INSTALLDIR"), uses the script path information that you select for the database installation. This installation places the script files in the same directory in which the installation creates the database files. To change the way the script uses the selected installation directory as the target for the primary database files, you need only modify the command. The code in Listing 2 writes the FILENAME value to the .sql script file.
The OpenTextFile command lets the CreateWhsDB.vbs script open the CreateWhsDB.sql script file. This command has a few parameters that aren't apparent in the script. The first parameter sets the path and the file's name. Appending the information from the first parameter to the installation directory generates the complete path at runtime. The second parameter shows whether the file should open for reading, writing, or appending; the value 2 represents writing. The third parameter value, True, directs the object to create the file if the file doesn't exist. The final parameter value, -1, tells the file's I/O section to use Unicode characters. The remaining Session.Property values represent custom values that you add to the Installer package. For this script, the values directly match the fields that Figure 2 shows. The values WHSDBNAME, WHSDBUSER, and WHSDBPWD come from the database installation that Figure 2 shows.
Each WriteLine command places a full line, including the carriage return-line feed in the CreateWhsDB.sql script file. The vbCrLf constant adds an additional blank line to the CreateWhsDB.sql script for readability. The script finishes by closing out the File and File System objects. Although the code in Listing 2 provides a way to create the database and users, it requires quite a bit of custom scripting. The solution I present in this article would have limited usefulness if every installation required this much script. Fortunately, you can generate both the database objects (e.g., tables, stored procedures) and the static data to import to the new database.
Generating SQL Scripts
Enterprise Manager provides an interface for automatically generating T-SQL scripts for everything from the database to tables, user-defined types, and other database components. Although this article dynamically defines the database, you can statically generate T-SQL for many common database elements such as tables and stored procedures by using Enterprise Manager. Because these common scripts play an important role in the overall installation, let's briefly look at some of the less obvious aspects of generating these scripts.
Right-clicking a table or database name in Enterprise Manager and selecting the All Tasks, Generate SQL Scripts item from the pop-up menu displays the Generate SQL Scripts window. Figure 3 shows the window's General tab, in which I selected several tables. Depending on how you reach this tab, you might need to use the Show All button on the tab's upper right corner to see all your source tables. You can also select one of the check boxes that automatically selects All objects, All tables, All stored procedures, and so on. For this article, however, I manually selected a subset of elements containing only tables to simplify the automated-permissions assignment that I discuss later in the article. I use this process again later to create a second T-SQL file that contains only stored procedures.
After you select the appropriate objects, tables, stored procedures, and so on from the General tab, proceed to the Formatting tab in the Generate SQL Scripts window, which Figure 4 shows. Figure 4 contains check boxes that specify how to script the objects you select. If your database runs on SQL Server 2000, you can ensure that scripts continue to be compatible with SQL Server 7.0 by selecting the Only script 7.0 compatible features check box. You can use SQL Server 7.0's Enterprise Manager to generate the scripts for databases that still run on SQL Server 7.0, but because of enhancements in the SQL Server 2000 script generator, I recommend using Enterprise Manager 2000 if it's available. Additionally, if you've created user-defined data types, you need to select the Generate scripts for all dependent objects check box to add the necessary user-defined data types to the .sql script.
Figure 4 also shows the generated file's script template. This file has a very specific format, so when you implement a dynamic read of the generated .sql files during installation, you can obtain object names dynamically at installation time. Using code to read the table or stored procedure names is easier if the tables are in a separate .sql file from the stored procedures. Note that database objects such as tables and stored procedures contain text lines that vary and require different calls to set their respective security settings. So, knowing a file's object type makes it easier to apply security to it.
At this point in the script-generation process, if you're working with objects other than tables, you can click OK on the Formatting tab to generate the .sql script. However, if you plan to use a .sql script to recreate a set of database tables, here's an important caveat: Before you click OK, you must proceed to the Generate SQL Scripts window's Options tab, which Figure 5 shows. This step is necessary because in SQL Server, major table elements such as indexes, triggers, and primary and foreign keys aren't set by default. If you don't select the four check boxes that are selected in Figure 5, your .sql script installation won't include these elements. Another item to note in Figure 5 is the file format, which defaults to Unicode. This selection is fine; I discuss its importance later in the article.
You can repeat the script-generation steps to create .sql script files for selected groups or all your database objects. For demonstration purposes, I provide two generated files that you can download from the SQL Server Magazine Web site: WhsDBStructures.sql, which contains tables, and WhsStoredProcedures.sql, which contains a subset of stored procedures. You can review these files by using a text editor or open them in Query Analyzer and run them against a new database to create tables and stored procedures. Now that you've generated the .sql scripts, let's look at creating the static data tables.
Packaging the Data
To transport static data to the new database, you need to export it into a comma-delimited file. (Note that you could use an XML tool to transport a large amount of data, but when you install a new database, your data is typically a limited subset, and importing a comma-delimited file requires less custom scripting than an .xml file.) The easiest way to create this file is to use SQL Server to generate it. Right-clicking the table and selecting the All Tasks, Export Data menu item starts the Data Transformation Services (DTS) Import/Export Wizard. In the first two windows, the wizard initializes—and lets you select—the exported data's source. Initially, the destination window, which Figure 6 shows, looks similar to the source window (e.g., they both identify the targeted database or file). However, the destination screen contains a drop-down menu that lets you change the default destination Microsoft OLE DB Provider for SQL Server to an existing file or to a new filename and path. Selecting Next in the destination window takes you to the Specify Table Copy or Query window. In this window, you accept the default Copy table(s) and view(s) from the source database for the DTS Import/Export Wizard.
Figure 7 shows the next wizard window, Select destination file format, which lets you select the source for the exported data. In this wizard window, you need to change a few defaults. First, no matter which table you selected for the data in Figure 6, the Source field on the destination window displays the first alphabetical table in the database. Therefore, you need to select the correct table or view from which to export the data. The Source drop-down box limits you to one table per file. Then, to use the delimited file's data for the database installation, you also need to modify the text qualifier. The text qualifier defaults to Double Quote \{"\}. However, when you enclose a string in a .sql statement, you use single quotes to enclose static text, so you need to select Single Quote \{'\}, as Figure 7 shows. Finally, this window lets you choose between ANSI and Unicode files the same way the Options tab in the Generate SQL Scripts window does. Note that in this case, the default is ANSI text files, which you retain.
After you complete the settings for the destination file, the wizard presents the scheduling screen. You don't need to immediately change the default setting Run immediately, which runs the export. A summary screen, which displays your final selections, follows the scheduling screen. Clicking Finish triggers a dialog box that tracks the data export's progress. When the export is finished, the wizard creates a simple text file, ChartTypeData.txt, which is one of the installation files available for download.
Setting Account Permissions
Just as you created a script for the CREATE DATABASE command, you use a script to set the new account permissions. You need to run this generated script last because a database, the objects that make up the database, and the user accounts must exist before SQL Server can grant permissions. Listing 3 shows sample code in which the GRANT command permits access to a stored procedure and a table. The GRANT command's basic parameters vary according to the type of object. For tables or views, you can assign a combination of SELECT, UPDATE, and DELETE permissions, whereas the only permission you can assign to stored procedures is EXECUTE. The GRANT command assigns permission to a user (e.g., NT or SQL Server), role, or group and supports the assignment of certain administrative permissions such as BACKUP DATABASE. However, assigning the administrative permissions earlier—at the database-creation stage—would be more appropriate. For this example, I focus only on assigning permissions to database objects.
Another item to note about the GRANT command is that for scripting simplicity, the command assigns permission to only one object at a time. Therefore, each table, view, or stored procedure that you want to grant access to must have its own command. For a large database, scripting a GRANT command for every table, view, and stored procedure would be difficult to accomplish. Instead, Listing 4 uses a set of loops and an array of object names to generate the appropriate T-SQL commands.
Near the top of Listing 4, the code sets variables to comma-delimited lists of table and stored procedure names. Instead of generating static lists, which require manual updates, the VBScript code could generate the object names during installation by dynamically examining the table and stored procedure T-SQL files. However, the comma-delimited list of names can also provide flexibility when you need to assign multiple permission levels to elements in the same T-SQL file.
Now, let's look at how you can manipulate comma-delimited lists of names. The VBScript Split() command is a standard method of taking a delimited string and automatically creating an array that contains one of each item in the string. The VBScript code then uses the generated arrays to create the SQL scripts that assign user permissions. As Listing 4 shows, the script splits a list of names. Then within a simple For loop, the script writes the lines needed to implement the GRANT command to the SQL script file. The script processes each string of table or stored-procedure names in a series. Because it knows the permissions and type of each group of objects, the script can use different settings for different tables. When the script in Listing 4 has executed, the installation is finished scripting statements to create objects and permissions.
Running the Scripts
After the Install wizard has generated the dynamic T-SQL scripts CreateWhsDB.sql and GrantWhsDBPermissions.sql, the Installer executes the final action RunScripts, which executes the RunWhsScripts.vbs file. The code in the RunWhsScripts.vbs file executes each of the T-SQL files against the database to create the database objects and assign permissions. The VBScript that Listing 5 shows opens a connection to the database server by using the connection information that Figure 1 contains. After the VBScript code has established a connection, the code opens each of the .sql script files in turn and executes the commands they contain. Then, the code opens the data files and imports the static data into the newly created database.
To connect to the database server, the VBScript code uses a connection without an ADO Data Source Name (DSN). You don't need a DSN because the code at callout A in Listing 5 uses an ADODB connection, which lets you connect to the database by using a dynamic connection string. At callout A, the code first creates an ADODB.Connection object. This object supports opening and holding a database connection as well as executing statements against that database. The ADODB.Connection object is the only COM object the VBScript code uses for all actions that create and populate the database.
Also at callout A in Listing 5, the code sets SQLOLEDB as the type provider for connection. The connection string uses the MASTERDBMACHINE, MASTERDBUSER, and MASTERDBPWD properties that you've defined in the installation and which you modify in Figure 1. The connection string has four parts when you use it with the SQLOLEDB provider. The first three parts are the account name (User ID), password (Password), and server machine name (Data Source). The fourth part in the connection string, set to Master, tells SQL Server which database to use on the server (in this example, the server is Initial Catalog). You use the master database because it provides a valid connection for creation of the new database. Because the installation needs to connect to the master database and create a new database, the account that the MASTERDBUSER property specifies needs to be an account such as the systems administrator (sa) account, which has administrator privileges on the database server. After you create the new database, the SQL USE command allows execution to continue in the context of the newly created database.
At callout B in Listing 5, the VBScript code executes the SQL scripts. The VBScript code begins by opening the .sql script file for reading. Note that the OpenTextFile command's second parameter is a 1, meaning that it reads one line at a time. If a line doesn't contain the GO command, you need to append the command to the string variable and read the next line. The loop continues to append lines from the script file to the variable strCommand until the loop finds a GO command. Then, the code executes the strCommand by using the database connection. After execution, the code clears the variable and starts to build the next command.
In the Generating SQL Scripts section, I mentioned that you can build lists of tables, views, and stored procedures dynamically. To do so, you can take advantage of the same concept that underlies SQL command execution. For example, each table definition starts with the string CREATE TABLE. As the code at callout B reads the WhsDBStructures.sql file, it can look for this string, then parse the table's name from the line. Parsing the name lets you replace the manually maintained lists of database object names that populate the code at the beginning of Listing 4.
Callout C in Listing 5 shows the VBScript code's final section, which inserts the static data into the database. I have two caveats for this code. Note that the OpenTextFile command's fourth parameter carries a potential gotcha: The DTS wizard that creates data files defaults to ANSI text instead of Unicode. To support ASCII files, you need to set this parameter to its default of 0. The next item of interest at callout C is the SET IDENTITY_INSERT command, which executes before the script begins reading data. This command lets you insert explicit values into a table's IDENTITY column. Turning off the IDENTITY column during static data insertion lets the script copy the exported data and maintain the same identifiers. You can find more information about the SET IDENTITY_INSERT command in SQL Server Books Online (BOL).
The code at callout C uses the same basic logic loop it uses to read each line from the data file, except that each data line contains the full set of data values that an INSERT statement can use. As I previously noted, you prepare the data by enclosing the static text in single quotes, thereby generating the file. For the insert to be successful, the resulting lines require no manipulation. You can enhance these files by adding the column headers to the exported data file, modifying the script to read the data file's first line, then using this line and the resulting line for the INSERT statement's column names.
Taking It from Here
After you download and extract the .msi file and scripts, you can customize both the .msi and .vbs files. Modifying the .msi file requires you to use third-party tools. You can also modify the installation's script files without recompiling or otherwise modifying the .msi file that executes them, as long as you use the same filenames. Additionally, when you run a Windows Installer package, you automatically generate an uninstall script. For this package, the uninstall script removes all the package's .sql and .vbs script files; however, the database that you created remains untouched so that you don't lose data.
After you run the installation package and examine both the newly created database and the .sql files, you can use and customize the tools and concepts I covered in this article to create and install SQL databases on the fly in a consistent and repeatable manner. You can enhance the installation's basic package and scripts over time by adding
- a check to validate a database connection before opening the script files
- a log file to record each command's success or failure
- a script to dynamically build the list of objects that require a GRANT statement to assign permissions
- a script to dynamically create additional roles and users
If you need more information about scripting and methods of manipulating the .vbs files in the installation package, see the Windows Scripting Solutions newsletter. At the newsletter's website, you'll find tips and tricks about mastering scripts and using them to automate your administration tasks.
