How can I do version control of Database Schema?

问题:

Is there away (Cheap or FLOSS) to do version control of SQL Server 2008 DB schema?

回答1:

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

回答2:

Create a database project for the database, in Visual Studio. Check that project into a library system, such as SVN or Team Foundation Server.

回答3:

In my experience there is no easy option in an enterprise environment.

The three methods below are the main choices (irrespective of tool set used).

1) Dump entire schema into a file and store file in repository

PROS: Easy

CONS: Big file - difficult to manually edit - hard to see what has changed since last version - can't deploy it so would need some mechanism to prepare a DIFF script between Dev and Test/Live systems

2) Dump every database object into a separate file, stored in repository.

PROS: Very easy to see what has changed. Can produce deployment scripts for most objects easily (although some things would still require DIFF script e.g. Column Definition changes)

CONS: Have to run scripts in a certain order - managing that process can be quite difficult.

3) Treat every change as a separate operation with it's own sequentially numbered SQL script.

PROS: Easy for devs to create scripts, same scripts can be run against each platform (in theory)

CONS: Nightmare to manage - ordering can become an issue, very difficult to see what has changed in a release, or when a given object changed.

Having run with all 3 options, I would say that 2 was lovely to work with, but took ages to set up in the first place - getting all the scripts executed in the correct order took ages - and it STILL required use of a Database diff tool to generate scripts for UAT/Live. So I would now recommend a mix between 1 & 2.