Managing Database Change – Community Responds
A few days ago I shared some of my experience with Database Change Control. Many of you chose to respond with excellent comments. Following is a response from Thom providing a nice bulleted list of the problems he faces. There aren’t really any solutions here, but he sure helps break down the nature of the problem.
Thom writes: "Hello, I saw that you are planning a talk on database change and I thought it was funny how I was recently put on a task to improve/document our process for managing database change. We had high hopes for the potential of Visual Studio Database Edition (VSDE) tools. Even though our current process uses a heavy duty modeling tool, I was willing and eager to look for ways to use VSDE as a way to integrate in the same way our .NET developers manage their code. However, after using VSDE alongside our data model tool, I concluded it was not there yet. I have been a data architect for some 15 years and used tools ranging from ER Win back when it was a LogicWorks product to now using Embarcadero’s ER Studio.
The problems I face are:
Making sure I know and communicate each element that is being touched
– Tracking what impact a change may have
– Understanding and communicating data lineage
– Coordinating changes to schema with changes being made to:
– Database code (view, stored procs, etc)
– ETL and EAI jobs
– Integrating with external databases systems that are linked to our databases
– Ensuring database consumers understand what is coming and how to use it
– Helping QA understand what they need to test
These problems have focused our processes and defined a variety of procedures and controls to ensure we don’t have outages due to roll outs. I assume these are some of the topics you are interested in.
My current environment is predominantly Microsoft SQL servers ranging from 2000 through to 2008. I support an iSeries Db2 system as well but not as deeply as the SQL platform. In our environment, no changes are made to the SQL Server schema without being modeled first. We use SQL for both transactional data and a data warehouse. "
Featured Script
dba3_DeleteTopByIncrements_demo
Requires TOP (variable) support of 2k5 & later – demo script… (read more)