Administering SQL Server Release Control
Author Nigel Rivett
4.4 User Acceptance Test (UAT)
6.3 Stored Procedures, Views, Functions
Scripts
7.1.1 Script concatenation file
7.1.2 Creating the script concatenation
files
7.2 Performing the release to
Integration test
7.3 Performing the release to System
test
7.4 Performing the release to UAT
7.5 Performing the release to Release
Test
7.6 Performing the release to Live
8 Detecting Uncontrolled Changes
9 Appendix 1 – Release Document
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.
The aim is
to provide a method of release control which will provide the following:
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
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.
The
release control system should define the state of the live system enabling
uncontrolled patches to be detected and objects recreated from scratch.
The
release control system should provide a history of all changes that have been
made to the controlled systems.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
Similar
to Tables.
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
DTS
packages should be saved as files and held in SourceSafe
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.
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.
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.
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.
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.
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.
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.
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.
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.
Resist the
temptation to make this document too general. That will make it unwieldy and
too many inapplicable fields will mask the applicable ones.
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
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
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
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 |
|
|