Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

On Database Migrations and Agility

By Phil Factor,

There is a reason why we like to make a good attempt at designing a database up-front, rather than evolving it from a vague, woolly idea of what it maybe ought to look like: it’s the data and its domains. For an empty database and a single developer, it all seems simple, as we can just delete and rebuild, especially if we can cut the prototype database directly from the Entity-Relationship diagram. We can be as free as an application programmer to evolve ideas and try things out.

Then we build the team. We can get around many of the problems of team-working by using source control. However, we have to work from an existing consensus of what the data model should be. Any changes to the model take both tact and discussion because of the wide ramifications of a design decision.

The bigger problems associated with radical change come when one has to preserve the data. This may initially just be for your test data, but ultimately it may end up as a data-migration process in a deployment. Any serious redesign in a database schema means that automatic data synchronisation isn’t entirely possible: it has to be supplemented with a hand-cut 'migration script' that is capable of handling such things as table-splits or even a rearrangement, or renaming, of columns. If you are deploying changes to an existing database without taking it offline, then you’d have to provide ways of rolling out, or rolling back a change whilst preserving any concurrent changes in the data. All this is quite separate to the problem of maintaining a compatible interface with one or more applications that are using the system.

One could go on and on about the practical repercussions of refactoring existing databases. It therefore always surprises me when Application programmers tell me that all 'Agile' techniques are directly applicable to database development. Ideally, yes; but reality gets in the way. It would certainly be great to experience this sort of freedom, but we need to be realistic about what is possible, without the appropriate tools and techniques.

Once you've experienced working on a large corporate database that is critical to the wellbeing of the company, it is hard not to be awed by the number of tasks involved in even the smallest change to a production system. Although it has become easier in the past decade with the advances in tools, it still isn’t a simple, automated process. It will be interesting to see what develops in the next couple of years to allow database development work the same freedoms enjoyed by Agile.

Phil Factor

Total article views: 141 | Views in the last 30 days: 1
Related Articles

Disconnected database development: solving a problem that needn't exist

A disconnected model is only really needed in the absence of a properly-defined database interface. ...


Database table Change

Database table Change


Is Your Database Application DeadLock and Timeout Resistent?

Is your application scalable under increased activity? Timothy Claason brings us a methodology for t...


Database Design for Blog application

Database Design for Blog application


Application Developers don’t own their data

As a data guy, I always smile when application developers refer to ‘their’ data. If only it were tha...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones