Introduction
Changing SQL scripts between different environments often causes issues for development teams.
Many continue to manually change scripts– making deployment more error prone and difficult to include database development as part of a continuous integration process.
This is the first in a short series that will look at techniques to automatically manage these changes. This blog will give an overview of the functionality offered by the build tool NAnt* which can be used to automate the process.
Future blogs will cover this topic in more detail – looking at how to handle specific issues, such as linked servers and security/permissions, which tend to change between environments.
For this series it is assumed that you have read Managing SQL scripts and continuous integration
*The techniques that will be outlined could also be applied using other build tools and to manage changes to other file types such as application configs.
General guidelines
As well as production, many applications often have a number of other environments – build, functional test, UAT, deployment, integration, pre-production/stage etc.
To manage the complexity multiple environments introduces – the guidelines below should be followed:
1) Fully automate the build and deployment process – no manual changes.
2) Minimise change between environments. But, where change has to occur – keep the places that have to change to a minimum – ideally to one place only.
NAnt functionality
Recording the expected environments
Knowing what the expected environments are is key if scripts are going to be auto created for them.
NAnt allows variables to be set as ‘properties’ – with each property having a name/value pair.
As shown below a property is named ‘releaseEnvironments’ and has a value which is a comma separated list all of the expected environments.
<property name="releaseEnvironments" value="BUILD,FUNCTEST,STAGE,LIVE"/>
The property can be used by throughout the build process using the following notation:
${releaseEnvironments}
To add a new environment – just add it to the list and ensure required environmental settings are set as per the following section.
Recording environments settings
Knowing what properties change between each environment and what the required values are is also key.
NAnt has a concept of a ‘target’ – effectively a function that can be called.
A target is created for each expected environment and its required settings recorded as shown in the example below:
<target name="LoadEnvironment-BUILD">
<property name="linkedServerName" value="LinkedServerNameBuild"/>
<property name="linkedServerDBName" value="DBNameBuild"/>
</target>
<target name="LoadEnvironment-FUNCTEST">
<property name="linkedServerName" value="LinkedServerNameTest"/>
<property name="linkedServerDBName" value="DBNameTest"/>
</target>
<target name="LoadEnvironment-STAGE">
<property name="linkedServerName" value="LinkedServerNameStage"/>
<property name="linkedServerDBName" value="DBNameBuild"/>
</target>
<target name="LoadEnvironment-LIVE">
<property name="linkedServerName" value="LinkedServerName Live"/>
<property name="linkedServerDBName" value="DBNameLive"/>
</target>
Note the naming convention for each target:
‘LoadEnvironment-ENVNAME’ – where ‘ENVNAME’ correlates to an entry in ‘${releaseEnvironments}’.
Two properties have been added – ‘linkedServerName’ and ‘linkedServerDBName’. Others can be added by creating appropriate name/value pairs.
These targets enable the properties of a specific environment to be set and then used.
For example:
<call target="LoadEnvironment-BUILD"/>
Would load the properties and values for ‘BUILD’.
ie the value of ${linkedServerName} would be ‘LinkedServerNameBuild’
<call target="LoadEnvironment-FUNCTEST "/>
Would load the properties for the ‘FUNCTEST environment.
ie the value of ${linkedServerName} would now be changed to ‘LinkedServerNameTest’.
For each loop
NAnt has a For each method that allows simple looping.
This can be usefully employed in a number of ways. For example, to build SQL scripts for each environment the properties for each environment will need to be set.
This can be done by combining ‘for each’ with the two section above – as the following sample shows:
<foreach item="String" in="${releaseEnvironments}" delim="," property="envName">/>
<call target="LoadEnvironment-${envName}"/>
</foreach>
‘foreach’ – loops through each of the comma separated entries in ${releaseEnvironments} – (BUILD,FUNCTEST,STAGE,LIVE) and sets a new property ‘envName’ with a particular value.
It then calls the target ‘LoadEnvironment-${envName}’ for each required environment.
The above is equivalent to the following:
<call target=" LoadEnvironment-BUILD"/>
<call target=" LoadEnvironment-FUNCTEST "/>
<call target=" LoadEnvironment-STAGE"/>
<call target=" LoadEnvironment-LIVE "/>
Build version
Each build can be provided with an overall build version – making it easier to ‘tag’ all components of the overall solution and making it possible to correlate ‘deployables’ with the source control repository (this was used in a previous blog Including a version number with your database schema).
NAnt provides Version to do this. It “Increments a four-part version number stored in a text file. The resulting version number is written back to the file and exposed using NAnt properties.**”
The last build version is stored in a text file (named in ‘SolutionBuild.number’ in the sample below) and is of the form Major/Minor/Build/Revision. For example, 1.0.1.78
The last two parts can be auto-incremented as part of the build process (the first two numbers have to be manually updated).
The following example shows how it can be used:
<version prefix="solution" buildtype="NoIncrement" revisiontype="Increment" path="SolutionBuild.number" />
This will get the last build version text from the file ‘SolutionBuild.number’ and only auto-increment the 4th – RevisionType. For example, 1.0.1.78 would be changed to 1.0.1.79
The latest build version can then be used throughout the build process by using ${solution.version}.
** Quotes taken from the NAnt site.
Create directories for ‘deployables’
NAnt has a function mkidr which can be used to create directories.
The SQL scripts created (along with other components of the overall solution) will need to be stored somewhere – ready to be deployed when required. It should be easy to distinguish scripts for particular build versions and for each environment of a particular build.
The following example shows how this can be done – note the use of ‘foreach’:
<foreach item="String" in="${releaseEnvironments}" delim="," property="envName">/>
<mkdir dir="${dir.BuildRoot}\${solution.version}\${envName}\SQL" />
<mkdir dir="${dir.BuildRoot}\${solution.version}\${envName}\WebApp" />
<mkdir dir="${dir.BuildRoot}\${solution.version}\${envName}\Reports" />
<mkdir dir="${dir.BuildRool}\${solution.version}\${envName}\SSIS" />
<mkdir dir="${dir.BuildRool}\${solution.version}\${envName}\SSAS" />
</foreach>
This would produce a directory structure (only showing ‘SQL’ dirs) like the following:
..Root\1.0.1.79\BUILD\SQL
..Root\1.0.1.79\FUNCTEST\SQL
..Root\1.0.1.79\STAGE\SQL
..Root\1.0.1.79\LIVE\SQL
Token and Text replacement
NAnt has two functions and that can be used to replace tokens/specified text within SQL scripts.
The first “replaces all token surrounded by a beginning and ending token. The default beginning and ending tokens both default to ‘@’.**”
This has been seen in a number of previous blogs – Versioning individual SQL objects and Including a version number with your database schema.
The second – “replaces all occurrences of a given string in the original input with user-supplied replacement string.**”
Both are filters that can be used on filter chains – “represents a collection of one or more filters that can be applied to a task such as the copy task. In the case of the copy task, the contents of the copied files are filtered through each filter specified in the filter chain.**”
It means that a set of original source SQL scripts – with tokens or specified text within them – can be copied to another directory and whilst copying these files – filter token or text replacements can be carried out.
An example of how this can be used:
<copy todir="C:\OutputDir\SQL">
<fileset basedir=" C:\SourceDir\SQL">
<include name="*.sql" />
</fileset>
<filterchain>
<replacetokens>
<token key="BUILD_VERSION_NUMBER" value="${solution.version}"/>
<token key="LINKED_SERVER" value="${linkedServerName}”}/>
<token key="LINKED_SERVER_DB” value="${linkedServerDBName}”}/>
</replacetokens>
</filterchain>
</copy>
This would do the following:
1. Copy all SQL files in ‘C:\SourceDir\SQL’
The ‘include’ *.sql means that only SQL file types in this directory will be copied – others file types will be ignored.
2. Copy them to C:\OutputDir\SQL
3. On copying the SQL files, any scripts that have the specified tokens in them eg @ BUILD_VERSION_NUMBER@, @ LINKED_SERVER@ and @ LINKED_SERVER_DB@ – the tokens will be replaced with the specified values.
To the change the example above to use ‘replacestring’ instead of ‘replacetoken’:
<filterchain>
<replacestring from="@BUILD_VERSION_NUMBER@" to="${solution.version}"></replacestring>
<replacestring from="@LINKED_SERVER@" to="${linkedServerName}"></replacestring>
<replacestring from="@LINKED_SERVER_DB@" to="${linkedServerDBName}"></replacestring>
</filterchain>
What next?
This blog has provided an overview of some of the functionality that the build tool NAnt can offer to help manage SQL scripts.
It has shown how to:
- Record the expected environments and the individual settings required for each.
- Autogen a build version
- Autogen the directories for each environment to store ‘deployables’.
- Auto replace tokens/specified text from scripts with settings for an individual environment.
The next blog in this series will go through a worked example showing how to use these features to automatically create SQL scripts for different environments.
Posted by Michael Baylon 
