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:
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
- 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.
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?
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.
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,
06-13-2011 7:23 AM