Much of what we do in IT seems easy, from a distance; even the modification of a live functioning database while preserving production data. However, any DBA who isn’t gripped by a cold terror at the prospect has probably never been responsible for a live trading OLTP system, on which an organization depends. A lot can, and occasionally does, go wrong, and with real financial consequences and loss of reputation for the organization.

So why would any DBA want to delegate this task to a development team, when the DBA is ultimately responsible for ensuring the service of the system, and the integrity of the existing production data?

The answer, of course, depends on the details of the system. Where the release system is essentially ‘chaotic’, this process means days of preparation and testing. It might start with the development team depositing a ‘bunch of individual object change scripts’ in a folder shortly before they wish to deploy. It can often take the DBAs many days to craft from them the migration scripts that they are confident will preserve existing production data exactly as intended, and fail gracefully and roll back if a problem occurs. Then it has to be tested.

Even with the aid of a synchronization tool that can auto-create the transactional code to update the existing database, from the source scripts, there are likely to be places where radical table modifications require hand-crafted scripts. It all has to be written and tested. It all takes time.

If the development team has a justifiable need to put changes into production frequently and rapidly, then this system is no longer appropriate. So what is the answer? Either deploy less frequently, or push responsibility for data preservation in the migration scripts down into the development team, but with closer collaboration with DBA, according to the ‘DevOps’ model.

Both options are scary, but the second one actually has a chance of working. Firstly, any team that has successfully grasped the nettle of Database Continuous Integration can’t avoid thoroughly testing these migrations many times before they get anywhere near release. Secondly, we now have migrations-based tools such as ReadyRoll and Flyway that support and automate the process.

So is the developer dream becoming a reality? Can we now have “push button” database deployments to production, or at least directly to staging prior to final checks, from “a bunch of change scripts”?

I’d certainly like your opinion on this, but I still wince at the idea that this is a panacea. To avoid hitting a brick wall in staging, the development team would have to perform tests that prove that the migration scripts work, while the system was working with a realistic data and load. After all, you can’t test a car without turning the engine on and driving it. Surely, the many governance checks have to be done, but can’t they be done in parallel with development rather by than sticking with the familiar chaotic procession down the deployment pipeline?