Is there a version control system for database structure changes?
In Ruby on Rails, there's a concept of a migration -- a quick script to change the database.
You generate a migration file, which has rules to increase the db version (such as adding a column) and rules to downgrade the version (such as removing a column). Each migration is numbered, and a table keeps track of your current db version.
To migrate up, you run a command called "db:migrate" which looks at your version and applies the needed scripts. You can migrate down in a similar way.
The migration scripts themselves are kept in a version control system -- whenever you change the database you check in a new script, and any developer can apply it to bring their local db to the latest version.
Do you use source control for your database items?
Must read Get your database under version control. Check the series of posts by K. Scott Allen.
When it comes to version control, the database is often a second or even third-class citizen. From what I've seen, teams that would never think of writing code without version control in a million years-- and rightly so-- can somehow be completely oblivious to the need for version control around the critical databases their applications rely on. I don't know how you can call yourself a software engineer and maintain a straight face when your database isn't under exactly the same rigorous level of source control as the rest of your code. Don't let this happen to you. Get your database under version control.
How can I do version control of Database Schema?
Here is a nice article by Jeff Atwood on database version control
You can use Team edition for database professionals for this purpose
Here is a list of tools that you can purchase which can be used too:
Red Gate SQL Compare from $295.
DB Ghost from $195
SQL Change Manager $995 per instance.
SQL Effects Clarity standard ed. from $139
SQLSourceSafe from $129.
sqlXpress Diff contact for price. :-(
Embarcadero Change Manager contact for price. :-(
Apex SQL Diff from $399
SQL Source Control 2003 from $199
SASSI v2.0 professional from $180
Evorex Source # shareware or $299+ (conflicting reports!)
Edit Just found this post which explains version control through svn: Versioning SQL Server database
How do you version your database schema?
See
Is there a version control system for database structure changes?
How do I version my MS SQL database in SVN?
and Jeff's article
Get Your Database Under Version Control
I feel your pain, and I wish there were a better answer. This might be closer to what you were looking for.
Mechanisms for tracking DB schema changes
Generally, I feel there is no adequate, accepted solution to this, and I roll my own in this area.
How to version control a record in a database
Let's say you have a FOO
table that admins and users can update. Most of the time you can write queries against the FOO table. Happy days.
Then, I would create a FOO_HISTORY
table. This has all the columns of the FOO
table. The primary key is the same as FOO plus a RevisionNumber column. There is a foreign key from FOO_HISTORY
to FOO
. You might also add columns related to the revision such as the UserId and RevisionDate. Populate the RevisionNumbers in an ever-increasing fashion across all the *_HISTORY
tables (i.e. from an Oracle sequence or equivalent). Do not rely on there only being one change in a second (i.e. do not put RevisionDate
into the primary key).
Now, every time you update FOO
, just before you do the update you insert the old values into FOO_HISTORY
. You do this at some fundamental level in your design so that programmers can't accidentally miss this step.
If you want to delete a row from FOO
you have some choices. Either cascade and delete all the history, or perform a logical delete by flagging FOO
as deleted.
This solution is good when you are largely interested in the current values and only occasionally in the history. If you always need the history then you can put effective start and end dates and keep all the records in FOO
itself. Every query then needs to check those dates.
Database structure and source control - best practice
We have all our database objects under source control using Visual Studio Database Edition (DBPro). It is a wonderful tool that version controls our schema, does builds, validations, allows code analysis, schema comparisons, deployments, data comparisons, refactoring etc. It was designed from the ground up to be a DB management and version control system. Highly recommended.
This is the blog site of the lead architect for DBPro:
click here
How do you track database changes in source control?
Use Visual studio database edition to script out your database. Works like a charm and you can use any Source control system, of course best if it has VS plugins. This tool has also a number of other useful features. Check them out here in this great blog post
http://www.vitalygorn.com/blog/post/2008/01/Handling-Database-easily-with-Visual-Studio-2008.aspx
or check out MSDN for the official documentation
Related Topics
How to Set Ignore_Dup_Key on for a Primary Key
Ora-00907: Missing Right Parenthesis
What's the Execute Order of the Different Parts of a SQL Select Statement
Does SQL Join Order Affect Performance
Error 1111 (Hy000): Invalid Use of Group Function
Best Equivalent for Isinteger in SQL Server
Table or Column Name Cannot Start with Numeric
When to Use Except as Opposed to Not Exists in Transact SQL
Sql: How to Select Only Groups That Do Not Contain a Certain Value
How to Remove Extended Ascii Characters from a String in T-Sql
Why Does Varchar Need Length Specification
Split String and Take Last Element
Regular Expression to Match Common SQL Syntax
Delete Parent If It's Not Referenced by Any Other Child