Data Deployment

Streamline Your Database Setup Process with a Custom Installer

Alek Davis

Code download available at:CustomDatabaseInstaller.exe(135 KB)

Contents

Choosing an Installer Technology
Writing the Database Installer
Verifying Database Existence and Performing Database Queries
Creating a Database
Checking the Database Version
Standardizing Database Files and Folders
Processing Database Files
Implementing Database Upgrades
Repairing Databases
Performing Common Operations
Defensive Programming
A Real-Life Example
Conclusion

If you ever had to install, repair, or upgrade a large database, you must know that database setup can be a daunting process, one that is especially prone to human error. Mishaps such as skipping an upgrade script or forgetting to recompile a new version of a stored procedure can happen easily, especially when the installation involves manual operations.

One way to reduce human error is through the implementation of an automated setup program such as a Microsoft® Installer (MSI) file or InstallShield application. Alternatively, you can write a database installation app or script. (Figure 1 summarizes the advantages and disadvantages of various database installer options.)

Figure 1 Database Installation Options

In this article, I will explain a general approach to writing database installers and illustrate it with a real-life example. The article is accompanied by helper scripts, which can be used for setting up databases and compiling T-SQL scripts. The download is available at the link at the top of this article.

Choosing an Installer Technology

If you decide to write a database installer, you must first choose a technology on which to base the installer. In Windows®, the most common types of application installers are MSI files, which you can build using Visual Studio® .NET or third-party products such as InstallShield Developer or Wise for Windows Installer.

In most cases, MSI-based setups handle application installations quite well, but unfortunately, MSI does not offer built-in support for database setups. While a typical Windows-based application setup normally involves deploying application files, registering COM objects or .NET assemblies, modifying the Windows registry, or creating shortcuts, none of these steps apply to database installation. And unless the database uses extended stored procedures, it doesn't even need to leave physical files on the system after setup is complete (by physical files, I mean T-SQL scripts used to build metadata, not the database or transaction log files.) So all a database installer would need to do is execute the setup scripts in the right order and then remove them at the end of the process. This can be done programmatically, but the current version of MSI does not provide T-SQL scripting capabilities; it relies on external programs for this purpose.

If you decide to use MSI, in addition to the setup project, you will need to write an external application to handle database installation. Implementation of MSI-based installers is, however, beyond the scope of this article, so I won't cover it here. (If you are eager to learn more about this topic, read "Scripting a Custom Database Installation" by William Sheldon.) Instead, I will explain how to write a robust script that can handle database setups. This script can be called from an MSI-based installer, command line, or custom installation program and it will work for most database installations.

Writing the Database Installer

I'll assume that by this point you've already written the T-SQL scripts responsible for creating database metadata and populating tables. Now you'll need to organize the scripts in a consistent manner so that the installer can find them. To allow the installer to find and execute the database scripts, they must be implemented following guidelines that I will define later. Now let me briefly explain how a typical database installer should work.

I cannot guarantee this logic to be comprehensive, but from my experience the steps defined in Figure 2 should be sufficient for most databases. Some database developers choose to combine several logical steps in one operation. For example, some might write a table creation script that includes constraint definitions as well as index- and trigger-creation code.

Figure 2 Database Setup Logic Pseudocode

Connect to a SQL Server database as an administrator Check if the specified database exists If the database does not exist Create the database Create user-defined data types Add database roles Create database users and assign them appropriate roles Define rules Build database tables and views Define indexes Define constraints Create triggers Populate database tables with initial data Else (if the database exists) Check the database version If the database version is up-to-date Run the repair scripts (hotfixes) Else (if the database version is out-of-date) Run the upgrade scripts End If End If Always recompile stored procedures and UDFs

Many databases do not require all of the steps (for example, not all databases use triggers), while some may require additional operations such as registering extended stored procedures. For the most part, these differences are not essential. I just want to show you typical operations in the order they must be executed. Obviously, the order of the steps is important since you can't populate a table before creating it.

This article comes with a script called dbsetup.vbs, which follows the logic defined in Figure 2. I will use this script as an example to illustrate how a database installer should work. You can use this script as it is, modify its logic and default settings to match your database requirements, or simply use it as a reference when writing your own installer. Now let's take a look at how the script works.

Dbsetup can be executed from the command line. The script expects the caller to pass certain parameters, such as connection information (server name, instance, and port) and authentication data (it supports both SQL Server authentication and integrated Windows authentication). To see the description of supported command-line parameters, execute the script using the /? switch. When dbsetup starts, it connects to the specified database server and checks the status of a particular database. To do this, the script first verifies the existence of the database and, depending on the result, it runs in one of the three main modes: database creation, upgrade, or repair (there are three additional modes which I will not discuss, but you can read about them in the script comments). If the database does not exist, the installer will create it and build all the necessary metadata, such as tables, views, stored procedures, and so on. If the database already exists, the script will check its version. If the version of the database is up to date, the script will apply the repair script (hotfixes). If the database is out of date, the script will perform an upgrade. In either case, the script recompiles both the stored procedures and the user-defined functions (UDFs).

Verifying Database Existence and Performing Database Queries

You can verify the existence of a database, as well as perform other database queries and operations, using a variety of technologies, such as COM-based SQL Data Management Objects (SQL-DMO) or ADO. The advantage of SQL-DMO is that it exposes database management functionality in a more logical and object-oriented manner and does not require an understanding of SQL Server™ internals. In SQL Server 2005, currently in beta, SQL-DMO is superseded by the new .NET-based technology called SQL Server Management Objects (SMO). To check for database existence using SQL-DMO, the installer can retrieve the Databases collection from the specified SQL Server instance and look for a database item with the matching name.

The disadvantage of SQL-DMO is that it may not be installed by default on a system without SQL Server. This can cause a problem if you try to install the database on a remote computer from a system that does not have SQL-DMO. Unlike SQL-DMO, ADO comes with all versions of Microsoft Data Access Components (MDAC) and will be available on most systems. Because of its wider availability, I recommend using ADO for database operations even though it may require modifications if changes in system metadata (which occasionally accompany SQL Server releases) cause ADO queries to break. This does not happen very often, though, and if it does, it shouldn't be too hard to fix. To check whether the database exists using ADO, the installer executes the T-SQL query shown in the following code:

SELECT 1 FROM master..sysdatabases WHERE name = 'databasename'

If the query does not return any records, the installer assumes that the database does not exist and will try to create it.

Creating a Database

The creation of the database may depend on parameters, such as the location of database data and transaction log files, which cannot be known and hardcoded at design time. Because the script cannot use existing SQL files for database creation, it generates any required T-SQL statements and executes them programmatically at run time (see the CreateDatabase method). If the expected runtime parameters are not specified, the script will use the defaults.

Checking the Database Version

While database existence can be verified by querying system metadata, getting the database version requires some creativity. Because SQL Server databases cannot be versioned the same way binary files are, you will have to implement your own versioning scheme. One way is to create a simple stored procedure or UDF that returns a hardcoded version string. To make this approach generic, this stored procedure or function must have the same name (such as GetVersion) in all of your databases and it must be recompiled to return a new version during every database upgrade. As an alternative, you can store the version string in a database table or determine it using some other criteria. Just make sure that the version-checking interface is consistent so that the installer can use it for every database it supports. Dbsetup assumes that the database that implements the GetVersion UDF (see the GetDatabaseVersion method).

After obtaining the database version, the installer must compare it with the current version, but where does the current version come from? The answer to this question depends on how you implement the installer. If you build it as a setup program, such as an MSI file, InstallShield setup, or Wise installer, the logical choice for the current version would be the product version, which is an intrinsic property of both MSI databases and proprietary InstallShield setup files (built using InstallShield Professional 6.x or earlier). If your database installer runs as a standalone application or command-line utility, you can either prompt the user to enter the current version or pass the version to the installer as a command-line parameter. Dbsetup gets the current version string from a command-line parameter passed using the /ver switch.

Database creation scripts must always reflect the most recent version of the database. Whenever the installer creates the database, instead of installing an older version and then going through an upgrade, it should build the most recent version.

Standardizing Database Files and Folders

Because database projects have so many similarities, it does not make sense to rewrite the setup script or application for each database. In order to have your installer handle different databases, it must be able to find the database files, understand the order in which to execute them, and determine which operation to perform on each file. The only real way to achieve this is to standardize the database folder hierarchy, the order of the script execution, the file names, and the extensions.

Figure 3** Folders **

You should logically group all scripts used to create, upgrade, or repair databases. This will allow the installer to easily find the right scripts for the right operations. Although you can implement an alternative hierarchy, I suggest using a folder structure similar to the one shown in Figure 3, which is what dbsetup expects to find. Figure 4 explains the purpose of each folder and subfolder.

Figure 4 Purpose of Each Setup Folder

Both the relative paths to the setup folders and the order in which the folders are processed are hardcoded in the global variables defined in the beginning of the script. If you think that some of these setup folders are unnecessary, just remove them from the corresponding global variables. Creating empty folders matching the script definition will also work. If the script does not find the expected setup folders, it will print an error message but will continue running regardless.

Processing Database Files

Along with the standard folder hierarchy that allows the installer to map database setup operations to folders, the installer must also know the order in which to execute the scripts within each folder. However, the order of script execution within a folder is not always important. For example, stored procedures can usually be compiled in any order. If a particular stored procedure is dependent on another stored procedure but compiled before it, SQL Server will issue a warning message, but the compilation will still succeed. If the order of script execution within a folder is important, the most straightforward approach would be to add a text file, like the one used by dbsetup (files.txt), that lists the names of the script files in the order they should be processed.

If the installer detects this file in a folder, it will process all files listed in it and ignore all other files. If the installer does not find the files.txt file, it will assume that the order of execution is not important and process all files with supported extensions located in the folder; otherwise, it will process the files in the same order they are listed in the text file.

All files must be specified using relative paths to the immediate folder. Usually, this would be just the file name, but files from the other folders can be referenced as well. For example, if a script that populates a table uses a stored procedure to insert the data, this stored procedure can be compiled as part of the data population step since by default stored procedures are compiled after tables are populated. In this case, the stored procedure file can be referenced using a path relative to the Data folder, such as:

..\..\Common\Procedures\AddUserAttribute.sql PopulateUserAttribute.sql

In addition to the execution order, the installer must know what type of operation to perform on a particular file. For example, scripts containing SQL statements should be processed differently from the files holding bulk copy data or executable programs. File extensions can help to address this requirement. Figure 5 specifies how dbsetup translates file extensions into operations.

Figure 5 dbsetup Translates File Extensions into Operations

Mapping file types to setup operations is rather straightforward. In the case of the files that contain T-SQL statements, such as the files that create tables or stored procedures, the installer reads the commands until it finds the GO statement and executes these commands one batch at a time via the ADO Command object. The same outcome could be achieved by executing T-SQL scripts using the command-line tool isql.exe, but performance will be slow. Using isql.exe will make the installation sluggish because this utility will have to establish a connection to the SQL Server database before executing every script and then close the connection after the operation is complete. For smaller or more simple databases, isql.exe will probably suffice, but for larger and more complex databases that come with many scripts, executing SQL statements using ADO is a much better option.

Executable programs, batch scripts, and Windows Script Host (WSH) files are not likely to be used by most database installers, but I added support for them just in case. It's possible that you might need them in certain circumstances if, for example, your database upgrade required reencryption of sensitive data using a new encryption key. To allow this program to connect to the database server, dbsetup passes all command-line parameters to it, including the connection information and the user credentials. The invoked application is responsible for retrieving and processing these command-line parameters.

Bulk copy files are a bit more complex than other files because they can have a variety of processing options. The installer must know which bulk copy settings to use for which file. Some of the most common options the installer must understand are the format of character-based data, the handling of identity values, the mapping of data defined in the file to the table columns, and file-to-table mapping.

Dbsetup implements file-to-table mapping via the file naming conventions. To let the installer know which table corresponds to which bulk copy file, the file name must match the name of the corresponding table. For example, to populate the Employee table, you must name the bulk copy file "Employee.bcp" or "Employee.dat". This example assumes that all character-based data in the Employee.bcp file are provided in the ASCII format. To indicate other character formats, add an encoding indicator for that format at the beginning of the file extension. Dbsetup supports the following indicators: "u" for Unicode (as in Employee.ubcp), "n" for Native (as in Employee.nbcp), and "un" for Unicode Native (as in Employee.unbcp). To indicate that the identity values must be inserted into the table, the file extension must start with the letter "i" (as in Employee.ibcp or Employee.iunbcp).

If the bulk copy file requires an explicit mapping of the file data to the table columns, this mapping can be provided via a matching file with the .fmt extension (such as Employee.fmt), which must be generated via the BCP utility or BULK COPY command. With the exception of the maximum number of errors causing the operation failure, which dbsetup sets to 1 instead of the default 10, it uses the default settings for other bulk copy options. If your database requires a finer-grained handling of the bulk copy files, you can add your own options.

While naming requirements for the bulk copy files are rather strict, other database scripts don't have to follow any particular rules as long as they maintain the correct extensions. Having a naming standard can help you better organize the files and simplify their maintenance. I found the following conventions helpful.

SQL scripts responsible for building and populating tables and compiling stored procedures and UDFs should not handle more than one object (table creation script should not create more than one table). Otherwise, it will be hard to keep track of what is defined where. Scripts performing other operations, such as creating user-defined data types or building indexes, normally contain short SQL statements which can be grouped so that they can encapsulate all related operations in one file or just a few.

Although names of database scripts are not critically important, with the notable exception of the bulk copy files, giving the scripts meaningful names can prove to be handy. First, it will make them easier to maintain. Second, meaningful names can be used by the installer to provide better feedback. This is why scripts responsible for building and populating tables and compiling stored procedures and UDFs should be named after the metadata objects they are handling.

Implementing Database Upgrades

Allowing the installer to support upgrades from different versions may sound like a complex requirement, but it is really quite simple. The idea is to let the installer choose the right folder containing the database upgrade scripts and then to execute these scripts in the right order.

When you release version 1.0 of your application, your database script folder hierarchy should reflect the structure illustrated in Figure 3 (without the Upgrade folders). When you are ready to release version 1.1 of the database, you will first need to modify all affected database files in the database creation folders so that they always reflect the latest version. This will take care of the new database installation. To handle the upgrade from version 1.0 to version 1.1 on systems where the older database version is installed, add a new folder to the Upgrade directory and name this folder after the previous version, such as "1.0". This folder will hold the upgrade scripts from version 1.0 to 1.1. The upgrade folders must always be named after the version from which the upgrade is performed.

It will get a bit more complicated when you move to version 1.2 because by then your installer must be able to perform two types of upgrades: from version 1.0 to 1.2 and from version 1.1 to 1.2. As in the previous example, you will need to add a new upgrade folder ("1.1") for the upgrade scripts from version 1.1 to 1.2.

There are two options for the upgrade scripts from version 1.0. One possibility is to copy all upgrade scripts from version 1.1 to the 1.0 folder and add them to the script execution order, so that after the installer executes the upgrade from version 1.0, the database will be at version 1.2. The disadvantage is that you will have duplicate scripts in all upgrade folders, so by the time you get to version 1.9, your 1.0 upgrade folder will include all the scripts from the other eight folders (namely, 1.1 to 1.8). This may not be an issue if you use a source control system, which allows sharing files between the folders, but if it becomes too messy, as it likely will, you can try an alternative approach. Instead of converting the database from any older version to the current version in one step, the installer can perform incremental upgrades, such as from version 1.0 to 1.1, then from 1.1 to 1.2, and so on until the current version is reached, and this is exactly how dbsetup works.

Dbsetup assumes that every upgrade folder includes a text file (called "next.txt") that contains the version to which the database is being upgraded. Before executing the first script in an upgrade folder, dbsetup reads the version string from the next.txt file and compares it with the current version of the setup. If both versions match, or if the version coming from the text file is more recent than the version specified by the setup, the installer assumes that this is the last upgrade step; otherwise, after upgrading to this version, dbsetup will switch to the upgrade folder matching the new but still out-of-date version and repeat the same steps until it gets to the current version.

Dbsetup also verifies that the next upgrade version is newer than the previous upgrade version to avoid infinite loop conditions. After completing an upgrade to each new version, dbsetup recompiles the GetVersion function to return the new version string (just in case setup encounters an error and aborts during the next upgrade step). The installer performs each upgrade step within a transaction, but this behavior can be overridden. To see how this functionality is implemented, take a look at the UpgradeDatabase function in the download.

Repairing Databases

There may be times when you hand your database installer to a testing team or production engineers only to find out later that there is a bug that has to be fixed. If your database version does not track build or revision numbers (and I don't think it should because it will make the upgrade folder hierarchy hard to maintain), upgrading the database will not make sense. In this case, you must apply a repair script or hotfix. Database repair scripts are executed only when the installer detects that the version of the database is up to date. They should not be called during initial database creation or upgrades from a previous version because all changes made by the repair scripts must be duplicated in the creation and upgrade scripts as well. Since repair scripts can be applied repeatedly, they should support redundancy. In the other words, each repair script should not fail or cause metadata or data corruption if it is executed more than once.

Along the same lines, remember that you should always perform a database backup before doing an installation, just in case something goes wrong. In fact, you might consider modifying your setup scripts to perform backups automatically, though in the case of a large database that operation might best be left to the administrator to handle separately.

Performing Common Operations

As you may have noticed, stored procedures and UDFs are always recompiled. Actually, you don't have to recompile all of them during upgrades or repairs, but instead explicitly define the procedures which have been changed from the previous release. However, this will introduce a potential for error, so why make life more complex than necessary? Since stored procedures and UDFs don't affect data, you can recompile them as many times as you want without any negative effects. Be sure to remember to get the latest versions of the source files from the source control system before performing the build. In order to recompile all stored procedures in a folder without running the setup, you should try the isqlexec.bat script which accompanies this article.

Defensive Programming

It is a good idea to implement SQL scripts in such a manner that they can be executed repeatedly. For example, a script creating a stored procedure should first check to see if that stored procedure already exists and, if it does, should drop the stored procedure before creating it. Table creation scripts shouldn't do anything if the tables they try to create are already defined. In order to avoid duplicate entries, table population scripts (with the exception of bulk insert scripts) should verify the existence of records before inserting them into tables.

Query Analyzer provides a number of templates to build stored procedures and other metadata objects. Just select the File | New menu option (which you shouldn't confuse with the New Query toolbar button that has the same icon), and you will see all of the available template categories. Each template category normally offers several versions of a template.

A robust setup program must roll back changes if an error occurs during installation. The same rule should be applied to the database installers so that if the installer encounters an error during initial database installation, it aborts the setup and drops the database, which is how dbsetup works. Errors generated during upgrades have a potential to cause more damage than original installation errors because they can leave the database in an inconsistent state. If an error occurs during database upgrade, the installer cannot simply drop the database. Doing so will result in the loss of data since the database was already there before the installer ran. An ideal option would be to roll back all operations performed during upgrade before the error occurred, but it will require all upgrade operations to run within a transaction. Depending on the number of upgrade steps and affected data, this may cause too much overhead. A less robust yet reasonably acceptable alternative would be to back up the database before the upgrade (which should be done anyway) and if the upgrade fails, simply restore the database from the backup. Because database upgrade errors mostly come as a result of programming errors and should be caught during testing, this option works in most cases. By default, dbsetup uses transactional upgrades, but this behavior can be overridden by the /notran command-line option.

A Real-Life Example

To illustrate the use of the database installation techniques I just described, let me explain how I used them to implement a setup program for one of the enterprise applications my team currently supports. The installer handles several applications which are parts of a single product. These applications include twelve Windows services, an ISAPI filter, three Web sites, seven COM objects, four GUI tools, a number of miscellaneous components such as shared DLLs, and four databases. The largest database consists of approximately 70 tables and about 400 stored procedures. Because of the size of the product and complexity of the previous installation methods, the main goal of the new installer was to simplify the process, making it fast and foolproof. The database installation procedure follows the same steps as the dbsetup, but unlike the latter, it is implemented as part of the larger setup program written in InstallShield Professional 6.x.

Now let's look at how the installer works. First, the user specifies which database to install by selecting a corresponding checkbox in the component tree (or the "feature" tree in MSI lingo) of the custom setup dialog. When setup detects that a database must be installed, it copies all database scripts to a temporary directory, creating a folder hierarchy similar to the one I described before. The installer uses a separate directory to hold files for each selected database. Because the database scripts are only needed during the setup process, the installer deletes them from the temporary folder at the end of the setup.

After the database files are copied to a temporary folder, the setup triggers an installation-specific event defined by the setup for this database. When the event is raised, it calls an event handler implemented in InstallScript. The script contains eight database-specific event handlers: one to install and one to uninstall each of the four databases. All installation events defined for each of the four databases follow the same logic. The only difference is that they use distinct root folders containing database scripts as the starting points. When a database installation event handler is called, it first checks global variables to see if InstallScript already has a database connection. If not, InstallScript prompts the user to specify the database server name and SQL administrator's credentials. If the script fails to connect to SQL Server using the specified credentials and connection information, it prompts the user to re-enter them. Upon successful connection, InstallScript saves the connection object in a global variable in case another database is selected for a setup operation.

After connecting to the database server, the installer checks to see if the specified database exists. If not, it prompts the user to select the location of the database data and transaction log files and creates the database device using reasonable default values for other database parameters, such as database growth. I decided not to prompt the user for anything other than the location of the database files because other database settings can be easily modified by the database administrator after the database is installed and may need to be adjusted in the future anyway. The installer gives the user an option to leave databases intact during uninstallation or drop them from the system. When performing the installation, upgrade, or repair, the setup program follows the same steps that I described before.

Certain installation steps, such as compiling 400 stored procedures, can take a while. To inform the user about the setup progress, the installer displays a status message for each performed operation, such as "Compiling stored procedure GetUserList..." Naming the SQL scripts after the metadata objects they create allowed me to display correct information in the feedback messages. If an error occurs during execution of SQL scripts, the installer displays a dialog box with a detailed error message, a snippet of the failed T-SQL batch, the name of the file, and the line on which the error occurred. Any error that occurs during SQL installation will cause the setup process to abort.

One operation I did not mention previously, which this installer has to perform, is registering an extended stored procedure. This operation is trickier than other setup steps because it requires coordination between the installer and the registration script. Let's look now at how extended stored procedure registration works.

First of all, a DLL implementing the extended stored procedure must be copied to the SQL Server host. The registration itself can be executed in a static SQL script via the sp_addextendedproc stored procedure, but the main problem with this method is that the T-SQL statement calling sp_addextendedproc must specify the full path to the DLL file, which can change depending on the environment in which the database is installed (database developers normally do not know where the application files will go in the test, pre-production, or production environments). To overcome this problem, instead of specifying the full path to the DLL file, I referenced the script by name, as shown here:

exec sp_addextendedproc 'xp_myproc', 'XPMyProc.dll'

If the DLL file does not include the full path information, SQL Server assumes that it is located in the BINN folder of the SQL Server program directory. The next step is to put this file there; this is the task for InstallShield. I associated the extended stored procedure DLL with the database components using a special file group. Unlike the regular database file group containing setup scripts which are copied to a temporary folder and removed at the end of the setup, the extended stored procedure file group does not get deleted. The setup program maps this file group to the BINN subfolder of a directory, the location of which is determined at installation time using the value of SQLPath under the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup. This key holds the path to the root folder of the SQL Server program files. Because the ability to install extended stored procedures relies on the installation technology, I did not bother to implement it in dbsetup.

Basing the installer on the older InstallShield technology provided several important advantages. First of all, because InstallScript offers all the features necessary to perform operations relevant to database installation, such as calling COM objects, getting error information, prompting the user for the feedback, and displaying Select File or Folder dialogs, I was able to implement all database installation operations in one InstallShield project and did not need to maintain any additional setup files or helper applications. Being able to control the installation process from a single source was another advantage. Having all operations defined in a couple of InstallScript files within the same project, where all functions and event handlers can easily access global variables and installation settings, helped to reduce the programming effort. This also made for a better user experience. Because the installer provides feedback to the user about the process in the same setup status dialog box, the installation process appears more seamless than it would if the installer was designed to launch some operations in annoying pop-up windows.

Another advantage of InstallShield setup is the ease of upgrading. The installer uses the same product code, allowing all upgrades to be performed without the user having to uninstall and reinstall the application. It does not require the user to remember any command-line parameters to launch the setup in the upgrade or repair mode. After the user double-clicks the setup.exe file, InstallShield figures out the mode it needs to run in and does not confuse the user with error messages complaining that the product is already installed.

Conclusion

Although the general approach that I have described and the dbsetup sample that accompanies this article should be quite sufficient for most database installations, there are a few limitations that you should be aware of, one of which is the inability to create multiple data or transaction log files during database creation. Only default settings for database creation options are supported, such as the maximum size, growth for data or transaction log files, and bulk copy operations.

Also, there is no XML support, which is unfortunate since XML is likely to gain more popularity with the release of SQL Server 2005. Finally, a person performing setup must make sure that no applications or users are connected to the database during setup. These limitations should not be hard to address, however.

If you want to avoid dealing with database setup problems, consider implementing a generic database installer. Eliminating manual operations from the database setup procedure will help you avoid errors and improve productivity. If your database requires an unorthodox approach, you may need to spend a little more time implementing the setup program, but the good news is that if you do it right once, you will be able to reuse it in other projects.

Alek Davis is a senior application developer at Intel Corporation in Folsom, CA. Alek holds an M.S. in Software Engineering from California State University, Sacramento. Reach him at alek.davis@intel.com.