This document describes a method of administering release control for a project. It encompasses source control of code and methods of release to the various environments.

2 Objectives

The aim is to provide a method of release control which will provide the following:

2.1 Safe Releases

Ensure that the code released is the same as that which has been tested.

Ensure that the changes released are expected i.e. the planned and only the planned fixes/functionality are included in the release.

Ensure that the necessary departments/personel are informed and agree that the release should take place.

The release has a backout procedure if problems are discovered

2.2 Usable Method

The release control system should aid in the control of a project and not hinder work.

It should be simple to administer and not take up too much of any ones time.

It should incorporate the possibility of quick but still reliable releases for small urgent fixes.

2.3 System Documentation

The release control system should define the state of the live system enabling uncontrolled patches to be detected and objects recreated from scratch.

2.4 Audit Trail

The release control system should provide a history of all changes that have been made to the controlled systems.

3 Use of Source Control

This document refers to SourceSafe but any source control method can be used.

Everything that is to be released should be kept in SourceSafe.

SourceSafe is also used give the following information.

A developer is working on a module.

A module is available for release.

The current state of the production system.

The current state of any test systems.

The data in SourceSafe should be considered as the master version of the source.

If it is necessary to extract live source code then the release control should be considered to have failed.

It is possible to reference the various environments via labels but I prefer to copy all the modules to a separate folder. Resist the temptation to branch in SourceSafe as this will cause headaches when it comes time to merge, it is simpler to control this manually by creating separate copies with suitable names.

4 Environments

4.1 Development

You will of course need a development environment.

I would advise each database developer has a local version of sql server on their own workstations.

This will enable them to test effects of different database/server properties, performance and to allow them to crash a server without affecting others.

A central development environment available to all developers is also necessary - this enables test data to be created and updated with releases. This environment is uncontrolled in that developers can change it at will - but agreement is needed before making changes that affect structure and common routines. This environment should be refreshed from time to time to get rid of test data.

4.2 Integration Test

This environment fulfils a similar function to the system test but in a less controlled manner and I prefer to think of it as the same environment. It is sometimes used as a halfway house between development and system test but still under control of the development team - in which case it can be considered part of the development environment.

4.3 System test

This environment is controlled in that all changes should be applied via formal release procedures. Developers should not have update access to it (in their development role). It would usually be a separate physical system to the development environment as the two will usually co-exist.

4.4 User Acceptance Test (UAT)

This is often this same physical system as the system test environment. Whether this is feasible depends on the release cycle structure. Note that while the system is being used for UAT no system testing can be done (and hence theoretically no code can be released).

This would mean that no bugs found from UAT can be fixed without regressing to the system test environment - in practice minor fixes can be released and tested to the UAT environment.

4.5 Release test

This environment is to test the release procedure. For a new system it should be created from scratch. For an update to an existing system it should be a copy of that system.

5 Setting Up SourceSafe

Create a local directory Vss

Set the working folder of the root directory in SourceSafe to the local Vss directory.

(This should automatically map all other working folders to the relative directory)

Create a subdirectory of Vss for the project

MyProject.

Create the following directory structure

VSS

MyProject

Databases

MyDatabase

Create

Data

Procs

ReleaseScripts

Tables

Updates

TestData

DTSPackages

This should give all the entities involved in the project.

The Tables folder will hold the current state of the tables and Tables\Updates holds the update / create scripts to get to that state.

For each release create a folder with a similar structure which just holds the modules for the release. Developers should not have write access to this folder. If necessary create a dev release folder which gets copied to the release folder when complete.

6 Script Maintenance

All scripts should be created and maintained using query analyser.

For a developer this means

Check the script out of SourceSafe (to a network directory that is backed up)

Load the script into query analyser

Change the script

Save the script

Run the script

Test the result

Check back into SourceSafe when complete.

This should not add much overhead to the development time and means that a server/workstation crash should never lose any work.

It will ensure that each developer is always working on the latest version of each script and that two developers never accidentally change the same code.

6.1 Database Scripts

The database creation script should be named Vss\MyProject\Databases\MyDatabase\Create\CreateDatabaseDev.sql

It will normally look something like

create database MyDatabasen

exec sp_dboption 'MyDatabasen', 'select into/bulkcopy', 'true'

exec sp_dboption 'MyDatabasen', 'trunc. log on chkpt.', 'true'

(I normally create all dev databases in simple recovery mode and set the model database to that on the server).

6.2 Table Scripts

The table creation scripts are named Vss\MyProject\Databases\MyDatabase\Tables\MyTable.sql

They are of the form

if exists (select * from sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[MyTable]

Create table MyTable

(

id int not null ,

Description varchar(50) not null

)

Create unique index ix_MyTable_01 on MyTable

(id)

It is up to your preference whether the indexes/constraints are held in the same script as the table to which they refer.

6.3 Stored Procedures, Views, Functions Scripts

Similar to Tables.

6.4 Data Scripts

These scripts are for loading static data or any other data maintained manually.

The simplest form is

delete MyTable

insert MyTable (id, col1, col2) select 1, 'a', 'b'

insert MyTable (id, col1, col2) select 2, 'a', 'b'

...

If the scripts are to be used to maintain data and need to work with foreign keys / archive triggers then they could be of the form

if exists (select * from sysobjects where id = object_id(N'[dbo].[updtblMyTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[updtblMyTable]

create procedure updtblMyTable

@id ,

@col1 int ,

@col2 int

if exists (select * from MyTable where id = @id)

begin

if not exists (select * from MyTable where id = @id and col1 = @col1 and col2 = @col2)

begin

update MyTable set col1 = @col1, col2 = @col2 where id = @id

end

else

begin

insert MyTable (id, col1, col2) select @id, @col1, @col2

end

exec updtblMyTable 1, 'a', 'b'

exec updtblMyTable 2, 'a', 'b'

...

drop procedure updtblMyTable

6.5 DTS Packages

DTS packages should be saved as files and held in SourceSafe

7 Releasing the version

7.1 Creating the scripts

You will need to create script files for all the objects to be created.

I like to create separate files for tables, stored procedures, data, ... but these could be included in a single script if you prefer.

The files created will be

Mydatabase_createdatabase.sql

Mydatabase_tables.sql

Mydatabase_procs.sql

Mydatabase_data.sql

To do this create a .com file with instructions to concatenate the various scripts together into a single file.

7.1.1 Script concatenation file

Create a file Vss\MyProject\Databases\MyDatabase\CreateScripts\CreateScripts.bat

this file contains

set fname=

set fdest=d:\vss\myproject\Databases\mydatabase\ReleaseScripts\mydatabase_Tables.sql"

cd d:\vss\myproject\databases\mydatabase\Tables

echo use MyDatabase >> "%fdest%"

echo print 'Tables' > "%fdest%"

set fname=spMyTbl1.sql

echo. >> "%fdest%"

echo print 'processing file - %fname% ' >> "%fdest%"

type "%fname%" >> "%fdest%"

set fname=spMyTbl2.sql

echo. >> "%fdest%"

echo print 'processing file - %fname% ' >> "%fdest%"

type "%fname%" >> "%fdest%"

echo. >> "%fdest%"

set fdest=d:\vss\myproject\Databases\mydatabase\ReleaseScripts\mydatabase_Procs.sql"

cd d:\vss\myproject\databases\mydatabase\Procs

echo print 'Procs' > "%fdest%"

set fname=spMySp1.sql

echo. >> "%fdest%"

echo print 'processing file - %fname% ' >> "%fdest%"

type "%fname%" >> "%fdest%"

set fname=spMySp2.sql

echo. >> "%fdest%"

echo print 'processing file - %fname% ' >> "%fdest%"

type "%fname%" >> "%fdest%"

echo. >> "%fdest%"

Now when you double click on this file it will create the files

Vss\MyProject\Databases\MyDatabase\mydatabase_Tables.sql

Vss\MyProject\Databases\MyDatabase\mydatabase_Procs.sql

which will contain the script to create all the stored procedures.

It will also include the name of the SP script before it is run so you will know where any errors occur.

7.1.2 Creating the script concatenation files

The script concatenation files can be created via an sql script.

Create a table ReleaseControl

Create table ReleaseControl

(

EntryType varchar(100) ,

Directory varchar(100) ,

Sequence int ,

Name varchar(128)

)

Put into this table entries for all the SourceSafe files to be concatenated

EntryType Directory Sequence Name

Proc Procs 10 mysp1

Proc Procs 10 mysp2

Proc Procs 10 mysp3

Proc Procs 1 myspCommon

Table Tables 10 myTable1

Table Tables 10 myTable2

Data Data 10 insert_myTable1

Data Data 10 insert_myTable2

Now run this sp which will create the script concatenation files

Note the entries in comments at the top of the SP which will use osql to create the output files and also execute them via xp_cmdshell.

Otherwise just save the output from the SP.

Create procedure CreateScripts

@DBName varchar(128) ,

@DestDir varchar(128) ,

@SourceDir varchar(128)

-- run create script SP - save results to .bat file

exec CreateScripts

@DestDir = 'd:\vss\ElmcrestFunerals\Databases\Elmcrest' ,

@DBName = 'ElmcrestNew' ,

@SourceDir = 'd:\vss\elmcrestfunerals\databases\elmcrest'

-- run create script SP using osql - automatically saves results to .bat file

declare @sql varchar(1000)

select @sql = 'osql -Usa -Pjanice -w1000'

select @sql = @sql + ' -o"d:\vss\ElmcrestFunerals\Databases\Elmcrest\ReleaseScripts\CreateScript.bat"'

select @sql = @sql + ' -Q"exec ReleaseScripts..CreateScripts '

+ ' @DestDir = ''d:\vss\MyProject\Databases\MyDatabase'' , '

+ ' @DBName = ''MyDatabase'' , '

+ ' @SourceDir = ''d:\vss\ MyProject\Databases\MyDatabase ''"'

exec master..xp_cmdshell @sql

-- run the resulting file to create the scripts

exec master..xp_cmdshell 'd:\vss\ElmcrestFunerals\Databases\Elmcrest\ReleaseScripts\CreateScript.bat'

-- run the script files

declare @sql1 varchar(1000), @sql2 varchar(1000), @file varchar(128)

select @sql1 = 'exec master..xp_cmdshell ''osql -n -Usa -Pjanice -w1000'

select @sql1 = @sql1 + ' -o"d:\vss\ElmcrestFunerals\Databases\Elmcrest\ReleaseScripts\Output.txt"'

select @sql2 = ' -i"d:\vss\ElmcrestFunerals\Databases\Elmcrest\ReleaseScripts\'

select @file = 'MyDatabase_Tables'

exec (@sql1 + @file + '.txt' + @sql2 + @file + '.sql''')

select @file = 'MyDatabase_Procs'

exec (@sql1 + @file + '.txt' + @sql2 + @file + '.sql''')