Visual Studio Database project is a good way to quickly get your database under a source control. You will find it very useful especially if your system has a large database schema with a huge pile of stored procedures, functions, views or other database centric stuff. Unfortunately, the deployment strategy brought with it is painful and detached from the reality. Automated migration of the production database to the latest version is, hmmm…., I won’t say impossible, just hard. When I read about it, it souded more like fixing sinking submarine with your bare hands. Some people use roundhouse which is a very nice project to manage database migrations using differential scripts. Below you will find some pros and cons of using both solutions.

Database project

Pros:

  • integration with visual studio. You can use a database designer tool instead of writing “alter/create scripts”
  • performs comparisons with other databases/database projects using schema comparison tool
  • keeps the whole schema in the project
  • creates a fresh database from the schema instead of running all migrations
  • live validation of database schema - very easy refactor process with Visual Studio

Cons:

  • automated database migrations sucks
  • no database version control solution provided out of the box
  • if you have a large database it takes a lot of memory to hold database schema

Roundhouse

Pros:

  • automate database migrations using differential scripts and updating only procedures/views/functions which have changed
  • database version control solution

Cons:

  • creating a “clean” database by running all migrations
  • You do not know what database schema looks like unless you create the database

Why not both?

Why not to use these tools together? Cooperation will bring you something like this:

Pros:

  • integration with visual studio. You can use a database designer instead of writing “alter/create scripts” (DB Proj)
  • performs comparisons with other databases/database projects using schema comparison tool (DB Proj)
  • keeps the whole schema in the project (DB Proj)
  • creates fresh database from schema instead of running all migrations (DB Proj)
  • live validation of database schema - very easy refactoring with visual studio (DB Proj)
  • automate database migrations using differential scripts and updating only procedures/views/functions which have changed (Roundhouse)
  • database version control (Roundhouse)

Cons:

  • if you have a large database it takes a lot of memory to hold database schema

Looks better, but of course it needs an additional work to be done as roundhouse does not play well with stored procedures scripts that come from database project. I had to customize rh library to support following:

  • schema bound views - I need this because we have that kind of views in the database.
  • database project create/alter script convention for stored procedures/functions - create if not exists then alter instead of only creating object
  • a way to record roundhouse migration in database without actually run it. Baseline mode.

All these things are implemented on my branch develop.

Development process

To show how the development process will look like I’ve created a sample solution called Sampletico. Just pretend that Sampletico will be another task management system, for the time it has only user management feature implemented and we will add tasks storage option. First we will add Task class

public class Task : Entity
{
  public int Id { get; set; }
  public string Note { get; set; }
  public int Priority { get; set; }
  public User CreatedByUser { get; set; }
  public User AssignedToUser { get; set; }
}

Then using database designer within visual studio we will add Tasks table

Tasks table - Visual Studio Designer

We also need schema comparison profile file which compares our db project with local database. By default we ignore stored procedures/functions/views because roundhouse takes care of them. To create a migration we will use Visual Studio schema compare tools either manually or using described by previous post adding migration powershell script. Here we have result migration file generated by script and cleaned from unnecessary data.

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;

GO
PRINT N'Creating [dbo].[Tasks]...';

GO
CREATE TABLE [dbo].[Tasks] (
    [Id]               INT            IDENTITY (1, 1) NOT NULL,
    [Note]             NVARCHAR (MAX) NOT NULL,
    [Priority]         INT            NOT NULL,
    [CreatedByUserId]  INT            NOT NULL,
    [AssignedToUserId] INT            NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

GO
PRINT N'Creating [dbo].[FK_Tasks_ToCreatedByUsers]...';

GO
ALTER TABLE [dbo].[Tasks] WITH NOCHECK
    ADD CONSTRAINT [FK_Tasks_ToCreatedByUsers] FOREIGN KEY ([CreatedByUserId]) REFERENCES [dbo].[Users] ([Id]);

GO
PRINT N'Creating [dbo].[FK_Tasks_ToAssignedToUsers]...';

GO
ALTER TABLE [dbo].[Tasks] WITH NOCHECK
    ADD CONSTRAINT [FK_Tasks_ToAssignedToUsers] FOREIGN KEY ([AssignedToUserId]) REFERENCES [dbo].[Users] ([Id]);

GO
PRINT N'Checking existing data against newly created constraints';

GO
ALTER TABLE [dbo].[Tasks] WITH CHECK CHECK CONSTRAINT [FK_Tasks_ToCreatedByUsers];

ALTER TABLE [dbo].[Tasks] WITH CHECK CHECK CONSTRAINT [FK_Tasks_ToAssignedToUsers];

GO

To perform a migration we will use roundhouse library. The simplest way is to use rh console application, but I’ve created extra console application to generate sample and static data and perform migrations. To kick migrations run_migrations.bat was created.

@echo off
set msbuild=%windir%\Microsoft.NET\Framework\v4.0.30319\msbuild.exe
%msbuild% /m build.xml /v:m /p:VisualStudioVersion=12.0 /t:RebuildDataProject

Sampletico.Data\bin\Release\Sampletico.Data.exe /migrations

Sampletico.Data.exe is simple.

class Program
{
    private static string connectionString;

    static void Main(string[] args)
    {
        bool migrations = args.Any(i => i.Equals("/migrations", StringComparison.InvariantCultureIgnoreCase));
        bool baseline = args.Any(i => i.Equals("/baseline", StringComparison.InvariantCultureIgnoreCase));
        connectionString = ConfigurationManager.ConnectionStrings["Simple.Data.Properties.Settings.DefaultConnectionString"].ConnectionString;

        if (migrations)
        {
            RunMigrations();
        }
        else if (baseline)
        {
            Baseline();
            GenerateSampleData();
        }
        else
        {
            GenerateSampleData();
        }
    }

    private static void GenerateSampleData()
    {
        Console.WriteLine("Generating data....");
        UserFactory.Generate();
        Console.WriteLine("Generating data - DONE");
    }

    private static void Baseline()
    {
        Console.WriteLine("Running baseline - applying migrations data....");
        Migrator.InitMigrationDbStructure(connectionString);
        Console.WriteLine("Running baseline - applying migrations data - DONE");
    }

    private static void RunMigrations()
    {
        Console.WriteLine("Running migrations....");
        Migrator.MigrateToNewestVersion(connectionString);
        Console.WriteLine("Running migrations - DONE");
    }
}

Key configuration to run baseline and migration are in Migrator class.

public static class Migrator
{
    public static string MIGRATION_PATH = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
        @"..\..\..\SampleticoDB");

    public static void MigrateToNewestVersion(string connectionString)
    {
        Migrate migrator = new Migrate().Set(c =>
        {
            c.Logger = new ConsoleLogger();
            c.ConnectionString = connectionString;
            c.SqlFilesDirectory = MIGRATION_PATH;
            c.SprocsFolderName = @"dbo\Stored Procedures";
            c.ViewsFolderName = @"dbo\Views";
            c.FunctionsFolderName = @"dbo\Functions";
            c.UpFolderName = @"Scripts\Migrations\up";
            c.RunBeforeUpFolderName = @"Scripts\Migrations\runbeforeup";
            c.RunFirstAfterUpFolderName = @"Scripts\Migrations\runfirstafterup";
            c.DownFolderName = @"Scripts\Migrations\down";
            c.Restore = false;
            c.Silent = true;
            c.WithTransaction = true;
            c.UsingVSDBProjectScripts = true;
        });

        var configuration = migrator.GetConfiguration();
        ApplicationConfiguraton.set_defaults_if_properties_are_not_set(configuration);
        migrator.Run();
    }

    public static void InitMigrationDbStructure(string connectionString)
    {
        Migrate migrator = new Migrate().Set(c =>
        {
            c.Logger = new ConsoleLogger();
            c.ConnectionString = connectionString;
            c.SqlFilesDirectory = MIGRATION_PATH;
            c.SprocsFolderName = @"dbo\Stored Procedures";
            c.ViewsFolderName = @"dbo\Views";
            c.FunctionsFolderName = @"dbo\Functions";
            c.UpFolderName = @"Scripts\Migrations\up";
            c.RunBeforeUpFolderName = @"Scripts\Migrations\runbeforeup";
            c.RunFirstAfterUpFolderName = @"Scripts\Migrations\runfirstafterup";
            c.DownFolderName = @"Scripts\Migrations\down";
            c.Restore = false;
            c.Silent = true;
            c.Baseline = true;
            c.WithTransaction = true;
            c.UsingVSDBProjectScripts = true;
        });

        var configuration = migrator.GetConfiguration();
        ApplicationConfiguraton.set_defaults_if_properties_are_not_set(configuration);
        migrator.Run();
    }
}

What about stored procedures?

What about them? It is simple, just add or update procedure, function or view in your database project. Roundhouse will take care of the rest. Let’s add new procedure to get all tasks

Adding new stored procedure - Visual Studio Database Project

Now we can run migrations

run_migrations.bat

And that’s it. To use this method you MUST remeber a few key concepts:

  1. All changes in the database schema must be done in the Visual Studio solution, never directly on the database.
  2. Changes in all databases must be done by running migrations.
  3. New database could be created by publishing database project and then running rh tools in the baseline mode. That will store information in the database of all already ran migrations.

Have fun.