There is perhaps no area within database administration more time-consuming or fraught with difficulty as the need to accurately shepherd the varied and ongoing vectors of change across an organization’s database infrastructure. A typical company has hundreds of databases, each with thousands of database objects, instantiated across multiple environments. The process of database change management touches many different people in the organization, including analysts, architects, modelers, developers, and DBAs; it also invokes common umbrella functions, such as change management, corporate security, data governance and SOA.
Today’s process for managing database change tends to be a loose agglomeration of smaller processes, some project-centered, others centered around particular stakeholders. These processes are often cobbled together in real time, relying on manual discretion to bridge process and technology gaps. The cost is efficiency and repeatability. Common areas of weakness are the lack of standard processes to ensure that changes applied directly to production make it back into the database design; inefficiencies in bundling changes together for migration between environments; and the ability to track what structures, settings, and privileges changed from one day to the next. The lack of a formalized approach to database change management results from process, as well as from technology gaps, and can lead to across-the-board impacts in quality and productivity.
The solution is not so far away and it relies partly on technology and partly on process. Professional cross-platform database tools provide an opportunity for facilitating key parts of the process once the necessary efforts have been made to define it. Tools can also ease the hand-offs between processes and between different stakeholders, improving accuracy and efficiency in areas that otherwise might be difficult or ambiguous without specialized technology.
Defining Database Change and Taking the First Steps
The first thing that comes to mind for many when asked the question, “What is database change?” is database structure - collectively, schema objects (For purposes of this article, the term “schema objects” will include database code such as triggers and stored procedures. The fact that the term “schema objects” can mean different things to different people underscores the need for a well-defined database change management process.) While these objects are clearly a central issue, settings and data also bear mentioning. A change to a single database setting can mean the difference between a well-performing system and a sluggish one, or a secure database and a vulnerable one. Understanding the differences in database settings over time and across environments has important security and performance implications. Similarly, maintaining database integrity hinges on an ability to quickly compare and troubleshoot data when things go wrong. In particular, static data can be argued to fall into the realm of database change management, as it is a necessary part of a fresh database build and the same data is often propagated across many environments.
Change must be considered across two key dimensions - time and space. Time refers to the release timeline over which a database evolves, while space refers to the numerous locations where a particular database may be instantiated (including development and test environments). The database design is inextricably tied to these two areas. Moving backwards, this begins with the physical and then the logical model, before cascading to an entire universe of tools that leverage database metadata. Some examples include development, architecture, and process modeling tools, business intelligence applications, metadata repositories and so on. Accurately pinpointing structures, settings, and data across time and space affects everything from software development to reporting.
Database Change Management vs. Software Change Management
A platform for better understanding the requirements of database change management is its nearest relative - software change management. Software change management combines the following three techniques to ensure accuracy and completeness across the release cycle:
1. Source Code Management: Key features include being able to capture and track versioned baselines of code, creating code branches to manage concurrent release efforts, providing developers the ability to work on the same piece of code at the same time, and the ability to visualize changes between versioned code lines.
2. The Build Process: One of the key facets is to ensure dependent components are compiled in the right order.
3. Deployment/Migration: During this procedure, some or all of a software bundle is placed into the target location, allowing for differences between environments (e.g., performance and security settings).
Database change management shares these requirements and adds some unique challenges:
* The need to preserve data when making structural changes.
* Disparate, overlapping and sometimes unclear areas of responsibility. For example, tablespace definitions and storage parameters are in the domain of DBA; table definitions are the shared responsibility of the DBA and the data modeler; stored procedures are the responsibility of the database developer, and sometimes the DBA.
* The vast number of metadata interdependencies, resulting from the fact that database metadata is widely propagated outside of the database.
* The variety of acceptable paths for changes. For example, the ability of the DBA to introduce certain types of changes directly into the database, as well as the possibility that applications themselves may change database structures, data or settings.
The Search for a Solution
The key to a robust database change management solution lies in a finely tuned combination of process, technology and communication mechanisms. An end-to-end solution will typically leverage multiple tools. Modern data modeling tools, for example, provide facilities for data model versioning and collaborative development. Database development tools allow debugging of stored procedures and the ability to view and manipulate objects and data, and often integrate with software versioning tools. Binding the various parts of the process together is a database change management tool. This tool plays several roles. First, it can serve as a link between the design layer to the physical database, documenting the latest release and serving as a hub from which that release can then be propagated. Next, it offers the ability to police changes by taking nightly snapshots of the database, and when differences in structure, settings or data are found, it will send an email notificaton to the DBAs, data modelers and developers. Another feature of these types of tools is the ability to produce properly ordered and syntactically accurate scripts to bring about change in the least intrusive manner, regardless of the database version and platform.
Modern technology can elevate the game significantly above common “homegrown” approaches, such as using software source control to manage database objects, encumbering the data modeling tool with version tracking across database environments, or, simply considering the production database itself to be the “master copy.”
Database change management can force some difficult questions: “Who is responsible for what type of database object?” “Where does one draw the line between static data and master data management?” “How does governance vary as a function of environment, organization, and project type?” There’s no one answer for every company. Each organization needs to determine the size and shape of the database change net it is casting and align these with existing functions and responsibilities. As a general rule, structures, settings, and data are the three primary areas and they need to be considered over time and across environments.
Before one can propose any process improvements, it is important to understand the existing tools, processes, and stakeholders, to find the inefficiencies and gaps, and to map these against a set of fine-grained requirements. Improvements to tools and processes should produce tangible benefits that ultimately improve productivity, eliminate ambiguity, and improve the end-to-end reliability and traceability of database change throughout the design and development cycle and through production.
With a clear vision, a bit of effort, and judicious use of process and technology, it is possible to robustly and systematically manage database change across the enterprise.