Getting Agile with Database Development

Once your organization becomes convinced of the advantages of database DevOps, there's no going back. Shorter time to value, lower risk and greater flexibility are among the most frequently cited benefits of the database DevOps approach.

Although most application developers now consider DevOps a mainstream approach, database developers — especially those working on relational databases — have been slower to embrace it because of the need to understand and respect the state of a database when deploying changes. Thus, database professionals have had to rely on manual processes that do not scale up to the faster development cycles at the heart of DevOps.

What if you could bring your database developers into the agile fold? What if you could show them how the current manual processes for database development can't scale to the level required to safely accomplish shorter release cycles? What if you could prove that the way to reduce risk and scale up is to automate as much of the testing, review and staging processes as possible?

This e-book is designed to help application developers, database developers and database administrators (DBAs) understand that automation is the key to synchronizing the development cycles of application software teams and database teams. It also offers ways to implement more agile database DevOps processes into your database development cycle to accelerate your DevOps pipeline.

Agile development for reduced risk

Agile Is A Reaction to Old-School, Rigid, Waterfall Methods of Building Software

Traditional software development methods rely on deep specification, design, documentation and months or years of heads-down work to deliver a product. They seek to reduce risk through careful and methodical attempts to deliver a complete and perfect product, which takes a long time. But because business requirements and customer landscape often change while all that work is under way, the company ends up launching its product into a very different market and missing the mark. Frustration ensues.

Agile principles emerged in an attempt to correct this, proposing instead:

  • Close collaboration between the programmer team and business experts
  • Face-to-face communication)
  • Frequent delivery of new, deployable business value
  • Tight, self-organizing teams
  • Ways to craft the code and the team such that the inevitable requirements churn was not a crisis

Without rigidly enforcing specific practices, agile embodies the best of these principles to more quickly deliver better software. Development teams can deliver value and improvements in smaller chunks more frequently, instead of saving all the value until the end of the project. When software development processes are properly automated, the result is actually less risky, even when teams are putting out releases more often, as depicted in Figure 1.

Figure 1: Shorter time to value, lower risk

Because the landscape changes during development, the company launches its product into a very different market and misses the mark. Frustration ensues.

Since its inception in 2001, agile has caught on among application developers. In 2009, Forrester found that 35 percent of the 1,298 IT professionals surveyed were using agile development methods. By 2014, agile tool vendor VersionOne found that 94 percent of 4,000 companies surveyed practiced agile development, and 53 percent characterized the majority of their agile projects as successful. That represents a significant change from Forrester's 2009 findings, in which a slight majority of companies indicated they had one or two agile projects under way.

What about agile database development?

One of the principles that agile organizations follow is to deliver working software frequently, from a couple of weeks to a couple of months, with a preference to the shorter timescale. Application software developers have successfully adopted that principle, resulting in shorter development cycles.

But what about database developers? Why have they been slower to adopt DevOps practices and shorter development cycles? The answer lies in a few important differences between application development and database (especially relational database) development.

Overwriting

Application developers write code for execution, and they can easily overwrite version 1 of that code with version 2. If there's a problem with version 2, it's easy enough to restore version 1 so that people can continue using the application in its previous state while the developers fix version 2.

But a database is more like a living organism with a current state that must always be managed to maintain the integrity of the data. It's not an option to overwrite version 1 of the database schema with version 2 because that could result in data loss. Instead, database changes must be made through scripting, telling the relational database management system (RDBMS) to adjust its state from the current form to the intended form. Thus, it's harder to post a change to a database, and if the change doesn't work or data is lost, then it becomes necessary to restore the entire database, which results in costly system downtime.

Version Control

Few software development teams can do without version control because it prevents multiple developers from working on source code at the same time and allows them to reconcile differences. It represents the single source of truth.

Database development teams, however, use it differently, if they use it. The database itself is regarded as the master version of the source, and its state must be maintained throughout the deployment process, so version control does not play the same role, in spite of the frequency and extent to which changes are made to stored procedures and functions. This fundamental difference between application and database development increases overall risk and makes it more difficult to implement automated deployment mechanisms.

Automation

Application developers use tools to automate the entire deployment process — version control, builds, unit testing, static code analysis, staging and even deployment — so they can overwrite previous versions of code and deliver new code to production multiple times each week or day. And their extensive use of automation for things like building and testing causes their level of risk to decrease, as shown in Figure 1.

To protect the data contained in the database, database developers and DBAs must manage its state through scripting that gracefully updates the database from one state to another. However, the scripting process is often devoid of automation, slowing the overall release process.

Urgent Changes

That lack of automation at the database level exacerbates matters when urgent fixes need to go into production. Application teams can easily get the fixes into the pipeline for the next release, with agile processes ensuring that they will undergo proper testing, unit testing and staging before go-live. But the database team must manually walk changes through a much longer cycle.

Without agile, changes of any kind go through the database development cycle much more slowly. That's not a problem, as long as the business can wait. But what if the business finds a critical bug in a procedure or function

Figure 2: Urgent change required by the business

Hot fix applied that requires an immediate change? In many cases, the production DBA will have to make the change and apply it directly to the live production environment with minimal, usually rushed testing (see Figure 2).

While that may solve the immediate concern, it knocks the other upstream environments — development, quality assurance (QA), unit testing, stage — out of sync with production. That increases risk for the work under way on them and requires yet more manual work to bring all the environments back into harmony with one another. As disruptive as this is, it's necessary to resolve the business issue.

There must be a better way

Scaling to shorten the development cycle

Manual processes do not scale in a linear fashion. After a certain point, adding more people will actually slow development down and lengthen the development cycle.

Consider a team of five people working on database code changes. Their average development cycle is three weeks. To shorten the cycle to two weeks, they add two or three developers, for a total of seven or eight. Next, they're under pressure to shorten the cycle to a single week. If they add three people, will they accomplish that? Possibly, but 10 or 11 developers working on the same code base makes for a lot of cooks in the kitchen, especially at such a breakneck pace.

Figure 3: Relational database as the bottleneck in the agile organization

Now suppose they need to shorten the cycle to one day to catch up with the application development team. Adding five to 10 more people — even if they could find and afford them — would almost certainly result in chaos and significantly more management overhead than progress. It would be almost impossible for the team to deploy daily in the sensitive database environment. When teams reach the point of diminishing marginal returns, productivity begins to decrease as people are added. This is not a number-of-people problem.

Still, in the world of application development, a single-day cycle is not extraordinary. In fact, many application teams deploy directly to production hundreds or thousands of times each day because they've automated those pieces of testing and integration that often take so long. Database development teams will never scale to the speed required to keep up with agile application development teams unless they introduce software automation into their processes as well. In fact, until database development processes — particularly development on relational databases like Oracle, SQL Server and DB2 — scale up to a similar level, they will continue to be the bottleneck in an otherwise agile organization (see Figure 3).

Scaling database development for DevOps

Relieving that bottleneck requires an automated pipeline with which database development teams can address risk, ensure quality and shorten the development cycle for the organization as a whole.

Figure 4: Redefining database development

1. Version Control

As noted earlier, version control plays a different role on database development projects, but it is still a valuable way of tracking data definition language (DDL) changes every step of the way. Tracking revisions to stored functions and procedures over time makes it easier to assess them, compare them side by side and diagnose any problems that arise on the path toward production.

2. Automated Unit Testing

The only way to be certain that a proposed change does not break something else is through automated unit and regression testing. Even if developers and QA teams are comprehensive and remember everything they need to test, manual testing is still vastly slower than automated tests that run against the code change at the point of check-in.

Automated testing provides a basis and a safety net for accelerating database deployments because it offers nearly immediate assurance that new changes do not break earlier work. Furthermore, by automating and executing this process at the point of check-in, developers will receive immediate feedback about a break and be in the best position to fix it quickly, when the relative cost to fix a bug is at its lowest (see Figure 5).

3. Static Code Analysis

Developers subject their code to peer review to ensure that they haven't missed something, introduced a security vulnerability, made a mistake in logic or inadvertently slowed the product down. Static code analysis software significantly accelerates this process and ensures adherence to company standards by reading the code and identifying the same kinds of patterns that peer developers look for.

Database developers, too, stand to gain from applying static code analysis after unit testing, perhaps even more than their application development peers. Given the extreme pressure and ever-tightening deadlines of the database development cycle, many teams rush the code review process or skip it entirely. Thus, automating the process at the database development level can reduce the time the team spends and increase overall consistency of code reviews. Automated code review can check against rules written to help enforce company standards and improve quality, performance, maintainability, security and flow.

4. Stage For Deployment

To reduce the risk of data loss and other mishaps, the database development path usually includes a DBA stop in the deployment stage for a review of code changes before they go into production. While automating around the DBA stop is not a good idea, automating the creation of ALTER scripts for deployment is a valuable step in shortening the development cycle.

Figure 5: Cost of fixing bugs at various stages of software delivery

DBAs managing the path to deployment can use automated tools to collect all the queued changes that have passed regression tests and static code analysis, compare them to the production environment and generate the scripts to commit them. Not only can this improve DBA efficiency and shorten the development cycle, but it can also ensure all of a project's changes make it into production.

5. Automation

As the organization moves away from manual processes and toward automated tools, database development cycles will start shrinking and all teams can begin to realize the promise of agile. Using software tools in a piecemeal fashion each step of the way is faster than pure manual processes, but it provides only incremental improvements.

Agile requires a fundamental shift in the way teams work. Instead of having people manage each step of the process, teams must look for ways to create fully automated pipelines that submit changes to regression testing, review and staging for deployment without further interaction from the team. DBAs can rest assured that code changes meet quality standards and adhere to company policy, and managers can see that code will meet project requirements and run properly in production.

Conclusion

Application developers have long taken advantage of agile practices to shorten their development cycle and reduce the risk associated with change.

Database development, on the other hand, has traditionally relied on manual processes that reduce the risk of data loss in a live production database. The resulting bottleneck, especially in a relational database environment, has kept the organization as a whole from realizing the full promise of agile: development.

What if you could relieve your database bottleneck, reduce application release time and spend less time fixing defects? You can. By automating as much of the software development lifecycle as possible, particularly time-intensive and repetitive processes such as testing, review and staging of changes. Excellent tools exist for application developers; database developers, if sufficiently inspired by the agile mentality, can adopt them for use in their projects as well.