In this first article of a 4 part series, Darren Fuller discusses the need for database change management and problems with current approaches. Future articles in the series will examine an automated methodology, the requirements to implement such an approach and describe the benefits that can be realised.
Introduction
Most approaches to database change management involve tedious, manual and costly processes that are error prone. In many cases there is no version control of the objects that reside within the database.
The result? Database chaos.
Typically, a database development project will suffer problems such as overwritten changes, code that won’t compile, lost data and confusion over what has been applied to which database. Release into production becomes a chaotic and costly phase, with everybody “fire-fighting” and in “panic mode”.
It is a fact that database systems are becoming more complex with ever-increasing demands. Businesses must continually
innovate and accept challenges. IT departments cannot afford to complacently use processes simply because it’s “always been done this way” or is the prevalent paradigm.
The Need for Database Change Management
One of the certainties in life is that things will change. This is especially true of database systems that must evolve and change in response to new user requirements and the continuous drive for competitive advantage. Changes to a database may be required for bug-fixing, resolution of defects and performance tuning e.g. de-normalisation. The complexity of some systems can be overwhelming when you consider the interaction and inter-dependencies between various technologies and protocols e.g. COM+, MDAC, Web Services, XML, HTML, ASP, SOAP and that many environments are heterogeneous.
When you think about database change management, the following questions may spring to mind:
- How are changes to database structures and static data managed in such complex and often mission-critical environments?
- How can you know with certainty what version a database is in a particular environment?
- How can you deploy new versions with accuracy and reliability?
- Can you reliably determine who made changes to your database objects?
- Do you have an audit trail recording what changes have occurred and at what point in time?
style='color:red'>
- If severe errors occur, can you back-out the corrupt version and rollback to a known state of integrity?
This is the essence of database change management. Every database development project must have a process or methodology in place to propagate and deploy upgrades to database schema and data. After all, the database is an extremely valuable asset, containing transactional, financial and customer information that are the lifeblood of an organisation.
Problems with Current Approaches
Now let’s discuss some of the current methods of database change management, outlining their flaws, and the degree of risk that they contribute to the application development lifecycle.
Probably the most common method of managing database change is via a manual process using script files. This involves
hand-crafted upgrade scripts for changes to database code, schema and static data. There may also be the need for a corresponding downgrade script to be used as a contingency to rollback a failed upgrade.
A typical scenario is that a DBA will receive an upgrade script from a developer that is different to the copy in the version control system (VCS), which is again different to the production environment. The DBA(s), while performing a vital quality assurance role, becomes a bottleneck as they must manually resolve such issues plus review every script for correct syntax, functionality (often requiring an understanding of the business rules) and performance. There is also a need to understand how the changed objects will affect their dependent objects (e.g. due to referential integrity) and the order in which changes should be propagated.
After the scripts have been executed against the target environment, if further issues are found, then another upgrade script is required to be coded and so on and so on. You very often end up with lots of incremental scripts just for one new piece of functionality. When you multiply that out over many developers and many new pieces of functionality, the number of scripts could run into the hundreds.
With manual processes, there is always the risk that the development and testing environments do not match the production database in some subtle way. This can lead to production outages that could have been avoided if there was a guarantee that what was tested, matches exactly what is placed on the production system.
Reliance on hand-crafted scripts to manage code baselines and propagate change within your database development project, cannot guarantee complete control. Many IT departments are still using cumbersome manual scripting, which ties-up highly skilled resources in tedious, time-consuming and error-prone tasks.
Summary of drawbacks
- manual process, therefore time consuming (expensive) and error prone
- requires upgrade and possibly downgrade scripts
- may require additional upgrade scripts if errors are found (may run into hundreds)
- increasing pressure upon DBA(s), resulting in bottlenecks
- quality assurance may be impeded if DBA(s) come under pressure
- production and test environments may still not match
- real risk of production outages
- complete database management control is not a reality
Create Brand New Database and Migrate all Data
This approach involves the development of scripts to create the database schema and code such as stored procedures. These
scripts encapsulate the changes required for the next version of the database. A brand new database is created, upon which
the scripts are executed, giving an empty schema. A migration process is executed against the new database involving routines to extract data from the original database and load it into the new upgraded database. This approach has the advantage of being less of a change process, with all its inherent complexities, to more of a creation and initial load process.
Summary of drawbacks
- a much longer time to upgrade due to the migration of potentially huge amounts of data (with an increased risk of data loss and corruption)
- additional requirements for disk space
- the creation, testing and maintenance of extraction and loading routines
- inefficient manual process to generate scripts to build the new database with the upgraded schema and
code
- complete database management control is not a reality
Team Development using Owner Qualified Objects
This method provides each developer with their own objects or schema within a common development database. Developers are given exclusive security access using a login, which also prefixes each object to identify ownership. They are then free
to code and unit test in complete isolation. Only the DBA has security access to the baseline database objects e.g. in Microsoft SQL Server, this would be the dbo (database owner) prefixed objects.
When development of an object is complete, the developer checks it into the VCS and informs the DBA it is ready for promotion. The DBA replaces the baseline object in the database with the new version and updates an overall upgrade script.
Summary of drawbacks
- tendency to only work for objects such as stored procedures and functions i.e. Data Manipulation Language (DML). Meaning Data Definition Language (DDL) must be maintained by the DBA(s)
- results in many manual steps which are time consuming and error prone such as promotion of objects and modifying the final upgrade script
- the DBA(s) must ensure integrity of the process, but in so doing, becomes a bottleneck
- far too much reliance on diligent communication between individuals
- complete database management control is not a reality
Ascertain Changes with SQL Queries
In order for the developer or DBA(s) to apply upgrades, they must ascertain what changes need to be propagated and in what order. One way to achieve this, is to develop SQL queries and stored procedures to compare schemas and extract object properties e.g. in SQL Server, interrogation of information schema views with a FULL JOIN and WHERE clause finding NULL values.
Summary of drawbacks
- this could entail a large amount of visual comparison of schemas, DDL files and system catalogues with the aid of a file comparison tool
- although such queries and stored procedures can tell you what is different, they rarely have the ability to actually deploy changes to upgrade or synchronise
- an ad hoc and manual approach that is tedious and error prone
- requires an excellent understanding of the database schema and system catalogues
- need in-depth knowledge of object dependencies and business rules
- complete database management control is not a reality
Next Article
In part 2 of this series, I will discuss an automated approach to database change management and outline the essential elements of an automated methodology.
About the Author
Darren Fuller began his IT career in 1990. Much of his experience has been gained by providing database consultancy on mission-critical systems and development projects within large organisations, often within heterogeneous environments using multiple technologies. Darren has specialised in database technologies with a particular interest in troubleshooting, optimisation and tuning, especially with Microsoft SQL Server (since 1994). He has a passion for ensuring a database system is finely tuned and the processes within a development project help to achieve this aim in an efficient and cost-effective manner.
Darren holds a Bachelor of Business in Computing and a Microsoft MCSE certification. He welcomes any feedback and can be contacted at darren.fuller@innovartis.co.uk
© Copyright Innovartis Ltd 2004. All rights reserved.
