As a database professional, are you curious about agile methodology? Are you considering joining one of the growing number of agile IT shops?

For six years, I’ve worked in a development team that has learned, gradually, how to apply agile techniques successfully to our development, including our database development, which we treat in more or less the same way as developing classes, libraries, and the GUI. We develop in-house solutions in a dynamic environment, which means that business requirements change quite quickly, and we need to develop or change systems quickly, in response. We find that adopting an agile methodology allows us to:

• Communicate with users very efficiently, so that we can quickly understand what our users want, and we do not waste our time developing things nobody wants.
• Develop well designed solutions that are relatively easy to change.
• Develop low maintenance solutions, so that our development is rarely interrupted by maintenance tasks.

As a result, we can deliver working and useful solutions, quickly. In this article, I’ll describe the agile principles that are important to the team, knowledge of which will help you, as a database professional, to collaborate better with agile developers. I’ll also outline a few of the most valuable techniques and tools that help make these principles a practical reality, for databases. I don’t have time, in a single article to offer much detail, but I hope to at least provide a useful framework for further research, should your team be considering adopting an agile approach, or extending your current approach to cover databases as well as applications.

One proviso, before we begin: I speak from extensive, though rather narrow experience. I am not a consultant, I do not get to see multiple environments and teams. I describe what has worked well for my own team, but I do not try to be prescriptive in my recommendations, as all teams work differently. Agile is a grassroots movement and one simply cannot force specific tools, techniques or approaches upon an unwilling team. In this spirit, I encourage you to take the bits of my advice that sound a good fit for your environment, and reject the rest.

Key Agile Principles for Database Professionals

Different agile teams work differently, using various tools and approaches; this is perfectly normal for a grassroots movement. Still, there are some common values to which we all subscribe. This section describes those overarching principles of agile development that are very important to our teams, and are common to different teams. I will only mention here the ones that seem to be the most relevant to database professionals, but I encourage you to read Principles behind the Agile Manifesto for a full list.

Welcome Changing Requirements

Welcome changing requirements, even late in development. Agile processes harness change for the customer’s competitive advantage.

We build in-house solutions in a dynamic environment, where requirements can change quickly. To justify the cost of in-house development, our overriding goal is to provide our users with the simplest solution that will fulfill each particular requirement. We spend a lot of time getting to know our users, communicating with them frequently, and striving to develop only the features they actually need, and to develop them really well. By building simple, easy to use and very well-tested software, customized to our users’ needs, we boost their productivity.

In a dynamic environment, our team, and the solutions we build, need to be able to respond quickly to the changing requirements of the business and of our users. In order to succeed in such an environment, we cannot spend months developing a single feature, only to find that the user’s requirements changed in the meantime. Agile development encourages teams to “deliver working software frequently, from a couple of weeks to a couple of months, with a preference to the shorter timescale.” In fact, our team sometimes delivers a working product or feature in a day or two, and then we iterate until we reach the best solution for the users.

Each time we deliver, we solicit direct feedback from the users and it is this feedback that drives the next iteration. This feedback loop is essential and the only way to ensure that at each iteration, we produce a solution that is more efficient to work with than the previous version.

Sounds obvious? Unfortunately, quite often the next version of a piece of software does not increase users’ efficiency, and the main reason for this is that the imagination and ambitions of the developers is the main driving force for change, rather than the needs and the feedback of users. This is a not a problem specific to software development.

Establish Efficient Communication

The most efficient and effective method of conveying information to and within a development team is face-to-face conversation.

Agile teams invest a lot of effort in establishing clear and efficient means of communication. We use scrum, stand-ups, and so on, to encourage developers and users to get together and talk to each other. The main reason is simple: we do not want to misunderstand requirements and waste precious time developing wrong functionality.

Even with frequent face-to-face communication, there is a danger that spoken requirements are misunderstood and morph into something subtly or even completely different when implemented in software. This is a particular danger when working in diverse teams, from different countries and with different native languages. To give a simple example, if someone tells me “this must be done by Friday”, as a Russian I could interpret this as “no later than 11:59 PM on Thursday” (I’ve made this mistake before). However, another member of the team may interpret is “by end of business day on Friday”. Furthermore, in organizations with different geographic locations, to which time zone does the deadline apply?

Such potential confusion is the reason why agile developers are big proponents of translating spoken requirements directly into tests that run automatically. To continue our previous example, we’d translate the requirement that a task must be complete “by Friday” into a test case that specified exactly the date and time for task completion, including the time zone, so eliminating all ambiguity.

Iterate and Simplify

Simplicity–the art of maximizing the amount of work not done–is essential.

As described, we try to deliver solutions quickly and then iterate frequently, based on direct user feedback, until we arrive at the most effective solution for our users. The beauty of this approach is that we don’t have to strive get it right first time. We focus on simple designs that are adaptable to change and then iterate the design to arrive at the simplest possible solution.

A common objection to agile methodology is that with more time spent on a single design phase, at the start, we are more likely to get it right first time (an approach sometimes termed “Big Design Up Front”, or BDUF). Unfortunately, the BDUF approach does not always work in real life. Even the best designers and developers often need several tries before they get it right. Maybe Wilbur and Orville Wright’s initial designs looked wonderful on paper, but the brothers needed many attempts to build a working airplane.

Likewise, most software developers need the latitude of trial and error, and a few iterations, to solve a problem well. Many solutions, including some of mine, begin life as over-complicated and over-engineered bloat-ware, and eventually simplify, if we have enough time to improve them. This evolution from complex prototypes to very simple, high quality products is quite typical in agile development.

To take a specific example, Linux, which to my mind is a truly amazing piece software, was to the best of my knowledge developed through trial and error. To quote the inventor of Linux, Linus Torvalds:

“Don’t underestimate the power of survival of the fittest. And don’t ever make the mistake that you can design something better than what you get from ruthless massively parallel trial-and-error with a feedback cycle. That’s giving your intelligence _much_ too much credit.”

When we use agile methodology, we essentially roll out many inexpensive tries, and let the fittest survive. Of course, this approach has direct implications for how we design our systems. A common misconception is that in agile development design “doesn’t matter”. This is simply untrue. I cannot emphasize enough the importance of good design to successful agile development.

High Quality Design and Implementation

Continuous attention to technical excellence and good design enhances agility

Systems must be highly adaptable to change and only well-designed and low maintenance systems can change quickly. Poorly designed ones are brittle and so difficult to change. In dynamic environments, poorly designed systems quickly obsolete because they just cannot change fast enough, so we have to replace them. This process is quite Darwinian: the systems that survive in the long run are not necessarily the ones that perform the best right now. Only the systems that are able to change and adapt in the long run manage to survive.

Later in this article, I’ll describe, more specifically, how all this affects the manner in which we must develop databases. We must invest considerable time and effort to maintain the agility of our database systems, for example, by finding ways to:

• Make frequent database refactoring a reality, without disrupting users
• Minimize the database maintenance and troubleshooting that could distracts us from new development
• Develop an extensive system of automated tests of various kinds to make frequent change sustainable in the long term.

Self-Organizing Teams

The best architectures, requirements, and designs emerge from self-organizing teams.

Build projects around motivated individuals. Give them the environment and support they need, and trust them to get the job done.

The need for frequent iteration and adaptability to change means that we naturally tend to build multiple, interconnected, simple solutions, each with a very specific focus, instead of consolidated systems. Of course, many will argue, correctly, that this leads to redundancy. However, we find that complex systems are less likely to adjust quickly enough in our dynamic environment. As a result, even though consolidation might save some resources in the short term, in the longer term we risk losing the whole system if we fail to change it fast enough. We develop small, tightly focused systems that do just a few things, but do them really well.

This has implications for our organizational structure. There is really no rigid hierarchy in our teams. We do not, for example, have a single team of software architects, with whom all development teams consult. Instead, each team takes responsibility for all aspects of each small, well-defined project. Exactly as specified in Conway’s law, the overall architecture of our systems more or less reflects our organizational structure.

Much of the decision making is decentralized, which means that most decisions are made at the lowest possible level, by the users who requested a feature and the teams that are implementing it.

As well as making most of the day-to-day decisions, our teams are free to choose the languages, tools, design, and coding style best-suited for the project. Our company has neither common coding standards nor a common technology stack. Different teams use different languages. We build each system such that it does not depend on the implementation details of any other system, so this approach that may seem haphazard to some, works out well for us.

Impact of Agile Principles on Database Development

Over the coming sections, I’ll drill deeper into what these agile principles mean for the way in which we develop our databases. Some of these approaches are “non-standard” and may surprise a few developers considering a move to an agile team.

Automated Database Testing

No agile development effort will succeed in the long term without the automated testing that gives us the confidence that we will spot immediately, and fix, any problems or errors we introduce as a result of our frequent changes.

Likewise, we need to spend time implementing a comprehensive database testing suite. I don’t have room to cover this topic in detail, but will refer you to my Close these Loopholes series of articles, and references therein, describing how to make “watertight” your suite of database unit tests.

Besides unit tests, we use other kinds of automated tests extensively:

• A comprehensive suite of integration tests verifies that individual modules correctly fit together. Our integration tests do not mock databases; we use the real thing
• Stress tests verify that our modifications work correctly under high concurrency
• Performance tests verify that performance meets users’ expectations.

Of course, the principle of evolving and simplifying the software we develop, over many iterations, applies also to the tools we develop to help us do this. Our original library for unit testing T-SQL had many features, including separate checks for the following conditions:

1. How many rows are there in a result set?
2. How many result sets are returned?
3. Is the result set ordered by some columns?
4. Which value is in some particular cell of some particular result set?
5. Exactly which tables and rows were touched by a modification?

Through using the tool, we started to realize that this abundance of features did not make us more productive. For example, even though we’d invested time in developing a way to test our database modifications, we learned that that, in practice, we were more productive if we did not explicitly test our data modification stored procedures (see How to Benefit from Unit Testing T-SQL: choosing what not to test for more details). Instead, we began to use these stored procedures to set up test data. We removed the unnecessary feature, explicitly testing modifications, and our next iteration of the testing tool had only one feature:

Verify all the output of some T-SQL, all the result sets and all the values in them, including their order

So, we ended up with a solution does only what we need and is implemented really well. It automates our common tasks to the fullest, and it has no bugs.

Silos instead of Consolidation

As discussed earlier, our teams build small, tightly-focused and highly-adaptable systems. As a natural consequence, we tend not to build large, consolidated systems. Instead, we strive for multiple interconnected systems that communicate via simple interfaces, with each system having no dependence on the implementation details of another system.

This attitude extends to our databases. We do not have one big database that stores all kinds of data for everyone. Instead, we prefer to develop separate systems that communicate via simple interfaces without depending on each other’s implementation details. We would not try to build a system to “absorb” and consolidate individual silos.

When we store our data in silos, we accept the fact that there is considerable redundancy between these silos. However, the reward is that each system remains simple and adaptable and we remove the risk of a single large system becoming obsolete, and failing to satisfy users’ requirements, simply because we cannot change it quickly enough.

As a hypothetical example (I am no expert in addresses and shipping), suppose we have a Shipping application, with a database that currently stores US addresses and other information related to shipping via mail within the USA. We now need to support shipping to a different country, such as Russia.

According to the DRY (don’t-repeat-yourself) principle, the obvious choice is to extend the current database design to begin storing Russian addresses. We avoid repetition and store all addresses in one system. However, is the choice really so straightforward? Currently, we have simple business rules in place to enforce the presentation of US shipping addresses in the format “City, State” (“Medford, WI”, and so on). Typical Russian addresses come in a variety of formats, such as “City” (for very big cities only), or “Province, City” or “Province, District, City”. To accommodate these, and many other differences, will substantially increase the complexity of our current, simple, US Shipping application.

As agile developers, we strive to avoid complexity and all the expensive problems caused by it. Quoting Ray Ozzie:

Complexity kills. Complexity sucks the life out of users, developers and IT. Complexity makes products difficult to plan, build, test and use. Complexity introduces security challenges. Complexity causes administrator frustration.

As a result, we may instead decide to implement a new Russian Shipping application, with a separate database, tightly focused on implementing our Russian business rules. If we need an aggregated view of all the company’s shipping data, we develop that as a separate system as well.

We will definitely be paying a price for redundancy, and for establishing communication between these separate systems, but this price may be lower than the price of maintaining one “monster” system that becomes more difficult to maintain, and almost impossible to change quickly, the bigger it grows.

To put it another way, if we build cars for a country that drives on the right, we put the driver’s seat on the left. If we start to supply cars to a country that drives on the left, we build a new car with the driver’s seat on the right. We do not try to develop a car where we can move the driver’s seat from right to left; such a car is likely to be both expensive and unreliable.

Frequent Database Refactoring

In previous sections, I discussed the need for a development process that allows software to evolve and simplify over many iterations. While this is an increasingly popular approach for application development, many still object to the idea of evolving database structure, stating that iterating it is too expensive to be feasible.

While the cost to the organization, and users, of changing the table structure remains prohibitively high then these objections are quite valid. Each time we have to shut down the system, migrate the data to the new structure, and re-deploy all the applications that depend on the database, all at the same time, requires downtime for our clients, overtime for us and our DBAs, and carries a high risk of something going wrong, leading to several cycles of expensive bug fixing. Everybody wants to avoid this sort of major surgery unless it is absolutely necessary.

Changing the structure of database tables is neither cheap nor easy, no matter how we do it, especially when the amount of data being restructured is large or users need constant access to the data. This is why many people recommend spending time up front to get the database structure right, to minimize the need for future change to database design, even if you allow the rest of the system to evolve over time.

The problem I see with applying “BDUF” to databases is a strong tendency to over-engineer table structures in attempting to anticipate every possible requirement. In most cases, such efforts end in defeat, as the requirements of the users change in ways the designer simply cannot anticipate. When this happens, not only is our “future-proof” design obsolete regardless of our efforts, but the resulting schema is overly-complicated and therefore harder to change.

As an example, let’s consider a small and ambitious e-business company that, some years ago, needed a system that would store all its customers’ details in an RDBMS database. It is currently a US-only company, and uses a ten-digit phone number to uniquely identify each customer, but has ambitious plans to expand its operations to other countries. They expect aggressive growth in the number of customers and need the database to be available 24×7, except maybe a few major holidays.

In designing the parent and child tables to store all the customer’s information, we might anticipate the need to, for example:

• Support country codes
• Validate phone numbers for different countries
• Support phone number extensions
• Allow several family members have separate accounts associated with the same landline phone number
• Distinguish phones and faxes

The team puts in considerable time and effort to make sure that the database design meets all these possible requirements. As a result, the product launch is delayed and other solutions appear on the market, in the meantime. This leads to profit and opportunity loss.

To add insult to injury, soon after our system is fully operational, the world around us changes in an unanticipated way: everybody else use emails to uniquely identify customers, and we need to catch up, and quickly. However, this is not necessarily straightforward because it will take time and effort to change our complicated design. We also have the time and effort wasted, developing features that have never been used.

So, although we have worked very hard, we did not actually manage to avoid restructuring our tables.

In our experience, trying to future-proof database design and accommodate for all possible future changes is usually counter-productive. Instead, when the environment is very dynamic and unanticipated change is very likely, we should design and develop databases defensively, so that we can refactor them with confidence, at a reasonable price and with low risk.

For a start, instead of trying to anticipate every requirement, we implement a database design that supports only what is required right now i.e. customer identification via 10-digit phone numbers. Then, we focus on making the schema resilient to change. We insulate all of our databases behind a well-defined interface. We do not expose tables and views to our users and instead provide an API of stored procedures. This allows us to change the procedures or the underlying tables without the risk of breaking applications. We also employ defensive design techniques that enable change. For example, we can add an identity column, and have all child tables refer to it, so that if we need to switch from phone numbers to some other customer identifier, there will be less changes in child tables. We then cover the whole system with a comprehensive suite of automated tests, so that we can change it with confidence when needed.

Also, in order to make future changes easier and cheaper, we invest time and effort in automated tests, as we discussed previously.

Refactoring database tables to keep up with changing requirements is still not cheap, but it may be substantially cheaper that trying to design up front for all possible changes in the future. In fact, in our experience, database refactoring is not prohibitively expensive at all. Our team can refactor live OLTP tables with no downtime for our users, no overtime for us and our DBAs, and with little risk. For further details on how we achieve this, please refer to the section, How to refactor a table without downtime in the article, Developing Low Maintenance Databases.

We have done it many times, always successfully, and this experience makes us very confident that, if needed, we can refactor database tables.

Emphasis on Minimizing Maintenance

If our database systems require a lot of maintenance, it distracts us from new development. It is unrealistic to expect us to quickly develop new features while we have to troubleshoot deadlocks, speed up slow queries, and such, all at the same time.

As such, keeping our database system low-maintenance is essential. The previously-referenced article on developing low maintenance databases also contains sections on other techniques that we use to keep maintenance costs low, including techniques to remove deadlocks, extensive use of constraints to ensure data integrity, and so on.

All these defensive approaches require some initial investment in time and effort, so that we do not lose precious time later, on troubleshooting.

Less emphasis on Complex Queries and Query Tuning

In dynamic environments, we do not know for how long we will require any particular module. A query that is essential today may soon become obsolete. Every time we change substantially, or just throw away, a query, so we also throw away all the effort spent tuning it.

This is why we do not want to spend too much time tuning queries. Of course, we employ as many sounds database programming techniques as possible to ensure we do not “kill” database performance. For example, we prefer to save and retrieve sets of rows, we use proper indexes and write index-friendly queries, and we do not reuse selects wrapped in stored procedures and scalar UDFs, and so on. In other words, we do all the basic groundwork to ensure reasonable performance, but we do not often go much further than that.

If this basic groundwork does not deliver acceptable performance, we would often rather add more hardware than spend a lot of time on intricate performance tuning. The reason is simple: if we need to change or retire a query or the whole system, the hardware stays with us and keeps being useful on other projects. On the other hand, all the time spent squeezing more performance out of a query is lost the next time we change our system.

Our team, and our users, place a high value on predictable performance. From our users’ perspective, a certain response time may be essential, and we may be willing to invest in hardware to ensure it. Suppose, for example, that we are required to ensure a response time of 0.5 seconds or less. One query consistently returns in 0.3 – 0.4 seconds, so it meets the requirements. Another query usually returns in 0.01 seconds, but intermittently takes more than 2 seconds, due to a different choice of execution plan. Even though on average the second query runs faster, and uses fewer resources we would rather use the first query, and add more hardware if needed.

Needless to say, complex SQL queries are less likely to deliver predictable performance. As such, they are less likely to stay around. As the time goes by, we replace complex queries with simpler ones, and we do not develop many new complex SQL queries at all.

Preference for Simple Tools and Techniques

Throughput this article, a recurring theme has been the need for simple, independent systems each of which performs a single function very well and is adaptable to change. In my experience, in order to achieve this it is important not to constrain a development team by imposition of ‘universal’ coding standards, approaches or tools.

In our organization, different teams take different approaches to database programming. For example, some teams do their own database design and SQL programming whereas others prefer to get help from the DBA. However, all teams benefit from being free to choose the tools and approaches that work best for them. In the true spirit of the agile approach, these tools will often be very simple and minimalistic. Just as the systems we build must be adaptable to change, so must the tools we use. As described earlier, over a few iterations our SQL unit testing tool has evolved towards simplicity and we could change to a different approach, if required, quite easily.

As another example, we evaluated SSIS for data loading and while it has many powerful capabilities, we only needed a small fraction of them and found it a complex tool to use.

For better productivity and agility, we decided to develop our own data loading library. We load data using just a few simple classes, written in C#, so that we completely understand how data gets loaded, and we can debug any problems easily, and change anything quickly. Many may scorn the idea of writing our own data loading tool as “reinventing the wheel”, but it works out for us, in this case, since the library is simple to use, does exactly what we want and nothing else.

Conclusion

In this article, I’ve attempted to summarize the core principles than underpin agile development, and then describe their direct impact on how we develop databases in an agile fashion. Many aspects of agile development may seem strange and counter-intuitive to a developer unfamiliar with working practices in an agile team, but hopefully with an understanding of how the pieces of the puzzle fit together, it all eventually begins to make sense.

Certainly, the principles, techniques and tools that I’ve described have been a direct benefit to our in-house development team, allowing us to produce, consistently and quickly, high quality software that meets the exact needs of our business and of our users.

Hopefully, this article will at least have made you curious to learn more about the agile methodology, and how to apply it to database development. Good luck with your learning!