Transcript Database Change Management

Database Change
Management
One solution to an often
complex problem
Kevin Hurwitz
Headspring Systems
[email protected]
The Problem




Most significant business applications rely on at least one relational
database for persisting data
As new features are developed, database schema changes are often
necessary – i.e. new tables, columns, views, and stored procedures
Database schema changes and corresponding code changes must
always be deployed together
While deploying software to a production environment, code files
and libraries may usually be deleted or overwritten – Database files,
however, must be intelligently manipulated so as not destroy vital
business data
Staging Database Environment


To ensure an application remains stable throughout the development
lifecycle, a data-driven application must be deployed to at least two
environments: Production and Staging
Database and corresponding code changes are applied and tested in the
staging environment before being deployed to production
Developers
Product Manager
Staging
Testers
Salespeople
Production
Users
Production
Many Database Environments



While two database environments represent the bare minimum, teams
become more productive when certain roles and individuals have their own
copy of the database
For example, a developer may make a change to the staging database
which breaks the application and derails testers and salespeople
Testers and salespeople may also want to work with their own set of data
Developer #1
Developer #2
Tester #1
Tester #2
Salespeople
Production
Users
Demo
Production
Database Synchronization



Many development shops shy away from creating numerous database
copies due to the challenge of keeping them all “in synch”
An automated process is needed to make the process of upgrading outof-date databases simple
Team members who maintain their own database will run the process on
demand, while shared databases will be upgraded by an automated build
Developer #1
Tester #1
Salespeople
Production
Users
Demo
Production
Individual database upgraded as needed
Developer #2
Tester #2
Shared database upgraded by build/deployment process
Incremental Schema Changes




All database schemas can be thought of as a compilation of incremental
changes made over time to accommodate new functionality
To automate the process developers will record all database changes as
SQL scripts which they will commit to the source control repository
A program can then be run to execute all of the change scripts against
databases which have not yet received the necessary updates
As updates are applied to a database, the changes will be recorded in a
table similar to the following:
Change
Date
1_Create_Customer_Table.sql
4-15-07
2_Add_e-mail_address_column.sql
4-17-07
3_Add_fax_number_column.sql
4-18-07
4_Add_transaction_table.sql
4-21-07
5_Add_transaction_status_column.sql
4-24-07
6_Add_customer-transaction_view.sql
4-27-07
Database Schema Change Lifecycle
Developer creates an
incremental SQL
change script and tests
the change locally
Once the build has succeeded, the
tester can get the latest version of
the software from source control
and run the upgrade process via a
GUI to upgrade her local database
Once the change is
validated by unit tests, the
script is checked into source
control in a special location
Time
The automated build process kicks off,
runs the upgrade process which executes
the change script against the integration
test database, and runs all unit tests to
validate the change
Once the build has been
validated by QA, automated
build processes can be run to
deploy the software to staging
and production
Creating SQL Server Change Scripts
Developer snapshots
the current state of the
database using Red
Gate SQL Compare
The developer saves the Red Gate change
script within the database script project
under the “Update” folder
Developer makes all
necessary changes to the
database using SQL Server
Management Studio
Time
Developers executes Red Gate SQL
Compare to create a transactional change
script for the change just made in SQL
Server Management Studio
The developer rebuilds the
database from scratch, reexecutes the database
integration unit test, and
commits the new script