Versioning In Your SQL Database

What Is The Point of Versioning?

How do you know change occurs in your database?

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?

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.

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]

Comments 1

  1. Pingback: Versioning In Your SQL Database - SQL Server - SQL Server - Toad World

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.