Introduction to Agile with SQL Server

Working with SQL Server in Agile software development can be difficult. Ben Brumm provides an overview of Agile, specifically Scrum, and what to expect when SQL Server, or really any SQL platform, is involved.

Overview of Agile

Agile software development can be broken down into several methodologies, such as Scrum and XP (Extreme Programming). For the purposes of this article I’ll be referring to Scrum but the theories apply to Agile as a whole.

Agile software development is a set of methods and practices based on the Agile Manifesto, which focuses on responding to change, short iterations of work, and team self-organization.

Scrum is one of the Agile methodologies that involves a team typically working in two to four-week iterations called sprints. These iterations have a fixed scope, and, outside the iteration, the Product Owner prioritizes a list of features on the product backlog.

XP is another Agile methodology, similar to Scrum, where teams work in iterations. The iterations are generally one to two weeks long. There are a set of practices in XP that may or may not be used by Scrum, such as test-driven development and paired programming

So, if you haven’t worked in an Agile environment, or are unsure what it refers to, here are the main points:

  • Smaller requirements, often referred to as ‘user stories’
  • Focus on business value and not solution functionality
  • Short iterations (2 to 4 weeks usually) and regular feedback
  • Testing and development happens at the same time

There are many benefits to working with Agile processes, but if you’re working with SQL Server, there can be some difficulties.

Business Value is Hard to Define

The focus of requirements and development in an Agile environment is on business value. The requirements, often written as user stories, focus on what the business value is. If the value of building something can’t be defined, then it shouldn’t be built.

A common template for writing user stories is ‘As a/I want/So that’:

The ‘so that’ line is used to define the reason why something is being built, or the business benefit. This can be easily defined for many features, especially if the main output is a web-based system.

However, in software projects, the data behind the application is just as important. Business users want to see how the data is being used and created, so they can report on it and make better decisions. This often means that reports need to be created, whether it’s in SQL Server Reporting Services or something else. In either case, there’s a lot of work by the developers on the team to get reports working.

The benefits of the reports are obvious, but there’s a lot of work that goes on behind the scenes, such as the ETL (extract, transform, and load) process and data investigation that consumers of the reports may not know about. How can the hidden work required to deliver the reports be defined as valuable for the business? It can be tempting to write a user story or a requirement as:

However, this requirement is quite large and doesn’t express the business value. When you’re working on SQL Server, you often work on a lot of behind-the-scenes work, stuff that the business user doesn’t see.

It would be difficult to develop requirements for a request this broad. It’s up to the business analyst, the product owner, and the team to discuss this and ensure that the requirements are written correctly. The tasks that come from these requirements can be technical tasks, such as developing or enhancing the ETL process, but the overall requirement should be business value focused.

A better example of this requirement could be:

This user story is more focused. It only mentions ‘active customer records’, which we have assumed are part of the data that needs to be loaded. The team only needs to focus on loading this data, which is likely to be easier than developing an ETL process for all data.

Teams Often Have Conflicting Priorities

If you’re working on a project that uses SQL Server, then you’ll probably have a few different areas that need development:

  • Report creation in SSRS
  • ETL processes
  • Database design and development for a related application

The balance of these priorities can be difficult. On one hand, you want to build the reports that the business is asking for, but you also need to develop the ETL processes, such as SSIS packages, to get the data from where it is to where you need it to be. Finally, you might also need to work with other web development teams to provide them with access to the data or explain the design of the database to them. These priorities can all conflict with each other and can cause delays in the project and issues with the business if they are not addressed.

Who gets to define the priorities? Who says that a new report is more important than a new feature for a web-based tool? Who decides that developing or fixing the ETL process takes priority?

This is where the product owner comes in. On an Agile project, such as one that uses Scrum, there is a single product owner and they are responsible for defining the priority of work. It’s up to the team to describe each piece of functionality or requirement to help the product owner understand, but ultimately, it’s the product owner’s decision.

The product owner is usually from the business side and has the authority to decide what needs to take precedence. This should, in theory, solve a lot of the issues about what the team should work on, however, sometimes there isn’t a single product owner. Perhaps the team working on the SQL Server database is working with several projects and teams, all of which have different product owners. Who gets to decide then? In that case, it would be up to the product owners to determine the priority. They would need to advise the team on what is most important.

The team should be able to provide input and explain what they think is important. For example, the product owners might not think that fixing an ETL bug is important, but if it’s not fixed, then it can cause issues and delays with the application. Once this is explained, the product owners may feel this is important.

Ideally this strategy should work to resolve the priority problems, but working in an Agile environment with SQL Server doesn’t always go as smoothly as this.

Testing is Different and Often Harder

If you’re working with SQL Server, and database code in general, it can be hard to run tests. In an ideal Agile environment an automated test suite is run regularly to ensure that any bugs are found early and can be fixed. The development team follows ‘test driven development’, meaning that unit tests are written before or alongside the development work which results in higher quality code.

This is well documented if you’re working on .NET code, but if you’re working on SQL Server and are developing database changes, how do you manage testing for new tables, views, stored procedures, and other processes? Well, the same principles can be followed.

The most important, I think, is having automated tests. The good news is that a lot of this can be easily developed in a database. It could be a series of SQL statements, perhaps inside a stored procedure. This code would then be run regularly, and the aim would be to find any bugs that appear.

The tests would include checking everything that has been developed: database views and tables for the web application to access, or reports to be run. Queries could be written to test that data exists and that counts and sums are within an expected range.

I don’t think this is documented as well as automated testing on a web application, and I’ve been in projects where this wasn’t done. I think there’s a big benefit in testing, as it ensures that the database stays operational and bugs are reduced.

A previous project I worked on had a web application running on an SQL Server database, which was also used for creating a data warehouse and for reporting. The kind of work the users did meant that the accuracy of the data in the data warehouse was their number one priority. There were many issues and delays on the project caused by incorrect data. In this situation, having an automated test suite, or even a manual test suite that checked the data, would have been helpful. It could have been run every day, or whenever something changed, to ensure that the data is still correct, and no issues have been found.

Creating and running tests on an SQL Server database can be harder than for a web application, but it’s possible and worth the effort.

Source Code Management is Often Forgotten

When working with application code, it’s necessary to have a place to store your source code. This doesn’t mean the code stored as the deployed application or on different environments. This should be a source code repository, such as Microsoft’s Team Foundation Server (TFS).

When you’re working with database code, how does source code management work? The application is defined by the code that is written. However, the database is actually defined by the objects inside, not by the code that was used to create it. They are two different things. This makes it challenging for teams to manage the source code for their database.

For example, a customer table might be created early in the project. This is then used by the application and data is added to it. Then, the table needs to be altered to add an extra column, because a new feature has been developed in the application. If we use the original scripts to create the table, they might not include that extra column that we added.

A previous project I was on did not have any source code management solution for their database code. All their code was stored in different places – a combination of the dev/test/prod servers. They also had a project file on TFS, but it wasn’t used correctly. It caused issues when older versions of code were deployed by mistake or other teams developed on the wrong versions of tables or procedures.

If you’re working with SQL Server on an agile project, you should use a source code management solution, and develop a process on how it is to be used. Yes, it’s a bit harder than managing source code for web sites, but it solves a lot of problems for your team and other teams.

In terms of the details on how to do it, there are other articles you can read, such as DevOps and “Shift Left” for Databases, and DevOps and Database Lifecycle Management. However, there are a few things to keep in mind.

Just like source code management for your website code, the source code repository should be the central place where source code is used and stored. If a stored procedure needs to be changed, then change the source code, check it in, and deploy it to a dev/test server. Don’t download the production or test code and change that. This is where wrong versions come from.

As a team, you’ll need to work out the best way to store everything you need inside the source code management solution, and develop processes which the team must follow. This includes scripts for creating the database, inserting any reference data, and creating all the other objects such as views and functions.

There Are Positives Too!

After reading the sections above, you might think that it’s all bad news when working with SQL Server. That’s not true. There are plenty of good reasons and experiences from working with SQL Server in an Agile environment.

Integrated with Microsoft Products

One of the great things about Microsoft’s development products is that they all integrate with each other well. Visual Studio is a great IDE for developing applications, which integrates with SQL Server. 

SQL Server Management Studio, the IDE for developing SQL code and running queries, works well. It’s integrated with the other database tools such as SSIS, SSAS, and SSRS.

These can all integrate with TFS and other Microsoft tools. Teams can also use Visual Studio with SSDT instead of SSMS. Using SQL Server brings with it a range of easy integrations with other Microsoft tools.

It’s Widely Used

SQL Server is a widely used database. SQL Server and Oracle Database are the two most popular database management systems I’ve seen in companies. As a result, there’s a lot of support available. Articles, other blogs, Stack Overflow questions, and other resources all help when you’re working on a problem.

There’s also a lot of people working with SQL Server that you can ask for help. You might know a few, such as current and former co-workers. You can also use the hashtag #sqlhelp on Twitter to find answers to questions you might have.

Conclusion

If you’re working in an Agile environment, or on a project that uses an Agile methodology, then using SQL Server has its own set of challenges. There are a few things to watch out for, and some are more important than others, depending on if you have a single team or if there’s a bigger solution involving data warehouses and reports.

There are some great benefits to using SQL Server in general and specifically for Agile projects. If you keep the issues in mind, develop processes, and talk about the challenges when they come up in your project, you should be able to avoid or reduce them in your team.