The Lean Machine: Bringing Agile Thinking to the Database
For some years now, Agile practices have been attracting application developers with their promise of short iterations, fast releases, and software that gets out there sooner. Those same practices are now entering the database space, but how can database development teams adapt, and where should they start? In the first part of a 2-part article, Matt Hilbert demonstrates how Agile thinking for databases begins with source control.
Agile has been claiming converts everywhere. Developers like it because it streamlines processes, improves software quality, automates repetitive tasks, and supports continuous delivery, freeing them to concentrate on what they’re good at: coding.
Many companies like it too because, as shown in the State of DevOps Report 2014 from Puppet Labs, those businesses using approaches like Agile are twice as likely to exceed their profitability, market share, and productivity goals.
Gartner have even put Agile software development in their top ten high tech trends of 2015, calling it one of the drivers of the digital economy.
But what about database developers and administrators? They can’t afford to rush database changes through at will. By default, they need to do a lot of design up front.
Applying Agile practices like continuous delivery and the automation of laborious tasks to database development is a real technical challenge because the whole process appears to be less open to agility. Agile means introducing new techniques, unfamiliar processes, and untried tools to existing practices, risking faster database deployments more often when current deployments are already hairy enough.
Or does it? By applying Agile thinking to databases, the development process becomes leaner and faster, there is less waste, and quality is built into the code from the start, avoiding many deployment problems later.
The rules of the game have changed
Truth is, while Agile and continuous delivery have been sweeping through application development like wildfire, there’s been a lot of Agile movement in the database development arena too. It’s a natural extension because business is moving faster, features need to be released sooner, and the database can’t be a bottleneck.
In database development, testing, and deployment, there are tools and processes that can be adopted alongside those used for applications. By treating the database as another piece of source code and using Agile practices, Database Lifecycle Management (DLM) becomes easier.
Used correctly, DLM relieves the burden on database administrators (DBAs), makes testing easier and faster, and turns deployments from occasional big bang releases full of worry to frequent releases that are simple and error-free.
At the same time, it transforms DBAs from gatekeepers who are sometimes perceived to hinder deployments into enablers who make smarter deployments possible.
That’s quite a change and, perhaps surprisingly, it’s relatively easy to start being Agile.
Get everyone on the same page
The first step is to think collaboration not competition. In the well-known novel about DevOps, ‘The Phoenix Project’, there’s a wonderful comment by the major character, Bill Palmer, about the division between application developers and DBAs:
You can't just throw the pig over the wall to us, and then high-five each other in the parking lot, congratulating yourselves on how you made the deadline. Wes is telling us that the pig will probably break its leg, and it'll be my guys who work all-nighters and weekends to keep that pig alive.
This divide doesn’t need to be there – and with Agile thinking for the database, can’t be there. The goal is to move to smaller, more frequent releases, with development and operations teams communicating and always cooperating on the bigger issue: getting the right software out there. For example, the ops team highlighting the effect proposed changes will have on the database and what the consequences will be to developers in advance.
David Poole, data architect for Moneysupermarket.com, agrees. In his article DBAs vs Developers: A Sad Tale of Unnecessary Conflict, he says in his conclusion: “From experience I know that, when developers and DBAs work well together, they don't just add to each other's effectiveness; they multiply it.”
Choose the right tools
The second step is to remember being Agile means enhancing the way you work. It doesn’t mean employing tools that enforce change and impose rigid, unfamiliar rules, tools, and processes on everyone. Instead, it calls for using the tools you already have in a better way because, as Alex Kuznetsov writes in 'Lessons Learned from Six Years of Agile Database Development':
Agile is a grassroots movement and one simply cannot force specific tools, techniques or approaches upon an unwilling team.
You could, for example, plug into the same source control, continuous integration, and release management tools used by your application developers to version control, test, and deploy your database changes.
That way, rather than introducing an untried tool that does the job but imposes controls over people, you enrich the way you work by doing more with database tools that work alongside existing application development tools.
By default, this also brings the added advantage of application and development teams working together toward the same goals, and there is true integration across the development process.
Continuous delivery, continuous integration, database management lifecycles – the phrases alone are enough to make the talk about Agile thinking for the database sound more complicated than it actually is.
The key is to start in a small way, by source controlling, or version controlling, your database first. Source control is not an Agile practice in itself: rather, it’s the enabler for Agile practices such as continuous integration. Without it, you can’t be Agile. By adopting it, you pave the way for Agile.
The bigger advantage is by source controlling the database as well as the application, versions can be synchronized and tested together. And with changes to the database code tied to changes to the application code, any working build can be reproduced at any time. As Grant Fritchey, SQL Server MVP, writes in ‘Why Put Your Database Into Source Control?’:
Having the database in source control directly alongside the application will integrate the database changes with the application code changes, so that you'll always know that the version of the database being deployed directly corresponds to the version of the application being deployed.
Tribal memory is not source control
The manual, script-driven process some companies and organizations still rely upon is often called source control. This is typically built around the close understanding of the database by one person or a small group, and a memory of what worked before and what will probably work again. Developers and DBAs work directly with scripts rather than the database, and a history of changes is hard to maintain.
This is not source control, it is a fear of source control based on the fact the existing data and structures must be protected at all times so data is maintained and nothing gets lost.
This is despite the fact that, with source control, multiple people or teams can all have access to pieces of code, or a database, at the same time. Each piece of code can be versioned, which is useful for branching and ensuring nothing gets lost. Or whole sets of code can be versioned, giving you the ability to deploy to a known state, or revert to a previous state.
As importantly, with a proper source control system in place, the very practice of source control becomes a normal part of everyone’s daily working life, rather than the domain of a limited number of people. So if issues do arise, they can resolved quickly rather than becoming a potential crisis.
Keep it simple
As has been seen, source controlling database code offers big advantages. Those same advantages would be negated if, by pursuing them, the job of developers and DBAs became more complex instead of being made simpler.
Introducing a source control tool that forces developers to work differently or with policies foreign to your existing practices will simply irritate people. Similarly, choosing one separate from your application source control system will continue the divide rather than bridging it.
The key is to integrate database source control into your current development process so it aligns with the tools and practices developers are already familiar with. Inevitably in the SQL Server world that means working alongside – preferably inside – SQL Server Management Studio.
With a plug-in source control tool, developers can continue to use the interactive development model, working with an "online" database. It automates the source control of object creation scripts, alerts users to differences between the database and source control versions, and makes it simpler to commit changes to source control.
With source control as part of the normal database development workflow there are productivity gains too. It takes less time, makes it less likely that committing code to source control will be forgotten, and reduces task switching, improving efficiency. With several teams likely to be involved in the application and database development process, a repository also makes it easier to inspect what has been done.
As can be seen, source controlling the database alongside the application is not only possible, it delivers many advantages. It synchronizes your database structure across the development, test, and production environments, reducing the work – and the errors – involved. Additionally, it ensures database development teams communicate changes with others, and provides a version to roll back to if required .
And of course, it opens the door to truly Agile practices like continuous integration.
Only be as Agile as you need to be
Probably the most important message about moving to an Agile way of working is you decide the pace of change.
Once source control is in place, you have the option to bring in continuous integration to build, test, and package your database changes alongside your application code, speeding up releases and reducing the risk of deployment problems. As well as validating the structure of your database, it runs unit tests with realistic test data, and checks if the database changes you want can actually be deployed.
You can then upgrade your release management process with all of the update scripts, change reports, and review steps you need to make database changes to production efficiently and safely.
The key thing here is automation. Tools are out there that take care of the repetitive, laborious tasks on the bottom of the list for every database developer and administrator yet take up so much time.
All of this automation frees DBAs to spend more time on important work – and lowers the risk database deployments will fail. No surprise, perhaps, that in a recent SQL Server Central survey, nearly 60% of companies already using Agile practices like continuous delivery have reduced the time to release changes; over 40% spend less time deploying changes; and over 30% say less time is lost to bad deployments.
It starts with source controlling your database alongside your applications, but the end benefit can be far greater.
Part 2 of ‘The Lean Machine’ will detail how database developers can benefit from Agile delivery practices like continuous integration and automated deployment.
About the Author
Matt Hilbert is a technology writer at Redgate with 20 years’ experience working for lots of the world’s biggest tech companies – and many of the smallest. He has a particular fascination for emerging technologies and is on a continuing mission to decompile, untangle, and explain techspeak to a wider audience, and excite people about the endless possibilities technology offers.