Using FluentMigrator with an existing database

Question

I'm looking for a migrations framework that will work with an existing project that uses .NetTiers, an older ORM that requires CodeSmith to generate the data access code.

We have some experience of roundhouse, and we have had success in using it. We are also able to automatically deploy schema changes when running deployments out of Octopus Deploy. Fairly easy since it is just a collection of SQL scripts.

I have been interested in moving to FluentMigrator. I like the FM DSL and I found this SO question quite useful, however there are a couple of things I don't grok:

1. What is the right way to import an existing database schema [*]?
2. What is the right way to deploy migrations to a production environment [**]?

[*] My assumption is that I generate a single script using SQL Server tools and use ExecuteEmbeddedSql as the initial migration. Is that correct?

[**] There appears to be three main ways to run the migrations (Command Line, NAnt runner, MSBuild runner). They will need access to the database so they can run. Imagine we want to deploy this to a PROD environment. The developers and build server has no access to this environment. How do you run these runners against that environment?

Our usual deployment process is to produce a collection of SQL scripts that need to be deployed as part of the deployment. Ops run these as part of the deployment, either automatically as part of the Octopus Deploy process (powershell), or manually run if deployment is outside Octopus).

One complication that we have in this particular project is .NetTiers. This means that we have to run CodeSmith code generation using .NetTiers to build the data access layer before we can code against those entities and data services. Our workflow would therefore have to be:

1. Write migration
2. Run migration to upgrade database (target a specific .NetTiers database)
3. Run .NetTiers against the specific .NetTiers database (central build server)
4. Code against newly .NetTiers generated entities, db fields, etc

I'd love to dump .NetTiers, but a refactoring sadly isn't currently a viable option.

Ashia

Answer 1

I have finally solved this. Most of my problems were to do with a lack of understanding concerning FluentMigrator. I'll pick out my original questions one-by-one.

What is the right way to import an existing database schema?

I couldn't find a 'right way', but I could find a way that worked for me! I made the following core decisions:

1. I scripted off the entire database as a baseline. I included all tables, procs, constraints, views, indexes, etc. I setup my first iteration as that baseline. I chose the CREATE option without DROP. This will be my migration up.
2. I ran the same script dump but choose DROP only. This will be my migration down.

The baseline migration just has to use the EmbeddedScript method to execute the attached script (I organise the scripts into iteration folders as well).

[Tags(Environments.DEV, Environments.TIERS, Environments.CI, Environments.TEST)]
[Migration(201403061552)]
public class Baseline : Migration
{
    public override void Up()
    {
        this.Execute.EmbeddedScript("BaselineUp.sql");
    }

    public override void Down()
    {
        this.Execute.EmbeddedScript("BaselineDown.sql");
    }
}

Baseline solved...

How to deal with .NetTiers

Ok, this was somewhat of a challenge. I created a specific .NetTiers database which I would use to run the .NetTiers code generation. In FluentMigrator you can 'tag' migrations. I decided to tag based on environments. Hence I have a 'tiers' tag as well as tags for 'dev', 'test', 'uat', 'prod', etc. How these get run will follow later.

When making schema changes I create the migration and use the tag 'tiers' to focus on the .NetTiers schema change. I then run migrate.exe out of Visual Studio external tools using that specific tag as a flag. The app.config database connection that matches my machine name will be the database connection used, so I point it at the tiers database. Now my migrate up has run my .NetTiers source database is ready. I can now run the .NetTiers Codesmith code generation tool to produce the new DLLs.

.NetTiers solved...

What is the right way to deploy migrations to a production environment?

I am using Octopus Deploy and to be perfectly honest, if you are deploying .NET applications, especially to multiple servers, this should be your absolute go-to-tool for doing so!

I won't go into the details of Octopus Deploy, but at a basic level you can hook TeamCity and Octopus deploy together. OD provide two items to get you going.

1. A program called Octopack that wraps up your application as a NuGet package.
2. A TeamCity plugin that makes TeamCity build the NuGet package and offer it as an artifact exposed on a NuGet feed.

Octopus Deploy then consumes that NuGet feed and can deploy those packages to the endpoint servers. Part of this deployment process is running a PreDeploy and PostDeploy Powershell script. In here is where I am going to run the migrate.exe application with my specific tags.

Deployment solved...

Ashia

.sql file needs to have Build Action: Embedded Resource – Yates

@MortenHolmgaard correct, sorry, I missed that out. – Ashia

I've faced same issue as we started using FluentMigrator on an already mature Database. We also had some population of initial data so I used RedGates products to create scripts for both schema and data – Peddada

How to use for existing tables/procs etc...Let's say I pack all my queries for tables/procs into an sql.I run my first migration.MigrationUp throws error saying item already exists. How to get out of that? I can do a hack by adding the first migration to version table saying its already done? Will that be proper way? – Advertisement

@SrimanSaswatSuvankar Have you found anything solution to the problem you were facing? – Euphemia

@Euphemia I can't paste a screenshot in here, but if you look at the advanced options modal in MSSMS for Tasks -> Generate Scripts, then under "General" is the option "Check for object existence". – Ashia