Databases and Agile: Confessions of a Recovering DBA

By | 2014-05-15T08:11:12+00:00 May 15th, 2014|Application Lifecycle Management (ALM)|3 Comments

The Confession

Database development is one of the last bastions of traditional development practices. The traditional large design up front with inflexible schema and structure are still mainly the rule. It feels good to do database work with huge up-front planning and piles of architectural drawings and data models. It’s safe, familiar and comfortable. That may be why as we have learned that more consistent successful development is achievable with more agile and lean methodologies, by and large, we have not applied this knowledge to our database efforts. As much as I hate to admit it, the time is long past to abandon the old traditional database development practices. Agile database development is not only possible it is a natural progression in agile adoption.

The Challenges—Troublesome DBAs and Evil Developers

There are some significant challenges in moving to more agile database development. The first barrier is guys (and gals) like me: old school DBAs. I have been working with databases for almost 20 years. I have been responsible for managing, architecting and administering a wide range of enterprise databases. Over the years I developed a strong bias and belief. The chief belief was that developers are evil. They all seemed to take childlike delight in breaking my databases. It was a constant battle to keep them out. I took great pains to create structured, normalized databases that would perform well and stand the test of time. Then a developer would come along with their SELECT * FROM… nasty code that just messed everything up. The real problem though was that my databases didn’t stand the test of time. As the applications rapidly changed and evolved, the databases couldn’t, or they were exceptionally difficult to refactor. The databases were masterpieces of design and static functionality for a volatile and changing world. It took me a while to come around, but applying agile principles to database development makes perfect sense.

All Code is Created Equal

As many organizations in the industry move toward a model of continuous delivery (which equates to continuous change) our database models must be designed to support the change. It takes a shift in how we think about databases, but it is not a difficult shift. We need to start thinking about databases the same way as we think about application code because there really is no intrinsic difference between application code and databases.

What Does Database Agility Look Like?

Oddly enough, agile databases look very much like traditional databases. It is the approach to development that changes, along with some historically ingrained tendencies. First, agile databases have much less up-front design. It is way too easy to spend weeks, if not months creating a grand database design that covers all conceivable possibilities. The YAGNI principle—“You Aren’t Going to Need It”—applies to database design just as it applies to application design. The principle applied to database development simply means, don’t build objects you don’t know you are going to need. To take it further, don’t build objects you are not going to need immediately. However, and this is important, don’t be silly either. Scott Ambler is much more frank and calls it “stupid” in his essay on agile data modeling:

“Just because we don’t want to overbuild my software doesn’t mean we need to be stupid about the way that we model the data schema. We can look ahead a bit and organize the database schema so that it reflects the domain and not just the specific requirements of the application which we’re currently supporting. However, we’re doing this in such a way that we don’t impact the application schedule and we’re doing it in a collaborative manner with the developers.”

The bottom line is, don’t try to build a database for the ages, you will fail. Build what you need and what makes sense for the immediate future, but design it so that you can extend it easily, just like you would design your code. This is where normalization is your friend. It is much easier to extend and maintain your databases if they are normalized. Adding new tables or columns to your design iteratively to support changing requirements should not be as painful as it sometimes is. Simplifying your database design will automatically reduce the amount of documentation needed as well. Well-written code should be self-describing. Well-designed databases should be as well.

Communication is King

It is no longer acceptable (if it ever was) for the database team to be in a separate silo from the development team. To ensure a seamless and ceaseless stream of communication the development team should include the database administrator/developer/architect or at least be in constant communication. It is additionally important that the operations and development DBAs have a clear line of communication—and a good working relationship.

Egos and Old Habits

Speaking from personal experience, old-school operational DBAs very often consider the production database their private domain (with some justification). They jealously guard it and insist any changes made are done by them personally with little to no automation, especially if the automation was written by a developer. Also speaking from personal experience, these DBAs need to get over it. Automation is the answer to a great deal of database challenges. Well written, well tested automated deployment and rollback procedures are key to bringing agility to the database world. No matter how good the DBAs are, they are still human and humans make mistakes. It is always a better option to design reliable automation, test it and depend on it.

Agility in Design

The technical aspects of agile database development, like application development involves writing and safekeeping good code as well as testing and deploying the code. Agile SQL code is written in such a way that it promotes:

  1. Repeatability: The code should be written so that it can be run multiple times in the same environment without failing. This can be challenging, but it is completely achievable.
  2. Compatibility: Databases should always be backwards compatible at least one version. It is much easier to rollback a production application change than it is to rollback a production database change. If we design our database changes so that they don’t break the existing application we avoid having to write the complicated code that we will need to rollback database changes. Interfaces are your friend. Views, functions and procedures can provide interfaces that insulate the application from database schema changes.
  3. Reversibility: Changes to SQL programming objects are the exception to the compatibility rule. They very often cannot be modified in a way that supports compatibility. Instead, rollback scripts are created to return the objects to their previous state.

The Whole Truth

It is critical to know what the truth is and it is imperative that there be only one version of the truth. Maintaining the database schema in source control alongside its associated application code enables us to know where and what the truth is for a particular release. Direct modifications to production code without the workflow and process of source control, build and deployment pipelines are typically unacceptable. The same should be true of database modifications.

Stepping Off the Soapbox

There is a great deal more that can be said about agile database design and there is plenty out there that can be read. I do not want to present a case that building databases with agile principles is easy or without some drawbacks. I do want to present the case that beginning the design with agile principles firmly in mind will significantly reduce the amount of time it takes to create a reliable, well-performing database design that is flexible and extensible.

About the Author:


  1. Olivia Jennifer June 5, 2014 at 10:27 pm

    Scrum is the most commonly used agile process for projects specifically more prominent for software development. As a product development framework scrum is applicable for any type of projects but you need to train your project managers in Agile Project Management .

  2. Paul Gregory October 29, 2014 at 1:39 pm

    How do you feel about using modern techniques like Entity Framework or even code first?

    Also how about the statement that all business logic should be in the application. Rather than in the database.

    Would your average DBA be cool with this? Or start foaming at the mouth?

  3. Dan Wood November 5, 2014 at 3:10 pm

    Paul, Entity Framework and other similar approaches certainly have their place. I personally don’t believe they have a significant place in medium to large applications where performance and scalability are of vital importance. I know there are plenty of developers who will disagree. To be perfectly honest I am not a fan of EF or code first, but my opinion is based on some pretty subjective experience that isn’t worth rehashing here. As far as business logic in the application, I think your average DBA will be cool with it as long as they have the same definition of business logic as I do. The bottom line is that databases do CRUD (Create, Read, Update, Delete) very well. They do complex string operations and mathematical operations not so well. Do all the complex business operations where they belong and do the CRUD stuff in the database. I worked at a company where the developers created a 5000 line stored procedure that consumed XML, performed intensive calculations on numbers pulled from the XML, inserted the values from the XML in to tables and then constructed new XML from even more calculations based on calculation performed on the data extracted from the tables. It was a mess. Maybe an extreme example, but I have found similar approaches many times. Appropriate tool for the job would be my short answer. Thanks!

Leave A Comment