The DBA is the custodian of database changes. Usually, the DBA is not the one to request a change; that is typically done by the application owner or business user. But there are times, too, when the DBA will request changes, for example, to address performance reasons or to utilize new features or technologies. At any rate, regardless of who requests the change, the DBA is charged with carrying out the database changes.
To effectively make those changes, the DBA needs to ensure that the database change management discipline incorporates proactivity, intelligence, analyses (planning and impact), automation, standardization, reliability, predictability, and quick and efficient delivery. Without a robust, time-tested product that is designed to effect database changes, the DBA will encounter a very difficult job. Why?
Well, today’s major RDBMS products do not always support fast and efficient database structure changes. Each RDBMS provides differing levels of support for making changes to its databases. But none easily supports every type of change that might be required. One quick example: most RDBMSs today do not enable a column to be added to the middle of an existing row. To do so, the table must be dropped and recreated with the new column in the middle. But what about the data? When the table is dropped the data is deleted, unless the DBA was wise enough to first unload the data. But what about the indexes on the table? Well, they were dropped when the table was dropped, so unless the DBA knows this and recreated the indexes too, performance will suffer. The same is true for database security – when the table was dropped all security for the table was also dropped. And this is but one simple example. Other types of database change that are required from time to time include:
- changing the name of a database, table, view, or column
- modifying a stored procedure, trigger, or user-defined function
- changing or adding relationships using referential integrity features
- changing or adding database partitioning.
- moving a table from one database, dbspace, or tablespace to another.
- rearranging the order of column in a table.
- changing a column’s data type or length.
- adding or removing columns from a table.
- changing the primary key without dropping and adding the primary key.
- adding or removing columns from a view.
- changing the SELECT statement on which a view is based.
- changing the columns used for indexing.
- changing the uniqueness specification of an index.
- clustering the table data by a different index.
- changing the order of an index (ascending or descending).
And this is an incomplete listing. Adding to the dilemma is the fact that most organizations have at least two, and sometime more, copies of each database. At the very least, a test and production version will exist. But there may be multiple testing environments for example, to support simultaneous development, quality assurance, unit testing, and integration testing. And each database change will need to be made to each of these copies, as well as, eventually, the production copy. So, you can see how database change quickly can monopolize a DBA’s time.
The solution is to use an automated product to implement database change. The product will enable the DBA to focus on all of the issues of change management because it is built to understand not just the discipline of change management, but also the RDBMS in which the changes are to be made. This built in intelligence shifts the burden of ensuring that a change to a database object does not cause other implicit changes from the DBA to the tool. And once the change has been identified and implemented for one system, it can easily be deployed on other database copies with minimal, or perhaps no changes.
Another feature of a database change manager product must be to support analysis and planning. When the impact of changes can be examined prior to implementing any change it becomes easier to ensure safe and efficient database changes. This type of tool also uses automation to minimize the resources required to implement database change. Instead of writing a new, complex change script from scratch for each database change, the DBA can rely on the change management tool to accomplish this. And application and database availability will be enhanced because the product will implement the change in the least intrusive, quickest manner possible.
All in all, a database change management product will improve availability, minimize errors, and speed up your time to market. And I think we all can relate to that!