Note: the following article is only targeting Microsoft SQL Server (I regret...).

In a previous article I have discussed the schema generation and schema update offered by NHibernate. In this post I want to discuss a way how you can generate and/or maintain your SQL Server database.

When practicing agile development one of the most important cornerstones of this methodology is implementing continuous integration (CI). That is any solution your team develops should be able to build in fully automated manner. One of the build steps is of course the creation and or update of the database and the database schema.

Tools to automate the build process

Many people use either nant or msbuild to fully automate their build. And when I say build it includes the following tasks(others are possible)

• versioning/tagging the sources
• compiling the sources (in Visual Studio called build)
• running unit tests, integration tests, acceptance tests, stress tests
• dropping and re-creating the database
• re-creating the database schema
• creating a package
• if Web: deploy the web site
• if Other: create installer
</ul>

In this post I'll discuss the two tasks marked in blue.

The nant task

There is an OSS project on Google Code which is dedicated to the change management of SQL server databases. It's called Tarantino and can be found here. One of the outcome of this project is a custom nant task called manageSqlDatabase. We will use nant and this custom task to manage our (sample) database.

You need the following files to be able to use the custom task

• Tarantino.CommonsCore.dll
• Tarantino.DatabaseManager.Tasks.dll
• Microsoft.SqlServer.BatchParser.dll
• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Smo.dll
</ul>

To get the files for your own solution either download the sample solution accompanying this post or download it directly from the Tarantino project which you can find here.

The build file

The general format of the custom nant task is

<manageSqlDatabase
  scriptDirectory="${database.script.directory}"
  action="${action}"
  server="${database.server}"
  integratedAuthentication="${database.integrated}"
  database="${database.name}"
  username="${database.username}"
  password="${database.password}"
/>

The sriptDirectory contains the path to the files which contain the schema creation and/or schema update statements.

The possible actions are

• dropDatabase
• createDatabase
• rebuildDatabase
• updateDatabase
</ul>

The server parameter must contain the name of the SQL Server (e.g. "localhost/SQLEXPRESS").

The parameter IntegratedAuthentication can be either true or false.

The parameter database contais the name of the database to re-create or update (e.g. "SampleDatabase")

The parameters username and password are only needed if IntegratedAuthentication is set to false.

A typical create database scenario could be as shown below

<manageSqlDatabase
  scriptDirectory="src\database"
  action="createDatabase"
  server="localhost"
  integratedAuthentication="true"
  database="SampleDatabase"
/>

Let's not construct a complete build file for nant which includes the task of dropping and re-creating a database and the creation of the database schema. We create a new empty file called default.build and enter the following XML fragments.

<?xml version="1.0" encoding="utf-8"?>

<project name="DemoSolution" default="builddatabase" 

         xmlns="http://nant.sf.net/release/0.85/nant.xsd">

    <property name="solution.dir" value="src" />

    <property name="database.script.directory" value="${solution.dir}/Database"/>

    <property name="database.server" value="localhost"/>

    <property name="database.name" value="${project::get-name()}"/>

    <target name="builddatabase" depends="dropDatabase, createDatabase" />

    <target name="dropDatabase">

  </target>

    <target name="createDatabase">

  </target>

</project>

In this file we define a new nant project called DemoSolution. The default target that is executed is builddatabase. Then we define some properties for reference in our project (if you are not fluent in nant syntax please consult the online documentation here.)

The builddatabase target does nothing else than trigger the targets dropDatabase and createDatabase, that is if the database already exists then it is dropped and then re-created. Finally the database schema is created. But wait, those two targets are empty at the moment and will do absolutely nothing at the moment.

Let's now add this helper target to the build file - we want to avoid duplication don't we?

<target name="manageSqlDatabase">

  <manageSqlDatabase

    scriptDirectory="${database.script.directory}"

    action="${action}"

    server="${database.server}"

    integratedAuthentication="true"

    database="${database.name}"

  />

  <if test="${action != 'Drop'}">

    <echo message="Current Database Version: ${usdDatabaseVersion}" />

  </if>

</target>

it will be called by the dropDatabase and createDatabase targets where each provides another action parameter. Note that the usdDatabaseVersion parameter is generated by the manageSqlDatabase custom task.

Now we complete the dropDatabase and createDatabase targets as follows

<target name="dropDatabase">

  <property name="action" value="Drop" />

  <call target="manageSqlDatabase" failonerror="false"/>

</target>

<target name="createDatabase">

  <property name="action" value="Create" />

  <call target="manageSqlDatabase" />

</target>

that is we use the standard call task of nunit to trigger the custom task. Note that the dropDatabase target calls the custom task with failonerror set to false (default is true). It is possible that the database does not yet exist. In this case the build should just continue.

The script files

One of the main parts in the automation process is still missing. It's the SQL script files which generate and/or update the database schema. These files have to be valid SQL scripts (you should be able to run these scripts without errors in e.g. a query session in "SQL Server Management Studio"). The scripts can contain DDL and DML statements. They have to be sequentially numbered, e.g.

• 0001_CreateBaseSchema.sql
• 0002_AddProductAndCategory.sql
• 0003_AddIndexes.sql
• 0004_InitialDataLoad.sql
• etc.
</ul>

You can freely choose any name after the underscore. The manageSqlDatabase nant task will apply the scripts sequentially starting with the lowest number and ending with the highest number. When creating the database the manageSqlDatabase nant task will add a special table to the database which keeps track of which scripts have been applied.

How to create the script files

The initial schema generation script I normally generate by using NHibernate's schema export utility. See this post for an in depth discussion. Alternatively you can also use the script database objects task of "SQL Server Management Studio". For any further modifications of the schema (especially if the first version is already in production) I use a product like Redgate's SQL Compare or the same product from Apex to generate the alter scripts.

Execute the build

We can now execute the build by invoking the following command

bin\nant\nant.exe -buildfile:default.build

we can write a batch file builddatabase.bat to further automate the process. The content of the batch file might be as follows

bin\nant\nant.exe -buildfile:default.build
pause

Sample Code

You can download a little sample from here. You need to have an SQL Server available. A local installation of SQL Server Express Edition is enough. Please adjust the properties database.server and database.name in the file default.build according your needs. Double click the file builddatabase.bat to test the creation of the database and the database schema.

Enjoy!