Skip to main content

Database change management

I thought I would get started with database change management and Tarantino, but after spending a day trying to figure it out, I eventually decided that it would go much faster to just roll my own.

What is database change management?

Most developers today are familiar with source version control. Even if some use it only for backups only, the main idea is for you to go back in history when needed or to try out different approaches in other branches. Using SVC (software version control) has become a standard in the industry, but very few apply the same idea on databases even though they also are a major part of most application development.

How not to version control databases

  • Check in the database to source control
    It works as long as you are only one developer to check in the binary database file to your repository. You will however not be able to merge changes from different developers and you will have to do manual updates of your production environment when the time comes.

  • Share database with team members on a central server
    The most common way to handle database development is not to version control at all, but to setup the database on a shared central server. This fails when you need to revert to a previous version, but it also makes it harder to work as a team. When you make changes to the database you will affect your team members because they don't have your code to support the database changes. In worst case the whole team will halt production until you've checked in your code to support the database change.

Why should I care?

If you manage to version control your database development, you will not only be able to revert to old revisions, work in branches but also automatically get old databases up to date within your release process. This is much more efficient than manually merging the databases at every release, and also less error prone.

  • You bring stability to your release cycle
  • You reduce hold ups in the production line for your team
  • You bring greater control to your database development process

How do I get started?

If you use NAnt or MsBuild as build script you should probably head over to Tarantino and download their software since it has excellent support for your environment. If not, you may keep on reading. Since I'm using psake at the moment, I decided to implement my database change management in PowerShell v2. The concept is very simple. You keep a directory in your source control where you store database creation/update scripts. The key component is that you never ever change any of these scripts after their first commit. When you need to change something in the database you create a new script. That way you will always be able to create a new database and bring it up to head revision at any time.

database change management

The naming convention here is very important. Next thing you need is a database where you have a table and a column that tells you what version this database is. As you've already guessed, this is exactly up to what revision the change scripts has been applied.

database versioning

All you need now is one function that can look at your database, decide what version it is and apply those changes that has not yet been applied. Actually, I do it in three functions and they look like this.

  • Update-Database $connection_string $database_directory
    Connects to the database and get the version number. Finds all change scripts in the database directory that are above the specific version and applies them to the database. Last it updates the database with the current version number.

  • Build-Database $sql_server $database_name
    Connects to the DBMS and creates an empty database. In this database it adds the necessary table and column to keep track of database versions.

  • Drop-Database $sql_server $database_name
    Removes the database from the DBMS.

Example usage

In my current project I want to run integration tests on a fresh database every time I check in code. That means I will have the following build process executed.

# Compile
Drop-Database "MAIA\SQLEXPRESS" "IntegrationTests"
Build-Database "MAIA\SQLEXPRESS" "IntegrationTests"
Update-Database "Data Source=MAIA\SQLEXPRESS;Initial Catalog=IntegrationTests;Integrated Security=True;" ".\Database"
# Run integration tests

When I check in code I will rebuild the database and execute my integration tests on it. That way I will not only test my code, but also verify that my change scripts are working. When it is time for release I will be able to run Update-Database on my production database, because I know that those build scripts has been thoroughly tested. Here is my PowerShell script if you're interested. I still see myself as a novice in PowerShell scripting, but it works and is quite minimalistic.

Here comes the complete database change management script.

###



Script for database change management



Author: Mikael Lundin



Website: http://mint.litemedia.se



E-mail: mikael.lundin@litemedia.se



Drop-Database



Will remove a database from the DBMS



Build-Database



Will create database in the DBMS and the mandatory Settings table



Update-Database



Will bring the database up to the most recent version



Will drop the database if it exists



Function Drop-Database ([string]$sqlserver, [string]$databasename)
{
    try {
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
        $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
        $s.Refresh();
        $s.KillDatabase($databasename);
        Write-Host "Killed database $databaseName"
    }
    catch {
        Write-Host "Tried to delete database $databaseName but failed, probably because it did not exist"
    }
}



Will update the database to the most current version in the database directory



Function Update-Database ([string]$connectionstring, [string]$databasedirectory)
{


$databaseVersion = Get-Database-Version $connection_string

# Get all source files that have higher database version number
$files = Get-ChildItem "$database_directory\*.sql" | Where { [int]::Parse($_.name.Substring(0, 4)) -gt $databaseVersion }

# For each of those files, run query on database
foreach ($file in $files)
{
    $fileName = $file.name
    Write-Host "Apply update script: $fileName"

    # Get-Content returns a string array of all the lines. We join that into a single string
    $fileContents = Get-Content "$file"
    $sql = [string]::Join([Environment]::NewLine, $fileContents);
    Execute-Sql-Query $connectionString $sql

    # Get this version number
    $version = [int]::Parse($fileName.Substring(0, 4))

    # Update the settings database with current version number
    Execute-Sql-Query $connectionString "UPDATE [Settings] SET [DatabaseVersion] = $version"
}



}



Will create a new database and add table to manage versions



Function Build-Database ([string]$sqlServer, [string]$databaseName)
{
    # http://sqlblog.com/blogs/allen_white/archive/2008/04/28/create-database-from-powershell.aspx
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
    $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
    $dbname = $databaseName


# Instantiate the database object and create database
$db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($s, $dbname)
$db.Create()

# Create table and column for handling database version
$db.ExecuteNonQuery("CREATE TABLE [$databaseName].[dbo].[Settings] ([DatabaseVersion] int NOT NULL)");
$db.ExecuteNonQuery("INSERT INTO [$databaseName].[dbo].[Settings] ([DatabaseVersion]) VALUES (0)");



}



Helper functions



Function Get-Database-Version ([string]$connectionString)
{
    [System.Data.SqlClient.SqlConnection]::ClearAllPools()


$sql = "SELECT TOP 1 [DatabaseVersion] FROM [Settings]"
## Connect to the data source and open it
$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$connection.Open()

$command = New-Object System.Data.SqlClient.SqlCommand $sql,$connection
$version = $command.ExecuteScalar();

$connection.Close()
$version



}



Function Validate-Connection ([string]$connectionString)
{
    [System.Data.SqlClient.SqlConnection]::ClearAllPools()


try {
    ## Connect to the data source and open it
    $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
    $connection.Open()
    $connection.Close()
    $TRUE
}
catch {
    $FALSE
}



}



Function Execute-Sql-Query ([string]$connectionString, [string]$sql)
{
    [System.Data.SqlClient.SqlConnection]::ClearAllPools()


## Connect to the data source and open it
$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$connection.Open()

$command = New-Object System.Data.SqlClient.SqlCommand $sql,$connection
$result = $command.ExecuteNonQuery();

$connection.Close()



}

An update to this database script has been posted here.

comments powered by Disqus