Change is a fact of life and each of the major DBMS products change quite rapidly. A typical release cycle for DBMS software is 18 to 36 months for major releases with constant bug fixes and maintenance delivered in between major releases. Indeed, keeping DBMS software up-to-date can be almost a full-time job.

The DBA must develop an approach to upgrading DBMS software that conforms to the needs of their organizations and minimizes the potential for disrupting business due to outages and database unavailability.

You may have noticed that I use the terms version and release somewhat interchangeably. That is fine for a broad discussion of DBMS upgrading, but a more precise definition is warranted. Vendors typically make a distinction between versions and releases of software products. A new version of software is a major concern, with many changes and new features. A release is typically minor, with fewer changes and not as many new features. For example, moving from Version 11g of Oracle Database to Version 12g would be a major change – a version change. But an in between point, such as Oracle Database 11g Release 2 would be considered a release – consisting of a smaller number of changes. Usually the DBMS vendors will increase prices for versions, but not necessarily for releases (but that is not a hard and fast rule).

Usually significant functionality is added for version upgrades, less so for point releases. But upgrading from one point release to another can have just as many potential pitfalls as version upgrades. It depends on the nature of the new features provided in each specific release. The issues and concerns discussed in this article pertain to both types of DBMS upgrades: to a new release and to a new version.

In a complex, heterogeneous, distributed database environment a coherent upgrade strategy is essential. Truthfully, even organizations with only a single DBMS should plan accordingly and approach DBMS upgrades cautiously. Failure to plan a DBMS upgrade can result in improper and inefficient adoption of new features, performance degradation of new and existing applications, and downtime.

An effective DBMS upgrade strategy must balance the benefits against the risks of upgrading to arrive at the best timeline for migrating to a new DBMS version or release. The risks of upgrading to a new DBMS release include:

  • An upgrade to the DBMS almost always involves some level of disruption to business operations. At a minimum, as the DBMS is being upgraded databases will not be available. This can result in downtime and lost business opportunities if the DBMS upgrade has to occur during normal business hours (or if there is no planned downtime). Clustered database implementations may permit some database availability as individual database clusters are migrated to the new DBMS version.
  • Other disruptions can occur including the possibility of having to convert database file structures, the possibility that previously supported features were removed from the new release (thereby causing application errors), and delays to application implementation timelines.
  • The cost of an upgrade can be a significant barrier to DBMS release migration. The cost of the new version or release must be planned for (price increases for a new DBMS version can amount to as much as 10% to 25%). The upgrade cost also must factor in the costs of planning, installing, testing, and deploying not just the DBMS but also any applications using databases. Additionally, be sure to include the cost of any new resources (memory, storage, additional CPUs, etc.) required by the DBMS to use the new features delivered by the new DBMS version.
  • DBMS vendors usually tout the performance gains that can be achieved with a new DBMS release. But when SQL optimization techniques change it is possible that a new DBMS release will generate SQL access paths that perform worse. DBAs must implement a rigorous testing process to ensure that new access paths are helping, not harming, application performance. When performance suffers, application code may need to be changed – a very costly and time-consuming endeavor. A rigorous test process should be able to catch most of the access path changes in the test environment.
  • To take advantage of improvements implemented in a new DBMS release, the DBA may have to apply some invasive changes. For example, if the new version increases the maximum size for a database object, the DBA may have to drop and re-create that object to take advantage of the new maximum. This will be the case when the DBMS adds internal control structures to facilitate such changes.
  • Another potential risk is the possibility that supporting software products may lack immediate support for a new DBMS release. Supporting software includes the operating system, transaction processors, message queues, purchased application, DBA tools, development tools, and query and reporting software.

After weighing the benefits of upgrading against the risks of a new DBMS release, the DBA group must create an upgrade plan that works for the organization. Sometimes the decision will be to upgrade immediately upon availability, but often there is a lag between the general availability of a new release and widespread adoption of that release.

When the risks of a new release outweigh the benefits, some organizations may decide to skip an interim release. Skipping releases is not always supported by the DBMS vendor but can be possible, at times, even if no direct support is offered. Although a multiple release upgrade takes more time, it enables customers to effectively control when and how they will migrate to new releases of a DBMS, instead of being held hostage by the DBMS vendor. When attempting a multiple release upgrade of this type be sure to fully understand the features and functionality added by the DBMS vendor for each release level between the previously installed level and the new level being implemented. For example, if moving from Version 8 to Version 10, the DBAs will need to research and prepare for the new features not just of Version 10, but of Version 9 and Version 10.

An appropriate DBMS upgrade strategy depends on many factors. Perhaps the biggest factor in determining when and how to upgrade to a new DBMS release is the functionality supported by the new release. Tightly coupled to functionality is the inherent complexity involved in supported and administering the new features.

Regardless of the new “bells and whistles” that come along with a release upgrade, there are always administration and implementation details that must be addressed before upgrading. The DBA group must ensure that standards are modified to include the new features, educate developers and users as to how new features work and should be used, and prepare the infrastructure to support the new DBMS functionality.

The type of changes required to support the new functionality must be factored into the upgrade strategy. When the DBMS vendor makes changes to internal structures, data page layouts, or address spaces, the risks of upgrading are greater. Additional testing is warranted in these situations to ensure that database utilities, DBA tools, and data extraction and movement tools still work with the revised internals.

Complexity is another concern. The more complex your database environment is, the more difficult it will be to upgrade to a new DBMS release. The first complexity issue is the size of the environment. The greater the number of database servers, instances, applications, and users, the greater the complexity will be. Additional concerns include the type of applications being supported. A DBMS upgrade is easier to implement if only simple, batch-oriented applications are involved. As the complexity and availability concerns of the applications increase, the difficulty of upgrading also increases.

You should also take into account the support policies of the DBMS. As new releases are introduced, DBMS vendors will retire older releases and no longer support them. The length of time that the DBMS vendor will support an old release must be factored into the DBMS release migration strategy. You should never run a DBMS release in production that has been de-supported by the vendor. If problems occur, the DBMS vendor will not be able to resolve any problems for you if their software is no longer supported. Sometimes a DBMS vendor will provide support for a retired release on a special basis and at an increased maintenance charge. If you absolutely must continue using a retired DBMS release (for business or application issues), be sure to investigate the DBMS vendor’s policies regarding support for retired releases of its software. This is a particularly important issue to consider whenever you delay a DBMS upgrade for any reason.

Consider also, your organization’s risk tolerance. Every organization displays characteristics that reveal its style when it comes to adopting new products and technologies. Industry analysts at the Gartner Inc. have ranked organizations into three distinct groups labeled Type A, B, and C. A type A enterprise is technology-driven, and as such, is more likely to risk using new and unproven technologies to try to gain a competitive advantage. A type B organization is less willing to take risks but will adopt new technologies once the bugs have been shaken out by others. Finally, a type C enterprise is very cost-conscious and risk-averse and will lag behind the majority when it comes to migrating to new technology.

Only type A organizations should plan on moving aggressively to new DBMS releases immediately upon availability; and even then, not for every new DBMS release, only when the new features of the release are judged to deliver advantages to the company. Type C enterprises should adopt a very conservative strategy to ensure that the DBMS release is stable and well-tested by type A and B companies first. And type B organizations will fall somewhere in between types A and C; almost never upgrading immediately, instead adopting the new release when the early adopters have shaken out the biggest problems, but well before type C enterprises.

When a DBMS vendor unleashes a new release of its product, not all platforms and operating systems are immediately supported. The DBMS vendor most likely will support the platforms and operating systems for which it has the most licensed customers first. The order in which platforms are supported for a new release is likely to differ for each DBMS vendor. For example, Linux for System z is more strategic to IBM than to Oracle, so a new DB2 release will most likely support Linux for System z very quickly, whereas this may not be so for Oracle. The issue is even more difficult to manage for Unix platforms because of the sheer number of Unix variants in the marketplace. Most DBMS vendors will support the most popular Unix platforms very quickly upon general availability. The most popular Unix variants are IBM’s AIX, Oracle’s Solaris and Hewlett-Packard’s HP-UX. Linux (the open source variant of Unix) is also a top-tier operating system that will be supported rapidly by most vendors, with the Red Hat and Suse distributions being supported more frequently and rapidly than others.

When planning your DBMS upgrade, be sure to consider the DBMS platforms you use and try to gauge the priority of your platform to your DBMS vendor. Be sure to build some lag time in your release migration strategy to accommodate the vendor’s delivery schedule for your specific platforms.

Be sure, also, to consider the impact of a DBMS upgrade on any supporting software. Supporting software includes purchased applications, DBA tools, reporting and analysis tools, and query tools. Each vendor of a supporting software application or tool will have a different timeframe for supporting and exploiting a new DBMS release. Many software vendors specifically differentiate between supporting and exploiting a new DBMS version or release. Software that supports a new release will continue to function the same as before the DBMS was upgraded, but with no new capabilities.

So, if a DBA tool, for example, supports a new version of Oracle, it can provide all of the services it did for the past release, as long as none of the new features of the new version of Oracle are used. A DBA tool that exploits a new version or release provides the requisite functionality to operate on the new features of the new DBMS release. So, to use a concrete example, IBM added support for hashing in Version 10 of DB2 for z/OS. A DBA tool can support DB2 Version 10 without operating on hashes, but it must operate on hashes to exploit DB2 Version 10.

Some third party tool vendors follow guidelines for supporting and exploiting new DBMS releases. Whenever possible ask your vendor to state their policies for DBMS upgrade support. It is likely that your vendors will not commit to any firm date or date range to support new versions and releases. That is to be expected because some DBMS versions are larger and more complicated and therefore will take longer to fully exploit.

Hardware Requirements

Every DBMS has a basic CPU requirement, meaning a CPU version and minimum processor speed required for the DBMS to operate… and this can -- and will -- change from version to version. Some DBMSs have specific hardware models that are either required or unsupported. Usually the CPU criterion will suffice for an Intel environment, but in a mainframe or enterprise server environment the machine model can make a difference with regard to the DBMS features supported. For example, certain machines have built-in firmware that can be exploited by the DBMS if it is available.

Furthermore, each DBMS offers different “flavors” of their software. I use the term flavor to differentiate this concept from the terms “version” and “release” which are used to specify different iterations of the same DBMS. However, DBMS vendors frequently offer different flavors of the DBMS (at the same release level) for specific needs such as parallel processing, pervasive computing environments (such as handheld devices), data warehousing, and/or mobile computing needs. Be sure to choose the correct DBMS flavor for your needs and to match your hardware to the requirements of the DBMS.

Storage requirements

A DBMS requires disk storage to run. And not just for the obvious reason – to create databases that store data. Storage also will be required for the indexes to be defined on the databases. But a DBMS will use disk storage for many other reasons too, such as for:

  • the system catalog or data dictionary used by the DBMS to manage and track databases and related information. The more database objects you plan to create, the larger the amount of storage required by the system catalog.
  • any other system databases required by the DBMS, for example, to support distributed connections or management tools.
  • the log files that record all changes made to every database. This includes active logs, archive logs, rollback segments, and any other type of change log required by the DBMS.
  • any startup or control files that must be accessed by the DBMS when it is started or initialized.
  • any work files used by the DBMS to sort data or for other processing needs.
  • any default databases used by the DBMS for system structures or as a default catchall for new database objects as they are created.
  • temporary database structures used by the DBMS (or by applications accessing databases) for transient data that is not required to be persistent, but needs reserved storage during operations.
  • any system dump and error processing files.
  • DBA databases used for administration, monitoring, and tuning. For example, DBA databases used for testing new releases, migration scripts, and so on.

Be sure to understand and adequately plan for the storage requirements for every new DBMS version well in advance of upgrading.

Memory requirements

Relational DBMSs, as well as their databases and applications, love memory. A DBMS will use memory for most internal processes such as basic functionality, maintaining the system global area, and to perform many DBMS tasks. Memory requirements for specific features, old and new, can change from version to version.

One of the primary reasons a DBMS requires a significant amount of memory is to cache data in memory structures to avoid I/O. Reading data from a disk storage device is always more expensive and slower than moving the data around in memory. The DBMS will use memory structures called buffer pools or data cache to reduce physical I/O requests. By caching data that is read into a buffer pool, the DBMS can avoid I/O for subsequent requests for the same data, as long as it remains in the buffer pool. In general, the larger the buffer pool, the longer the data can remain in memory and the better overall database processing will perform.

The DBMS will cache other structures in memory as well as data. Most DBMSs set aside memory to store program structures required by the DBMS to process database requests.[1] The program cache will store things like “compiled” SQL statements, database authorizations and database structure blocks that are used by programs as they are executed. By caching these structures, database processing can be optimized by avoiding additional I/O requests to access them from a physical storage device.

Memory typically is required by the DBMS to support many other features such as to handle lock requests, to facilitate distributed data requests, to sort data, and for some optimization processes and SQL processing.

When upgrading to new version be prepared to ensure that the DBMS has a more than adequate supply of memory at its disposal. Doing so will help to optimize the performance of database processing and minimize potential problems.

Configuring the DBMS

The manner in which the DBMS functions and the resources made available to the DBMS are controlled by configuring the system parameters of the DBMS. Each DBMS allows its system parameters to be modified in different ways, but the installation process usually sets the DBMS’s system parameters using radio buttons, menus, or panel selections. During the installation process the input provided to the installation script will be used to establish the initial settings of the system parameters.

Each DBMS also provides a method to change the system parameters once the DBMS is operational. Sometimes the systems parameters can be set using DBMS commands, sometimes you must edit a file that contains the current system parameter settings. If you must edit a file, do so very carefully because an erroneous system parameter setting can be fatal to the operational status of the DBMS.

What sort of things do the system parameters control? Well, for example, system parameters can be used to control DBA authorization to the DBMS, the number of active database logs, to set the amount of memory used for data and program caching, and to turn on and off DBMS features. Although every DBMS has system parameters that control its functionality, each DBMS has a different method of setting and changing the values. And, indeed, each DBMS has different “things” that can be set using system parameters.

Be sure to analyze any generated scripts containing configuration parameters. Compare the new parameter values to the existing values and find any changes. Consult the documentation for the new version to understand why a change was made and whether it is within the operating requirements of your environment. Failure to do so can result in an incorrectly configured database environment and that can cause performance problems, data integrity problems or possibly even DBMS failure.

Connect the DBMS to Supporting Infrastructure Software

Part of the DBMS upgrade process must be the verification that all system software connections to the DBMS are still viable and operational. Typical infrastructure software that may need to be configured to work with the DBMS includes: networks, transaction processing monitors, message queues, other types of middleware, programming languages, systems management software, operations and job control software, web servers, and application servers.

Each piece of supporting infrastructure software will have different requirements for interfacing with the DBMS. Typical configuration procedures can include installing DLL files, creating new parameter files to establish connections, and possibly revisiting the installation procedures for the supporting software to install components required to interact with the DBMS.

Fallback Planning

Each new DBMS version or release should come with a manual that outlines the new features of the release and describes the fallback procedures to return to a prior release of the DBMS. Be sure to review the fallback procedures provided by the DBMS vendor in their release guide. You may need to fallback to previous DBMS release if a bug is found with the upgrade, performance problems ensue, or other problems are encountered during or immediately after migration. Keep in mind that fallback is not always an option for every new DBMS release.

If fallback is possible be sure to follow the guidance of the DBMS vendor to enable falling back. At times, you may need to delay the implementation of certain new features for fallback to remain an option. Be sure to fully understand the limitations imposed by the DBMS vendor on falling back, and exploit new features only when falling back is no longer an option for your organization.

Migration Verification

Similar to new installation verification, be sure to implement procedures to verify that the DBMS release upgrade is satisfactory. Be sure to perform the same steps as with a brand new DBMS install, but also be sure to test a representative sampling of your in-house applications to verify that the DBMS upgrade is working correctly and performing satisfactorily.

Verification should include running a battery of tests to verify that the DBMS has been properly installed and configured. Most DBMS vendors supply sample programs that can be used for this purpose. Additionally, you can ensure proper installation by testing the standard interfaces to the DBMS. One standard interface supported by most DBMSs is an interactive SQL interface where you can submit SQL statements directly to the DBMS.

Create a set of SQL code that is comprised of SELECT, INSERT, UPDATE, and DELETE statements issued against sample databases. Running such a script after installation helps to verify that the DBMS is installed correctly and operating as expected.

Further, be sure to verify that all required connections to supporting software are operational and functioning properly. If the DBMS vendor does not supply sample programs you may need to create simple test programs for each environment that can be run to ensure the supporting software connections are functioning correctly with the DBMS.

Synopsis

In general, be sure to design a DBMS release upgrade policy based on the issues discussed above. Each specific DBMS upgrade will be unique, but the guidelines presented above will help you to achieve success more readily. A well thought out DBMS upgrade strategy will enable you to be prepared to support new DBMS releases with a minimum impact to your organization and in a style best-suited to your company.



[1] In DB2, the area used for caching program structures in memory is referred to as the EDM Pool. In SQL Server it is called the SQL Cache and in Oracle two structures are used, the PGA and the shared pool in the SGA.