Database Change Management

One of the most interesting sessions at SQLBits was about managing change in databases. We still haven't reached the end-goal of a DBMS that prevents changes to objects until you check them out for editing, but it was the first time I'd heard of a solution which actually sounds solid enough to rely on.

When people talk about source-controlling their database they normally mean storing a set of CREATE scripts for every object in the database (there are some alternatives in this white paper). This approach gives you an audit trail so you can work out when an object changed, who changed it and why. Storing CREATE scripts also allows you to document your design using extended properties - it's dangerous to rely on SQL Server storing these, as they can easily be lost during upgrades.

The source-controlled scripts can be used to create an empty database but obviously aren't much use if you're regularly updating a production database - you can't just drop and re-create tables which have any data.

The main problem, though, is that this approach relies on discipline. If a developer or DBA makes a change to the development database and forgets to change the CREATE script then the source control loses its value.

Another problem is that upgrading the production system still requires a bunch of ALTER scripts for the changed tables. Either the developers have to write these at the same time as they make the changes or, when the system is upgraded, the DBA uses a comparison tool to ensure the production database ends up the same shape as the development one. The first of these involves three parts to keep in synch (database, CREATE script in source control and ALTER script for the upgrade) and the second leaves you in a position where the source controlled CREATE scripts are never checked against either development or production databases - although they may still be useful they could get horribly out of synch.

At SQLBits, András Belokosztolszki presented an enhancement to the process just described, which sounds like it might make the difference. András is the architect of Red-Gate's SQL Compare, so was obviously trying to sell the product, but it seems to be the last piece in the puzzle.

What the new version does differently is to compare databases against scripts. When I read about this feature it didn't strike me as particularly useful, but after talking to András I can see how powerful it is.

Firstly it removes the problem of developers/DBAs forgetting to update the CREATE scripts as they make changes. When the time comes for an upgrade to the production system, SQL Compare can be used to check that the source-controlled scripts reflect the development database. Anything that was omitted can be automatically synchronised, so you can be sure that your scripts are bang up to date.

Secondly there's no need to write the ALTER scripts. SQL Compare can compare the source-controlled CREATE scripts against the production database and can generate (and even apply) the scripts required to synchronise, maintaining any data. The scripts generated by SQL Compare 5 haven't always got dependencies in the right order so I'll be interested to see how version 6 does.

There's still a slight issue of losing incremental changes if an object is updated several times and only the final version is reflected in the script, so the fully source-controlled database remains the target. Until then, this seems like the best solution.

Thanks to András for a very interesting talk.

Thanks also to Simon and Tony for organising SQLBits.

Published 09 October 2007 11:02 AM by jonsayce
Filed under: ,

Comments

# Wanderer said on 16 October, 2007 11:35 AM

Jon,  thanks for the overview. I chose to do one of the alternative tracks, so I didn't see this presentation by Andras. It would be interesting to see how different it was to the presentation he gave at the SQL Server User Group in London, earlier this year.

Did andras cover how he/SQL Compare manages changes on significantly large objects (say for example a 500 million row table) ? Or is there a 90/10 (80/20) rule that says it'll do 90% of what your are likely to need, but the 10% that is unusual needs to be done manually?

# jonsayce said on 17 October, 2007 09:44 AM

Hi Wanderer - I don't remember any mention of large objects - I would guess that a 500 million row table would be altered in the same way as a 5 row table, so it would be up to you to change the script if you found the automatically generated one was too slow.

I think the 80/20 rule is probably the best way to look at it - although I haven't used version 6 yet, I'd be amazed if it always created perfect scripts. It sounds like it should do the bulk of the work though, which is good enough for me!

# Wanderer said on 18 October, 2007 01:35 PM

Thanks Jon,

I mention the sizing because I've seen products in the past the took the approach of creating a new table with the new schema, then "selecting into" the data from teh old schema to the new object, then renaming. That is a bad idea when you've got a 500 Million row table, and all you are doing is adding a nullable BIT column at the end, for example.

I think we are on the same page though - use it to generate, and then customize/replace the bits which , based on the DBA's expereince, will need to be handled as special cases. And test, test, test before going anywhere near live :D

# Jon Sayce said on 02 November, 2007 08:54 AM

Following an excellent post by Jeff Atwood on Coding Horror, I've found some further reading about database