What Is The Point of Versioning?
How do you know change occurs in your database?
Now, I’m not talking about version control, I am talking about stamping your database with a version number so you can keep track of how many modifications may be made in an environment not completely in your control.
I have used this concept in the past in a development environment where I had a full data team working on revisions in our version of the database. We did weekly releases to the development team into their development database.
My team was able to use the version number we put in the database to see if anyone on the developer team was making changes to the database structure without following the required processes and weekly code reviews. The version number also gave us a check before we were to release a new ‘build’ that would change the database’s structure. We could do one simple check and know if the database had been modified since our last ‘release’.
The script below build a DDL trigger that fires when DDL changes are made and increments the version number. The trigger was built this way as I didn’t care who made the change, and I didn’t want to point fingers. I just wanted to know if there was a change made.
Has your database changed since you last looked?
What’s in a Version Number?
In general a version number consists of 4 parts in the format XXX.YYY.ZZZ.AAAA
The XXX is the major release number. The YYY is a minor release, think service pack. ZZZ is the build number, like a cumulative update. The final AAAA represents the revision number, like a hot fix.
In the script below I just used a 3 part number as I was not so concerned with the granularity of the changes as I was using it as a type of monitoring tool. You could adapt the script to have the 4th number as well if you like.
When you read through the code you will notice I labelled my sections as main, minor and revision. When my data team did our weekly code review and release, we would manually increment the minor release number, the YYYY number, so we knew when we last made a change. Before we would apply the actual changes, we would double check that the version number was the same. If it was not, and we had no documented changes made to the environment, we knew we had to compare copies of the database and possibly approach the dev team to determine what changed. If needed we could then add the change to our weekly review, or overwrite it with our release.
What to watch for changes?
Based on various SQL Server database events found here: http://technet.microsoft.com/en-us/library/ms186456%28v=sql.90%29.aspx I chose the following to use to watch for changes made to the developer’s environment.
[one_third]DDL_TRIGGER_EVENTS
[list_check] [li]ALTER_TRIGGER[/li] [li]CREATE_TRIGGER[/li] [li]DROP_TRIGGER[/li][/list_check][/one_third] [one_third]DDL_VIEW_EVENTS
[list_check][li]ALTER_VIEW[/li] [li]DROP_VIEW[/li] [li]CREATE_VIEW[/li][/list_check][/one_third] [one_third_last]DDL_TABLE_EVENTS
[list_check][li]ALTER_TABLE[/li] [li]CREATE_TABLE[/li] [li]DROP_TABLE[/li][/list_check] [/one_third_last] [one_third]DDL_SCHEMA_EVENTS
[list_check][li]DROP_SCHEMA[/li] [li]CREATE_SCHEMA[/li] [li]ALTER_SCHEMA[/li][/list_check] [/one_third] [one_third]DLL_INDEX_EVENTS
[list_check][li]ALTER_INDEX[/li] [li]DROP_INDEX[/li] [li]CREATE_XML_INDEX[/li] [li]CREATE_INDEX[/li][/list_check] [/one_third] [one_third_last]DDL_FUNCTION_EVENTS
[list_check][li]ALTER_FUNCTION[/li] [li]CREATE_FUNCTION[/li] [li]DROP_FUNCTION[/li][/list_check] [/one_third_last]DDL_PROCEDURE_EVENTS
[list_check][li]ALTER_PROCEDURE[/li] [li]CREATE_PROCEDURE[/li] [li]DROP_PROCEDURE[/li][/list_check]
Obviously you could choose more, or less, depending on your needs. For the situation I was in, this was enough to let me know what was going on.
The trigger itself doesn’t do much other than update an extended property that was added to the database.
USE <database>
GO
CREATE TRIGGER DBTrg_Ver_change ON DATABASE
FOR DDL_TRIGGER_EVENTS, DDL_VIEW_EVENTS, DDL_TABLE_EVENTS,
DDL_SCHEMA_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS,
DDL_INDEX_EVENTS
AS
-- Here's the code to add the extended property rather than creating a new object in the database just to hold the value
-- For this trigger to work, you need to have the DBVersion extended property created and set to whatever value you want to start with.
-- EXEC sp_addextendedproperty
-- @name = N'DBVersion', @value = '000.000.0001';
-- GO
DECLARE @verno VARCHAR(25) ,
@main INT ,
@minor INT ,
@rev INT
-- Read & extract the current version number parts from the database
SET @verno = ( SELECT CONVERT(VARCHAR(12), value)
FROM sys.extended_properties
WHERE class = 0
AND NAME = 'DBVersion'
)
SET @main = SUBSTRING(@verno, 1, CHARINDEX('.', @verno, 0) - 1)
SET @minor = SUBSTRING(@verno, CHARINDEX('.', @verno, 0) + 1,
CHARINDEX('.', @verno, CHARINDEX('.', @verno, 0))
- 1)
SET @rev = SUBSTRING(@verno,
CHARINDEX('.', @verno, CHARINDEX('.', @verno, 0) + 1)
+ 1, LEN(@verno))
--- this code tests the value of the individual variables to confirm they were extracted properly
--select convert(varchar(5),@main) + '.' + convert(varchar(5),@minor) + '.'+ convert(varchar(5),@rev) as previous
-- Now the fun starts. Incrementing the version number.
-- since I was using 3 character numbers checks had to be made to increment accordingly.
-- you can note the checks for a 999 value and the increment of the higher level number if the lower rolled over.
-- this way we could start at 000.000.000 and the system will continue to operate automatically up to version number 999.999.999
BEGIN
SET @rev = CASE WHEN @rev < 9999 THEN @rev + 1
ELSE 0
END
SET @minor = CASE WHEN @rev = 0
AND @minor < 999 THEN @minor + 1
WHEN @rev = 0
AND @minor = 999 THEN 0
ELSE @minor
END
SET @main = CASE WHEN @minor = 0
AND @rev = 0 THEN @main + 1
ELSE @main
END
END
-- Now we combine the various parts together into a single variable to update the extended property
SET @verno = RIGHT('000' + CONVERT(VARCHAR(5), @main), 3) + '.'
+ RIGHT('000' + CONVERT(VARCHAR(5), @minor), 3) + '.' + RIGHT('0000'
+ CONVERT(VARCHAR(5), @rev),
4)
-- perform the actual update of the extended property
EXEC sp_updateextendedproperty @name = N'DBVersion', @value = @verno;
GO
----- confirm new number version number set by querying the sys.extended_properties table
--SELECT value
--FROM sys.extended_properties
--WHERE class = 0
-- AND NAME = 'DBVersion'
--GO
Yes, this is a trigger, and there is controversy about them, but this made things easier in my situation. The key to using triggers is knowing what ones you have and what they actually do. You can learn more about how to do that in this blog post.
Either way, trigger or not, using some method of versioning in your database can help you quickly identify when unauthorized changes occur in your database.
As a DBA trying to keep my systems running as smoothly as I can, I find things like this that make my job a little easier, pretty darn cool.
[icon_box icon=”users” title=”Who is Chris?”] Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com.
Click here to contact Chris or to follow him on twitter.
Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp. New in 2015 WaterOx Consulting is introducing the SQLPunch series of half day training events designed to help others learn to efficiently and effectively use SQL Server.
He is the founding president of PASSDC and organizes the annual SQLSaturday for the DC area. Chris frequently speaks at and attends SQL Server events, sharing his passion for all things SQL Server.
In 2012 Chris was a finalist in the worldwide Exceptional DBA competition and in 2014 he received the Microsoft MVP award in recognition of his open sharing of his knowledge with the technical community. His blog is currently syndicated to SQLServerCentral.com and ToadWorld.com [/icon_box]