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:

1. On the development machine, create a directory called VSS (or similar)
2. 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)
3. Create a subdirectory of VSS for the project e.g. MyProject.
4. Create something like the following directory structure:

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:

1. Check the script out of Revision Control (to a network directory that is backed up)
2. Load the script into query analyser/SSMS
3. Change the script
4. Save the script
5. Run the script
6. Test the result
7. 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
               
               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

 Controlled releases of database applications

The following sections provide guidelines for releasing a software version to the various stages of testing, and then to the live productions system.

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.

Performing the release to System test

Create a folder in Revision Control: Vss\MyProject\Release\SysTest\R001

Copy all the scripts to be released into this directory.

Create a document that 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 and so on), 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 Revision Control. 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.

Performing the release to UAT

Create a folder in Revision Control: 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.

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 whoever has been selected to be responsible for the live system, usually a DBA.

Create a folder in Revision Control: 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 a good time to test the release documentation so use this, and make sure it is correct, and can be followed in an emergency by anyone.

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.

Performing the release to Live

The script will already be in Revision Control 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.

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 Revision Control. 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

http://www.simple-talk.com/sql/database-administration/automating-common-sql-server-tasks-using-dmo/

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.

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.

A sample release document is included with the code download.

Information required 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

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

1. 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.
2. Stop any affected systems
3. Take any required backups
4. Release the modules
5. Take any backups required
6. Perform the release confidence tests
7. Restart systems
8. If any failures perform the release backout procedure

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