Database Migration with DbUp and VSTS

with 8 Comments

All too often I find myself in projects where there is no efficient strategy around updating databases. Databases should be able to be migrated to the latest version without too much effort. If it’s too hard, developers will start sharing databases, or even worse, use a test environment database directly.

If you usually do migrations by comparing the schema of two databases, now is an opportunity for you to do something better. Besides schema and security, a database also consists of data, and data is troublesome. Large tables takes both time and resources to alter. A tool simply cannot generate resource efficient migrations, or for example figure out where the data in that dropped column should go instead.

Therefore you will always need a process or another tool to run transitional scripts beside the schema comparer. If you instead focus on the transitional script runner and have it logging which scripts that has been run to some persistent storage, you can use that log as a simple means to version your database.

Also, do not forget to include configuration data, or sample data for test and development, in your migration process.

Run Migrations with DbUp and VSTS (or TFS 2015)

A favorite transitional script runner of mine have long been DbUp.

DbUp is a .NET library that helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date.

DbUp Documentation.

One way that you can get started with DbUp is by importing its NuGet-package in a .NET console project. But I prefer to invoke it through PowerShell. That way nothing needs to be compiled, and as long as you have access to your migration scripts you are good to go. The PowerShell way also makes a good match for deployment scenarios with Octopus Deploy or Release Management.

I have made a VSTS Build and Release Task for this purpose. But, if you would like to run DbUp elsewhere, the important part of the task is the following PowerShell script:

Run Your Tools Often

As with all deployment tools, you should run them often. The more you run them the higher the probability gets that you will have found all mistakes before deployment is made to the production environment. This does not only mean that the same tool should be run in your CI builds, but also that each developer should use it to set up their own personal database. Never underestimate the value of dogfooding your deployment tools!

Non-transitional Changes

All database objects do not need to be changed transitionally like tables. For example regarding stored procedures and security objects, a more pragmatic approach is to keep a set of idempotent scripts that are run on each deploy. This is supported by the PowerShell script above with the Journal parameter.

Follow Johan Classon:

Azure enthusiast, .Net Developer, PowerShell empowered DevOps hacker, and Solutions Architect.

8 Responses

  1. Jonathan Counihan
    | Reply

    Thanks for the post.

    I’m trying to do something similar, but I can’t get DbUp to log any changes to the powershell console.

    My config looks like this:

    $dbUp = [DbUp.DeployChanges]::To
    $dbUp = [SqlServerExtensions]::SqlDatabase($dbUp, $connectionString)
    $dbUp = [StandardExtensions]::LogScriptOutput($dbUp)
    $dbUp = [StandardExtensions]::LogToConsole($dbUp)
    $dbUp = [StandardExtensions]::WithScriptsEmbeddedInAssembly($dbUp, $ScriptsAssembly)

    Do you actually get output in console? Like the results of the scripts?

    • Johan Classon
      | Reply

      Normally I am happy with only getting errors and a list of scripts that is run, which is handled by calling the LogToConsole() method. IMHO, migration scripts are (should be?) run so often that one can have faith that once that they are invoked they do the right thing.

      Until now, I never tried to use LogScriptOutput()… But I tested now, and I do get output in the console from all SQL print statements, and select statements. (DbUp executes the scripts with a simple IDbCommand.ExecuteReader())

      This is how a dummy migration run looks for me:

      If you build a console application, do you get log output then? What type of log output are you after? Number of affected rows?

      • Jonathan Counihan
        | Reply

        Actually all I am after is the DbUp output to indicate where in the process it gets to, in case something fails. (we are trying to use this for our CI server)

        So far I cannot get DbUp to print anything to the powershell console, even the “Beginning database upgrade” message etc. I was trying different combos of $dbUp = [StandardExtensions]::LogToConsole($dbUp) and so far no luck.

        Next step is to use a exe wrapper around the DbUp dll if I can’t get the powershell script to work directly.

  2. Devops Online Training
    | Reply

    great article i ever seen, thanks for sharing ,this article was very useful for me while learning the devops, thanks for sharing please share more article like this.

    • Johan Classon
      | Reply

      Glad you found it useful. Good luck with your path down the devops road!

  3. JamesD
    | Reply

    Fantastic work, the developers usage page is very vague, this was exactly what I needed.

    I have two deployed changes: Schema and Recreatable.

    Schema is versioned using the SchemaVersion table
    While the Recreatables deploy will always run drop-recreate SP changes, this tutorial set me on the right path to achieve this, many thanks.

  4. Nitin Jain
    | Reply

    I am trying to run above scripts… Could you please let me know path parameters are we need to set before use above line of code…

    While running whole copied script it give multiple error..

    Can you please share what is correct way to use your PS scripts and parameter lists to be set before or update in code.

Leave a Reply