v:shapes="_x0000_s1060">Database Change
Management Best Practices
Achieving
an Automated Approach and Version Control
Inside
The need
for database change management
The
disadvantages of current practices
Understanding
an automated approach
Characteristics
and benefits of an automated approach
Substantial
cost savings achievable for development projects
Innovartis helps
organisations transform and radically improve their database development
processes with its DB GhostTM change management product family. With
database and development specialists, Innovartis offers a range of consulting
services and solutions including assessment of the database system, design and
implementation of change management processes, database administration and optimisation
and tuning.
Information about
Innovartis and DB GhostTM can be reached at:
Web http://www.innovartis.co.uk
Email enquiries@innovartis.co.uk
Contents
About Innovartis. 2
Executive Summary. 3
The Need For Database Change Management 3
Problems With Current Approaches. 3
Manual
Scripting. 3
Create
Brand New Database and Migrate all Data. 3
Team
Development using Owner Qualified Objects 3
Ascertain
Changes with SQL Queries 3
An Automated Database Change Management Methodology. 3
What
is an Automated Methodology?. 3
Elements
of an Automated Approach. 3
Implementing An Automated Approach. 3
Benefits Of An Automated Approach. 3
Improved
Quality and Faster Software Delivery. 3
Auditable
and Version Control 3
Sarbanes
Oxley Compliance. 3
Controlled
Rollback. 3
Benefits
for ISVs 3
Parallel
Code Development 3
Improved
Productivity. 3
Accurate
Project Estimation & Planning. 3
Implementation Considerations. 3
Return
on Investment Analysis 3
Conclusion. 3
“Your paradigm is so
intrinsic to your mental process
that you are hardly
aware of its existence, until you
try to communicate with someone with
a different
paradigm.”
Donella
Meadows, The Global Citizen
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”. Can you afford to have one of
your most expensive departments working with inefficient processes?
It is a fact that
database systems are becoming more complex with ever-increasing demands. Futurists believe that the world will change
more over the next 20 years than it has over the past 100 years. Requirements for new functionality and services,
timely delivery of information, 24x7 availability, 100% integrity, audit and
corporate governance (e.g. Sarbanes Oxley), place enormous demands on database
development projects and the teams who administer and support the environments.
Businesses must
continually innovate and accept challenges.
Can IT departments afford to complacently use processes simply because
it’s “always been done this way” or is the prevalent paradigm?
This paper will
outline the drawbacks of current approaches to database change management,
examine an automated approach and describe the benefits that can be
realised. Some of the benefits include:
Remove the
need for time consuming manual scripting
Elimination
of errors
Reduce the
length of the development life cycle
Minimise
the cost of development projects
Empower
employees and increase their productivity
Integration
to a source code version control system, which provides the ability to version
your database and achieve auditable, reversible (controlled rollback for
contingency) and repeatable database changes
Increase
in accuracy of project estimation and planning
The efficiencies
gained by adopting an automated methodology cannot be ignored, as the improved
performance of the IT department quite often equates to improved performance of
the organisation. This approach is an
accelerator to the application development lifecycle – the time to market of
your application is reduced, and quality of the end product increased. Ultimately, the implementation of an
automated database change management methodology will have a positive impact
upon a businesses bottom line.
“Change should be a
friend.
It should
happen by plan, not by accident.”
Philip Crosby,
Reflections on Quality
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:
System
integration and inter-dependencies
The
interaction of various technologies and protocols e.g. COM+, MDAC, Web
Services, XML, HTML, ASP, SOAP
Heterogeneous
environments e.g. multiple database management systems
Multiple
applications e.g. billing, e-commerce, CRM
Various
environments through which a new release must pass e.g. test, functional test, user
acceptance, QA and production
Question: How are
changes to database structures and static data managed in such complex and
often mission-critical environments?
Question: How can
you know with certainty what version a database is in a particular environment?
Question: How can
you deploy new versions with accuracy and reliability?
Question: Can you
reliably determine who made changes to your database objects?
Question: Do you
have an audit trail recording what changes have occurred and at what point in
time?
Question: 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.
But how efficient and
cost-effective are your current database change management processes? Are you able to account for every change made
to your database systems in a controlled manner? Do you think there’s room for improvement
within one of your most costly and business critical departments?
This section
describes 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. Consequently,
this section enables you to understand why an automated approach to database
change management adds value to your IT effort by eradicating the issues
outlined over the following pages.
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.
How much does it cost
you to have a database failure in the production system? 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.
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
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.
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
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.