The Database Administrator as Development ‘Traffic Cop’

The database, as a core element for software product development, needs to be secure and stable, and perform as expected. Ensuring this is the role of the database administrator, in addition to database planning, development and troubleshooting. The DBA works with multiple development teams at once, providing assistance and guidance to the database developers. Then, many DBAs must merge code from different teams, as well as from third-party vendors where needed, and monitor activity in the database. The goal is to make sure there are no overwrites or conflicts among interdependent teams.

In most cases, the DBA invests a lot of time and effort in manually reviewing code from the developers and preparing the deployment script.  At times, this goes beyond fine-tuning and actually involves rewriting entire code segments, simply because the DBA has a better understanding and overview of the database. Similarly, when database problems arise during deployment or production, DBAs may be called upon to resolve them by fixing unfamiliar code without access to the original developers.

No matter the number of development teams and their potentially overlapping needs, the DBA is tasked with protecting the integrity of the data and ensuring availability. In order to perform this behind-the-scenes “traffic duty”, the DBA must balance the requirements of the various development teams with daily database maintenance routines and administrative responsibilities. This includes defining database management and maintenance processes for the relevant operations team.

Considering how much is riding on the success of the database administrator, it is surprising that current database deployment methods remain so vulnerable to error, leading to costly rework, downtime, and even data loss. The contributing risk factors are known to every DBA:

• Supporting multiple developers, in increasing numbers of teams and third-party "black box" modules is a challenge. Database configuration drifts are a constant risk due to multiple contributors to the change process, with the quality becoming increasingly unpredictable. And audit trails in these circumstances are often insufficient for regulatory compliance.

• Merging code for deployment means handling dependencies between the different teams, while maintaining enough flexibility to respond to changing deployment plans.

• Dealing with developers and production personnel, as well as acting as arbiter between them at times, is very taxing. Each role requires a different mindset.

• With all of the manual database work required during development and deployment, human error is all but inevitable. And a mistake in the database usually results in cross-application errors, accidental overwrites or downtime in the entire system.

These challenges only become more acute with the current demand for ever-faster time-to-market and consistent customer satisfaction. For strategic business reasons, companies sometimes want multiple features under development at the same time. With all of the development teams making changes to the same database objects, there are ultimately a lot of coupling problems in deployment. This can get even more disruptive, and costly, when a feature has to be rolled back at or after deployment.

Add to this the general customer expectation of minimal-to-zero downtime, and there is clearly great pressure on the DBA just to stay on top of integration and deployments. The problem is that the more attention they need to pay to those aspects of their role, the less time they have for proper maintenance, researching new technologies, assisting database developers (especially early in the development lifecycle, when database modification is less costly), and actual database administration.

How a New Approach to the Database Can Make DevOps a Success (And a DBA’s Life Easier)

For database administration to fully contribute to the DevOps workflow, and avoid the pitfalls that can easily break a deployment, a new approach is needed. The key is a move to greater systematization and automation, ensuring uninterrupted consistency from defining tasks through application deployment.

Specifically, a fully automated enforced version control for the database, much like that used for code development, can address one of the most vexing problems of database stability and security: consistent versioning. This ensures a single source of truth throughout development, creating an automatic process for preventing code overrides and conflicts, configuration drifts, and other causes of instability that can break a deployment.

An effective control mechanism includes a check-in/check-out process and database object locking, preventing employees from straying from a well-defined revision process, whether out of absentmindedness or habit. As no object (table, schema, etc.) can be checked out by more than one user at a time, all revisions are managed and sequential.

In order to mitigate the risk of configuration drift, baseline aware analysis is used to check changed object structure (or content) against the object as it is maintained in the source control repository. That repository preserves a baseline that has been defined for every object and in accordance with every check-in, allowing for three-way comparisons. That is, when a developer wants to commit and deploy his changes, the version control solution should automatically compare the developer’s copy, the object baseline (the object as it was when the developer checked it out), and the most current revision of the object in the target environment (e.g., integration or QA). If a difference between the baseline and the current revision is detected, then that would mean someone else, maybe from another team or project working on another development branch, has already modified the object. In that case, the developer would not be able to deploy his changes, until the conflict between his local copy and the newer updates to the target environments are resolved.

By automatically analyzing code and changes produced by developers, such safe automation can be achieved. The DBA has a clear interest in assuring that bad practices, or non-policy actions, are blocked automatically or trigger a warning, rather than depending on his ability or responsibility to review every bit of code being pushed forward by developers. Practices like dropping an index, rebuilding an index in mid-day, and so on, should trigger a warning before rolling all the way to production.

The result of all this automation is an evolutionary database development that ensures zero downtime, high availability, scalability, and fail-safe continuous delivery in any environment.

What Does It Mean for Developers?

For developers, a reliable and responsive database provides agility and flexibility, while increasing productivity and efficiency, as well as eliminating conflicts with the DBA down the line. For operations teams, it is a promise of security, stability, repeatability, and validity in deployment configurations.

Preventing database-linked errors at the coding or integration phases, as well as raising flags of potential conflicts and non-policy changes, at early stages of SDLC, allows a faster and much more efficient response. Reducing the amount of errors and conflicts early on, long before production, is critical to a better deployment process. The DBA would become less of a bottleneck for the development teams, with less downtime and faster deployment, which translates to better customer experience.

Moreover, many database-linked problems could be resolved by development team leaders in early consultation with DBAs, or even on their own in many cases, thanks to detailed database version logs. The overall improved self-sufficiency among the development teams, as a result of the automated database versioning, reduces the number of errors and increases team efficiency. For added security and control in such a situation, the database management solution should be configured to granularly define personnel roles and responsibilities. Such effective separation of duties, alongside enforced version control, is critical to preventing unauthorized or accidental version overrides.

Effective monitoring and versioning should be consistently applied at all stages, with a solution that can analyze database versions and object change histories at any point in their lifecycle. This will provide the audit trail necessary for regulatory compliance and key aspects of quality control. And as technology advances and consumer protection develops accordingly, comprehensive audit trail capabilities will become more and more important for regulatory adherence.

In order to ensure the most streamlined, cost-efficient and documented compliance, the reporting solution should be directly and automatically connected to the database monitoring system. This minimizes manual documentation requirements for development teams and cuts down on the time required to reply to compliance audit requests for information.

In the event of an emergency, reliable source control, safe automation and a monitoring mechanism provide a clear, functional recovery point for the database.

What Does It Mean for DBAs?

Safe automation and source control enforcement in the database reduce the number of errors developers insert into the system, by scanning code for non-policy changes and preventing code overrides. A full and comprehensive object change history provides information on each change, including the developer who made the change and comments regarding the original business requirement. Labels and a baseline for deployment scripts provide the DBA with additional and valuable information, while risky updates are immediately highlighted or blocked so they can’t trickle down to production.

This includes identifying otherwise undocumented hotfixes – the configuration drift problem that is one of the great banes of developers and DBAs - implemented in the target schema at any point during development. And if teams are working in separate development environments, only coming together during a QA phase, then identifying changes made to the target environments will also prevent code overwrites.

The source control also identifies who made what change to the database, possibly providing more information on the business requirement behind the change.  This is invaluable information for DBAs dealing with problematic scripts during deployment.

Minimizing the errors during development and deployment, while providing additional information about the changes and identifying those that are potentially harmful, allows the DBA to be more efficient. Reducing the amount of “noise” (in the form of developer errors they have to deal with) lowers the DBA's stress levels and makes the development teams more self-sufficient. This allows the DBA to focus more time on tasks such as tuning, maintenance, planning the deployment process, and providing expert guidance.

What Does It Mean for Management?

A systematic and automated approach to database management requires fewer resources to run and monitor it – and the results will be far more consistent. By mitigating the risk of human error through automation, the company can lead employees to greater efficiency, prevent workarounds and redo's due to overrides, and track down harmful updates before they are executed.

Database changes and monitoring should also be driven by clear business requirements, such as tasks from TFS, JIRA or similar work management tools. In this way, the database is both up to date and playing its role in meeting business goals. 

Safe automation, fully enforced database source control and change tracking, integrated into the development processes, provides compliance with regulatory requirements. This alleviates the need for additional third-party applications.

This end-to-end process, along with open communication and enhanced collaboration internally, optimizes the development to production cycle and reduces its cost. The result is accelerated time to market and greater customer satisfaction, as the end product becomes more stable, of better overall quality, and often more quickly reflects user feedback from previous releases.

Taking the Company Forward

Effective database administration can influence the success of DevOps initiatives, which in turn contributes to the success of the business. The key is safe automation and enforced source control for the database, which prevents many errors from reaching the deployment stage.

The result is more independent development teams, faster and earlier corrective measures, as well as more stable deployment configurations. For database administrators, relieved of the pressure of constantly having to juggle and merge various teams’ database changes, automation frees up time to help their organizations take bigger steps forward in ongoing innovation.

About the Author

Yaniv Yehuda is the co-founder and CTO of DBmaestro, an enterprise software development company focusing on database development and deployment technologies. Yaniv is a DevOps expert who spent the last couple of years raising awareness about the challenges surrounding database development and deployment, and how to support database Continuous Delivery.