Fulfilling my promise at TechEd, I’ve finally completed a self-directed demonstration of the database versioning techniques I first presented at last year’s TechEd and which was alluded to during this year’s TechEd SQL Tricks presentations.  It’s published here on my resource page at MSDN Code GalleryNo platform snafus this time, either..

The 12.5mB file contains the following:

  • VersioningDemo.bak: a SQL Server 2005 database backup used in the demonstration.
  • VersioningDemo.pptx: excerpts from last year’s TechEd presentation on the topic; use this file as an overview to the discussion.
  • LightweightVersioningDemo.sql: a simple demonstration of scripting variables and SQLCMD.
  • HeavyweightVersioningDemo.sql: the core of the demonstration.
  • spRefreshChannelCache.sql: a stored procedure of which you’ll create multiple versions while running the HeavyweightVersioningDemo.sql file.
  • spCloneVersion.sql: source code for a stored procedure invoked during the HeavyweightVersioningDemo; included for informational purposes only.

To get the full impact of the demonstration, you’ll need to restore the database backup onto a SQL Server 2005 or SQL Server 2008 instance.  If you name the resulting database VersioningDemo, the scripts will function without modification.

Examine the contents of the PowerPoint file for an overview of the design considerations which informed this effort.  LightweightVersioningDemo.sql offers a trivial demonstration of scripting variables, while HeavyweightVersioningDemo.sql offers a glimpse into the full power of this technique.

HeavyweightVersioningDemo.sql offers a discussion of the schema which enables this approach, as well as an open-ended demonstration which will allow to to instantiate as many version of data and code as you like.

There are detailed instructions embedded in both the Lightweight and Heavyweight scripts.

I hope this demonstration is useful to you, and I appreciate your patience in awaiting its release!

As always, please let me know if you have any questions or comments.

     -wp