Derik Whittaker

Syndication

News


Database Migrations–Picking a strategy and getting the ball rolling w/ RoundhousE

Anytime you use a relational database it is always a challenge to keep your changes in sync between your different environments.  If you work with what I would call a typical setup you have your local database, your shared development database, your shared qa database, possible a shared unit/integration test database and your production databases. 

When making changes to any one of these you of course need to push these changes to the others and this can be simple, or it can be tricky and complex.  Now the trick is how do you propagate your changes from environment to environment?  I guess there are multiple ways you can do this including (and not limited to):

  • Manual changes, hand copy from database to database
  • Scripted changes which are run via some command line tool (i.e. nant, or something similar)
  • Scripted changes which are run via some migration tool
  • Migrated changes with a commercial tool such as RedGate’s Sql Compare

Honestly all the above options are valid, can get the job done and can allow you to be successful.  Of course each of them provide their own list of trade-offs.  In my mind option #1 is for the birds as it is way to error prone and just asking for failure.  #2 is a great option, but why would hand roll the updates and ‘up/down’ logic when #3 provides tools to accomplish this for you.  #4 is awesome, but many places do not want to shell out any money to provide its developers/dba’s with a tool to simply do database schema migration (hey I know that is being short sided but lets face it many companies are this way).

Recently at work we decided that we wanted to move away form #1 and move to a more automated process.  We quickly decided that we did not want to purchase any commercial tools, less from a cost perspective and more from a ‘we are not 100% SURE what we need just yet so why spend money’.  we also knew that we did not want to hand roll our own script running process which pretty much leaves us with #3.

Since we had made the decision to use a scripted migration tool the first question was which one?  A quick google search this will show there are a few out there including (and not limited to):

After looking at these we made the decision to move forward with RoundhousE, of course I feel that we could have chosen any of these and we could have been successful.  I guess the real question is why did we choose RoundhousE over the others?  Below is a brief list of some of our thoughts:

  • Documentation
    When we started to look at the various OSS projects listed above some of them had NO documentation that I could find (looking at you RikMigrations) while others had decent documentation (RoundhousE does not have a ton online, but they did provide a great ‘getting started’ word doc).  I know that everyone says that the code or examples should provide enough documentation, but I personally call BS on this.  When learning a new tool, especially an OSS one, I don’t want to spend my time pouring though some code examples to simply see how to get up and running.  I want a simple, easy to follow guild to at least get me up w/ the basic features.

    Oh, it also never hurts if you are buddies w/ the author as this allows you to reach out to them for help if you are stuck Smile
  • Ease of Use
    One would think that this would be a given, but in many cases w/ tools the others really do not think about ease of use from the perspective of an outsider.  I can say that after reviewing the above tools they all seemed pretty easy to use and implement.
  • Migration Technology
    This may be the most important component for OUR team in terms of picking a migration tool.  Our team is strong in sql and our preference was that we could find a tool which allowed us to script our DB changes via SQL and not some other language.  When we looked at some of the others they wanted you to script your changes in JSon or via some Fluent Interface.  No of course there is NOTHING wrong w/ a framework which uses non-sql to perform its migrations it was just not what our team was looking for.  This was our decision and it may not be what you are looking for or even care about.
  • Features
    Of course anytime you are looking at using any new library you need to consider what the library does and what YOU need the library to do.  It is possible to find the perfect library but if it does not meet your needs it is useless to you.
  • Commit/Change History

    When it comes to any open source project I am always interested to see how often is maintained or updated.  If I find a project which has sat stale for a long period of time (typically greater than 1 year) I am always skeptical that it is dead.  And a dead project means it will not be maintained, fixed or extended (yes I know I could take ownership of the project, but to be perfectly honest that is NOT high on my priority list when first looking at a new tool).

Given the list of our thoughts above, what made us choose RoundhousE?

  • Documentation
    To be perfectly frank non of the above libraries had a ton of great documentation.  However, RoundhousE does have a great word document which outlines exactly what is needed to get started using the library.
  • Ease of Use
    After playing w/ a few of the libraries we just felt that RoundhousE was the simplest to use.  You pretty much just put your sql scripts into a series of folders and run the .bat executable.  Of course there is some setup needed to tell RoundhousE to point a given database, but this is pretty trivial stuff.
  • Migration Technology
    As I stated above our team is very comfortable w/ SQL and we already have a series of sql migration scripts so when we found a tool which would allow us to leverage our skills we already had we were all set.  Again, this was a decision that made sense for our team so your team may make a different decisions and that works too.
  • Features
    Of course features are #1.  The features we liked about RoundhousE are
    • Ability to create migration scripts via sql
    • Ability to ‘up’ migrate (we really did not care about down migrations at this point)
    • Ability to create environment based scripts, scripts which can be targeted towards given environments or servers.
    • Ability to script the execution on the migration
    • Ability to tweak pretty much every one of the default settings in the migration tool
  • Commit/Change History
    When looking at the various libraries RoundhousE had by far the most recent changes and improvements.  This tells me that it is being used and it should be being maintained over time.

Now that we have picked out migration tool it is time to put it to the test and get it working.  I hope my thought dump can help you make a decision on which migration library to use and keep in mind I am sure that all the libraries are great and do a wonderful job, but it is important that when looking at the various ones you have your list of ‘wants’ in mind.

Till next time,


Posted 06-13-2011 7:23 AM by Derik Whittaker

[Advertisement]

Comments

Sri wrote re: Database Migrations–Picking a strategy and getting the ball rolling w/ RoundhousE
on 06-13-2011 10:56 AM

Thank you.

gg wrote re: Database Migrations–Picking a strategy and getting the ball rolling w/ RoundhousE
on 06-14-2011 5:46 AM

That typo proves your point nicely: "way to error prone"

Dan Sutton wrote re: Database Migrations–Picking a strategy and getting the ball rolling w/ RoundhousE
on 06-14-2011 12:59 PM

I wrote a language to do that kind of migration, once. You gave it the structures of the tables in each database, what the keys are, and how one relates to the other - it could also split source records into entire hierarchies, and so on, if it felt like it. The language would compile into SQL, VB6 or FoxPro (this was a long time ago!) and you could include what it generated in your programs (it thought on a record-by-record basis). Can't remember what I did with it now... there's probably a copy of it lying around somewhere.

Chris Anderson wrote re: Database Migrations–Picking a strategy and getting the ball rolling w/ RoundhousE
on 06-19-2011 1:30 AM

Excellent article.  My current project is doing something between #1 and #4 between 3 environments, using an OSS tool DBDiff (http://dbdiff.codeplex.com/) to assist in identifying and constructing change scripts, then applying through SSMS.  

Will tag myself to take a look at RoundHouse, especially to get some support for versioning.

On a side note, having the tool script database changes in anything other than SQL (i.e. in JavaScript, .Net, or some novel markup) would be a showstopper for me.  Our lives are complex enough without having to mentally switch into some other language and translate migration code into day-to-day operating database code.  Assuming the RDBMS world, of course.  

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)