Av rating:
Total votes: 18
Total comments: 4
Pop Rivett and the Uncontrolled Release
15 February 2007
Pop Rivett gazed reproachfully at Clarke Minor. 'I ended up teaching Needlework to the infants today dy' know, Clarke Minor. Dashed embarrassing for a Technology teacher, what, given that it is not really my subject'
Clarke Minor shuffled his feet and looked embarrassed.
'Do you know how it happened? Did it, by chance, have anything to do with you making changes to the live Timetable system, Clarke Minor?'
'Well, the Beak wanted a timetable change made in preparation for speech day, and I sort of, well, altered a stored procedure. It seemed a pretty harmless change'
'Harmless?' expostulated Pop Rivett, 'Dash it Clarke Minor, haven't I been able to drive the principles of revision control or release management into your thick skull?'
'Revision control?' Clarke Minor sensed a lecture was imminent. He gazed mournfully out of the window and saw the prospect of a splendid playtime in the fresh air fading away.
Pop's voice cut through his reverie.
'Yes, blast it! Revision control, man!' The full horror of the anarchic needlework session flooded back as he vented his irritation. 'Take a seat boy, it seems like we need to go back over some basics!'
He stepped over to the blackboard and in bold chalk letters…
Why revision control?
'When you're writing SQL Server applications, dash it, you have to make certain that the code released is the same as that which has been tested, and that everyone knows about the release and has agreed to it. And you need to be sure that no unplanned and untested fixes or functionality are made directly on the live system.'
Pop glowered significantly at Clarke Minor.
'Everything that is to be released should be kept in Revision control so that you have a complete record of all changes that have been made to the live system. The Revision Control System (RCS) is also used to show which developer is working on which particular module, which modules are available for release, the current state of the production system and the current state of any test systems.
The data in the RCS is the master version of the source. You can make sure that only the correct code goes into the live application. If a rogue developer does patch the live system, you’ll be able to find out exactly what they did, and roll the system back to the most recent version in the RCS. By the same token, if a planned releases gets ugly and you can't correct it, you can simply reverse out, back to the previous release.'
The development and testing environment
By this time, Pop was getting into his stride and had started to draw obtuse diagrams the blackboard. Clarke Minor groaned inwardly. Would he ever get outside for a game of cricket with the chaps?
'In your development environment, each database developer should have a local version of SQL Server on their own workstations. This will enable them to test effects of different database/server properties on performance and to allow them to crash a server without affecting others. Of course, there should also be a central database development environment or cell available to all, which will also allow 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 should be refreshed from time to time to get rid of test data.
The efforts of the developers are coordinated through the RCS, which will need to ensure that each developer is always working on the latest version of a script for a given object (procedure, table etc) and that two developers never accidentally change the same code. There must also be provision in your RCS for release of code to the various stages of testing.
In most cases, there will need to be provision for the following test environments, and the controlled release of code to these various test stages:
|
Integration Test |
Integration test 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. |
|
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. |
|
User Acceptance Test |
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. |
|
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." |
Clarke Minor gazed wistfully out through the leaded-pane window of the staff-room, to the sunlit playing-fields beyond, from which the occasional sound of cricket-bat on ball emanated. Alas, he was, instead, stuck like a wedding guest buttonholed by the ancient mariner. Except that this ancient mariner was going to tell him how to use SourceSafe….
Working in SourceSafe
'As you know, I use SourceSafe for release control' sniffed Pop 'It is possible to reference the various environments via labels but I prefer to copy all the modules for a particular environment to a separate, dedicated 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.'
This is the scheme I recommend in order to set up an effective RCS using SourceSafe, but other systems are very similar:
- On the development machine, create a directory called VSS (or similar)
- 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 e.g. MyProject.
- Create something like the following directory structure:
VSS
MyProject
Databases
MyDatabase
Create
Data
Procs
ReleaseScripts
Tables
Updates
TestData
DTSPackages
Note that each type of database entity is stored in a separate folder. The Tables folder will hold the current state of the tables and Tables | Updates holds the update and create scripts that will get the database to that state.
For every revision, you will need to create a copy of the directory structure shown above, which will hold the scripts and code as the revision moves toward being a release. Every time a developer alters a script he must:
- Check the script out of Revision Control (to a network directory that is backed up)
- Load the script into query analyser/SSMS
- Change the script
- Save the script
- Run the script
- Test the result
- Check the script back into Revision Control when complete.
This should not add much overhead to the development time and means that a server/workstation crash should never lose any work. This scheme will also ensure that each developer is always working on the latest version of each script and that two developers never accidentally change the same code.
Pop Rivett paused and looked dignified.
"Is that it?" asked Clarke Minor.
"Well yes, but I'll provide you with a couple of handouts which will give you the code and the details. Firstly, the database scripts, and secondly the actual rigmarole required to do the various releases. Mark them well M'boy and I trust I shall never end up being timetabled to teach Needlework to Gels again."
Gratefully, Clarke Minor ran from the staff room into the warm summer afternoon, clutching Pop's notes on the Database Scripts and Release Procedures. Clarke Minor had promised himself the pleasures of what Pop Rivett might have called an 'uncontrolled environment', before going back to impose Release Control on the School Timetabling system.
Putting database scripts into source control
At the end of a development cycle, the developers will need to have assembled scripts in Source Control that will allow the new schema and data to be recreated in the test environment. The following sections demonstrate some example scripts for this purpose.
Database creation scripts
The creation scripts for any databases should be stored in Vss\MyProject\Databases\MyDatabase\Create
Each script will normally be of the form:
CREATE DATABASE MyDatabasen
EXEC sp_dboption 'MyDatabasen', 'select into/bulkcopy', 'true'
EXEC sp_dboption 'MyDatabasen', 'trunc. log on chkpt.', 'true'
I normally set the model database to Simple Recovery Mode on the development server. So that all dev databases are subsequently created in simple recovery mode as well, which is normally best for a test system.
Tables, stored procedures, views, functions scripts
All database objects should be stored in their separate folders according to their type, each in their separate files. For example, each table creation script is named as follows:
Vss\MyProject\Databases\MyDatabase\Tables\MyTable.sql
Each script will be of the form (scripts for stored procedures, views, functions and so on will be similar):
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.
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
DTS packages should be saved as files and held in Revision Control
Releasing the version
If developers follow the processes described in the previous sections, then when they are ready to release-to-testing a particular version, they will have a set of script files, stored as described, for creating all of the necessary databases, tables, stored procedures, data and so on.
Before releasing the version, they will need to concatenate each set of individual scripts for creating, for example, a required table, into one script to create all the tables. So we will end up with a set of scripts such as:
- Mydatabase_createdatabase.sql
- Mydatabase_tables.sql
- Mydatabase_procs.sql
- Mydatabase_data.sql
To do this, we need to create a script concatenation file with instructions to concatenate the various sets of scripts together into a single file. You can either use a batch file or a stored procedure to do this. You can, alternatively, script out all database objects using the spScriptObjects stored procedure, found here at:
http://www.simple-talk.com/sql/database-administration/more-database-administration-and-development-automation-using-dmo/
Before creating these scripts, ask the developers if all changes to be made are complete in the Revision Control System, and if everything currently in Revision Control is to be released.
Creating a script concatenation file via batch 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 following files:
- Vss\MyProject\Databases\MyDatabase\mydatabase_Tables.sql
- Vss\MyProject\Databases\MyDatabase\mydatabase_Procs.sql
These will contain the script to create all the tables and stored procedures, respectively. Also included is the name of the table/stored procedure script before it is run so you will know where any errors occur.
Creating the script concatenation files via a stored procedure
The script concatenation files can be created using a stored procedure. First, create a table called ReleaseControl:
CREATE TABLE ReleaseControl
(
EntryType VARCHAR(100) ,
Directory VARCHAR(100) ,
Sequence INT ,
Name VARCHAR(128)
)
GO
Insert into this table entries for all Revision Control files that are 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 the following stored procedure, which will create the script concatenation files. Note the entries in comments at the top of the stored procedure, which will use osql to create the output files and also execute them via xp_cmdshell. Otherwise just save the output from the SP.
-- 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