11 Tools for Database Versioning

'DTM Schema Comparer' on Thu, 14 Sep 2006 03:44:08 GMT, sez:

it's not bad.

'Dan F' on Thu, 14 Sep 2006 04:16:07 GMT, sez:

We use a homegrown nant script + vault nant tasks + a homegrown exe that uses the redgate libraries to script each database object to a file. It's not perfect (damn thing crashes often) but at least it gives us a once a night checkpoint of the current state of the database world. Couple that with the history views in vault and we can zoom in on changes pretty easily.
On top of that we use the redgate products heavily when rolling patches to clients. I can't believe we ever used to "maintain" databases without them!

'Paul Coddington' on Thu, 14 Sep 2006 04:29:11 GMT, sez:

At present I am working in SQL 2000, so I am using my own generic NAnt scripts calling built-in SQL Server command line tools to dump scripts for each object, clean them up internally, and place them into structured folders with nice filenames for each object.

This works well and cost me nothing but time to implement. It is easily integrated with Subversion and can script and create any number of databases defined within the project folder very quickly at a single click.

DB Ghost is the best of what I have tried of the commercial tools, so far, though it has some annoying glitches - for example, machine names and paths are encoded into the scripts, so you still need NAnt to clean them up to become 'generic' so they will work on each developers local machine (and the server) without alteration.

The overwhelming strength of DB Ghost is, of course, the ability to merge differences into an existing database, but this does not come cheap and is well beyond the budget of IT contractors, such as myself.

Some of the other tools I have tried are woefully incomplete for the hefty prices charged - for example, some only do stored procedures and nothing else. By contrast, my home-brewed script does every object and lets you selectively version data as well. For this reason, I am not impressed with some of the offerings out there.

'Jeff Atwood' on Thu, 14 Sep 2006 05:05:02 GMT, sez:

You forgot the upcoming Visual Studio Team System Database Edition. It does full version control of all database objects *and* it includes schema/data comparison and script generation tools, too.

Oh yeah and you can write unit tests against your database with it.

All in all, SUPER FREAKIN' SWEET.

'lb' on Thu, 14 Sep 2006 05:09:16 GMT, sez:

>Visual Studio Team System Database Edition

something else to try! i am too short of time.

'Mike Woodhouse' on Thu, 14 Sep 2006 07:56:12 GMT, sez:

On my "to-do" list is investigating the Migrate bits of Ruby on Rails, which looks like an interesting approach.

'Jiv' on Thu, 14 Sep 2006 08:47:46 GMT, sez:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/TEDBPro.asp

Yeah i too played around with Data Dude ... last weekend, looks decent but would be a while before it catches up with Red Gate compare.

Its in CTP 5 right now.

'Casey Barton' on Thu, 14 Sep 2006 10:55:30 GMT, sez:

I use an approach like Paul's (building the DB from a set of text files kept in conventional source control) and I'd never go back to a system that wires itself into your database.

I'm using a VBscript db build script that was on MSDN: http://msdn.microsoft.com/msdnmag/issues/04/09/CustomDatabaseInstaller/ I've been customizing it as I need more functionality.

The chief advantage is that the db version control is completely integrated with the rest of your app. The database is just another component in your application, why should it be off in its own repository? Labels and patches then cover all aspects of your app. And in Subversion, when I commit codependent db and code updates, the commit will succeed or fail as a unit and never break the build.

'http://' on Thu, 14 Sep 2006 11:39:12 GMT, sez:

Check out SQL Delta. I swear by it for SQL Server.

'Tom Pester' on Thu, 14 Sep 2006 11:44:03 GMT, sez:

I have used red gate sql compare (and data compare) for a while which are good products.

But sadly they charge a lot of money for updates that they release very often and are not worth it. They abuse their position as market leader atm...

I bough the apex version for this reason and I could upgrade from redgate at a special price. I believe it was 160 dollars or so.

Although apex is not perfect, the people over there provide a support that is unprecedented and they give basicly a life time of upgrades.

So I advice both red gate (which I consider better in terms of usability and slighlty better in quality) and apex sql (they listen and act upon what you say).

Its also important to note that the scheme comparision as well as data comparision is integrated in apex.
If you go for the red gate bundle this will set you back $490 dollars so apex is better value for money.

'Leonardo Herrera' on Thu, 14 Sep 2006 12:50:46 GMT, sez:

We use a cron job that runs a perl script that extracts all the procedures from our Sybase database, checkout a current version from our CVS, copy the extracted procedures over the CVS files, perform a cvs update, and if there are any differences (cvs diff), commit them. Kind of cumbersome but very effective.

'Mike Hadlow' on Thu, 14 Sep 2006 17:37:13 GMT, sez:

Thanks for the mention Secret Geek! Just to fill in a few more details about DbGhost. It was developed by a guy I worked with at NTL (here in the UK), Mark Baekdal. He started the company with a couple of the other guys there, Malcolm Leach and Abdul Baruwa. They're all top notch developers and Malcolm especially has been really helpfull when I've introduced DbGhost into subsequent projects. I'd recommend trying it out if you're looking for that kind of tool and if you're in the UK and looking for large scale project build management consultancy give Malcolm a call, he almost single handedly saved the Harmony project at NTL.

Saying all that, I haven't looked at the team system stuff that Jeff Atwood mentions above. I'll have to investigate.

'http://' on Thu, 14 Sep 2006 20:40:14 GMT, sez:

http://www.sqlmanager.net/products/mssql/dbcomparer

looks good. Does anyone have experience with this?

'Heiko Leuze' on Fri, 15 Sep 2006 12:47:20 GMT, sez:

I'm using the light version of "xSQL Object 2" (compares schema and synchronise it) and "xSQL Data Compare 2" (compares data and synchronise it) from www.x-sql.com.

Until now it always did a good job for me !

'Dale' on Fri, 15 Sep 2006 21:33:58 GMT, sez:

DeZign for Databases - http://www.datanamic.com/

30-day free trial download
Pricing starts at $229

'Chris' on Mon, 18 Sep 2006 23:11:42 GMT, sez:

All i do is in sql 2000 use right click, all tasks, generate script, 1 file per object.

I script this to a DB directory and include it in normal source control (svn).

It works great, will show table changes, index changes, sp changes, etc.

Since migrating a few projects to 2005 this script function is no longer available. It will be returned in the next service pack, but for the moment i have written a custom SMO app which achieves the same purpose.

'Steve Hebert' on Tue, 19 Sep 2006 06:59:09 GMT, sez:

Another option is using a Database Project is Visual Studio.

Schema upgrade scripts must be capable of running multiple times on the same server without erroring out.

I blogged the process of gathering the scripts for redistribution here...

http://codebetter.com/blogs/steve.hebert/archive/2004/11/11/31689.aspx

'John G' on Wed, 20 Sep 2006 02:03:42 GMT, sez:

I have been using the free lite edition of xSQL Object (www.x-sql.com) - so far it's been great. The interface takes a bit getting used to but they have some cool features that have come in handy at times, like scripting schema and data together, taking snapshots of the schema etc. By the way, the "official" tool for our department is Embarcadero's Change Manager but xSQL Object (I compared the full edition) seems to outperform it - hands down!

'Martin Lange' on Mon, 16 Oct 2006 01:14:43 GMT, sez:

I'm another DB Ghost fan. Like Mike Hadlow, I worked at NTL on the Harmony project where I was introduced to DB Ghost. I can't rate it highly enough. Mike's right, DB Ghost saved the Harmony project.

The main point about DB Ghost is that it doesn't just do version control. What makes it unique is that it's a compiler for your database source code. DB Ghost can verify all your database source code BEFORE you check it in.

In 2004 I left NTL and returned to Canberra (Australia) where I'm working as a technical lead for a .NET development team. I introduced DB Ghost here and the team have never looked back. We use it daily. In fact, DB Ghost has been so successful that it's been adopted as a standard development tool for the entire IT department. To learn more about what we did, go to http://www.dbghost.com/compare_sql_case_studies_dest.asp.

Do yourself a favour, get yourself an eval copy of DB Ghost and check it out.

Martin.Lange@DEST.gov.au

'Me' on Thu, 14 Dec 2006 05:54:19 GMT, sez:

What about powerdesigner?

'Max Guernsey, III' on Mon, 14 Jan 2008 18:49:57 GMT, sez:

I use Hexagon Software DataConstructor (which, admittedly, I wrote). With it, I'm able to develop my database using TDD. I use one database create script for my acceptance tests, database unit tests, transition tests (testing getting from one version to another), test servers, and production. Because my the exact database upgrade path I use is tested about a thousand or more times in a sprint and the same for every instance of my databse, I never sweat a database deploy: it's just like rolling out any other part of my application.

'SUMsoft Solutions' on Tue, 15 Jan 2008 06:18:59 GMT, sez:

Schema Version Control for Oracle (SVCO)

It's the integrated version control solution for Oracle database server schema objects! You can track all schema objects changes direct within Oracle database. No extra version control tool is required. You can go on and use your favourite IDE for Oracle or even mix different tools. You can also integrate Schema Version Control for Oracle in your own development process.

'lb' on Tue, 15 Jan 2008 06:22:22 GMT, sez:

"SUMsoft" -- you've read their spam, now try their software

I censored my own comment here... too harsh. Sum soft looks alright

'Max Guernsey, III' on Tue, 15 Jan 2008 22:16:24 GMT, sez:

lb - I know. Seeing as how I just posted a similar comment, I'm inclined to give them a break, though. It's hard to get the word out, man. ...and, if you think you have a product that can help people, it's not altogether ignoble.

-- Max

'lb' on Tue, 15 Jan 2008 23:32:25 GMT, sez:

@max, @SUMsoft

guys i really apologize -- i was way too harsh.

'Max Guernsey, III' on Wed, 16 Jan 2008 13:31:27 GMT, sez:

lb - If someone cannot chuckle at what you posted, which was actually quite funny, then they are not cut out to build a business.

-- Max

'Albert' on Mon, 04 Feb 2008 11:26:02 GMT, sez:

+1 for xSQL Object & xSQL Data Compare from www.xsqlsoftware.com

'Andreas Jansson' on Tue, 05 Feb 2008 10:09:17 GMT, sez:

I can recommend SQL Examiner for comparing databases and generating update scripts. I found it to be the best when I compared a number of similar tools about a year ago, and I use it frequently.
Have a look at it: http://www.sqlaccessories.com/Default.aspx
Costs about $300 for a one-user license.

'michael rosquist' on Wed, 27 Feb 2008 09:43:37 GMT, sez:

Recommends Power Designer (12.5)
Excellent reporting and drawing facilities.
A very good script generating facility with good way to customize db object names.

'gk' on Sat, 01 Mar 2008 08:32:44 GMT, sez:

Anyone knows anything about mySQL Version Control??

name


website (optional)


enter the word:
 

comment (HTML not allowed)

All viewpoints welcome. But the right to delete any post for any reason is reserved. Don't make me do it. Comments may be republished, emailed to your loved ones or printed and used as toilet paper. Who reads this legal bit anyhow?

TimeSnapper_{Know thyself!}

TimeSnapper is a life analysis system that stores and plays-back your computer use. It makes timesheet recording a breeze, helps you recover lost work and shows you how to sharpen your act.

TimeSnapper won last year's Developer Competition at Larkware.com, and is used by over 10,000 people.