Free tool to implement Scott’s schema version-control system described here : DbUpdater - Database Schema Version Control [http://www.tewari.info/2008/02/18/dbupdater-database-schema-version-control/]
Thanks. Good post and good links. I get all my work under version control, and only need backup version control database every day.
funny to read this. I have been doing version control for all applications for at least 4 years now. And you put this as a new big thing…
Howdy,
One of the problems with “getting your database under version control” is trying to make an analogy between the database and the directory tree that most source code projects use.
A couple of years ago I worked at a client where the vast majority of the development effort was in the database, either as schema or metadata. All the “software” bits were an afterthought. We developed at CM technique I have (arrogantly, self-interestedly) named Longacre Deployment Management.
The technique acknowledges that you just can’t “roll back” a big database. Instead, you roll forward – if you have to “fix” a problem, you fix it by delivering more changes. Sometimes those changes revert the database to a state very close to something that existed in the past.
The technique is documented at CM Crossroads, in an article from April of last year: “Technical Introduction to Longacre Deployment Management.” The url is http://www.cmcrossroads.com/content/view/7910/135/
try SQLVSS
integration solution for SQL Version Control
script database objects into sourcesafe.
http://www.tsqldev.com
I liked Ruby on Rails’ Migrations so much that I ported it to .NET…
Checkout the project on CodePlex: http://www.codeplex.com/dotnetmigrations
sdsd
I feel that the database server vendors should provide a -DEV switch for their server. This would handle version control inside the database server for large multi-user development teams. TFS is way to complicated to the average team and there are too many entry points to change a database object.
Very interesting blog and comments.
Currently we use redgate to carry forward schema changes.
I have a question for you who currently keep your db versioned:
Take this scenario:
A team has started working on a project with some new functionality that will go live in 8 weeks. In the first week a dev makes a change to some table schema, saves the .sql file (am I right here?) and checks this in to source control.
Now, a week later, an urgent production fix needs to go out that will involve changing the same tables.
Surely when the schema in source control goes live, there are chances of overlapping changes, meaning potentially the schema scripts that have been versioned will fail when they are run in to the live environment whereas they ran fine in test the first time?
I guess my misunderstanding here comes from the fact that at least with code you can merge your changes no matter where in the software cycle you are working whereas it seems here like the schema would ‘track’ the code, meaning it is effective for that branch in scm only with no relationship/effect to any other ongoing build?
Isn’t this just something a version control tool like a href=http://www.accurev.comhttp://www.accurev.com/a, a href=http://wwww.perforce.comhttp://wwww.perforce.com/a or a href=http://www.SVN.comhttp://www.SVN.com/a would integrate with rather than doing on their own?
You get the idea:
Isn’t this just something a version control tool like www.accurev.com or www.perforce.com or www.SVN.com would integrate with rather than doing on their own?
Even if per agile manifesto the tool does not matter, let me point to deltasql.
deltasql is able to tag sql scripts with a version number, and produce the SQL needed to upgrade a database from one version to another, which is very useful for upgrade scripts.
You can test deltasql here: http://www.gpu-grid.net/deltasql (if you login with user admin and password testdbsync)
deltasql can be downloaded at http://sourceforge.net/projects/deltasql
All,
dbMaestro (www.dbmaestro.com) makes a product called Teamwork which enables you to solve many of the issues mentioned in this article.
It provides database level locking of schema objects and enables check-in/check-out type operations. It also manages versioning of schema objects and deployement of objects.
I think I understand your last question. How we handle releases is that we have a release branch, the head of this release branch will mirror the production application and can be used to patch the current production release without introducing anything that is in development. Now this release branch would also contain any scripts needed for creating the database and change scripts. The urgent production fix (or change to the schema) would be done on this branch. Later on you would want to merge this fix back into the trunk to ensure that the next major release of your application also contains this fix.
---x
Obviously this blog is ugly formatting the posts, so for the picture in my post look here:
http://img134.imageshack.us/img134/9340/branching.jpg
You have discussed the simple part, which is putting the DB schema and/or scripts under source control. That seems obvious to me. No big problem to solve here.
The harder question, one actually more deserving of a blog, is the situation where the data itself is the product you are selling. Consider a situation in which the schema stays relatively stable or even constant for years and what you sell to your customers, and need to control, is a constantly changing proprietary knowledge base.
Some customers will still be on old versions, others willing to upgrade to the newest. Sure, anytime your stubborn customers have a problem you can force them to upgrade, but that isn’t practical. You need to correct data “bugs” in older versions of the data and re-release it. Further, when a customer does upgrade, if there are customer-created tables/DBs referencing your changed data, you may need to provide them a means to automatically upgrade the “relationships” between their tables and your data (e.g., if key fields such as IDs have changed).
Hi Jeff,
After many years of development, I also still get blank stares from developers.
To this end, I’ve released an open-source tool for SQL server databases:
http://dbsourcetools.codeplex.com/
Let me know what you think.
- Nathan
As others have pointed out, the analogy of “source control” to databases breaks down in several instances: branches, rollbacks, test cases, patching, and so on. Code is stateless and data is, well, state; because data is intermingled with schema, you sometimes have to treat the schema as immutable.
That’s not to say that effective database versioning isn’t a positive goal, but it’s a lofty one and it’s important not to fool ourselves into believing that we’ve attained it. What you get with existing tools is a very limited kind of version control: the ability to create a new database from scratch that’s compatible with any given version. That’s all. It’s great if you do a lot of new rollouts, but only marginally useful if you work on a long-term production system.
As long as it’s understood that having your database scripts in SCM isn’t going to afford the same protections as having your code in there, and that you should still be very conservative with schema changes, I think it’s probably a good thing to do if you have more than 3 or 4 developers. If you’re a pair or a one-man show, it’s probably not worth the effort, because the frequency of changes should be rare.
‘It doesn’t matter what tools you use’ What utter nonsense.
‘-- per the agile manifesto, individuals and interactions are more important than processes and tools.’
From the agile manifesto:
"… we have come to value:
Individuals and interactions over processes and tools"
Putting a value on some entity is not the same as ascribing importance to it.
A litre of water costs 1, a kilogram of food costs 2. Which one is more important?