Managing SQL Server migrations

In two projects I recently made a tool for managing the version of the SQL Server database manually. When writing almost the same utility code twice, there would be a third time. So I created a NuGet package to manage the SQL Server version. On start up it checks the database version and then run any upgrade scripts if needed. It also ensures the scripts run only once.

The first time I started with code that somehow had to be called from the application start. Because the Repository assembly was not known by the entry assembly, the call when through the application layers. The second time I thought of a solution by making an entry attribute in the assembly to start the migrations with reflection. This way we could manage multiple SQL Server databases without hard references to repository assemblies from the application start point.

In the first setup on Azure we noticed that sometimes two servers where running the scripts at the same time. To handle this I added a database lock for the migrations.

The next step was to make a general NuGet package without any extra references to an OR-mapper. And add extended logging. With the following steps you will be able to use the package:

1. Create a user on your database with enough rights to modify your database
2. Add the migrations NuGet package to your repository and startup project.
3. Add a directory Migrations with embedded resource migration SQL scripts. The script must have numeric names 1.sql or 201512011655.sql. They will run in asc order.
4. Add the assembly attribute to AssemblyInfo.cs

   [assembly: Migrations.Config.SqlServerMigration("myconnectionstringname")]
   

5. Add the myconnectionstringname to your application configuration
6. Call the migration manager in your application startup to run all migrations:

   MigrationManager.RunMigrations();
   

[Download NuGet Migration package]