Portals eNewsletters Web Seminars dataWarehouse.com DM Review Magazine
DM Review | Covering Business Intelligence, Integration & Analytics
   Covering Business Intelligence, Integration & Analytics Advanced Search

View all Portals

Scheduled Events
Archived Events

White Paper Library
Research Papers

View Job Listings
Post a job


DM Review Home
Current Magazine Issue
Magazine Archives
Online Columnists
Ask the Experts
Industry News
Search DM Review

Buyer's Guide
Industry Events Calendar
Monthly Product Guides
Software Demo Lab
Vendor Listings

About Us
Press Releases
Advertising/Media Kit
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

Database Change Management Tips and Techniques

  Article published in DM Direct Newsletter
July 8, 2005 Issue
  By Mike Moser

Databases are living and changing things. As the business grows and changes, the systems it needs to collect and manage data about that business must change as well. Dealing with these changes is a key responsibility for any DBA. This article introduces Freddie, the lead production DBA at XYZ Manufacturing and outlines some of the key areas a DBA must be concerned about when managing change, along with some tips and techniques for dealing with those areas.

One bright Monday morning, Freddie's boss comes bounding into his cube at XYZ Manufacturing with a large grin on his face; "Freddie I've got GREAT news. We just acquired ABC Manufacturing, our biggest competitor! Now all we have to do is migrate their data into our systems and shut down their data center. I told management it'll be done in three months. So, when can we start?"

Piece of cake, right? Unload some data here, load some date there and make a few changes to a few tables. Nothing can be easier!

Well, fast forward six months. XYZ has a sophisticated business service management (BSM) strategy that allows them to quickly evaluate the business services that need to be integrated upon completion of the merger and understand the complete range of IT components and systems that must be touched to bring the companies together. The project planners have outlined their migration plans, the data analysts have analyzed and the system programmers have written the conversion routines. Freddy is now ready to start planning the implementation of all of the changes into the production systems to enable the team to do the migration.

Freddie is concerned of course. Any problem or glitch in this process could put the entire XYZ Manufacturing supply chain "out of order." Armies of order entry clerks, warehouse managers, fork-lift drivers, finance managers and a host of other people (not the least of which: XYZ's customers) will be taking an extended coffee break if the move to production does not happen smoothly and with out error.

So ... It's the bottom of the ninth, the team is in a position to win and it's now up to Freddie to put the ball in play. If he does everything correctly, the team will win the game; if he strikes out, it's back to the minor leagues (a.k.a. shame and unemployment)!

Managing Database Change

One of the most difficult tasks DBAs face is change management because it involves unloading and loading data, changing objects, removing and adding objects - all of which have complex and often-unclear interdependencies. In order for the operations to be successful, DBAs are required to do things in a specific sequence, the order of which is often not obvious.

When working with any kind of change environment, the DBA must be able to answer the who, what, when and where questions. To do so, DBAs must set baselines and establish change and access control to definitively answer these questions. Change-management solutions equip a DBA with the tools necessary to provide the execution controls and the detailed information for these processes within the database environment.

Successful change management is predicated on planning and coordination. Freddie is fortunate in that XYZ Manufacturing has a sophisticated BSM strategy in place. The IT disciplines of change and configuration management are in place and a careful round of business-oriented impact analysis and project planning have been completed to put Freddie in a position to execute the changes, but all of that will come to naught if the changes themselves are not executed smoothly and without error. DBAs become engaged via the organization's change management process, but, after that, change execution becomes the primary task. In order to execute the change, Freddie:

  1. Gets a description of the change and specifies the modifications in the form of commands and scripts (or some other method) so he can understand what he will be implementing. This process is often manual and tedious. It can involve hand-written scripts, cut-and-paste from other sources or attempts to export DDLs from other systems.
  2. Analyzes the change by using a change-management tool to automatically look for inconsistencies and dependencies. By knowing which changes will cause problems, Freddie can address the issue before running potentially damaging jobs. For example, a DBA dropping a column that is referenced or is a key on another table could cause an unexpected problem.
  3. Builds the scripts and jobs necessary to execute the change to ensure everything is run in proper order, that relationships are preserved and reestablished, privileges are respected and, most important, that the integrity of the structure and data is guaranteed.

Though this process sounds simplistic, it can be quite complex. For this reason, automated change-management solutions are often the key to expedite these time-consuming and error-prone processes so that Freddie can focus on the change itself, not the details and idiosyncrasies of all of the technical mechanics involved in the process.

Automated change-management solutions can optimize the sequence of operation, take advantage of opportunities for parallelism, and leverage high-speed utilities for load and unload are crucial in ensuring availability. Any process must have an audit trail so the DBA can prove what was done as well as debug any issues that may arise from the change. If a problem occurs during execution, the ability to restart from the point of error is important so valuable time isn't wasted going back to the beginning. Automation and instrumentation of the process will ensure the integrity and accuracy of the change while deploying it across a large-scale environment.

Automated change-management solutions can help DBAs, such as Freddie, to eliminate change-related risks. For example, these solutions can:

Plan and orchestrate alterations. Most real-world database changes cannot be implemented without complex, multistep processes. Problems typically occur when the changes required go beyond the native database alter commands. For successful implementation, alterations must be planned and orchestrated perfectly to realign the tables, dependencies, data and privileges. Database change-management systems can allow the DBA to specify the steps required for the change up front. Based on this description, the tool will generate the commands, while checking for order and dependency and ensuring that the changes result in a valid configuration, without any loss or damage to the system.

Perform before and after object image comparisons. When a change is considered or validated, the DBA often has to compare the "before" image of the object definitions that they want to change to an "after" image that represents the desired object state. This can be a daunting process, especially as each image might be tens of thousands of lines of DDL. Change-management solutions can perform comparisons such as these with accuracy, speed, and relative ease. Automated compare facilities compare two dataset objects or schemas and reports the differences, but they go further. Far beyond simply reporting the differences, compare facilities present the results in a graphical format and provide reports in HTML to review the differences in detail. Although knowing what is different is useful information, the ability to make the databases alike is the real goal. With these tools, DBAs can easily generate the DDL script to synchronize the databases with the click of a button.

Test changes required for local variations. Environments in which a master database is used to define changes that are then propagated across a wide environment have an added problem: local customizations. For example, XYZ Manufacturing has multiple plants, and, therefore, must ensure that its materials receiving application has been customized at each site to deal with local freight-handling regulations. Change-management solutions automate data structure changes, migration and versioning by providing built-in intelligence that enables Freddie to test changes required for local variations in a non-production environment. He can then apply those changes when fully tested without losing any customization that are local to each environment.

Once the obstacles have been addressed and the change packages are built and tested, the process of moving the changes into production is now critical. For this process, automated change-management solutions provide a high degree of accuracy and performance to ensure that any change-triggered system downtime (as a result of implementing the change) is minimized. They also guarantee the integrity of objects and data, and analyze all of the submitted changes to ensure that the implementation is streamlined for maximum efficiency.

Without effective change-management solutions as part of a business service management strategy, DBAs like Freddie are unable to develop a comprehensive picture of the global impact that IT changes will have on a company's IT environment. Prior to rolling out changes, DBAs need to see which IT infrastructure components or business services are affected by the planned changes and determine a strategy to implement these changes. Automated change-management solutions can facilitate this activity, as well as provide information on the scope of the effort required to implement the planned changes by reviewing the statistics of similar changes performed in the past. By identifying in advance the impact of change on the IT infrastructure and staff, as well as on business services, Freddie and his boss can work together to maintain a smooth change process that ensures efficient utilization of the IT staff and minimizes disruption to the business.


For more information on related topics visit the following related portals...
Databases and Project Management / Development.

Mike Moser, product management director, is responsible for the SmartDBA product strategy at BMC Software, Inc. In his role, he has responsibility for ensuring the continued implementation of the SmartDBA product line strategy including its impact across both mainframe and distributed systems. Moser also manages the infrastructure for the product line and ensures that it is tightly integrated with and enhances BMC Software's Business Service Management strategy. Moser has extensive experience in complex engineering and IT environments both as a practitioner and as a manager, including software process improvement consulting, IT strategic planning and application development responsibilities for a large-scale decision support system.You can reach him at mike_moser@bmc.com.

Solutions Marketplace
Provided by IndustryBrains

Design Databases with ER/Studio: Free Trial
ER/Studio delivers next-generation data modeling. Multiple, distinct physical models based on a single logical model give you the tools you need to manage complex database environments and critical metadata in an intuitive user interface.

Data Quality Tools, Affordable and Accurate
Protect against fraud, waste and excess marketing costs by cleaning your customer database of inaccurate, incomplete or undeliverable addresses. Add on phone check, name parsing and geo-coding as needed. FREE trial of Data Quality dev tools here.

Free EII Buyer's Guide
Understand EII - Trends. Tech. Apps. Calculate ROI. Download Now.

Data Mining: Levels I, II & III
Learn how experts build and deploy predictive models by attending The Modeling Agency's vendor-neutral courses. Leverage valuable information hidden within your data through predictive analytics. Click through to view upcoming events.

Click here to advertise in this space

E-mail This Article E-Mail This Article
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
Site Map Terms of Use Privacy Policy
SourceMedia (c) 2006 DM Review and SourceMedia, Inc. All rights reserved.
SourceMedia is an Investcorp company.
Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.