3

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

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Snow_Mac
  • 5,727
  • 17
  • 54
  • 80
  • I've added a link to a SO post which explains doing version control through svn... Check it out in my edited answer – reggie Jun 30 '11 at 15:50

3 Answers3

7

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

Community
  • 1
  • 1
reggie
  • 13,313
  • 13
  • 41
  • 57
  • we tried that - like most MS products, I suppose it can work if your entire development system is set up "The Microsoft Way" - however, in practice we found it extremely limiting and pretty much useless. – BonyT Jun 29 '11 at 16:56
  • Thats the reason i added a link to point out to a list of tools that can be used to attain the same. – reggie Jun 29 '11 at 17:12
  • 2
    Red Gate SQL Compare is a great product. – BonyT Jun 29 '11 at 17:18
  • Liquibase https://www.liquibase.org/ might be worthwhile to mention, I've just played around a bit but so far I really like it + it's free – CervEd Nov 21 '21 at 10:55
1

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

Mike Christian
  • 1,526
  • 1
  • 15
  • 27
  • 2
    +1: Beat me to saying "commit DDL to version control (IE: CVS, SourceSafe, SVN, Git, etc)". – OMG Ponies Jun 29 '11 at 16:47
  • @OMG: Yes, except CSV and SourceSafe. Never use it in 2011 – abatishchev Jun 29 '11 at 16:49
  • Can I do this with an existing DB? – Snow_Mac Jun 29 '11 at 16:51
  • 1
    I don't know if they've fixed it yet - but we had major problems using database projects - see this article regarding data loss on incremental updates. http://www.vitalygorn.com/blog/post/2008/01/20/How-to-make-incremental-DB-update-script-in-VS2008.aspx – BonyT Jun 29 '11 at 17:05
1

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.

BonyT
  • 10,750
  • 5
  • 31
  • 52