.net - Using FluentMigrator with an existing database -

i'm looking migrations framework work existing project uses .nettiers, older orm requires codesmith generate data access code.

we have experience of roundhouse, , have had success in using it. able automatically deploy schema changes when running deployments out of octopus deploy. easy since collection of sql scripts.

i have been interested in moving fluentmigrator. fm dsl , found this question quite useful, there couple of things don't grok:

1. what right way import existing database schema [*]?
2. what right way deploy migrations production environment [**]?

[*] assumption generate single script using sql server tools , use executeembeddedsql initial migration. correct?

[**] there appears 3 main ways run migrations (command line, nant runner, msbuild runner). need access database can run. imagine want deploy prod environment. developers , build server has no access environment. how run these runners against environment?

our usual deployment process produce collection of sql scripts need deployed part of deployment. ops run these part of deployment, either automatically part of octopus deploy process (powershell), or manually run if deployment outside octopus).

one complication have in particular project .nettiers. means have run codesmith code generation using .nettiers build data access layer before can code against entities , data services. our workflow therefore have be:

1. write migration
2. run migration upgrade database (target specific .nettiers database)
3. run .nettiers against specific .nettiers database (central build server)
4. code against newly .nettiers generated entities, db fields, etc

i'd love dump .nettiers, refactoring sadly isn't viable option.

i have solved this. full outline solution can found @ http://benpowell.org/deploying-database-migrations-in-net-using-fluentmigrator-teamcity-and-octopus-deploy/.

i have summarised blog post below. of problems lack of understanding concerning fluentmigrator. i'll pick out original questions one-by-one.

what right way import existing database schema?

i couldn't find 'right way', find way worked me! made following core decisions:

1. i scripted off entire database baseline. included tables, procs, constraints, views, indexes, etc. setup first iteration baseline. chose create option without drop. migration up.
2. i ran same script dump choose drop only. migration down.

the baseline migration has use embeddedscript method execute attached script (i organise scripts iteration folders 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 deal .nettiers

ok, of challenge. created specific .nettiers database use run .nettiers code generation. in fluentmigrator can 'tag' migrations. decided tag based on environments. hence have 'tiers' tag tags 'dev', 'test', 'uat', 'prod', etc. how these run follow later.

when making schema changes create migration , use tag 'tiers' focus on .nettiers schema change. run migrate.exe out of visual studio external tools using specific tag flag. app.config database connection matches machine name database connection used, point @ tiers database. migrate has run .nettiers source database ready. can run .nettiers codesmith code generation tool produce new dlls.

.nettiers solved...

what right way deploy migrations production environment?

i using octopus deploy , honest, if deploying .net applications, multiple servers, should absolute go-to-tool doing so!

i won't go details of octopus deploy, @ basic level you can hook teamcity , octopus deploy together. od provide 2 items going.

1. a program called octopack wraps application nuget package.
2. a teamcity plugin makes teamcity build nuget package , offer artifact exposed on nuget feed.

octopus deploy consumes nuget feed , can deploy packages endpoint servers. part of deployment process running predeploy , postdeploy powershell script. in here going run migrate.exe application specific tags.

deployment solved...