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:
- SQL Server Database Projects in Visual Studio is a full featured database solution but unfortunately takes a “State” approach to keeping databases up to date. For one-off sync scenarios it is a perfect solution but for incremental database changes it is less than ideal. In my opinion (and in my experience) this is a problematic approach because many changes to databases are *transitional *rather than stateful in nature.
- Entity Framework Code First Migrations is fairly solid migration framework but unfortunately only works for objects that have an Entity Framework DbContext defined for. If you are working with an existing, large database where EF is not used throughout, you are out of luck. Also, it does not work with Stored Procedures.
- RoundhousE is another full featured solution that is well thought out but I find it a bit cumbersome to work with and think it is overly complex in nature. It lacks Package Manager Console scripts and is a little tricky to integrate into a deployment process.
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:
- Run New-Migration from the Package Manager Console. A new .sql script is added to your DbUp project in the \Migrations folder.
- Edit the new migration script and write the SQL to migrate your database.
- 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.