Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

What are the best practices for managing revisions to the database schema and configuration ?

what tools do you use ?

Michael

share|improve this question
1  
Which dbms are you interested in? You might also consider asking this (or searching for it) on stackoverflow.com –  squillman May 27 '09 at 19:40
    
yes, this was asked on SO. –  Assaf Lavie May 27 '09 at 20:20

5 Answers 5

up vote 5 down vote accepted

There is a great series of articles by K. Scott Allen via Coding Horror: Get Your Database Under Version Control about database version control. They have a SQL Server focus, but it could be applicable to other environments.

  1. Three rules for database work
  2. The Baseline
  3. Change Scripts
  4. Views, Stored Procedures and the Like
  5. Branching and Merging
share|improve this answer

I'm afraid I like the old school approach of using the native dbms tools to dump the schema to a text file and putting that in version control. I'm sure some of the fancy tools do a nice job, but my existing box of tools works just fine for my needs. I'd only consider the fancy stuff if I needed automated migration or something like that. YMMV.

share|improve this answer

The big thing for controlling this is to limit permissions and have a choke point through which deployment changes go. You can use a VCS system with a database, just be sure people check script files in and out. Even if they use a tool to make changes, they should get the script used to actually make those changes (SQL Server includes this capability, not sure about other platforms).

Save the script files like any other file for your app in a VCS.

share|improve this answer

i acquired a VBS script that uses SQL-DMO to script SQL Server's objects. It then uses SourceSafe integration to check them into source-control. Run it nightly and you have historical DDL.

C:\WINNT\system32\cscript.exe vssAgent.vbs theServerName

vssAgent.vbs (Link 1, Link 2, Link 3)

You'll have to customize some login/passwords in the vbs to match your VSS credentials.

share|improve this answer

Instead of storing the actual DDL scripts, I source control a description of the schema metadata that can be diffed against another database. I use SchemaCrawler - a free, commandline, open-source tool I wrote in Java, and designed for this purpose.

share|improve this answer

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.