Monitoring Data Model Changes

  • Article

Have you ever wanted to view how a database model has evolved over time?  It's not that hard to setup an infrastructure for supporting this.  In this series of articles, I'll go over the process for this.  For a jump start on the whole process, I've provided a PowerPoint and a paper that I wrote on the entire process.  In this series, I'll provide more of a step-by-step, since the end-to-end solution encompasses more components than can easily be digested in a single sitting. 

Approach

  1. Create a schema to support capturing all entities and their relations along with a change log table with a revision id as the root key for all related tables.
  2. Create a stored procedure to insert from the system tables and views into the appropriate tables
  3. Add a DDL trigger to invoke the stored procedure to insert the snapshot
  4. Create a services layer to query the model
  5. Create a Click-once application to query the model and view in either replay or real-time mode.

Schema

Below is the schema I chose to support the required data. 

image

Here is a summary of the key tables and their purposes:

- ChangeLog – Contains details about the database change – originating user, date/time, object changed, command issued, and change log version ID. Each entry in the change log correlates to a complete snapshot of a database schema for a point in time.

- SchemaTable – Contains detail about the table changed including the affected schema for a particular database version.

- TableRelation – Identifies the relationships between different tables for a particular database version.

- RelationColumnPair – Identifies the table columns that are used to join two tables together in a relationship.

That's all I have time for today - got to catch the train.  For the next post, I plan to get more into the guts of the stored procedures that actually populate this data and I'll provide a snapshot of what the functionality looks like.  Hopefully, I'll have a demo version up and running on my server in the next couple of day so you can see the interface.  I'm busy setting up an ISA Server to allow me to take advantage of my 8 different IP addresses to facilitate having more than 1 web server tied to my leithisers.com domain.