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