Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Mass Versioning Of Database Components In SQL Server 2005 (or, Jeff Ball Is My New Hero)

Mass Versioning Of Database Components In SQL Server 2005 (or, Jeff Ball Is My New Hero)

  • Comments 4
  • Likes

I'm so excited about this, I've got to blog it before I walk the dogs..

A requirement emerged recently for our team's database platform to support multiple versions of code within a single database.  The database will know which users are running which versions of our front-end components, and will also know which versions of those components are tied to particular database versions.

The challenge is one of maintainability.  How can we make over 2,000 source files version aware, and, more importantly, how can we maintain multiple versions going forward.  (Who's Jeff Ball, you ask?  Jeff is a colleague of mine at Microsoft, and his passionate argument for the technical feasibility of mass versioning of database components, when no one else in the conversation was comfortable with the idea, got me to thinking, which led me to..)

Here's a script which demonstrates the technical feasibility of Jeff's idea.  It must be run in SQL Server Management Studio with SQLCMD mode enabled (when SQLCMD mode is enabled, the :setvar directive will appear highlighted as depicted below).

:setvar VersionMnemonic V1.2
SELECT 'this is a test of naming$(VersionMnemonic)' AS [SelectedText$(VersionMnemonic)]
GO
IF
NOT EXISTS (
    SELECT  routine_name
    FROM    information_schema.routines
    WHERE   routine_name = N'test$(VersionMnemonic)'
    AND     routine_schema = N'dbo'
    AND     routine_type = N'PROCEDURE')
EXEC (N'CREATE PROCEDURE [dbo].[test$(VersionMnemonic)] AS SELECT GETUTCDATE() AS [Baseline$(VersionMnemonic)]')
GO
EXEC [dbo].
[test$(VersionMnemonic)]
GO
ALTER PROCEDURE [dbo].[test$(VersionMnemonic)]
AS
SELECT
'this is a test of naming$(VersionMnemonic)' AS [FromProcedure$(VersionMnemonic)]
-- silly test to prove we can do it..
IF
'$(VersionMnemonic)' <> '$(VersionMnemonic)'
    SELECT 'something is seriously wrong' AS [PlanetaryMeltdownFromProcedure$(VersionMnemonic)]
ELSE
    SELECT 'all is well' AS
[CoreTemperatureNormalFromProcedure$(VersionMnemonic)]
GO
EXEC [dbo].
[test$(VersionMnemonic)]
GO
SELECT
  routine_name
FROM
    information_schema.routines AS VersionedRoutines
WHERE   routine_name LIKE 'test%'

The first line :setvar VersionMnemonic V1.2 is where the unique suffix for the version is set.  The remainder of the script demonstrates how this can be used.  To simulate a different version, change V1.2 in the line above to another value and run the script again.  In development, we could apply a bulk change to the appropriate code tree to update the line above with the appropriate new version.

We can simply insert the line above into every versionable component of the database and update every appropriate object reference to include the $(VersionMnemonic) reference, and we’re there.  We have one round of heavy touches to get that done, but thereafter our version update consists of a mass copy and a bulk search-and-replace operation.

Pretty darned maintainable, eh?  Now you know why Jeff is my hero, at least until Monday..

     -wp

Comments
  • Late-breaking news related to my earlier announcement that I'm presenting at next month's TechEd Developers

  • Another busy day here in Barcelona. I started this morning by heading to La Rambla de Catalunya in the

  • During today’s session, many of you were sufficiently interested in the “ versioning of database components

  • Fulfilling my promise at TechEd , I’ve finally completed a self-directed demonstration of the database

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment