revision control for database schema and configuration [closed]

Question

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

what tools do you use ?

Michael

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.