Well first of all how much time do you spend upgrading your DB from version X to X+1?
Is it because you are doing a lot of actions?
Is it because the server is slow or too far?
How do you manage the process (the order of the steps and the scripts)? Excel? TFS?
Who does the Upgrading? A DBA? A programmer?
Who writes the scripts to upgrade the code? A DBA? A programmer? (I once had a system where a programmer wrote the scripts but an applicative DBA (me and two others) checked them)
Do you want something different?
No – Ignore this: if you are satisfied with the current status quo please don’t change it. This change is only useful if you need it.
Yes – Read this
FluentMigrator is a programming interface which allows quick and easy creation of changes in the DB for programmers with next to no experience in SQL (you will need some experience in a general DB system). All you do is save blocks of changes to DB in code with a marked version. This allows you quick and easy upgrading and downgrading your DB between versions.
All the coder need to do is write a class, like this:
- [Migration(201008240930)]
- public class User : Migration
- {
- public override void Up()
- {
- Create.Table("USERS").InSchema("GIS")
- .WithColumn("Id").AsInt32().PrimaryKey().Identity()
- .WithColumn("Name").AsString();
- }
- public override void Down()
- {
- Delete.Table("USERS").InSchema("GIS");
- }
- }
With an attribute with a long for the migration version number.
The class implements the Migration abstract class.
The Up method contains changes to the DB for upgrading the version.
The Down method contains changes to the DB for downgrading the version.
The syntax is very simple, after writing Create. You get by the wonder of IntelliScense the options of:
(Column, ForeignKey, Index, Schema, Table)
After Choosing table you get:
(InSchema, WithColumn)
(WithColumn)
With every action you get a different set of actions you can do from this point on. You already set the Schema? well FluentMigrator won’t let you enter it again, you skipped the Schema? FluentMigrator will enter the user’s default schema
On the next post I will write how FluentMigrator manages that in their code.
And that’s it. The console application is given to you from the framework.
Upgrading is as simple as running:
migrate -a Company.DbDeployment.Migrations.dll -db SqlServer2008 -conn "Password=PASS123;Persist Security Info=True;User ID=USER123;Initial Catalog=DB_NAME;Data Source=localhost" -profile "Debug" -t=migrate --version=23452351
Downgrading is as simple as running:
migrate -a Company.DbDeployment.Migrations.dll -db SqlServer2008 -conn "Password=PASS123;Persist Security Info=True;User ID=USER123;Initial Catalog=DB_NAME;Data Source=localhost" -profile "Debug" -t=rollback:toversion --version=0
(I actually enhanced the console a bit by removing the “-t” and relying only on the version number)
Well now lets answer the questions from the top of the post:
For the teams I have been in:
- The process of upgrading (we never did a downgrading) took between a few hours to a day, but when the server was far or slow that time usually multiplied.
- The list of actions to do was usually managed in Excel (scripts were saved on the TFS) and usually had lots of manual tasks. My current team used a notebook/human memory approach…
- The upgrading was always done by our applicative DBA.
- The scripts were written by programmers and DBAs.
With FluentMigrator:
- The Scripts are written by the programmers in code (right now it is only me but I am working on that…)
- Can be run by everyone (even the IT guy (who doesn’t know what is an index) can do it).
- The list of actions is managed in code in the TFS.
- The whole process is automatic meaning we don’t waste the time of a DBA
- The whole process can be run on the DB server meaning distance won’t be an issue (but if the server is slow it can’t be helped)
In the next several posts I am going to write about how the inside of the framework is written, how to extend the framework and even give you my extension for Migrating SDE layers (unfortunately I can’t share ArcEngine Dlls in Github because of licensing rights, so sharing the code that way will only mean it will never compile – a waste of time…).
Resources:
Getting Started With FluentMigrator : Your first migration
Github: FluentMigrator Project
Keywords: FluentMigrator, Database upgrade, DB upgrade, upgrade, downgrade, Fluent, example, unit test, console,slow server, distance, DBA, TFS
