Database automation motivation

The first question I thought was: if applications development includes “by default” concepts as source control, continuous integration and continuous delivery, why not database development?

As Len Bass said in a conference: “A script is just a program”. All database changes can be scripted, so what is so special about databases? The answer is simple: data, specifically data persistence. One of my favorite answers was given by Grant Fritchey: “Deploying databases has the inherent problem of retaining the data after the deployment”.

Faced with this challenge the second question that I made was: why should I invest my time and effort in automating the database development process? I found the following  answers/reasons in literature and in my day to day job:

  •   Database are out of pace with application development
    • The database changes usually come from the development teams, however in most cases the DBA is responsible for applying this changes. This means that development teams and the DBA need to be synchronized, in the best case scenario this is a communication challenge;
  • No traceability of database changes (changes history)
    •  There will always be a moment where you need to answer the following questions: What was changed? Who changed it? When it was changed? Why it was changed?
    • In the ideal world the source control system should be able to answer this questions;
  • Manual database processes prevent the Continuous Integration (CI) and Continuous Delivery (CD) in their full extent
    • The strength of your process is equal to the strength of your weakest step of the process;
    •  Is acceptable to say that if the process contains a manual step, you can not say that you have a automated process? (Almost automated is not automated)
  • Manual database release process
    • This process is time consuming and error prone;
    • This make the release process less frequent and risky;
  • Tests are manual or do not exists
    • This have a serious impact in the application quality;
  • Bugs in production environment
    • Database related bugs are only discovered after deployment to production;
    • Fixes and hot-fixes have time cost, what can lead to delay a release;
  • Inability to roll back to the previous version of the database
  • Database setup in a new environment is time consuming
  • Databases become a bottleneck in agile delivery processes
    • Databases can be the most expensive component and the bigger troublemaker (part of it is because it’s “easy to blame” the database for the application problems).

The adoption of the database automation allows to:

  • Enable control over database development
    • If you have control, you have better conditions to improve;
  • Keep a versioned “history” of database states;
  • Increase release frequency through repeatability of processes
    •  Greater predictability over releases;
    • Efficiency by eliminating the repetition of a range of manual activities
  • Reduce time spent fixing bugs
    • With more control you are less expose to error;
  • Speed up response to change
    • Automated deployment of smaller units of change;
  • Remove/reduce human intervention in the release process
    • The build step is automatic triggered by a “push” into source control repository;
    • The deploy step is automatic triggered by a successfully build process;
  • Have greater reliability of the release process
    • Providing a series of automated test stages prior to deployment;
  • Maintain an agile development environment

PortoData 15 December 2015 – Road to database automation

Last Tuesday (15 December 2015) I made my first presentation in Porto.Data community. “Road to database automation” was the title and the this session had two main goals: discuss the challenges of database inclusion in the Application Lifecycle Management (ALM) and present a set of strategies and tools to achieve this inclusion.

The effort of database inclusion in the “agile context” is designated as Database Lifecycle Management (DLM) and is composed by 3 main parts: database source control, database continuous integration and database continuous delivery.

In the first approach to database automation are presented some strategies/techniques and tools, such as Mercurial (TortoiseHg) and Git (SourceTree) for source control and Flyway for database migrations management,  to deal with the challenges that a database version control system implies.