Is there away (Cheap or FLOSS) to do version control of SQL Server 2008 DB schema?
- I've added a link to a SO post which explains doing version control through svn... Check it out in my edited answerreggie– reggie2011-06-30 15:50:54 +00:00Commented Jun 30, 2011 at 15:50
Add a comment |
3 Answers
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.
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. :-(
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
4 Comments
BonyT
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.
reggie
Thats the reason i added a link to point out to a list of tools that can be used to attain the same.
BonyT
Red Gate SQL Compare is a great product.
CervEd
Liquibase liquibase.org might be worthwhile to mention, I've just played around a bit but so far I really like it + it's free
Create a database project for the database, in Visual Studio. Check that project into a library system, such as SVN or Team Foundation Server.
4 Comments
OMG Ponies
+1: Beat me to saying "commit DDL to version control (IE: CVS, SourceSafe, SVN, Git, etc)".
abatishchev
@OMG: Yes, except CSV and SourceSafe. Never use it in 2011
Snow_Mac
Can I do this with an existing DB?
BonyT
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. vitalygorn.com/blog/post/2008/01/20/…
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.
