home

Administering SQL Server Release Control

Author Nigel Rivett

 

1      Overview.. 2

2        Objectives  2

2.1       Safe Releases  2

2.2            Usable Method. 2

2.3            System Documentation  2

2.4            Audit Trail 2

3      Use of Source Control 2

4        Environments  3

4.1            Development 3

4.2            Integration Test 3

4.3            System test 3

4.4       User Acceptance Test (UAT) 3

4.5            Release test 3

5      Setting Up SourceSafe. 3

6      Script Maintenance. 4

6.1            Database Scripts. 4

6.2            Table Scripts  5

6.3            Stored Procedures, Views, Functions Scripts  5

6.4       Data Scripts  5

6.5       DTS Packages. 6

7      Releasing the version. 6

7.1            Creating the scripts  6

7.1.1                Script concatenation file  6

7.1.2                Creating the script concatenation files  7

7.2            Performing the release to Integration test 10

7.3            Performing the release to System test 10

7.4            Performing the release to UAT  11

7.5            Performing the release to Release Test 11

7.6            Performing the release to Live  11

8      Detecting Uncontrolled Changes. 12

9      Appendix 1 – Release Document 13

9.1            Information required  13

9.1.1                Pre-release information  13

9.1.2                Release instructions  13

9.1.3                Post-release procedures  13

9.2            Sample Release Document 14

 


1         Overview

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]

GO

 

Create table MyTable

      (

      id          int not null ,

      Description      varchar(50) not null

      )

go

 

Create unique index ix_MyTable_01 on MyTable

      (id)

go

 

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]

GO

 

create procedure updtblMyTable

@id ,

@col1 int ,

@col2 int

as

 

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

end

else

begin

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

end

 

go

 

exec updtblMyTable 1, 'a', 'b'

exec updtblMyTable 2, 'a', 'b'

...

 

drop procedure updtblMyTable

go

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)

      )

go

 

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)

 

as

/*

-- 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''')

 

select @file = ' MyDatabase_Data'

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

*/

 

set nocount on

 

declare @s       varchar(1000) ,

      @ID         int ,

      @Maxid            int ,

      @EntryType      varchar(100) ,

      @Dir        varchar(100)

 

if right(@SourceDir,1) <> '\'

      set @SourceDir = @SourceDir + '\'

if right(@DestDir,1) <> '\'

      set @DestDir = @DestDir + '\'

 

      create table #a

            (

            id          int identity (1,1),

            Name       varchar(128) ,

            EntryType      varchar(100) ,

            Directory      varchar(100) ,

            )

           

      insert       #a

            (

            name ,

            EntryType ,

            Directory

            )

      select       name ,

            EntryType ,

            Directory

      from       Release0001

      order by

            case EntryType

                  when 'Table' then 1

                  when 'Proc' then 2

                  when 'Data' then 3

                  when 'TestData' then 4

            end ,

            Sequence

     

      select       @id = 0,

            @maxid = max(id) ,

            @EntryType = '' ,

            @Dir = ''

      from       #a

     

      while @id < @maxid

      begin

            select @id = min(id) from #a where id > @id

           

            if @Dir <> (select @SourceDir + Directory from #a where id = @id)

            begin

                  select @Dir = @SourceDir + Directory from #a where id = @id

                  select @s = 'cd ' + @Dir

                  print ''

                  print @s

                  print ''

            end

           

            if @EntryType <> (select EntryType from #a where id = @id)

            begin

                 

                  select @EntryType = EntryType from #a where id = @id

 

                  set @s = 'set fdest=' + @DestDir + 'ReleaseScripts\' + @DBName + '_' + @EntryType + '.sql"'

                  print @s

                  print ''

                 

                  select @s = 'ECHO print ''' + @EntryType + ''' >> "%fdest%"'

                  print ''

                  print @s

                  print ''

                 

                  set @s = 'ECHO use ' + @DBName + ' > "%fdest%"'

                  print @s

                  print 'set fname='

                  print ''

            end

           

            select      @s = 'set fname=' + name + '.sql' from #a where id = @id

            print @s

           

            print '           ECHO. >> "%fdest%"'

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

            print '           TYPE "%fname%" >> "%fdest%"'

      end

      drop table #a

go

 

Ask the developers if all changes to be made are complete in SourceSafe and if everything currently in SourceSafe is to be released before creating the scripts.

7.2      Performing the release to Integration test

This is usually a fairly uncontrolled environment as it is often the first time that created modules have been run together in an environment anything like live. Be sure to test across servers and through firewalls at this stage. Even though a feasibility study should have been conducted developers may have introduced code without realising the consequences.

7.3      Performing the release to System test

Create a folder in SourceSafe Vss\MyProject\Release\SysTest\R001

Copy all the scripts to be released into this directory.

Create a document which will contain the instructions for executing the release scripts and any checks that can be made.

For an initial release this will probably be quite a simple document. For further releases it will contain a description of the changes, any preparation (departments to be informed, systems to be taken down, backups to be taken,...), instructions for executing the release, means of testing the result and the method of reversing the release (usually restoring the backup).

 

The release now should be fairly simple to execute.

As this is the system test environment all code released should be implemented via release scripts in SourceSafe. There will be times where the implemented code does not work correctly and this can be corrected (after extracting suitable contrition from the responsible party) by adding another directory (R002) containing the updated modules. At some point though all the scripts should be regenerated and compared against the previous to make sure the changes have been applied.

7.4      Performing the release to UAT

Create a folder in SourceSafe Vss\MyProject\Release\UAT\R001

The release to UAT is the same as the release to system test except that this is more formal as there are not expected to be any problems with the system. It is for users to check that they are getting what they expect and to carry out user training.

7.5      Performing the release to Release Test

This is to test the release procedures for the live release. As such it should be executed close to the live date - usually a day or two before. It should be carried out by someone responsible for the live system - usually a dba.

Create a folder in SourceSafe Vss\MyProject\Release\Live\R001

Note that this is the live release folder as these are the scripts that will be run on live.

An up to date copy of the live system should be placed in the Release Test environment and the scripts run.

This is also a test of the release documentation so this should be followed.

After the confidence tests have been run the release backout procedures should be run to test that they also work. If it is a simple restore then there should be no need to test this as it should be part of the normal live procedures.

The scripts should also be checked by the DBA. Compare them for changes against the previous scripts and make sure that any changes are mentioned in the release documentation. This is also the time when you will notice any poor or incorrect code and can refuse to carry out the release until it is corrected. If this happens a few times you will find yourself being included in projects at an earlier stage and your job will be a lot easier.

7.6      Performing the release to Live

The script will already be in SourceSafe from the release test - but it is worth checking that nothing has been updated.

It should be copied to a live folder as a record of what has been released.

Some companies like the release document to be a physical document and signed by various departments. This is a good thing as it makes sure that everyone has stated that they know what is happening and agree with the timing. Obtaining the signatures can be a time consuming task though and should be delegated. If this process is required it should be taken seriously and the release should not go ahead without the correct documentation.

Check the modules being released against the previous versions to make sure that nothing is being released that is outside the stated scope. This is especially important if several changes are in UAT and only some are to be released. This is also a last chance to monitor the code for dubious practices – always be ready to reject a release or at least get an agreement (documented) that dangerous code will be replaced within a timescale or the system will be considered not part of the production environment

The release itself should come as an anti-climax as everything has already been tested and should be a matter of following the release instructions - make sure these are followed. I always print them off and mark items as in process and completed even if this is not required.

8         Detecting Uncontrolled Changes

These will always get through – maybe just by emergency changes made at 2:00 in the morning to keep the system running.

Create a job which scripts all objects in all databases and keep this in SourceSafe. It should be inspected regularly to check for changes. If these changes are not documented (e.g. changes that you have made and forgotten about) then they should be followed up.

See

www.nigelrivett.net\DMOScriptAllDatabases.html

Don’t try to conceal changes even if they are to cover up your mistakes. Always own up to mistakes, only people who are out of their depth try to cover things up. It always causes problems and concealing information does a disservice to your colleagues.


 

9         Appendix 1 – Release Document

Resist the temptation to make this document too general. That will make it unwieldy and too many inapplicable fields will mask the applicable ones.

 

9.1      Information required

9.1.1      Pre-release information

Environment

Project

Release identifier

Release owner

People that need to agree the release can go ahead

People that need to be informed that the release is occurring

Date and time at which the release should be performed

9.1.2      Release instructions

This should be in the form of a checklist. The executer of the release should mark when an item has started and when completed and be able to add comments

 

Obtain agreement that the release can go ahead

This should be from the release owner. Depending on the environment it may be pre-authorised or it may be an on-the-spot go ahead.

Stop any affected systems

Take any required backups

Release the modules

Take any backups required

Perform the release confidence tests

Restart systems

If any failures perform the release backout procedure

9.1.3      Post-release procedures

Notify the release owner of the result of the release (this may include notifying all persons named on the release document).

Complete the release document and file with all associated documentation


9.2      Sample Release Document

 

Release Document

 

Project

 

Environment

 

Release

 

Description

 

 

 

 

 

 

 

Release owner

 

 

Signoff

Name

Signature

Date

Mr. DeptManager1

 

 

Mr. DeptManager2

 

 

Mr. User1

 

 

Mr. User2

 

 

Mr. ProdManager

 

 

 

Notification

Name

Signature

Date

Mr. DeptManager1

 

 

Mr. DeptManager2

 

 

Mr. User1

 

 

Mr. User2

 

 

Mr. ProdManager

 

 

 


Release Procedure

 

Release administrator name

 

 

 

Complete

Comments

Obtain agreement from release owner

 

 

Release start date/time

 

 

Stop systems

 

Complete

Comments

Take down web site mywebsite

 

 

Remove user access to mysvr2

 

 

Stop scheduler on mysvr1

 

 

Take backups

 

Complete

Comments

Backup server mysvr1.mydb1

 

 

Backup server mysvr1.mydb2

 

 

Backup server mysvr2.mydb1

 

 

Take copy of DTS packages in \\mysvr1\dtsdir\

 

 

Copy ASP code from \\mysvr2\website\

 

 

Release modules

 

Complete

Comments

Copy DTS packages from \\relsvr\reldir\relxxx\mysvr1\dtsdir\ to \\mysvr1\dtsdir\

 

 

Execute script \\relsvr\reldir\relxxx\mysvr1\mydb1\releasescripts\ReleaseScript.sql

 

 

Execute script \\relsvr\reldir\relxxx\mysvr1\mydb2\releasescripts\ ReleaseScript.sql

 

 

Copy ASP code from \\relsvr\reldir\relxxx\mydb2\releasescripts\

 

 

Take backups

 

Complete

Comments

Backup server mysvr1.mydb1

 

 

Backup server mysvr1.mydb2

 

 

Backup server mysvr2.mydb1

 

 

Take copy of DTS packages in \\mysvr1\dtsdir\

 

 

Copy ASP code from \\mysvr2\website\

 

 

Test Release

 

Complete

Comments

Run script \\relsvr\reldir\relxxx\mydb2\releasescripts\Test.sql

Check for no errors

 

 

Start web site in admin mode – run tests defined in test doc

 

 

Run scheduled job \\mysvr1\DownloadData

Check for job success – should take about 5 mins to run

Run script \\relsvr\reldir\relxxx\mydb2\releasescripts\TestJob.sql

Should return about 100 rows

 

 

Enable system

 

Complete

Comments

Start scheduler on mysvr1

 

 

Enable user access to mysvr2

 

 

Start web site mywebsite

 

 

 

 

Complete

Comments

Release Complete date/time

 

 

Notify release owner

 

 

 

home