SQL Clone
SQLServerCentral is supported by Redgate
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: 143 | 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


Making VSTS Deployment Changes to Databases without Breaking Your Application

One of the things that I do a lot is demo changes to databases with CI/CD in a DevOps fashion....