Tidbits on software development, technology, and other geeky stuff

SQL Server Migrations Done Right

When working in the .NET / SQL Server world, I’ve always been envious of Rails Active Record Migrations.  It seems there is no great way to handle migrations with SQL Server.  Sure, there are some tools out there but it seems all of them leave something to be desired.  Specifically:

Then I came across DbUp.  DbUp is great because it’s simple, uses the transitional approach and is easy to integrate into a deployment tool like Octopus Deploy.  What it lacks in features it more than makes up for in doing 95% of the things I care about well.  However, there were a couple of things it lacked which I thought could really make it perfect: (1) Package Manager Console scripts and (2) Object Scripting.

Package Manager Console scripts

I thought if there were a couple of simple Package Manager Console scripts, (1) one to create a new timestamped migration script, mark it as an Embedded Resource, and add it to the project and (2) a script to run the DbUp console application and migrate the database, the DbUp process would be much more streamlined.

Object Scripting

When I presented DbUp to my team at work and pitched it as a viable option for our database migrations, the biggest piece of feedback I got was the need to have object definitions scripted at the time the migrations run.  With Active Record Migrations in Rails, when you run db:migrate, after the migrations are applied, *db:schema:dump *is automatically called which updates your *db/schema.rb * file.  This file has the object definitions for your database.  As you run migrations, your schema.rb file gets updated which enables versioning of your schema, clear diffs for pull request reviews and a collection of baseline scripts to build a new database from scratch.  I realized if DbUp could support scripting of changes objects alongside running migrations, it would really be a great solution.

dbup-sqlserver-scripting

So, I wrote some Powershell scripts and leveraged SQL Server SMO to script out object definitions when DbUp migrations are run.  I cleaned it up and packaged it as a NuGet package for others to use.  I have been using this solution for a few months and have to say it is really ideal.  The workflow is:

  1. Run New-Migration from the Package Manager Console.  A new .sql script is added to your DbUp project in the \Migrations folder.
  2. Edit the new migration script and write the SQL to migrate your database.
  3. Run Start-Migrations from the Package Manager Console.  The output will show that your new migration(s) has been run.  Also, you will notice that any object(s) in your migration script(s) that were changed are also scripted and saved to the \Definitions folder at the root of your project.

That’s it!  With the above 3 steps you were able to migrate a database and update a local definition representing your database object state.  This is very similar (if not exactly the same) as Active Record Migrations.  When it comes time to run migrations on another environment, you only need to run the DbUp console application, just as the DbUp documentation describes; it’s just a console application after all.

Demo

This demo video shows just how easy it is:

 

More Information

The NuGet package is located here: https://www.nuget.org/packages/dbup-sqlserver-scripting/ and can be installed by running Install-Package dbup-sqlserver-scripting.

The GitHub repo is located here: https://github.com/bradymholt/dbup-sqlserver-scripting** **and contains more details setup and usage information.

 

Discuss on Twitter