Database versioning with RoundhousE

Following my post about database versioning (Vietnamese version), this post shows one solution to handle this issue in real life. Let’s start with .NET project and SQL Server for an example as they are simpler and wide-range use.

Solution

As I mentioned in the previous post, the DB versioning should run as soon as possible during the application startup. And of course, during the build is better at least for 3 reasons:

• It could save time
• The conflicts (or failure) could be detected soon.
• It makes sure the test codes run properly as they may require the latest DB.

For .NET project, RoundhousE seems a good lightweight solution, at least it’s free compared to the features that RedGate provides. But unfortunately, its document isn’t good enough for the newbie and it just focus on the migration stage. So my post could show you an easier way. But please note that it’s a getting started guide because RoundhousE has a lot of features that you could use.

Hand-on

Step by step guideline:

Setup

In your .NET solution, create a library .NET project, name it DbVersioning.{Your_DB_name}, target to .NET 3.5. In this example, I named it DbVersioning.MusicStore, you could replace the MusicStore by your DB name in all the settings below.

All the following steps are in DbVersioning.MusicStore project:

Install the RoundhousE MSBuild Nuget package. It may create some files automatically. 2 files that we should take care are DBDeploy_MSBuild.proj and sample.xml with the information of DB that RoundhousE will work with.

Create folder lib and copy these files to it: log4net.dll, roundhouse.dll, roundhouse.task.dll. You could find these files in packages folder of this solution with the right target .NET version. Make them always copy to output directory.

Create folder db and its appropriate sub-folders. You could find the folder names and their purposes here.

Unload the DbVersioning.MusicStore project and add these lines to DbVersioning.MusicStore.csproj file. Please change your DB name.

<Target Name="AfterBuild">
      <MSBuild Projects="DBDeploy_MSBuild.proj" Properties="VersionAssembly=$(TargetDir)DbVersioning.MusicStore.dll"/>
</Target>

Update the information in DBDeploy_MSBuild.proj. Some important fields are: DBServer, DBName, DBConnectionString

<?xml version="1.0" encoding="utf-8" ?>
<Project DefaultTargets="DBDeploy" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <!-- Visit http://roundhouse.googlecode.com/ for details -->
    <UsingTask AssemblyFile="lib\roundhouse.tasks.dll" TaskName="roundhouse.tasks.Roundhouse" />
    <PropertyGroup>
        <Environment>TEST</Environment>
        <DBServer>my.server.com\MSSQLSERVER2014</DBServer>
        <DBName>MusicStore</DBName>
        <DBConnectionString>Data Source=my.server.com\MSSQLSERVER2014;Initial Catalog=MusicStore;User ID=MusicStore;Password=mypassword;</DBConnectionString>
        <DBSqlFilesPath>db</DBSqlFilesPath>
        <RepositoryPath>https://bitbucket.org/mycompany/musicstore.git</RepositoryPath>
        <VersionFile>_BuildInfo.xml</VersionFile>
        <VersionFileXPath>//buildInfo/version</VersionFileXPath>
        <DBRestore>false</DBRestore>
        <DBRestorePath></DBRestorePath>
        <DBRestoreOptions></DBRestoreOptions>
        <DBCreateDatabaseCustomScript></DBCreateDatabaseCustomScript>
        <DBDrop>false</DBDrop>
        <Interactive>true</Interactive>
    </PropertyGroup>
  
    <Target Name="ApplyDatabaseChanges">
        <!--<Message Text="Running RoundhousE on $(DBServer) ($(DBName)). Looking in $(DBSqlFilesPath) for sql scripts." />-->
        <Roundhouse
            ServerName="$(DBServer)"
            DatabaseName="$(DBName)"
            ConnectionString="$(DBConnectionString)"
            SqlFilesDirectory="$(DBSqlFilesPath)"
            CommandTimeout="60"
            CommandTimeoutAdmin="300"
            RepositoryPath="$(RepositoryPath)"
            VersionFile="$(VersionFile)"
            VersionXPath="$(VersionFileXPath)"
            AlterDatabaseFolderName="alterDatabase"
            UpFolderName="up"
            DownFolderName="down"
            RunFirstAfterUpFolderName="runFirstAfterUp"
            FunctionsFolderName="functions"
            ViewsFolderName="views"
            SprocsFolderName="sprocs"
            IndexesFolderName="indexes"
            PermissionsFolderName="permissions"
            SchemaName="RoundhousE"
            VersionTableName="Version"
            ScriptsRunTableName="ScriptsRun"
            ScriptsRunErrorsTableName="ScriptsRunErrors"
            EnvironmentName="$(Environment)"
            Restore="$(DBRestore)"
            RestoreFromPath="$(DBRestorePath)"
            RestoreCustomOptions="$(DBRestoreOptions)"
            RestoreTimeout="900"
            CreateDatabaseCustomScript="$(DBCreateDatabaseCustomScript)"
            Drop="$(DBDrop)"
            DoNotCreateDatabase="false"
            OutputPath="RoundhousE_runs"
            WarnOnOneTimeScriptChanges="false"
            Silent="true"
            DatabaseType="roundhouse.databases.sqlserver.SqlServerDatabase, roundhouse"
            WithTransaction="false"
            RecoveryMode=""
            RunAllAnyTimeScripts="false"
            DisableTokenReplacement="false"
 />
    </Target>
  
    <Target Name = "DBDeploy"
            DependsOnTargets="ApplyDatabaseChanges;">
    </Target>
</Project>

Add file _BuildInfo.xml as

<?xml version="1.0" ?>
<buildInfo>
    <projectName>MusicStore</projectName>
    <companyName>My Company</companyName>
    <versionMajor>0</versionMajor>
    <versionMinor>1</versionMinor>
    <versionPatch>1</versionPatch>
    <buildNumber>1</buildNumber>
    <revision>6</revision>
    <version>0.1.1.0</version>
    <repositoryPath>https://bitbucket.org/mycompany/musicstore.git</repositoryPath>
    <microsoftNetFramework>net-4.5</microsoftNetFramework>
    <msbuildConfiguration>Release</msbuildConfiguration>
    <msbuildPlatform>Any CPU</msbuildPlatform>
    <builtWith>MSBuild v. 4.5</builtWith>
</buildInfo>

Please replace by your project information.

Make sure DbVersioning.MusicStore.csproj will be built. (Right click on your solution -> Properties -> Configuration Properties)

Build your solution. If you got an error about unable to load log4net assembly, you should copy these files in lib folder in my sample project on Github and restart your machine, everything will work well 🙂

Use

Since now, whenever you update the DB, you need:

• Script your changes to files and add them to db\alterDatabase folder. In my opinion, each change should be in separated .sql file and named in order such as 0005_AddCategoryTable.sql, 0006_AddProductTableWithConstraints.sql
• Update revision / version in _BuildInfo.xml file. It of course is larger than current numbers.

Known issues

There are some issues that you could meet with RoundhousE:

• .NET version. RoundhousE MSBuild package is still in .NET 3.5 and no longer updated from 2013. But it runs well right now and you could download and recompile it from source at Github.
• It might use older version of log4net.
• RoundhousE will create some tables under RoundhousE schema, so it requires the appropriate privilege login in connection string.

You could find my sample project here.

It’s unnecessary to create another project DbVersioning.MusicStore as you can do it in the main project. But in my opinion, it should be better with a dedicated project for DB versioning as we can easy to detect the changes. And because RoundhousE uses .NET 3.5, it should be in another project to don’t affect to your project settings.