Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What version control methodologies help teams of people track database schema changes?

share|improve this question
    
What are you trying to accomplish here? Are you trying to alert people to changes or create an audit trail of who changed what and when? Something else entirely? –  ScottCher Jan 3 '11 at 20:57
    
@Scott - Essentially to be able to create almost like an audit trail for other devs - so we know we have the most up to date dev schema and that test and live sites can be easily checked to see what schema they are running. –  Toby Jan 3 '11 at 20:59
1  
This is simular to the question I just posted but I think they are different enough to answer them both dba.stackexchange.com/questions/64/… –  Beth Whitezel Jan 4 '11 at 1:12
1  
@BitOff - I agree and I think my answer to the question you referenced stands here as well. Using a tool like PowerDesigner or ERWin can help version control a database design - if the database is modeled externally, and all changes are planned through the model, you can better control and disseminate those changes for review. –  ScottCher Jan 6 '11 at 17:12
1  
Check out Liquibase or Flyway –  a_horse_with_no_name Sep 23 '13 at 8:56
show 6 more comments

5 Answers

up vote 36 down vote accepted

just a couple of minutes ago I was checking this: A table that should exist in all projects with a database, seems simple enough to put in practice, check it out:

It’s called schema_version (or migrations, or whatever suits you) and its purpose is to keep track of structural or data changes to the database. A possible structure (example in MySQL) is:

create table schema_version (
     `when` timestamp not null default CURRENT_TIMESTAMP,
     `key` varchar(256) not null,
     `extra` varchar(256),
     primary key (`key`)
) ENGINE=InnoDB;

insert into schema_version(key, extra) values ('001', 'schema version');

Whether you add this table from the beggining of the project or just after you’ve deployed the first version to a staging or production server is up to you.

Whenever you need to execute an SQL script to change the database structure or perform a data migration you should be adding a row in that table as well. And do that via an insert statement at the begining or end of that script (which is committed to the project’s code repository).

share|improve this answer
1  
@David - This is nearly a question in it's own right, but could you explain very quickly how the table could feedback to your VCS? Just with a manual dump? –  Toby Jan 3 '11 at 21:11
    
@Toby - with a deploy, the scripts were put in SVN and the developer provided the SVN rev or JIRA bug # - we (dbas) inserted their quick notes and rev # into this table. To tie it together, though, we created a web page to point to this table with a hyperlink to JIRA. –  David Hall Jan 3 '11 at 21:17
    
Yes. Basically the OP has it backwards - you use deployment scripts which you version, you do not work from database deltas. Those only work in degenerate (simplified) cases anyway - you may have to do multi step transforms to update a schema, which a tool can not generate backward. But deployment scripts can handle this. –  TomTom Sep 23 '13 at 7:59
add comment

SchemaCrawler is my tool to produce a text file with all of database schema objects. I designed this text output to be both human-readable, as well as diff-able against similar output from another server.

In practice, what I have found is that outputting a text file of the database schema is useful, when done as part of the build. This way, you can check the text file into your source code control system, and have a version history of how your schema has evolved over time. SchemaCrawler is designed to automate this too, from the command-line.

share|improve this answer
    
For oracle look at code.google.com/p/oracle-ddl2svn –  popalka May 7 '13 at 10:29
add comment

I think the best method is to have the database generated as part of your build process. Keep all of the scripts in source control with the rest of the code, and everyone is responsible for their own environments.

Failing that, RedGate has a tool to integrate source control into SSMS and SQL Compare is useful for comparing/synchronizing MS SQL Server schemas. Visual Studio Database Edition also has a built-in schema compare tool.

Another SO question lead me to Migrator Dot Net which I am going to begin investigating during my copious free time. It looks like a good method, but it might be more of a time/overhead investment than you are willing to make.

share|improve this answer
    
The "database edition" of Visual Studio used to be a separate product but is now included in editions including Team Server. Personally I prefer the RedGate tool (SQL Compare) for keeping them in sync. –  Tangurena Jan 4 '11 at 2:22
add comment

eiefai already mentioned A table that should exist in all projects with a database. This is a great blog post, but IMO it only goes part of the way to a working solution for database revision control. I think any attempt to "answer" this question in the real world needs to consider some of the other information about VCS and databases:

share|improve this answer
    
+1 - great list of Agile resources! –  AlexKuznetsov Feb 1 '12 at 2:10
add comment

There are a couple different angles to approach this question, I think. The "tool-first" angle, I believe, is going to vary based on platform and personal preference. Case in point: I'm using a Database Project in MS Visual Studio, but I'm not sure that this is a great solution for MySQL. I also know people who are pretty sold on their favorite tools from Redgate, Erwin, Embarcadero, etc.

There's also a "process-first" angle for this question, which will (hopefully) be revisited on this site in subsequent questions. The keystones in this process are getting your schema under source control and managing changes such that you can apply schema changes from version "x" to version "y" pretty much on demand.

A definitive answer to this topic is going to end up looking like a book, so it's probably worth starting out by referencing one: Redgate recently published a free ebook called "The Red Gate Guide to SQL Server Team-based Development", and while there's plenty in there to be debated, it's a pretty good place to start debating, IMO. Contrary to the name, much of the material in this book is general enough to apply to any DB (not just SQL Server) and any tool set (not just Redgate). If you haven't seen this already, it's definitely worth a skim, at least.

Finally, it's probably worth linking in the "legacy answer" from stackoverflow.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.