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??

'The Daily WTF' on Wed, 28 May 2008 21:21:40 GMT, sez:

Quote From TheDailyWtf (http://thedailywtf.com/Articles/Overdue-Retirement.aspx)


I looked at the SQL Server database they have behind their web product and asked 'How is version control done?' The exact response (not a single word of this is a lie):

"'We upload a version of the database to one of the customer websites and if it works we gradually copy it over to the other customer websites. We don't install it on all of them so that if we need to look at an older database version we know which customer website will have it and we just copy that back.'

'Thomas' on Mon, 28 Jul 2008 10:04:30 GMT, sez:

This looks interesting:

http://code.google.com/p/migratordotnet/

1. Open source
2. Inspired by Rails ActiveRecord
3. Handles migration roll-forward/roll-back

Will trial it over the next few months

'Alexander Karmanov' on Thu, 18 Sep 2008 16:42:11 GMT, sez:

Almost all of those tools address problem of synchronization/existing database update too late.
I created a tool for myself that updates the database from scripts introduced via change log.
Files in my case are organized not by type (SP vs. UDF vs. table) but rather grouped by purpose in config files. Those config files are linked to each other. Thus (1) the script deploying database is quite simple, (2) it is stored in TFS/SourceSafe/Subversion easily.
See article - there is the tool itself and example folder/file structure.

'Tiziano Mengotti' on Thu, 29 Jan 2009 10:48:36 GMT, sez:

hi, if you create your sql scripts by hand and want to manage them, you might be interested in deltasql.

It is a webpage where developers submit all scripts in a centralized manner. On each database schema there is a special table that stores the version of the latest executed script.

The software is open source and can be tested at http://www.gpu-grid.net/deltasql
(if you login as admin with password testdbsync)

To download the tool, point your browser to http://sourceforge.net/projects/deltasql

'itamar' on Fri, 27 Feb 2009 18:20:14 GMT, sez:

Nob Hill's SQL version control.

http://www.nobhillsoft.com/Randolph.aspx

'Rob G' on Sat, 28 Feb 2009 05:27:36 GMT, sez:

We are using dbMaestro TeamWork (Oracle version).
It allows checkin/checkout on DB objects + bunch of all kind of change management actions, like version comparison, rollbacks, merging, deployment scripts generation, code source control tools integration (it's TFS in our case),etc.

Real nice.

http://www.dbmaestro.com/DbMaestro/products/1/6/448.aspx

'Frank' on Fri, 08 May 2009 12:55:00 GMT, sez:

We are using Sync For SQLServer from SleepyAnt Software. It is the basis for the complete db update process. It simply creates an SQL script which changes the schema from one version to the next version. We then create exact version transition packages from e. g. version 1.3 to 1.4. You can also take over data from one version to the other.

'Nathan Rozentals' on Mon, 27 Jul 2009 02:22:19 GMT, sez:

Hi All,
I've used Visual Studio for Database Pros for a few projects, but some of my clients are not able to afford the price tag.
So for SQL Databases, I've written my own tool using SMO objects and simple dos .bat files.
Have a look:
http://dbsourcetools.codeplex.com/
- Nathan

'David Atkinson' on Mon, 19 Oct 2009 07:04:53 GMT, sez:

Red Gate is developing SQL Source Control, a SQL Server Management Studio add-in that links a development database with a source controlled representation, providing object-level committing and retrieval. Although the official release won't be available until 2010 there will be early builds available. If you're interested, register your interest at:
http://www.red-gate.com/products/SQL_Source_Control/index.htm

David Atkinson
Product Manager
Red Gate Software

'MikeK' on Tue, 27 Oct 2009 16:34:44 GMT, sez:

We use a modified DbDeploy java progarm. Allow for incremental changes to database that are aligned with middle tier development. Also allows for a unified deployment process.
see the google project at:
http://code.google.com/p/dbdeploy

'René de Bloois' on Fri, 19 Feb 2010 14:54:31 GMT, sez:

Consider using SolidBase. SolidBase delivers real version control using ordinary SQL. Supports multiple upgrade paths. Ant task and Maven plugin available.

See: http://code.google.com/p/solidbase/
Or: http://solidbase.org/

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.