The Non-Technical Art of Being a Successful DBA - Application Change Management Best Practices

I started my career working in a mainframe environment. Fellow blogger Craig Mullins and I administered DB2 databases on huge, big iron platforms. Platforms that supported thousands (upon thousands) of concurrent users. One of the benefits of this background is that it taught us the importance of change management best practices.

Craig and I learned that a key ingredient of a trouble-free mainframe environment is ensuring that there are no "surprises" when a change is implemented in production. Changes that can affect the day-to-day business operations of an entire organization. Throughout my career, I have applied these "mainframe style" best practices to all other database ecosystems that I was responsible for supporting.

It works. The first company I applied these best practices to was selected by Oracle as a "Showcase Environment". This was back in the old days, when Scott's tiger was just a cub. Oracle identified shops that it thought had rock-solid support environments and asked them to host visitors from other organizations that wanted to create their own high-quality support infrastructures.

I thought I would provide you with a few helpful hints and tips on change management best practices.

Database Design Reviews
One of my earlier blogs provides information on database design reviews. Database design review meetings foster effective communications between the DBA unit, system support personnel and application developers throughout the entire application design and implementation process. When Oracle design issues are addressed early in the development lifecycle, problems are minimized and the migration from test to production is more easily accomplished.

If you haven't read the design review blog, you should. I'm intentionally not covering the importance of rigorous testing of any change before it is implemented in production because it is covered in-depth in the design review blog. From simple changes to new application implementations, there is simply no reason not to perform the test, review, change, test, review, change iteration lifecycle. Although the blog covers new application implementations, the blog will show you how important I think it is to follow a rigorous test plan.

Proceduralize the Change Request Process
Database administrators usually support different business units with each unit having their own set of unique procedural requirements. Formalizing and documenting the change request process minimizes the potential for miscommunication between the business units, application development areas and the database administration unit.

The notification lead-time required for the database administration team to perform a requested change should be documented and distributed to business and application development units. This will prevent your team for getting a request to migrate a database from test to production in the morning with a required date for that afternoon. Of course, we all know that never happens.

If your organization doesn't have a formal change request process in place, (and many shops don't) create your own! There are dozens of change management and source code versioning tools available on the market today. The prices can range from thousands to tens of thousands of dollars.

Although I highly recommend these types of products, I wouldn't let the lack of having one prevent me from formalizing the change management process. You do the best with what you have.

OK, so you don't have the good fortune of having a formal change management process in place. What do you do? You can begin the formalization of the change request process by:

• Creating standardized change request documents
• Establishing change management meetings
• Creating Service Level Agreements (SLAs), which include change request turnaround times.

Standardized Change Request Documents
Standardized request documents help to increase the quality of the change request process. The forms are sent to the database administration unit by the application owner of the data to be processed. Any requests not sent or signed off by the application owner should be rejected. Keep copies of all completed work requests for auditing purposes. Application owners can be virtually any member of the organization that is identified as having the authority to sign off on change requests. The most common persons are application development team leaders, section heads, department heads, etc..

Each request form contains the following common information:

• Form identifier - naming convention that allows the form to be easily identified
• Application name
• Database name
• Name and contact information of the person requesting the change
• Request date
• Required date
• Application owner signoff
• Data security signoff (if required by shop standards)
• A free form request area for nonstandard requests
• An area that the DBA will fill out when executing the change that contains the following information:
  • DBA executing change
  • DBA contact information
  • Date and time change was processed
  • Verification procedures followed

Here are a few examples of specific forms that will help formalize the change request process:

Oracle Authorization Request Form
This form is used for requesting authorization changes to the Oracle database environment.

The Oracle Authorization Request Form contains the following information pertinent to Oracle authorization requests:

• Grantee listing for security grant or revoke
• Type of security granted or revoked

Oracle Test Environment Change Request Form
The Oracle Test Environment Change Request Form will be used for requesting physical changes to Oracle test databases. It is used for requesting both new objects (tables, indexes, views, stored programs, etc.) and object alterations (adding new columns, changing datatypes, adding columns to indexes, etc.). In addition, the form notifies the DBA team to perform ad-hoc activities (test database refreshes, exports, etc..) in test environments.

The Oracle Test Environment Change Request Form contains the following information pertinent to Oracle change requests:

• Schema owner of object to be changed or created
• Object name to be changed or created
• Object type (i.e. table, index, view) of the object to be changed or created
• Detailed description of change requested
• Data administration sign off for new data objects
• Free from area for ad-hoc activities

Oracle Production Environment Change Request Form
This form will be used for requesting the migration of Oracle objects (databases, tablespaces, tables, indexes, etc.) from test to production and the alteration of existing production objects. For new database implementations, please see my blog titled "Database Design Reviews". In addition, the form notifies the DBA team to perform ad-hoc activities in production environments.

Each Production Environment Change Request Form must have an associated Oracle Test Environment Change Request Form counterpart. If the change wasn't made in test, you don't implement it in production. To facilitate this process, the identifier for the test change request form that was used to notify the DBA team to create or alter the test objects should be provided on the production change request form.

The Production Environment Change Request Form contains the following information pertinent to production environments:

• Oracle Test Database Change Request Form. Allows DBA to determine if the change was implemented in test. If no change request is found, the DBA needs to determine the reason why
• Object name of the object to be migrated or changed
• Object type for the object to be migrated or changed
• What time these activities should be performed
• The name of the database the object will be migrated from (source database)
• The name of the database the object creation/alteration will be implemented in (target database)
• A freeform area for special processing required during the migration or alteration process
• Free from area for ad-hoc activities

Change Management Meetings
If you read my earlier blog on database design review meetings, you know I'm a proponent of constant communication between all units that are involved in the change management process. How often should you hold these change management meetings? As often as you implement objects in production. If your organization makes changes to production environments on a daily basis, the meetings should be held daily. This is not as big of an imposition on your time as you may think. We provide remote database services for several very large organizations that have these change management meetings on a daily basis. The process takes about 15 to 20 minutes. Not a lot of time spent to ensure that everyone knows what is happening.

To shorten the amount of time these meetings consume and to make them as productive as possible, the following discussion items should be a standard part of the meeting's agenda:

• Application name being changed
• Date and time change will be implemented
• Change description
• Potential business impact if the changes don't go as expected (include both units affected and how they will be affected)
• Backoff procedures
• Requestor
• Tested by

Service Level Agreements
Identifying support needs and expectations is required to provide high quality support. You probably won't be meeting all of your customer's expectations if you don't know what any of them are. As stated previously, each application has it own unique set of support requirements and expectations. Service Level Agreements (SLA) help to solidify support requirements and dispel any inflated expectations a business or application development unit may have. They probably won't be aware of your current workload and resulting work request lead times until you tell them. The DBA team lead should meet with each unit supported to establish a set of measurable Service Level Agreements that include work request lead times, support activities required and application performance and availability objectives.

Wrapup
This is by no means an all inclusive list of activities you need to perform to standardize the change request process. It is intended to give you a head start in the right direction.

Thanks for Reading,

Chris Foot
Oracle Ace