SqlMigrations aka Continuous Integration & Deployments for MS SQL - Part I

Introduction and Background

Trying to handle SQL database changes manually is nothing but challenging and problematic. I have never witnessed a manual deployment on a sql database be a success. The main reason is due to "Oh I forgot to include something" or "I didnt run it in the right steps"!

MS-SQL databases have always been difficult when it comes to Continuous Integration and Continuous Deployments.

There are really rich tools such as the Red Gate SQL suite that can try make your life easier, but these things come at a cost and you also find that you can only use Red Gate source control etc.

This doesn’t help me because I have already got a project in source control with all its history etc. 

I also don’t want to be confined to the deployment manager they have, as I am already using Octopus Deploy (really awesome stuff) to manage my deployments.

There is also the alternative SqlCompare.exe which comes with the SSDT tools for free, but like Red Gate, the delta script for updating the database is created on the fly when comparing the 2 databases!

This to me doesn’t work because of the following reasons:

• The change script cannot be tested and verified to work on all servers. There is the risk that the script generated between dev and the CI server wont be the same as the script generated between the CI and the QA / Staging/ Production servers. I want 1 script to run them all. SqlCompare.exe does have the ability to “script” the change to file, but how do we then manage that script?
•  Working in environments disconnected from source control server -  We have a “strong hold room” of which no CI tools such as Team City can connect to, so only USB sticks can be used to do the deployment! J

One of the best things I have seen when it comes to handling upgrade migration scripts is Entity Framework Code First Migrations, but I am not keen to use code first for large enterprise applications. 

I have my database in TFS already using a SQL Project, and I don’t want to have to go change all my code to hook-up EF code first.

I thought to myself how grand it would be to be able to mimic the power of having small increment scripts like in EF migrations, and still harness the power of SqlPackager.exe and SqlCmd.exe.

The only reason I need SqlCmd.exe is because of the scripts the sqlpackager.exe generates – they can only be run by sqlcmd.exe, which I think is a good thing personally but that debate is not part of this article.

Another really difficult part which we will explore is the database rollback. This is a huge challenge of which most Ops guys will care about. As a developer, you should have the ability to rollback a database to its previous version in case a deployment goes pear shape.

Objectives

Before we start, I want to get a list of objectives together that I want to achieve before we start looking at my proposed solution.

•      Must be easy to deploy and rollback upgrade scripts
•           Must be easy to generate update and rollback scripts
•           Must be able to be deployed from a cmd driven approach for things like Octopus deploy

In Part II, I will take you through a practical step by step example on how to using the SqlMigrations tool I created (available on Nuget). It is still in beta, but will be investing more time to make it great.