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.
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 this 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.