Configuration

The upgrade files are required to be located in a single directory. The directory must contain:

config.xml	The configuration file.
<control file>.xml	The script control file
*.sql	The SQL scripts to be applied.

An example configuration directory, including a sample control file is included with the zip file.

The Database Upgrade Manager is intended to support two scenarios:

a) The Database Upgrade Manager executable, configuration settings and scripts are sent out as a single package (this can either be run directly, eg from a CD or wrapped into an installer program). In this scenario, the configuration settings and scripts should be located in a directory "config" under the directory of the executable.

This is the default setting and requires no command line arguments. Unless the /p command line argument is specified, the application will check for a "config" directory below the application directory on startup. If the config directory exists and the config.xml file is found, the config.xml file will be automatically loaded and display the Pre-Install confirmation screen.

b) In the second scenario, the Database Upgrade Manager is installed on the client's PC and only the configuration settings and scripts are sent out for the upgrade. The client can then run the pre-installed application and select the directory containing the upgrade files. The easiest implementation for this scenario is not to create a "config" directory from the application directory, however the /p (prompt) command line argument can be used to force the user to be prompted for a directory.

Configuration File

The configuration file contains information about the product being updated to allow customisation. The configuration file must be called config.xml

Application Name	Your product name.
Upgrade Version	The version that the product is being upgraded to.
Default Database Name	The default name of the SQL Server database to be upgraded.
Connection Timeout	The timeout for connecting to the database. This should generally be set to a short amount of time (eg: 3 or 4 seconds) If not specified, this defaults to 4 seconds.
Command Timeout	The timeout for each batch command. If not specified, this defaults to 6000 seconds.
Script Control File	The file name of the xml control file containing the scripts to be run.
Pre-Install Text	User defined text to be displayed at the start of the installation. It should recommend that the database is backed up prior to commencing the upgrade and should be worded to suit Exit/OK buttons.

Control File

The control file has three sections:

Transaction Block	A transaction block is a wrapper around scripts that should be performed within the same database transaction. If any script within a transaction block fails, the entire transaction block will be rolled back. The transaction block contains a flag <AbortFailsPostProcess> to indicate whether the failure of a script in the transaction block should prevent the Post Process being run.
Script	A script is an SQL script that is to be run against the database. The script can contain multiple batches (separated with the "go" command). Each script can be in clear text or encrypted (encryption is not available in the free version - it is intended only to keep honest people honest in any case - a user with appropriate privileges can extract clear text stored procedures from the database). The Command Type can currently only be "SQL". Future implementations will include support for stored procedures and parameters. The Abort on Error flag indicates whether the remainder of the processing should be carried out if the script fails. - if set to False then the current Transaction Block is rolled back but processing will continue from the next transaction block. - if set to True, the current Transaction Block is rolled back and no further processing will be carried out.
Post Process	As a simple method of tracking whether an upgrade has been applied, the Post Process option was added in version 1.2. This section contains a single script that is only executed if all scripts in transaction blocks where the Abort Fails Post Process flag is set were successfully processed. Refer to *Using the Post Process* below for a suggested implementation.

The the xsd for the Control File is included in the installation.

Using the Post Process

The intention of the Post Process was to use a table similar to the following which is populated with a version number (note that here I've used a character version number, which makes the next bit easy, but for the next check to work the version number will need to be zero padded - eg: 01.01.01. You should also add a unique key constraint on the product code)

CREATE TABLE [dbo].[MyProductVersion] (
   [ProductCode] [Varchar](20) NOT NULL,
   [Description] [varchar](200) NULL,
   [Version] [varchar](20) NOT NULL )

Create the first transaction block as:

and the first script that is processed can then do something like:
if exists (select productcode from MyProductVersion where productcode = 'MYCODE' and version > '01.01.01') raiserror('Database already upgraded!', 11, 1)

The Post Process will only be run if ALL scripts in the control file executed successfully, so the in the post process you can then update the version number if everything worked successfully.

with a script similar to (with appropriate transaction handling):
if exists (select ProductCode from dbo.MyProductVersion where ProductCode = 'MYCODE')
delete from dbo.MyProductVersion where ProductCode = 'MYCODE'
insert into dbo.MyProductVersion (ProductCode, Description, Version)
values ('MYCODE', 'My Product Description', '01.02.00')