If your organization has continuous integration in place, it will most likely contain a build step in which your latest application source code gets pulled from source control, compiled, and optionally subjected to a various tests.
If you’re a Microsoft shop, chances are that your application works alongside an instance of SQL Server.
SQL Source Control allows you to put your schema objects in source control by maintaining a set of per-object creation scripts representing in your existing version control system, such as Team Foundation Server, Subversion, SourceGear Vault, etc.
Here I will describe how to use the appropriate Red Gate command line interface to recreate the database from these source creation scripts, which is essential in order to achieve database continuous integration.
In SQL Compare Pro’s Program Files folder, you will find the command line interface executable, sqlcompare.exe. Below I will provide examples of how this is called from a DOS batch file and NAnt.
You can either deploy a new database or deploy to an existing one. If doing the former, use sqlcmd.exe to drop and recreate the database as follows.
DOS Command
Sqlcmd.exe -E -SYourCIServer -Q "ALTER DATABASE YourCIDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE YourCIDatabase "
Sqlcmd.exe -E -SYourCIServer -Q " CREATE DATABASE YourCIDatabase"
NANT
<target name="drop_CI_DB">
<exec program= "sqlcmd.exe">
<arg value="-E"/>
<arg value="-SYourCIServer "/>
<arg value="-Q "ALTER DATABASE YourCIDatabase SET
SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE
YourCIDatabase""/>
</exec>
</target>
<target name="create_CI_DB" depends="drop_CI_DB">
<exec program= "sqlcmd.exe" failonerror="true">
<arg value="-b"/>
<arg value="-E"/>
<arg value="-SYourCIServer"/>
<arg value="-Q "CREATE DATABASE
YourCIDatabase""/>
</exec>
</target>
The -b switch ensures that an exit code of 1 will be return if the command fails, aborting the script.
We use sqlcompare.exe to apply the database changes by setting the source as the creation files scripts folder, and the target as the database (YourCIDatabase).
DOS Command
Sqlcompare.exe /scripts1:YourScriptsFolder /server2:YourCIServer /db2:YourCIDatabase /sync
NAnt
<target name="DeployToCIDatabase" depends="create_CI_DB">
<exec program="sqlcompare.exe" failonerror="true">
<arg value="/scripts1:YourScriptsFolder"/>
<arg value="/server2:YourCIServer"/>
<arg value="/db2:YourCIDatabase"/>
<arg value="/sync"/>
</exec>
</target>
The /sync switch ensures that the changes are applied. It is possible to use /ScriptFile:YourDeploymentScript.sql to generate the script.
You may have noticed the absence of a command to pull the latest scripts folder from source control. This is a task that is generally taken care of by the continuous integration tool, rather than the build script itself.
In an upcoming blog post I will describe how to configure JetBrains TeamCity with a NAnt script to monitor source control for schema changes, and trigger a process to keep our continuous integration database up to date.
<div style="margin: 0px; padding: 0px; float: none; display: inline;" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:66811b12-0a44-4aa7-b7b6-749356782948" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/SQL+Server" rel="tag">SQL Server</a></div>v
Before we start thinking about database continuous integration, described in an earlier blog post, we need to pick a build tool in which we define the commands for our build process.
NAnt is a .NET version of Ant, a tool to drive build processes. Like MSBuild, Microsoft’s equivalent, NAnt build scripts can be driven by continuous automation tools, such as TeamCity, CruiseControl, Bamboo and many others.
Yes, it would be possible to drive your scripts with DOS batch files or PowerShell, but NAnt specializes in build-related processes, which is why I decided to adopt this as the scripting engine for my SQL Server continuous integration set-up.
Typing NAnt into Google comes up with http://nant.sourceforge.net/ , its SourceForge page. It’s not immediately obvious, but to download NAnt you need to expand the chosen version and select the download option from the left-hand tree.
After downloading I extracted the contents of nant-0.91-bin.zip to c:\nant-0.91
NAnt.exe can be found in C:\nant-0.91\bin
To test that my set-up was working I simply ran nant.exe from a command prompt only to get:
C:\nant-0.91\bin>nant.exe
log4net:ERROR XmlConfiguratorAttribute: Exception getting ConfigurationFileLocation. Must be able to resolve ConfigurationFileLocation when ConfigFile and ConfigFileExtension properties are not set.
Googling the error message comes up with the solution. The zip file apparently protecting me from myself and needs to be ‘unblocked’.
http://sourceforge.net/tracker/index.php?func=detail&aid=3415420&group_id=31650&atid=402868
In windows explorer, invoke the zip file properties and click Unblock.
Extract the zip contents once more, and try running nant.exe. The error should have gone away.
Another issue I encountered when installing NAnt on a colleague’s machine is that unless the FULL version of the .NET Framework is installed, another error occurs.
Could not load file or assembly 'System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
Again, I found the solution via a Google search:
http://surfjungle.blogspot.co.uk/2011/11/tip-running-nant-091-on-windows-7.html
Simply install the Full .Net Framework and try again.
Now let’s test NAnt using a very simple example.
For this I created a text file C:\temp\helloworld.build
<?xml version="1.0"?>
<project name="Hello World" default="hello">
<property name="hello.string" value="Hello World" />
<target name="hello" description="Echoes 'Hello World'">
<echo message="${hello.string}" />
</target>
</project>
And invoke it from a command prompt as follows:
C:\nant-0.91\bin\nant.exe -buildfile:C:\temp\helloworld.build
This should return:
NAnt 0.91 (Build 0.91.4312.0; release; 22/10/2011)
Copyright (C) 2001-2011 Gerry Shaw
http://nant.sourceforge.net
Buildfile: file:///c:/temp/helloworld.build
Target framework: Microsoft .NET Framework 4.0
Target(s) specified: hello
hello:
[echo] Hello World
BUILD SUCCEEDED
Total time: 0 seconds.
Now we have a way of invoking NAnt manually from a command prompt, which is very useful for testing and troubleshooting build script issues outside of your continuous integration process.
Overall I was disappointed that the experience of installing NAnt wasn’t nearly as smooth as I would have expected given the popularity of the tool. The errors that were returned when the zip file hadn’t been unblocked, and when the Full .NET framework hadn’t been installed, were less than useful, so hopefully this blog post will assist others encountering similar issues.
Now that we’ve got NAnt installed and working, we’re in a position to build a new database directly from source control. This will be the topic of my next post, "How to build a database from source control"
Technorati Tags:
SQL Server