Issue Tracking for Databases

Any database development project will be hard to manage without a system for reporting bugs in the code, anomalies and incidents from live environments, and for keeping track of new features. Matthew Skelton discusses the critical role of an integrated issue tracking system in database lifecycle management.

DevOps, Continuous Delivery & Database Lifecycle Management
Culture and Organization

An issue tracking system is a repository of all the issues found in the application or product being developed, and a record of all the steps taken to remove the problem. Also recorded are the ‘amelioration’ steps, the necessary precautionary steps to make sure the effect of any issue or defect is minimized if it is already in the production system.

An effective issue tracking system is critical for any project, but are especially important for larger projects where communication is the key to rapid delivery. Within organizations where databases tend to be at the hub of a number of different teams and processes, there are many advantages to managing issue-tracking properly.

Issue reporting

Database issues can be uncovered by a variety of test exercises, such as unit tests, build, integration test, performance tests or scalability tests. As well as being identified by testing, issues can come from helpdesks, operations and monitoring, as well as from feedback from the application itself, existing end-users or even reports in the media. As soon as an issue is reported, its existence is alerted to the team. This can be done by email, or preferably through a team-coordination tool.

When an issue is reported, it must be recorded. The report should, if possible, include the identity of who reported it, when and how it was found, and the steps to reproduce it. Enough of an investigation must be made to make a reasonable guess at the following questions.

  • Reality: Is the issue confirmed, or is it an artefact, possibly due to faulty hardware, incorrect installation, network issues and so on.
  • Ramifications: How important is the issue? Is this, for example, going to impede the work of the development team? Will it affect time to delivery?
  • Complexity: How much time and resources will it take to fix? How complex is it, and how easy to reproduce and test?
  • Severity: Does the issue affect production systems (e.g. does it cause a security vulnerability, is there a risk of financial loss or legal action against the company)
  • Loc ation: Where in the database application, as near as possible, is the issue occurring?

In the light of these judgements, several actions need to be taken. A severe issue that affects production systems will require very different scale of action to a peripheral edge-case that is relatively harmless and unlikely to be seen in production.

At this stage ownership needs to be assigned.

A lifecycle is normally attached to an issue. There are a variety of workflow systems around the issue tracking and fixing process. A project manager will seek to make sure that issues are fixed in order of importance, but relative importance is always a matter of judgement.

Issue tracking systems provide valuable feedback to managers and other parts of governance who need to get a feel for the backlog of a project and even some of its technical debt.

Problems caused by poorly controlled, reactive issue tracking

At its most rudimentary, an issue tracking system is little more than a ‘dumping ground’ for bugs. It means that bugs and other issues are less likely to get lost, but no other benefits are gained. Some organizations fall into the habit of keeping track of issues by sending round a flurry of emails when an issue occurs or a new requirement is raised. Although email can be useful for following up on specific issues, tracking issues by email does not scale, keeps details hidden, and prevents issue tracking activities from integrating easily with other tooling. Common problems with this approach include:

  • Issues tend to be recorded in an unsystematic way – issue descriptions are poor; vital information such as the steps needed to reproduce the issue are skipped.
  • Issues tend to be owned by the tester – whoever finds the issue is assumed to be responsible for ensuring that the issue is fixed.
  • Issue backlog grows rapidly – No system in place for allowing the developer the time and resources to fix the issue; no adjustment of deadlines and ‘diamond points’ for delivery of functionality.
  • Issues fester – Hard to determine when am issue is fixed. Again, this requires careful testing and assignment of time to do the work.

With this instinctive, ‘chaotic’ approach to issue tracking, the chore of bug-fixing can get unbalanced because the allocation process is so haphazard. The developers most willing to fix bugs experience the brunt of the burden, and risk slipping behind on their development work. In the worst cases, these developers begin to be considered guilty by association, and even accused of creating the bugs. The ‘blame-game’ can get entirely out of hand.

It is much more effective to have a process in place that can be supported by an issue-tracking tool so that the process is, at least, repeatable.

Integration into a DLM approach

We see the most benefits of database issue-tracking when it is integrated with the issue tracking systems of applications, and when other tools in the database lifecycle can report issues to it. These contributing tools might include the version control system, documentation systems, user feedback systems, ETL systems, downstream reporting and analysis, and so on.

Any issue tracking tool that we choose should have an API so we can easily integrate with other tools and extend the behavior of the tool when new approaches require it. Whatever is used, we must be able to automate aspects of reporting and issue management via the API.

When an integrated and efficient issue tracking system is in place, it becomes instinctive to check it after any change to the production system, such as the application of patches. A sudden increase in reports of issues can quickly alert to a deployment problem even if there is no apparent link between the symptoms of the issue and the original action.

Integrating the issue tracking of application(s) and database

It is often not straightforward to pin down the exact cause of the issue. A database problem can, for example, cause mysterious application problems that are not immediately attributable to the database. Also, issues that seem to be caused by the database are often eventually found to be within the application, and the converse is just as true. It sometimes happens that a database problem can cause mysterious application problems that are not immediately attributable to the database, but can be quickly spotted by a database developer, such as when database warnings are discarded by the interface.

If database issue tracking is integrated with the application issue tracking, then database experts, whether they are developers or DBAs, can help a great deal in quickly working out what is causing the issue and where it is happening.

A common problem we see in IT departments is that each team has been free to choose its own issue tracking tool. The Development team usually chooses a lightweight tool that integrates with version control but that has little support for Production incidents, while the Ops/DBA team tends to choose a tool that excels at service management but has little or no support for version control integration or agile/iterative software development. The result is that knowledge is difficult to share, and cooperation between DBAs/Ops people and Developers suffers in consequence.

The issue tracker you choose to support DLM should be accessible to both Development teams and Ops/DBA teams, as well as to application support staff, and have the functionality to support all their requirements. Tickets need to be visible and shareable between Dev and Ops/DBA teams (where a ‘ticket’ means the details of a single issue, bug, or requirement that we track with our issues tracking tool). Ticket workflows need to be configurable and flexible; at a minimum, tickets must be cross-linkable between Dev-focused areas of the tracker and Ops-focused areas, even if Dev areas use different workflows to Ops/DBA areas.

2377-issuetrackingdiagram.png

Figure 1

Sometimes, it’s not possible for the two teams to use the same issue tracker, due to vastly different requirements. Some dev teams work best off of simple prioritized lists, whereas the operations team need to track a lot of different moving pieces that all need to be dealt with at once. In such cases, then at least give each team access to the other’s system, so they can collaborate.

It’s very important that bug amelioration (workarounds) should be visible to support, and also that the right people are able to see issue reports that could end up being financially or legally damaging.

Whichever tool you choose to use, make sure that knowledge and awareness of issues are not kept in separate silos, and that the way in which the tool is configured helps with collaboration and communication between different groups.

Be wary of issue tracking tools designed for service desks.

Service desks, with an IT Operation team, are often set up to issue tickets associated with support calls from a single individual. Default ticket-access permissions tend to hide ticket details from everyone except the person who first happened to report the problem. In my experience, these tools are not suitable for use as issue trackers for team-based development efforts.

Atlassian JIRA is a commonly-used tool for issue tracking, for shops that have mature software delivery approaches, such as DLM and Continuous Delivery. Other strong tools are Team Foundation Server (TFS), a manual Kanban board, and AgileZen

Atlassian JIRA has third-party integrations and programmability via an API, making it easier to configure different workflows for different teams – Agile, Kanban, Service Desk – so avoiding a very common trap of having a different issue tracking tool in each’ department’.

VCS integration

The most obvious advantage that is offered by a DLM approach to issue tracking is in the integration of the issue tracking system into version control. If a change to an object is made in response to an issue report, then the ID of the issue is placed in the header of the code. When this is saved into version control, this alerts the tester, who will then need to confirm that the new build fixes the issue.

Issue trackers that integrate with version control can provide end-to-end traceability for database and application changes. For example, the issue tracker tool you choose should be able to scan commit messages in your VCS for ticket IDs. This means that within the web UI comments and collaboration on commits can be seen alongside the commits, which helps to make technical decisions ‘flow’ through version control.

VCS integration with issue tracking: examples

  • GitHub’s integrated issue tracking system looks for Github IDs in Git commit messages of the form #Num (e.g. #123 or #71), and auto-links to the corresponding ID from within the application.
  • The private code-hosting tool CodebaseHQ uses [touch: NUM] as the syntax in commit VCS messages for referring to issues in its tracker; a commit message of “[touch 71] Fix the URL formatting” will link the commit with issue #71, adding details of the commit to the issue tracker.
  • Other tools have similar schemes.

Integration with customer feedback tools

When a team practices short, frequent cycles of database software delivery, based on customer requests and feedback, it becomes important to create a direct links between these requests, and specific issues in the tracking system. The team is on the lookout for opportunities to link issues and user-requests, as reported using a tool such as UserVoice. If a feature is not widely used, according to usage tracking statistics, then the request or reported bug can be de-prioritized.

In essence, we extend the traditional definition of a bug or issue to encompass ‘deficiencies’ in the way that existing features work, i.e. indications that the software doesn’t do what the users need it to do.

Make it as easy as possible to process issues

The team needs to make issues as easy as possible to process, thereby avoiding the buildup of a massive backlog that nobody then has the energy to fix.

We’ll cover a few ways that we can improve the issue allocation and processing efficiency here, but it will also involve efforts to improve the instrumentation of database code and processes, and improve the quality of code reviews.

Use meaningful issue descriptions

The title of the ticket should contain enough detail that other team members can understand the context of the ticket. Generally, it should generally enable the team to do top-level prioritization of tickets in the issue tracker without looking at the full details.

Titles such as ‘Test failures’ and ‘Stored procedure needs updating’ do not convey much useful information. Better examples include:

  • Data load fails when number of locations exceeds 12000
  • Extend the Customer table to include Twitter handle
  • Recent new index on DeliveryPreference causes weekly data extract to fail

Cross-link to other tickets where appropriate and include screenshots, sections of log messages, and extracts of configuration files where these provide context for other people looking at the description.

Include details of how to reproduce the problem

Issue tracker bug or incident tickets often have a ‘steps to reproduce’ section. These are immensely useful when attempting to debug the problem, and also helps to remind the person who creates the ticket to double-check that the problem really exists.

Use it; it helps to avoid duplicates and false alarms. Also, after you have written out the steps to reproduce, follow the steps yourself again in order to verify that the problem is indeed exactly as you describe.

Tag issues that relate to audit and compliance

For tickets that may relate to areas of the system that are geared for the requirements of auditors or compliance people, use search tags that help aggregate groups of related tickets. For example, if you are creating or updating a ticket that relates to Personally Identifiable Information (PII), then consider using a tag like ‘PII’ or ‘SensitiveData’ so that people who need to know about these areas can be informed easily.

Make all tickets visible to all people and teams

Everyone involved in building and operating the software systems should be able to search and view every issue. At least all people in the technology department should have access, perhaps not to modify, but certainly to comment on all tickets.

As a rule, perhaps 1 in 500 tickets might contain sensitive data, but such tickets should be dealt with on an exemption basis, or better, the details moved to a secure area. The security needs of a tiny fraction of tickets should not override the need for transparency and sharing between teams. This implies that ‘service desk’ setups (for example, to fix problems with the CEO’s Blackberry or iPhone) should be separate from issue trackers for core software systems. It also means that if parts of the delivery and operations process are outsourced, some teams will need to use issue trackers of either their client or their supplier.

Automate tests to recreate bug conditions

The development team needs to explore ways of creating automated tests to re-create bug conditions before bugs are fixed, to speed the process, and to instrument the database in such a way that alerts are fired if the bug recurs, or similar bugs appear. For example, in order to reproduce the conditions for a bug, we may need to create an automated test that generates memory pressure on the target database server. Automation of testing will free up testers to allow them to participate in the end-of-life of a bug by certifying that it can no longer be reproduced as a result of the bug fix. See the Database Testing article for further coverage of this topic.

Have a very clear escalation strategy

Database bugs can cause corruption and data-loss that can be very damaging to the organization. Most likely, your business needs the problem solving now, if not sooner. Enter the hotfix.

A hotfix is a single, cumulative package that is used to address a problem in a software product. DBAs deal routinely with hotfixes that are applied to SQL Server to fix a specific problem such as a security vulnerability.

DBAs are also used to doing controlled changes to production databases, most commonly to fix a performance problem. A hotfix is a perfectly normal outcome from an issue in order to resolve it. It is merely a workflow item in the resolution of an issue and has to be tracked in the same way as a patch, an amelioration or new release.

At some point in the lifecycle of an issue, the decision has to be made as to when to apply the fix to the database system. It can just be included in the next release. It may have to be done as a hotfix to the current production version as well as being enshrined in the next version of the database. In certain cases, the hotfix solution will be different from the permanent solution.

Sometimes, though, they have to be done urgently outside office-hours. These ‘Hotfixes’ must then be retro-fitted into the issue-tracking system so that the change to the system that was made is not lost to the system. Any subsequent change to the source code needs to be tagged to the hotfix so that everyone is reminded of why and when the change was made. The system must be told that it was added in retrospect to prevent a security/auditing alert.

Automate documentation of fixes

If the documentation process that associates a fix with a particular release is not automated then it can quickly become too time-consuming. A database developer can develop many ways of minimizing the chore. The comment headers of routines such as views, functions and procedures are usually the most convenient place to apply notes about the issue that a change resolves. These can then be read via SQL code and used to update the issue-tracking system. If the database developer uses a temporary user identity for each issue, it is easy to subsequently read the default trace to extract all the DDL code that was used to make the change. This can then be pasted into the issue report.

Perform ‘autopsies’ for future protection

Each issue should have an ‘autopsy’ which looks at ways of protecting the database system against similar problems, to find out what lessons can be learned. For example, if a table is successfully accessed by a user that was not supposed to have access rights, then it is worth considering if there are other related vulnerabilities that exist, or whether the entire user-access strategy is wrong. The accent is on prevention.

Reports need to be driven by the essential information of describing what happened, when, who fixed it, and how they went about doing it. If a similar incident happens subsequently, this helps the debugging process a great deal.

There is more that can be done, of course. It is possible to triage bug and incident reports, looking for repeating patterns, related tickets and longer-running problems. Track incident priorities (P1, P2, P3, etc.) along with metrics such as Mean Time to Detect (MTTD), Meant Time to Resolve (MTTR) and Meant Time between Failures (MTBF).

Your goal is to improve the system across the board, not just in specific areas. Regular ticket reviews help to gather undisputable metrics that form the basis of informed decisions rather than guesswork.

Summarizing the benefits DLM-based issue tracking

Keeping track of new features, testing anomalies, and incidents from live environments is an important part of DLM. In fact, issue tracking addresses all the TRIM aspects of DLM:

  • Traceability and Visibility – issue tracking provides a rapid way to search for known problems or possible causes, surfacing details of system behavior
  • Repeatability – issue tracking helps us to focus on capturing enough detail to be able to reproduce problems
  • Improvability – issue tracking provides a focus for improvements
  • Measurability – issue tracking helps us to track metrics around different kinds of problems and requirements

As the teams gradually integrate issue tracking into a DLM approach, it becomes accepted as being intrinsically part of the database development process and the management of the process becomes a team responsibility.

Issue tracking provides traceability

Effective issue tracking helps us to provide this traceability by linking version control commits to an issue tracking ID, and by associating sub-tasks with an over-arching issue tracker ticket ID. The issue tracker becomes part of a ‘single source of truth’ for traceability and audit. Essentially, we can build change management off of issue tracking, though they shouldn’t be the same system, since the former requires the information in a different form (an executive summary and less detail, typically).

However, for software systems that are subject to external regulation, such as in finance, healthcare, credit card payments and so on, or internal regulation, such as for due diligence prior to a takeover, an efficient issue tracking system can often provide proof that changes were made only according to an approved requirement or request.

Issue tracking helps us to make better decisions

To give value, the issue-tracking system must provide a coherent way for changes to be linked together so that people can easily discover the wider context for themselves and therefore make decisions from a more informed viewpoint. If this isn’t done, it can happen that, by the time that DBAs receive a request for change or a schema update script, the DBA has very little context for the change; the business driver for the update has been lost somewhere ‘upstream’, perhaps with the Development team.

Issue tracking should provide insights into our software systems

A good issue-tracking system will help a development team that is working well. Otherwise, they will be a tiresome chore that produce white noise. They are most valuable when they can quickly show who made any particular change and why. They also sometimes turn the nagging suspicion that a bug ‘rings a bell’ and might have happened before into golden route to fixing the new issue. If the system is easily searchable it is possible to avoid re-fixing almost identical bugs over and over again. We often need to look for trends or past examples of issues in order to help us to understand current or future problems. Good issue tracking practices can help to provide these deep insights into our software systems, allowing us to draw inferences about behavior and incident causes.

Issue tracking helps us to reduce problems and costs

By adopting good issue tracking practices, we aim to reduce problems relating to testing, deployment, and live operation of database-driven systems. Over time, we are able to identify areas where the cost of maintenance is high, where deployments regularly go wrong, and where we have the wrong skills mix within a team. By collecting data and information methodically in an issue tracking system, we can address these problems in concrete terms with statistics and charts, rather than relying on people’s fallible memory.

DevOps, Continuous Delivery & Database Lifecycle Management
Go to the Simple Talk library to find more articles, or visit www.red-gate.com/solutions for more information on the benefits of extending DevOps practices to SQL Server databases.